Calculating this month's values in report based on accumulated values

  • Thread starter Thread starter krellingsen
  • Start date Start date
K

krellingsen

Hi,
I have a table where, for each month, accumulated financial reporting
data is stored (the users want to input accumulated values, not this
month's values).

In a report for February, I can then show Actual accumulated YTD data
for each company.

I also want to show this month's data in the report, based on the
difference between Accumulated values at month end and Last month's
accumulated values. Is it possible to build a query that gets last
month's accumulated values, and e.g. use Dlookup to display the values
from the query and then do a calculation of the difference? Is there
another way that might be easier?
 
A DLookup() to get last month's accumulated value will work, but a subquery
will probably be quicker.

This kind of thing:
SELECT TheMonth, CompanyID, YTD,
YTD - (SELECT TOP 1 YTD
FROM tblSales AS Dupe
WHERE Dupe.CompanyID = tblSales.CompanyID
AND Dupe.TheMonth < tblSales.TheMonth
ORDER BY Dupe.TheMonth DESC) AS ThisMonth
FROM tblSales;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html
 
Back
Top