Selecting multiple "maxes"/latest actions?

R

Richard Perry

OK, folks, I'm stumped on something. I'm putting together a vehicle service
database. For multiple vehicles, there are multiple service actions required
at various intervals.

What I've been working with is one table showing all service actions in the
fleet, recording which service was completed on which vehicle ID at a given
mileage and date.

What I need to see in the end is forecasting - when the next various actions
are due for a vehicle. I'm trying to do that by selecting the last actions of
each type for a vehicle from the table, add the period for each service (from
another table) to those numbers and reporting back.

It's the first part of this that's giving me fits at the moment - I'm NOT
very competent at SQL/programming this, and all my reference books on this
subject are in storage right now (long story). What I see at the moment is
that I can use the "max" function to select the latest action for a vehicle,
or the latest action (throughout the fleet) of a given type of service action
,but how do I get it to return the latest action of each type for a vehicle?

Is there an 'easy' way to do this that I'm missing? If I reorganized my
tables into seperate tables for each vehicle or each service action, would
this data be easier to mine?
 
J

John Spencer

Group by Vehicle and by Service action and get the max date for that
combination.

SELECT VehicleID, ServiceType, Max(ServiceDate) as ServiceOn
FROM YourTable
GROUP BY VehicleID, Action


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
R

Richard Perry

John - just to verify in the code - is "ServiceType" in line 1 the same as
"Action" in line three? If so, I think I understand.

Thanks!
 
J

John Spencer

Yes, you are correct. Typo on my part.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top