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
  • Last Modified:

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

0
marellano
Asked:
marellano
  • 6
  • 5
  • 2
  • +1
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Scott ... I just sent you a email ... re another Q .... just an fyi.

mx
0
 
Patrick MatthewsCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Patrick MatthewsCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I believe (as Scott said) it's old "You can't manipulate data that ain't there"  :-(

mx
0
 
Patrick MatthewsCommented:
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
 
marellanoAuthor 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
 
Patrick MatthewsCommented:
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
 
marellanoAuthor 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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)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
 
Patrick MatthewsCommented:
marellano,

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

Regards,

Patrick
0
 
Patrick MatthewsCommented:
>>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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
LOL ... well, it's not like I had a possible solution ... but still ...

mx
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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