Query loop?

[kosmokramer]

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:

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?

 

[Golom]

You're almost there
[blue][tt]
SELECT A.Contract, A.[Date]

FROM Actions A

WHERE A.[Date] =

(Select MAX(X.[Date]) FROM Actions X
Where X.Contract = A.Contract);
[/tt][/blue]
I eliminated the
Actions.Contract IN (Select DISTINCT Actions.Contract FROM Actions)
because the "Contract" field will always contain one of the values in a list of all contracts.

 

[kosmokramer]

Cool! Thanks for the fast response

 

[Golom]

Upon reflection ... even simpler is
[blue][tt]
SELECT A.Contract, Max(A.[Date]) As [MaxDate]

FROM Actions A

GROUP BY A.Contract
[/tt][/blue]

 

[kosmokramer]

Alright, now you're just showing off! Just kidding. Thanks for your help

 

[lespaul]

don't take it personally, he's always like that!



leslie