Calculating a value from last to records

[Guest]

I have a database which among other things stores the current mileage of a
vehicle.
A user fills out a form like a questionnaire, then prints out a report that
outlines the information entered in the form.
I would like to have a field on the report that shows if the vehicle has
exceeded 12000 km between the current form being filled in and the last time
the form was filled in.
I know how to create the calculations in vba, but I'm unaware of how to
access the previous mileage from the table.
I assume I will have to do it on the form and have a new field in the table
to contain the difference in mileage?
Thanks

 

[Guest]

Hi,

Let me give you some ideas. I don't know the exact mechanics of your user
interface but there will be some time in the sequence of events when the
vehicle id is known. You could have implemented this by getting the value
before displaying the 'questionnaire' or you could have included a control on
the questionnaire itself for vehicle id.

Anyway, include an unbound, disabled control on your questionnaire for
Previous Mileage. Let's assume you name this txtPrevMileage. Select the
event where you first know the vehicle id and code a routine in this event
something along the lines of

txtPrevMileage = DLookUp("Max(Mileage)","Vehicles","VehicleId = " & vehicleid)

You can now compare the entered mileage with the previous value.

The same principle may be used for the most recent date - in fact find out
the date first and then use this for looking up the most recent mileage.

Remember to cater for nulls. Also this method fails once you have saved a
record for this instance.

Regards,
Rod

 

[Guest]

Your always there to help me Rod
You wrote the questionnaire of course!
I'm just adding some extra bits
Thanks
Ian

 

[Guest]

One thing you should not do is add a new column to the table for the mileage
difference. That would constitute redundancy and leave the table open to
possible update anomalies as the value in the mileage difference column could
be changed independently of the recorded mileages or vice versa. You can
either compute the difference in the report at runtime along the lines
suggested by Rod, or you can do it in the report's underlying query, e.g.

PARAMETERS
Forms!YourForm!VehicleID LONG,
Forms!YourForm!dateLogged DATETIME;
SELECT VehicleID, DateLogged, Mileage,
(SELECT Mileage
FROM MileageLog AS ML2
WHERE ML2.VehicleID = ML1.VehicleID
AND ML2.DateLogged =
(SELECT MAX(DateLogged)
FROM MileageLog AS ML3
WHERE ML3.VehicleID = ML1.VehicleID
AND ML3.DateLogged < ML1.DateLogged))
AS PreviousMileage
FROM MileageLog AS ML1
WHERE Ml1.VehicleID = Forms!YourForm!VehicleID
AND Ml1.DateLogged = Forms!YourForm!DateLogged;

or using two instances of the table rather than 3:

PARAMETERS
Forms!YourForm!VehicleID LONG,
Forms!YourForm!dateLogged DATETIME;
SELECT VehicleID, DateLogged, Mileage,
(SELECT MAX(Mileage)
FROM MileageLog AS ML2
WHERE ML2.VehicleID = ML1.VehicleID
AND ML2.DateLogged < ML1.DateLogged)
AS PreviousMileage
FROM MileageLog AS ML1
WHERE ML1.VehicleID = Forms!YourForm!VehicleID
AND ML1.DateLogged = Forms!YourForm!DateLogged;

In the report's detail section you can include two unbound text box
controls to show the mileage difference, and/or if it exceeds 12,000 km with
ControlSource properties such as:

=[Mileage] – [PreviousMileage]

= IIf([Mileage] – [PreviousMileage] > 12000,"Limit exceeded", "")

Ken Sheridan
Stafford, England

 

[Guest]

Hi Ian,

I'd forgotten you had an alter ego with a self-demeaning pseudonym. Glad to
hear your prroject is alive, well and developing.

Ken's solution has merits and will introduce you to the concept of nested
SQL - something you can't readily define using the Access Query Grid; you
have to type or paste an SQL string in the criterion field.

Good luck,

Rod