Query an entry in previous record for calculation in current record

[n4467r]

I have a database based on airplane usage. When the airplane returns from use a reading (hour meter) is taken and placed in the log. I have been trying to develop a query that will take the current reading and subtract the reading from the previous record. The result would be the flight time. The table is Aircraft log and Tach is the meter reading. The table also has an auto number ID.

I have not found a thread that has worked for this query but, being new to queries I may have missed the "answer" somewhere.

I have tried formulas based on my last thread and have not been successful. They seem to fail because they never actually reference the previous record (or my syntax is incorrect).

If I can get the following query to retrieve the data from the prior record (Using ID????) I am set.

Start Tach: [Aircraft log].[tach]

Perhaps I missed something in my previous post or lack an understanding of the Alias names but I could not get that to work either.


Thanks for all your help!

David
n4467r@comcast.net

 

[danvlas]

Try:
PrevValueMax(&quot;Tach&quot;, &quot;Aircraft log&quot;, &quot;[IDFieldName] < &quot; & [IDFieldName])

That will work provided your ID field is always incremented (each record has a greater value than the previous one). Autonumber is good (not perfect) for a single-user application. Another way is to have a timestamp of the record - default value = Now.

Good luck



Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[n4467r]

Worked like a charm as:

Start Tach: DMax(&quot;Tach&quot;,&quot;Aircraft log&quot;,&quot;[ID] < &quot; & [ID])

There is a date field in the table as well but I am not sure about the second option's syntax. I might explore a a bit when I get the chance.

Thanks so much for the help!

David
n4467r@comcast.net

 

[danvlas]

Nope...it didn't work...
Try:

DLookup(&quot;Tach&quot;, &quot;Aircraft log&quot;, &quot;ID = &quot; & DMax(&quot;ID&quot;, &quot;Aircraft log&quot;, &quot;[ID] < &quot; & ID)

I must have been drunk when I wrote the previous expression...that will return the maximum value in the &quot;tach&quot; field...

Sorry...it happens sometimes...


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[n4467r]

You are probably right about returning the max value but it does seem to work for my table. Perhaps because the previous tach reading would be the max value and the expression &quot;[ID] < &quot; & [ID] must take the max value before the current entry???

Not sure but I may try using the DLookup function and compare results.

Thanks for going above and beyond with this!!!

David Brackett
n4467r@comcast.net

 

[danvlas]

The difference between the expression:

DMax(&quot;Tach&quot;,&quot;Aircraft log&quot;,&quot;[ID] < &quot; & [ID])
will take the maximum value in the field Tach from the records that have the ID less than the 'current' ID. If Tach is incremented (and I think it is in your case), everything is OK, because the maximum ID will always correspond to the maximum Tach. In this case, the expression could be converted to:
DMax(&quot;Tach&quot;,&quot;Aircraft log&quot;,&quot;[Tach] < &quot; & [Tach])
and will display the same result.
But if Tach is random, then my first expression would result in a total fiasco, as the max Tach will not be located in the previous record...


DLookup(&quot;Tach&quot;, &quot;Aircraft log&quot;, &quot;ID = &quot; & DMax(&quot;ID&quot;, &quot;Aircraft log&quot;, &quot;[ID] < &quot; & ID)

This restricts the records to those having the ID less than the 'current' one, gets the maximum ID from the recordset and then looks it up in the table to return the value in te Tach field. It runs slower, as there are 2 domain functions within one expression.

So if I was lucky to get the right result through the wrong formula, use it as it's considerably faster...


Regards,



Daniel Vlas
Systems Consultant
danvlas@yahoo.com