Subtracting Q over Q

[Guest]

The following is what I'm looking for. I have the first 3 columns and I
can't figure out how to get the 4th. I need (WCTA - WCTA 4 DateCounts ago)

Date DateCount WCTA WCTA Difference
9/30/07 0 0.25 (0.25-0.50)=-0.25
6/30/07 1 1.25 (1.25-1.75)=-0.50
3/31/07 2 0.75 (0.75-0.50)=0.25
12/31/06 3 0.25 n/a
9/30/07 4 0.50 n/a
6/30/07 5 1.75 n/a
3/31/07 6 0.50 n/a

Thanks for the help!
-jeff

 

[Jeff Boyce]

I don't understand how you are getting from the sample/example data you
provided to the "WCTA Difference".

What is the underlying data structure (is that what you provided)?

Don't use "Date" as a fieldname - this is a reserved word in Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

IF the DateCount column is accurate, and is unique, and is a number (not
just a text string that has number characters), you should be able to use a
correlated subquery in the SELECT clause to get the values.


SELECT [Date], DateCount, WCTA
, WCTA - (SELECT Max(WCTA) FROM YourTable as YT1 WHERE YT1.DateCount =
YT2.DateCount + 4) as WCTADiff
FROM YourTable as YT2

If you are doing this in the query grid
Field: WCTADiff: WCTA - (SELECT Max(WCTA) FROM YourTable as YT1 WHERE
YT1.DateCount = YT2.DateCount + 4)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

The date field is actually DateRef and I only included it thinking the
problem would make more sense conceptually as Quarter over Quarter (the last
3 dates should be 2006, sorry). But you can ignore Date. The data provided
is actual data.

[WCTA Difference] is what I am trying to get. [WCTA Difference] asks "what
is the difference between the current WCTA and the WCTA of 4 DateCounts ago?"
I'll try to explain using the first record as an example:

The first record has a WCTA of 0.25 at DateCount 0
The fifth record (4 DateCounts ago) has a WCTA of 0.50
The difference between these two WCTAs is -0.25. This is the [WCTA Difference]

In the real world, this example would be understood as '3rd Quarter 2007'
minus '3rd Quarter 2006'. I use DateCount because actual dates are not
always comparable in other applications.

Hope this explains things better. Thanks again...

-jeff