melikebeer
IS-IT--Management
(First time poster - please let me know if I have posted inappropriately in any way.)
I'm building an Access 2002 asset & employee management db with a number of date log tables...they are all similar with:
PRIMARY KEY is an Autonumber, DATE (of the status change), fkITEM (foreign key of the item or person changing status) and other info.
A specific example is my fleet of trucks, so the logFleet table is pklogF, DATElogF, fkTRUCK, fkDRIVER(the DATE would be the date that the truck was assigned to that driver) and...
logService table which is [n]pklogS, DATElogS, fkTRUCK[/b], and then checkboxes for service items (oil change, etc)...(the date is the date of the service obviously).
What I am trying to accomplish is a join query between the two log tables (as well as the employee table and truck table) that tells me who the driver was at the time of the service. (Service often gets entered after the fact, so it wouldn't help much to have a 'current driver' field in the logService table)
I can add the criteria {DATElogF <= DATElogS} easily enough, but that will return ALL drivers who had the truck prior to the service. How do I get only the most recent of those dates?
I've played around with DMax() a bit, unsuccessfully. Perhaps this can only be done by using another 'sub'query?
Thanks in advance, and my most humble apologies if I'm in the wrong forum or if this has already been answered (I couldn't find it).
Jordo
I'm building an Access 2002 asset & employee management db with a number of date log tables...they are all similar with:
PRIMARY KEY is an Autonumber, DATE (of the status change), fkITEM (foreign key of the item or person changing status) and other info.
A specific example is my fleet of trucks, so the logFleet table is pklogF, DATElogF, fkTRUCK, fkDRIVER(the DATE would be the date that the truck was assigned to that driver) and...
logService table which is [n]pklogS, DATElogS, fkTRUCK[/b], and then checkboxes for service items (oil change, etc)...(the date is the date of the service obviously).
What I am trying to accomplish is a join query between the two log tables (as well as the employee table and truck table) that tells me who the driver was at the time of the service. (Service often gets entered after the fact, so it wouldn't help much to have a 'current driver' field in the logService table)
I can add the criteria {DATElogF <= DATElogS} easily enough, but that will return ALL drivers who had the truck prior to the service. How do I get only the most recent of those dates?
I've played around with DMax() a bit, unsuccessfully. Perhaps this can only be done by using another 'sub'query?
Thanks in advance, and my most humble apologies if I'm in the wrong forum or if this has already been answered (I couldn't find it).
Jordo
