Subtracting subtotal in a query

[Guest]

I would like to subtract the results of one weeks performance from another
using a query,

Table1:

Week Name A-Rank L-rank WL-Rank
1 A 3.2 1.2 2.2
2 A 1.1 3.4 2.5

result should be

Week Name change A-Rank Change L-Rank Change
WL-Rank
2-1 A -2.1 2.4
.3

 

[Guest]

Does your table have any kind of sequential field like an autonumber or
date/time stamp? In order to do this, it would be helpful to have such a
field so we can find the previous record.

Barry

 

[Guest]

It does not currently but I could add. However, data is entered quit
randomly for a given week. "Week" values are in order 1,2,3,4,etc. but names
are not "B,D,X,A, etc". The "week" field is actually a date (each sequential
Monday , 9/11/06, 9/18/06, 9/25/06, etc).

 

[Guest]

The date will work to guarantee sequence. Try this:

SELECT Table3.Week, Table3.Name, Table3.[A-Rank], [A-Rank]-(Select [A-Rank]
FROM Table3 AS t3 WHERE t3.Week =(SELECT Max(t4.Week) from Table3 AS t4 WHERE
t4.Week < table3.[week])) AS [ChangeRank-A], Table3.[L-Rank],
[L-Rank]-(Select [L-Rank] FROM Table3 AS t3 WHERE t3.Week =(SELECT
Max(t4.Week) from Table3 AS t4 WHERE t4.Week < table3.[week])) AS
[ChangeL-Rank], Table3.[WL-Rank], [WL-Rank]-(Select [WL-Rank] FROM Table3 AS
t3 WHERE t3.Week =(SELECT Max(t4.Week) from Table3 AS t4 WHERE t4.Week <
table3.[week])) AS [ChangeWL-Rank]
FROM Table3;

If you have a bunch of records, this might be a little slow, but it could be
sped up with some indexes.

Barry