last entry compare of record

[iuphim]

I have a set of data I want to compare possible changes but am having a hard time writing the query criteria to do what I want.

I tried writing a query of all max date then create another query with all data but removed all the max dates in the 1st query. Then max the remaing entry. But it's not doing what I need it to do.

For example:
Acc # Date entered Color
123 1/1/2008 yellow
123 1/2/2008 blue
123 1/3/2008 blue
123 6/2/2007 green

For each record, I want to see every time it has a colored change based on a comparison of the date entered with the previous of that date entered.

I want to compare the new changes to the previous changes for the month. So for example, line 123 - 1/1/2008 - yellow, i want to select line 123 - 1/2/2008 - blue. For line 123 - 1/2/2008 - blue compare it with line 123 - 1/3/2008 - blue and for line 123 - 1/3/2008 - blue compare it with line 123 6/2/2007 green. How do I I write so that it autoselect the previous entry of the the date entered?

Thanks.

 

[The_Doc_Man]

With VBA and a query-based recordset, this is easy. Doing any other way gets complicated. The problem is that there is no direct construct called "Previous record" in SQL because SQL recordsets are imposing order on something that isn't really ordered.

The SQL-only syntax requires nesting of at least two Domain Aggregate functions and the quoting syntax for that nesting will become nightmarish.

What I'm thinking, and you need to look into the Access Help for the Domain Aggregate functions to do this from SQL, is to do a DLookup of the color for the record having the same account number and having the date equal to the DMax of all records having the same account number and a date less than that of the current record. See why I said this is nightmarish?

Whereas in VBA and having a simple query that orders by date and groups by account number, this is a trivial process. You would use VBA to remember the "previous" record rather than using a complex dynamic lookup. The VBA would reset itself every time the account number changed so that the first entry for the new account doesn't get compared to the last entry for the previous account. With VBA, this isn't so bad.

 

[pbaldy]

Here's one kludgy way of doing it in a query:

http://www.baldyweb.com/PrevValue.htm

 

[iuphim]

Can I get a count of the number of times it changes within a month?

Whereas in VBA and having a simple query that orders by date and groups by account number, this is a trivial process. You would use VBA to remember the "previous" record rather than using a complex dynamic lookup. The VBA would reset itself every time the account number changed so that the first entry for the new account doesn't get compared to the last entry for the previous account. With VBA, this isn't so bad.

 

[iuphim]

This is actually what I want to do. Thank you!

But can I but a month set criteria under DorDate for all of May? Would that affect the PrevEnd date being restricted in May only or can it select the previous date even in April?

Here's one kludgy way of doing it in a query:

http://www.baldyweb.com/PrevValue.htm

 

[pbaldy]

Putting a criteria on the main query would not affect the previous value; a value in April should still appear as the previous value. You could prevent that by adding a criteria in the subquery, but it doesn't sound like that's what you want.

 

[iuphim]

How would I rewrite this to make it run faster? Should I do a query first for CarNum, DorDate and SpeedoEnd then join to the Original VehicleMiles table?

Putting a criteria on the main query would not affect the previous value; a value in April should still appear as the previous value. You could prevent that by adding a criteria in the subquery, but it doesn't sound like that's what you want.

 

[hyp527]

I have a similiar audit trail situation, but my data are so large (999,000) that this query is too slow for me to run. I ran this for over 4 hours and only produced about 1 bar even with an append table query.

SELECT VehicleMiles.CarNum, VehicleMiles.DorDate, VehicleMiles.SpeedoEnd, (SELECT SpeedoEnd FROM VehicleMiles AS Alias WHERE DorDate = (SELECT Max(DorDate) FROM VehicleMiles AS Alias2 WHERE Alias2.DorDate < VehicleMiles.DorDate AND Alias2.CarNum = VehicleMiles.CarNum) AND Alias.CarNum = VehicleMiles.CarNum) AS PrevEnd, [SpeedoEnd]-[PrevEnd] AS MilesDriven
FROM VehicleMiles
ORDER BY VehicleMiles.CarNum, VehicleMiles.DorDate, VehicleMiles.SpeedoEnd;

Is there a way to rewrite this into VBA or a module to help speed the run?

Thank you ahead for your help.