Comparing a Field to a Field in a Prior Record

  • Thread starter Thread starter Bob Martin
  • Start date Start date
B

Bob Martin

I have a project where I need to calculate the difference
between the current record (which is an odometer reading)
and the prior record for 200+ vehicles. I can easily
calculate this information in Excel, however, I want to
figure out how to do it in Access. Microsoft Knowledge
Base Article - 120273 describes a procedure that returns
the previous record, but, I have found that this
expression produces the wrong prior record if the vehicle
was not used during the period. Can anyone help me
resolve this problem? I am using Access97.
 
I have a project where I need to calculate the difference
between the current record (which is an odometer reading)
and the prior record for 200+ vehicles. I can easily
calculate this information in Excel, however, I want to
figure out how to do it in Access. Microsoft Knowledge
Base Article - 120273 describes a procedure that returns
the previous record, but, I have found that this
expression produces the wrong prior record if the vehicle
was not used during the period. Can anyone help me
resolve this problem? I am using Access97.
Click to expand...

An Access Table, unlike an Excel spreadsheet, HAS NO ORDER. There are
no "row numbers".

You can use a Query or a DLookUp function to find another record in
the "bucket of data" which constitutes a table. For this example, I'd
suggest using

DMax("[Odometer]", "[tablename]", "[CarID] = " & [CarID] & " AND
[Odometer] < " & [Odometer])

to find the maximum odometer reading for this car less than the
current record's odometer reading.
 
John's response helped, but, it did not resolve my
problem of dealing with a vehicle that was not used
during the period. The formula produces the maximum
odometer reading less than the current odometer reading.
For example:

Date VehID Odometer PREV
1/1/04 007 12919 12800
2/1/04 007 12941 12919
3/1/04 007 12941 12919
3/15/04 007 13500 12941

The number we need for the PREV on 3/1/04 is 12941. This
tells us that the vehicle was not used for the period.

I am trying to calculate how much each vehicle was used
in the period.


So in cases where the odometer reading
-----Original Message-----
I have a project where I need to calculate the difference
between the current record (which is an odometer reading)
and the prior record for 200+ vehicles. I can easily
calculate this information in Excel, however, I want to
figure out how to do it in Access. Microsoft Knowledge
Base Article - 120273 describes a procedure that returns
the previous record, but, I have found that this
expression produces the wrong prior record if the vehicle
was not used during the period. Can anyone help me
resolve this problem? I am using Access97.
Click to expand...

An Access Table, unlike an Excel spreadsheet, HAS NO ORDER. There are
no "row numbers".

You can use a Query or a DLookUp function to find another record in
the "bucket of data" which constitutes a table. For this example, I'd
suggest using

DMax("[Odometer]", "[tablename]", "[CarID] = " & [CarID] & " AND
[Odometer] < " & [Odometer])

to find the maximum odometer reading for this car less than the
current record's odometer reading.


.
Click to expand...
 
John's response helped, but, it did not resolve my
problem of dealing with a vehicle that was not used
during the period. The formula produces the maximum
odometer reading less than the current odometer reading.
For example:

Date VehID Odometer PREV
1/1/04 007 12919 12800
2/1/04 007 12941 12919
3/1/04 007 12941 12919
3/15/04 007 13500 12941

The number we need for the PREV on 3/1/04 is 12941. This
tells us that the vehicle was not used for the period.

I am trying to calculate how much each vehicle was used
in the period.
Click to expand...

Maybe rather than using a criterion in the DMax() call referencing the
odometer reading, you can reference the maximum prior date:

DLookUp("[Odometer]", "[tablename]", "[VehID] = '" & [VehID] & "' AND
[Date] = #" & DMax("[Date]", "[tablename]", "[VehID] = '" & [VehID] &
"' AND [Date] < #" & [Date] & "#))
 
Back
Top