Need help with top values and a sub-query

[bobcrosley]

I'm trying to put together a work order reporting system and am having trouble with some queries. We have work orders and project managers have to enter updates whenever a status on a project changes. Then the general manager gets a report every week showing every work order and the most recent update on the project. Currently, they're using a shared excel worksheet for this. I have been tasked with creating an access database.

I have two tables. The first is a workorder table that has work order numbers and the other info tied to a work order, such as customer name, contact info, etc.

The second table is an update table. It has the work order number, an update number (and auto incrementing field) as well as current problems, current resolutions, and date of the update.

I need a query that will take give me the most recent update for each work order in the workorder table.

I have a query that will go through the update table and pull the information for the last entered update. I use the top values setting and sorted descending by date entered to only give me the most recent update.

However, when I join that to my work order table, I pull only the work order info and update info for the last update entered for any work order. So even if I have 100 work orders in the DB, I get only the work order that had the last update.

What I need is to iterate through the workorder table and for each work order, pull the most recent update.

Thanks,
Bob

 

[cmmrfrds]

You can try a sub query to get the latest date. The id from the outer query will reference back as the criteria for the inner query.

SELECT Y.workorderid, Y.etc,
(SELECT top 1 X.work_date
FROM updates X
where X.workorderid = Y.workorderid
order by X.work_date) as lastestworkdate
FROM workorder Y
GROUP BY Y.workorderid, Y.etc