Subtract Previous Value from Next Value

[SpursGirl]

Need help building a query that will subtract the previous value from the next value at each change in date

Date Tier 1
1/6/2006 4.22
1/13/2006 4.27
1/20/2006 4.27
1/27/2006 4.314
2/3/2006 4.314

for example, 4.22-4.27 = -0.05

 

[theprez]

Not sure if you can do this with a simple query. Queries usually refer to a particular row and does not refer to previous or next row. You may need to do this with code and build a Table with the results. The results will probably have atleast three fields, the 2 dates and the difference.

 

[Brianwarnock]

I'd use Excel

Brian

 

[MarkK]

You can define a query field that does something like this...
Difference: [Tier]-DLookup("Tier", "yourTable", "dDate = #" & DMax("dDate", "yourTable", "dDate < #" & [dDate] & "#") & "#")
The DMax() returns the previous date. The DLookup() returns the tier associated with that date.
If there are two same dates in yourTable you'll get erroneous data with no warning.

 

[The_Doc_Man]

There is a basic concept problem here. "ORDER" means nothing inside a table unless you are talking about prime key order. Tables are unordered sets. SQL and other methodologies using the set theory that is the basis of all modern databases just cannot cope with the concept of "ORDER."

To impose order such that you can talk about records being "next" or "previous" or "adjacent" you must use SQL to impose the order but step outside of SQL to actually "see" the adjacent records. This is because SQL is rooted deeply in a concept that says you can do the same thing to a bunch of records in any order as a starting point. So SQL doesn't really care about the order of the records fed to it. (It might care about the order of the records it spits out if there is an ORDER BY clause in the SQL...)

VBA can do what you want with a little judicious tweaking and an appropriate query to handle the initial imposition of order.

 

[MarkK]

Doc Man:
I'm not sure what you're saying here. I wrote a little mileage system to keep track of business auto usage. The following field, in a query, returns how long the trip was.

Km: [tUsage].[ODO]-DMax("ODO","tUsage","ODO < " & [tUsage].[ODO])

It's irrelevant if the table asserts some sort of internal order or not. The length of the trip is the current odometer minus whatever the next smallest odometer is (returned by DMax()). This is one degree simpler than the problem posted above, since the above problem does not use the "ordered" field as the value, so in addition to the DMax(), a DLookup() is indicated.
Is there a theoretical problem with this approach?

 

[stallyon]

You can define a query field that does something like this...
Difference: [Tier]-DLookup("Tier", "yourTable", "dDate = #" & DMax("dDate", "yourTable", "dDate < #" & [dDate] & "#") & "#")
The DMax() returns the previous date. The DLookup() returns the tier associated with that date.
If there are two same dates in yourTable you'll get erroneous data with no warning.

One problem i can see with this is query runtime. The DMax() Function would run for every record. This aint so bad if you have an index on the fields you use in your DMax statement but if you dont you could get into some serious lag.

Best option would be to write a piece of VBA code stepping through each record and appending the new results to the temp table.

 

[MarkK]

One problem i can see with this is query runtime.

This makes sense to me. In my mileage system the odometer field is indexed, and the table has 150 records so performance is fine.
Thanks,

 

[SpursGirl]

Thanks for responses

Thanks guys. i didn't have much time to work on this so i just went with Excel.

 

[Cosmos75]

You can also use SQL to do this, which I think is faster than using an aggregate function such as DMax().

Previous Date & Sum of Month/Year to-date using a correlated subquery

Example:

SELECT dtDate, lngValue,
       (SELECT TOP 1 a.dtDate
               FROM tblData As a
               WHERE a.dtDate < tblData.dtDate
               ORDER BY a.dtDate DESC)
       AS PrevDate_Top1,
       (SELECT Max(a.dtDate)
               FROM tblData As a
               WHERE a.dtDate < tblData.dtDate
               GROUP BY tblData.dtDate)
       AS PrevDate_Max,
       (SELECT TOP 1 a.lngValue
               FROM tblData As a
               WHERE a.dtDate < tblData.dtDate
               ORDER BY a.dtDate DESC)
       AS PrevValue_Top1
FROM tblData;