finding previous months difference

  • Thread starter Thread starter Tedj13
  • Start date Start date
T

Tedj13

I have a query that combines 2 tables, a printers table and a readings table
the printer table contains Div group location and s/n and the reading table
contains date and reading
Div SN Group Location Date Counts
1 140044 Net Invoicing Area 29-Oct-07 4266483
1 140044 Net Invoicing Area 28-Nov-07 4433762
1 140044 Net Invoicing Area 28-Dec-07 4604691
1 140044 Net Invoicing Area 30-Jan-08 4792835
1 154243 General Office Area 29-Oct-07 379450
1 154243 General Office Area 28-Nov-07 387872
1 154243 General Office Area 28-Dec-07 396561
1 154243 General Office Area 30-Jan-08 407328
I need to calculate the # of pages that were printed in the previous month,
and compare that toal to the # of pages that were printed in the month prior
to that. Each printer has a unique SN. Any suggestions how to do this?
 
SELECT a.sn, a.date, MIN(a.counts - b.counts)
FROM myTable As a INNER JOIN myTable AS b
ON a.sn=b.sn AND a.date>b.date
GROUP BY a.sn, a.date


should do. Having two 'pointers' to your data, note that pointer_b reaches
only data strictly occurring prior what pointer_a points to. Given the
nature of the counts, always increasing as time passes,
MIN(a.counts-b.counts) compute the difference you want. If the counts were
not continuously increasing, we would have been obliged to specifically
point to the exact previous data. In other words, that solution DOES NOT
WORK if you want to compute, say, month-by-month changes for a market value
that can go up, or down, as time passes.




Hoping it may help
Vanderghast, Access MVP
 
Michael,
this calulates the diff for each monthly reading, but then I need to show
side by side in a report the 2 valuse
Sn Dec 07 Jan 08
140044 170929 188144

How do I get the report to reach back to the previous month?
 
You run a Crosstab query based on the previous query (rather than being
based on a table).



Hoping it may help,
Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Back
Top