Most recent date query

[marsha_jean]

I have two tables. The first is a list of current SOPs, sorted by revision date. The second table is a routing list, sorted by date, and each item in the first table can have many entries in the routing list.

I want to run a query that will return from the first table the list of current SOPs (all of them), and from the second table only the most recent entry for each SOP. I’ve been trying to use Dmax, but it just returns one record, the most recent entry from the second table. I want each SOP listed, and the latest routing information for each SOP.

 

[dcx693]

You probably need to run a Totals query where you join the two tables together and select the max of the date from the second table.

If you want to view more detail than just the SOP and the max date, you'll need to join that new query back to one of the original tables in yet another query. Sounds complex, but it really isn't too bad.

 

[betheball]

I have a similar problem except I don't need to join two tables. I have 15 employees who appear several times in my db. I want to pull just the most recent record for each employee which works great if I just pull Max(ID) and Employee. However, I need to display other fields from the records, but when I add them, it makes it so all records are returned instead of one per employee. I tried creating a query that just included Max(ID) and Employee and then a new query that included other fields but that didn't work either.

Ideas?

 

[Dylan]

Betheball

Take results from your first query giving you MaxId and employee.
Then make a new query based on the first query results and the other information you need from the source table you used in the first query to get the other employee information that you require.

I.e. Use results from first query (Qry_1) and then join with original table (Tbl_1) to pick up the rest of the fields you need.

 

[betheball]

Thanks a million. I was doing what you suggested, but was joining on a value in the query that wasn't unique. After reading your answer the light finally came on. Thanks.