marellano
asked on
Subtract Count from Previous Record
Hello,
I'd like to get help on creating what would seem to be a simple subtraction problem.
I want to be able to subtract the results from a previous record to get the difference.
I have a query that brings in Account, Date, Count. Then I wan to add another column to the query to provide me with the difference in Count for each previous record count.
Here's my table:
Account Date Count Current - Previous
123456789 7/2/2007 10 2
123456789 6/28/2007 8 1
123456789 6/25/2007 7 1
123456789 6/22/2007 6 1
123456789 6/22/2007 5 1
123456789 6/12/2007 4 1
123456789 6/4/2007 3 1
123456789 6/1/2007 2 2
I'd greatly appreciated if anyone can provide me with the solutions.
I'm in a time constraint and like to get this out today.
Thank you in advance
Access cannot reference the "previous record", since it has no real concept of Rows ... even with an OrderBy clause (which would order your records), you still can't calculate based on the previous record. In the sample data you're showing, for example, you could possibly order this by the Date, but if you do this you end up with two records with the same Date (6/22/07) ... how, then would you "tell" the next record (on 6/25/07) to use a cound of 6 instead of 5 ...
Scott ... I just sent you a email ... re another Q .... just an fyi.
mx
mx
SELECT t1.Account, t1.[Date], t1.[Count], t1.[Count] -
(SELECT TOP 1 t2.[Count]
FROM YourTable t2
WHERE t2.Account = t1.Account AND t2.[Date] < t1.[Date]
ORDER BY t2.[Date] DESC) AS Diff
FROM YourTable t1
ORDER BY t1.Account ASC, t1.[Date] DESC
(SELECT TOP 1 t2.[Count]
FROM YourTable t2
WHERE t2.Account = t1.Account AND t2.[Date] < t1.[Date]
ORDER BY t2.[Date] DESC) AS Diff
FROM YourTable t1
ORDER BY t1.Account ASC, t1.[Date] DESC
marellano said:
>>123456789 6/22/2007 6 1
>>123456789 6/22/2007 5 1
This is going to be a problem. My SQL was assuming that account and date were only present
in inique combinations...
>>123456789 6/22/2007 6 1
>>123456789 6/22/2007 5 1
This is going to be a problem. My SQL was assuming that account and date were only present
in inique combinations...
I believe (as Scott said) it's old "You can't manipulate data that ain't there" :-(
mx
mx
marellano,
Assuming this is Access, and that Account and Date really did provide a unique index...
The first SQL statement I posted will show a null for the "earliest" row for each account.
This SQL statement produces a non-null:
SELECT t1.Account, t1.[Date], t1.[Count], t1.[Count] -
Nz((SELECT TOP 1 t2.[Count]
FROM YourTable t2
WHERE t2.Account = t1.Account AND t2.[Date] < t1.[Date]
ORDER BY t2.[Date] DESC), 0) AS Diff
FROM YourTable t1
ORDER BY t1.Account ASC, t1.[Date] DESC
If this is SQL Server and not Access, replace Nz with ISNULL.
Regards,
Patrick
Assuming this is Access, and that Account and Date really did provide a unique index...
The first SQL statement I posted will show a null for the "earliest" row for each account.
This SQL statement produces a non-null:
SELECT t1.Account, t1.[Date], t1.[Count], t1.[Count] -
Nz((SELECT TOP 1 t2.[Count]
FROM YourTable t2
WHERE t2.Account = t1.Account AND t2.[Date] < t1.[Date]
ORDER BY t2.[Date] DESC), 0) AS Diff
FROM YourTable t1
ORDER BY t1.Account ASC, t1.[Date] DESC
If this is SQL Server and not Access, replace Nz with ISNULL.
Regards,
Patrick
ASKER
Thanks for all the replies. I might just have to do this using Excel instead. I know Account and Date will not provide a unique index for Access to run the query successfully. I suppose I can also try to add an auto key to the data being imported...wouldn't that work?.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi matthewspatrick. I'll have to try your solution later today and get back to you with the results. Thank you very much for your time.
"I might just have to do this using Excel instead"
Well ... of course, that is always a VBA code solution for this ...
mx
Well ... of course, that is always a VBA code solution for this ...
mx
"Hi matthewspatrick. I'll have to try your solution later today and get back to you with the results. "
Ahhh ... Not to take anything away form MP, but why then have you Accepted the answer if you have not tried it?
mx
Ahhh ... Not to take anything away form MP, but why then have you Accepted the answer if you have not tried it?
mx
marellano,
BTW, it's bad practice to use words like Date and Count as field names :)
Regards,
Patrick
BTW, it's bad practice to use words like Date and Count as field names :)
Regards,
Patrick
>>Ahhh ... Not to take anything away form MP, but why then have you Accepted the answer
>>if you have not tried it?
Gotta love the trust :)
>>if you have not tried it?
Gotta love the trust :)
LOL ... well, it's not like I had a possible solution ... but still ...
mx
mx