What function will return the most recent entry date for a record?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a vehicle database and I need to come up with a function that
will compare the most recent daily milage entry with the most recent service
milage entry and tell me when a particular vehicle needs to go in for
service. I have set up a specific service interval mialge for each different
type of vehicle for the query to refer to. Can anyone help?
 
You'll have to tell us more details about the table(s) and its/their fields
and data.....
 
Think on this ----
Vehicle ---
VehID - autonumber - primary key
Make - text
Model - text
Vin - text
MilesPer - number single - miles per day usage to project next service
DateAcq - datetime
Waranty - datetime
Notes - memo

Services ---
SvcID - autonumber - primary key
Description - text - include gas fillup as a type so as to record mileage
Interval - integer -
Type - text - D or M for days or miles
Projection - integer - number of miles/days advance

VehSvc --
VehSvcID - autonumber - primary key
VehID - integer - foreign key
SvcID - integer - foreign key
SvcDate - datetime
Miles - integer
Gas - number - single
Remarks - memo

Create a service record of every type that will be performed on the vehicle.

Have append query to create VehSvc record when service is due or past due.
Query criteria based on type of service, last performed vs calendar vs
projected, mileage vs last miles vs MilesPer vs calendar, etc.
 
compare the most recent daily milage entry with the most recent service
milage entry
Click to expand...

When you say "the most recent entry date for a record", do you mean
that all the dates are in the same record (row)?

SELECT ID,
Date1 AS result1,
IIF(Date2 > result1, Date2, result1) AS result2,
IIF(Date3 > result2, Date3, result2) AS result3,
IIF(Date4 > result3, Date4, result3) AS result4,
result4 AS max_date
FROM Test;

Or are the dates on different rows in another table?

SELECT T1.ID, MAX(T1.TheDate) AS max_date
FROM Test1 AS T1
INNER JOIN Test2 AS T2
ON T1.ID = T2.ID
GROUP BY T1.ID;

Jamie.

--
 
Back
Top