Solved

# Subtract Count from Previous Record

Posted on 2007-07-24
435 Views

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
Question by:marellano

LVL 84

Expert Comment

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

LVL 75

Expert Comment

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

mx
0

LVL 92

Expert Comment

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

LVL 92

Expert Comment

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

LVL 75

Expert Comment

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

mx
0

LVL 92

Expert Comment

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 Comment

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

LVL 92

Accepted Solution

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 Comment

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

LVL 75

Expert Comment

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

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

mx
0

LVL 75

Expert Comment

"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

LVL 92

Expert Comment

marellano,

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

Regards,

Patrick
0

LVL 92

Expert Comment

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

LVL 75

Expert Comment

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

mx
0

## Featured Post

### Suggested Solutions

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …