The first step is to tell us what your table structure is. I assume you have
some sort of DriverID, Bus#, AssignmentDate. Are there other fields that you
need?
This is not one of the easiest queries you can do, because it requires two
parts. The simplest method is to create two queries.
1. Query #1 will get the maximum (most recent) assignment date for each
bus. To do this:
a. Create a new query, adding your table to the mix
b. Drag the Bus and AssignmentDate fields into the query grid.
c. Look for the Sigma (looks like an M rotated 90 degrees counter
clockwise) symbol on the menu bar (or ribbon if you have 2007), and click
that symbol. This will add a "Total:" row to the query grid, and should have
"Group By" listed under each of the fields.
d. Change that value from Group By to Max for the AssignmentDate field.
e.If you look at the SQL view, it should look something like:
SELECT yourTable.Bus,
Max(yourTable.AssignmentDate) as MaxAssignmentDate
FROM yourTable
GROUP BY yourTable.Bus
f. Now save this query as Query1
2. To get any additional information from your table, you will have to join
this query with the table.
a. Create a new query, select your table and this new query from the
table/query list
b. Create two joins between the tables (one on the Bus field, and the other
on the AssignmentDate field). This join will ensure that the only records
that are visible in your query are those that match the most recent
assignment dates for each bus.
c. Drag the additional fields you need from your table into the grid (or
select the *) to get all the fields
d. In the SQL view, the query will look something like
SELECT *
FROM yourTable
INNER JOIN Query1
ON yourTable.Bus = Query1.Bus
AND yourTable.AssignmentDate = Query1.MaxAssignmentDate
e. Run your Query
