Query in Comparing Values 1

[sherly]

Hello, I'm using Microsoft Access & I'm trying to comparing values from the following tables.

Hi all, I have 2 tables. One is called [master] and another is [details].
[Master] will hold all the unique counter name where else the [details] will hold all the records of
the counters.

Master
-------
CounterName
ABC
DEF
GHI

Details
--------
CounterName Year Value Change
-----------------------------------------
ABC 1998 10
ABC 1999 12
ABC 2000 9
DEF 1999 15
DEF 2000 12
GHI 1999 20
GHI 2000 21

How to compare and calculate the changes in the value for each Counter every year?
I wish to get the following result.

CounterName Year Value Change
----------------------------------------
ABC 1998 10 NULL
ABC 1999 12 2
ABC 2000 9 -3
DEF 1999 15 NULL
DEF 2000 12 -3
GHI 1999 20 NULL
GHI 2000 21 1


I used this statement and it works well.
SELECT D.CounterName, D.Year, D.Value, D.Value - P.Value As Change
FROM Details D, Details P
WHERE D.Countername = P.CounterName
AND D.Year = P.Year + 1

The question is : What if I need to add one more column called Month into the database?

CounterName Month Year Value Change
----------------------------------------
ABC Jan 1998 10 NULL
ABC Dec 1998 12 2

Jan 1998 comes before Dec 1998, so I need to compare them and the change value for Dec 1998 is 2.

How should I modify my statement to take months into consideration?

 

[Steve101]

Interesting approach Shirly.

Try the following:

SELECT D.CounterName, D.Year, D.Month
D.Value, D.Value - P.Value As Change
FROM Details D, Details P
WHERE D.Countername = P.CounterName
AND CVDate("1/" & D.Month & "/" & D.Year) =
DateAdd("m",1,CVDate("1/" & P.Month & "/" & P.Year))

Notes:
(a) The only changes are the inclusion of D.Month in the SELECT clause and the replacement of the YEAR comparison in the WHERE clause to the comparison which effectively compares the year and the month.

(b) This approach does of course rely on all of the data being available for each month in the range. For example, having Jan, Feb, Apr, Jun, Aug leaves "holes" in the data, and as such would'nt work. You'd probably need code here to determine delta's between these sorts of gaps.

(c) The use of the date functions here is the key to the solution.

Hope this helps,
Cheers,
Steve