Link to home
Start Free TrialLog in
Avatar of marellano
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

Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

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
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
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...
I believe (as Scott said) it's old "You can't manipulate data that ain't there"  :-(

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
Avatar of marellano
marellano

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
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
"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
marellano,

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 :)
LOL ... well, it's not like I had a possible solution ... but still ...

mx