kosmokramer
Programmer
I have a table that has actions listed for each company, and since a company can have multiple actions, the company name is listed multiple times. These actions all have different dates as well.
I want to create a recordset that will pull out the latest date for each of the companies. If I limit the query to one company, it is very easy to pull out the max date just by saying MAX(date), but I want this for each company. It almost seems like I need a loop that would go through each company and evaluate the max date for each company and then combine the results into one recordset.
All I have gotten it to do so far is to find the maximum date in the whole table, and then only show the companies for which this date matches.
Here's my query:
Anyone have any ideas on how to make it work right?
I want to create a recordset that will pull out the latest date for each of the companies. If I limit the query to one company, it is very easy to pull out the max date just by saying MAX(date), but I want this for each company. It almost seems like I need a loop that would go through each company and evaluate the max date for each company and then combine the results into one recordset.
All I have gotten it to do so far is to find the maximum date in the whole table, and then only show the companies for which this date matches.
Here's my query:
Code:
SELECT Actions.Contract, Actions.Date
FROM Actions
WHERE Actions.Contract IN (Select DISTINCT Actions.Contract FROM Actions)
AND Actions.Date IN (Select MAX(Actions.Date) FROM Actions);
Anyone have any ideas on how to make it work right?