sorting records

[daveonion]

howdy, here we go, i have a table with details of cars, a table with details of employees and a table with which employees are driving which cars. one car can have many drivers and vice versa, however in the table saying which driver drives which cars i have a date field stating the date that they will be using the car from.
Question
I need to create a report showing the current driver of each car, however when i try and do this (by choosing the max variant in the query for date) it shows all records, and all drivers that have drove the car.
hope u understand what i mean
please help

 

[SarahG]

I have assumed table and field names, and this isn't the exact SQL syntax, but I'm sure you'll follow:

Query1:
select max(startdate) CurrDate, carcode from tblDriverCar group by carcode

Query2:
Select tblCrossRef.* from
Query1 joined with tblCrossRef joining on carcode and tblCrossRef.StartDate=Query1.CurrDate

You can modify Query2 to join with tblDriver to get Driver Name etc.

Good Luck

 

[JIRRERA]

I think I understand the issue. I'd try is using a nested query to first determine the latest record for each car. Using the MAX variant on a date is okay but in a one to many situation, if the table has a lot of records, you really you want to have an AutoNumber field with a unique ID for each record, then create a compound primary key on Car ID + Record ID for effeciency. Make it a "Group By" query grouping on the Car ID, Employee Name, and Date, along with Max([Your AutoNumber Record ID]).
Save this query and insert it into a new query Then simply add your auto detail table and link on Car ID.
Hope this helps.

 

[daveonion]

howdy, i've tried doing what you both asked and when i group by date and only include the car number then its fine and returns only the current records, however as soon as i join the query to the other table and add employees i end up with loads of records, the date and car is duplicated and the employee names seem to be just randomly included against each record, oohh

 

[daveonion]

once again tek tips comes to the rescue, thanks