Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 524

# 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.

0
marellano
• 6
• 5
• 2
• +1
1 Solution

Infotrakker SoftwareCommented:
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 ...
0

Commented:
Scott ... I just sent you a email ... re another Q .... just an fyi.

mx
0

Commented:
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
0

Commented:
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...
0

Commented:
I believe (as Scott said) it's old "You can't manipulate data that ain't there"  :-(

mx
0

Commented:
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
0

Author Commented:
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?.

0

Commented:
marellano said:
>>I suppose I can also try to add an auto key to the data being imported...wouldn't that work?

Yes, it would!

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.AutoNumberFieldName DESC) AS Diff
FROM YourTable t1
ORDER BY t1.Account ASC, t1.[Date] DESC
0

Author Commented:
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.
0

Commented:
"I might just have to do this using Excel instead"

Well ... of course, that is always a VBA code solution for this ...

mx
0

Commented:
"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
0

Commented:
marellano,

BTW, it's bad practice to use words like Date and Count as field names :)

Regards,

Patrick
0

Commented:
>>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 :)
0

Commented:
LOL ... well, it's not like I had a possible solution ... but still ...

mx
0

## Featured Post

• 6
• 5
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.