finding previous months difference

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tedj13 - 19 Feb 2008 19:43 GMT

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?

Reply to this Message

Michel Walsh - 19 Feb 2008 19:57 GMT

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

>I have a query that combines 2 tables, a printers table and a readings
>table
[quoted text clipped - 15 lines]
> prior
> to that. Each printer has a unique SN. Any suggestions how to do this?

Reply to this Message

Tedj13 - 20 Feb 2008 14:24 GMT

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?

> SELECT a.sn, a.date, MIN(a.counts - b.counts)
> FROM myTable As a INNER JOIN myTable AS b
[quoted text clipped - 32 lines]
> > prior
> > to that. Each printer has a unique SN. Any suggestions how to do this?

Reply to this Message

Michel Walsh - 20 Feb 2008 17:09 GMT

You run a Crosstab query based on the previous query (rather than being
based on a table).

Hoping it may help,
Vanderghast, Access MVP

> Michael,
> this calulates the diff for each monthly reading, but then I need to show
[quoted text clipped - 44 lines]
>> > to that. Each printer has a unique SN. Any suggestions how to do
>> > this?

Reply to this Message