Calculations Between Records

[Guest]

Using Access 2003....I have a db with a Truck Table and a Maintenance Records
table. There is a one to many relationship between the two of them based on
the Truck #.

I have a Truck form in single form view with a Maintenance Records subform
in continuous forms view that lists all the maintenance records for that
particular truck.

I have 2 calculated controls on my Maintenance form which need to look at a
previous record to do a calculation. The two controls are # of Miles and #
of Days. I need to put in a formula for each of them respectively that will
calculate the following:

1) The difference between the mileage from the previous maintenance record
(which will have to be by date-however I suppose there could be 2 records on
the same date) for that truck and the current (next) maintenance record's
mileage.

2) The # of days between the Service Date from the previous maintenance
record for that truck and the current (next) maintenance records' service
date.

I know how to do basic functions to calculate these if it were within the
same record, but I don't know how to make this happen to look at the previous
record (by date) and make the calculations BETWEEN the records.

I have a very small amount of experience with coding so I'm hoping this
isn't going to require writing some serious code to make it happen.

Thanks in advance,
Tammy

 

[Allen Browne]

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210504

 

[Guest]

Thanks for the quick response Allen. Looks like this won't work for me
because it requires an autonumber ID with no missing numbers. Because the
maintenance records are entered for various trucks, the autonumbers on the
maint records will be spread among the trucks, and therefore not necessarily
adjacent from a numbering scheme on one particular truck's associated child
records.

Thank you,
Tammy

 

[Allen Browne]

The article describes two methods, and at a cursory read I don't see where
you got the idea that it requires contiguous autonumbers.

The WHERE clause - e.g. 3rd argument of DLookup() - will need to indicate
the same truck, and you will need some way to determine the order of records
(AutoNumber, or date/time field, or odometer reading or something).

 

[Guest]

Perhaps I'm misunderstanding then this information in the 'Method' where it
says "NOTE: This technique assumes that you have a table with an ID field of
a Number data type, and that the ID values are not missing any number (or
numbers) in sequential order."

 

[Allen Browne]

Thanks, Tammy. The article does say that for Method 1.

There are several workarounds for that. One would be to use the Extended
DLookup() function in this link:
http://allenbrowne.com/ser-42.html
It has an extra argument that lets you specify a sort order, and using this
argument you can sort by the autonumber field descending so you retrieve the
value from the previous record even if the autonumbers are not contiguous.

Another is to use a subquery to get the desired value. That would be *much*
more efficient, but the results are read-only so may not be suitable. There
is a discussion about how to do that and 3 other approachs in this link:
http://www.mvps.org/access/queries/qry0020.htm

HTH

 

[Guest]

Thanks very much for your time Allen in responding to my questions.

As I mentioned, I'm not a programmer and have done just bits/pieces with
coding. I've read through your code and I know it says "the function returns
the first value found, or Null if there are no matches". I'm trying to
understand if that gives me the ability to pull the value from the previous
record, as it's sorted, or if it limits me to only the 'highest-max' or
'lowest-min'.

I guess in essence I'm not clear what 'returns the first value found' would
translate to......in terms of criteria that I would need to be specifying the
'previous record'.

Thanks again,
Tammy

 

[Allen Browne]

ELookup() lets you pick a value which is not necessarily the max or min,
based on another value in the query.

For example, to get the most recent OrderId for client 99, based on the
OrderDate field (not the OrderId field):
=ELookup("OrderId", "tblOrder", "ClientId = 99", "OrderDate DESC")

If you are actually trying to get the value from the previous row of a form
that could be sorted or filtered by a user at runtime, there will be more to
it, but this might be useful:
http://www.lebans.com/rownumber.htm

 

[Guest]

Thanks very much for your help, Allen. I really appreciate it!
Tammy