title: Access Subquery
body: <p>I've been struggling with a query problem for a few days and browsed for answers and think that I may just be unable to apply the logic found because I'm not great at SQL. I have two tables, [0100] and [Product] and need to calculate the variance between Actual and Forecast for each Category. I have tried a number of subqueries but cannot get them to work so am back to this:</p>
  
  <pre><code>SELECT
      Product.Description,
      Sum([0100].Actual) AS Actual,
      Sum([0100].Forecast) AS Forecast,
      Sum(IIf([0100]![Forecast]=0,
              IIf([0100]![Actual]=0,
                  0,
                  IIf([0100]![Actual]&gt;0,9.99,-9.99)
              ),
              ([0100]![Actual]-[0100]![Forecast])/[0100]![Forecast]
          )
      ) AS Variance
  FROM
      0100
      INNER JOIN Product ON [0100].Category = Product.ID
  GROUP BY
      Product.Description;
  </code></pre>
  
  <p>Once I have the Variance Column working, I will remove Actual and Forecast Columns but I am unable to get the Variance Column to work correctly and I suspect it is because I need a subquery. Having tried many times, I am at a loss as to how to proceed.I have it working in Excel as per the following snapshot with the formula <code>=IF(AND(B2=0,C2=0),0,IF(C2=0,IF(B2&gt;0,9.99,-9.99),(B2-C2)/C2))</code></p>
  
  <pre><code>**Column A                             B           C          Formula
  Account Group                     Actual      Forecast     Variance**
  Admin &amp; Management staff costs    -8256.61    -8256.61           0%
  Bank interest                       0                0           0%
  Care staff 4 weekly paid             268           268           0%
  Care staff travel   -70 -70 0%
  Contracts - health &amp; soc services -748317.97   -788317.97       -5%
  Donations &amp; fundraising             -9665.69     -9465.69        2%
  Fundraising &amp; promotion              4695.68      4695.68        0%
  Grants                                -28687       -39687      -28%
  </code></pre>
  
  <p>Could anyone provide help in getting the Variance query right so it returns the same result as the Excel formula? I have a sample database with just the product table and the <code>0100</code> table with the records returning the Excel results provided and will see if I can attach/provide a link.</p>
  
  <p>Many Thanks,</p>
  
  <p>Simon</p>