Select Latest Date

[aldeb]

I have a table that shows me 3 fields:
PROCESS
EFFECTIVE_
OPERATION_

My issue is each process and operation may be there multiple times due to multiple Effective dates. I only need to see each Process and Operation one time based on the latest Effective date. Below is what I have NOW:


PROCESS EFFECTIVE_ OPERATION_
1/010/1-8TCOWLFEEDER 2005-11-01 10
1/010/1-8TCOWLFEEDER 2005-11-01 20
1/010/1-8TCOWLFEEDER 2005-11-01 30
1/010/1-8TCOWLFEEDER 2005-11-01 40
1/010/1-8TCOWLFEEDER 2005-11-03 10
1/010/1-8TCOWLFEEDER 2005-11-03 20
1/010/1-8TCOWLFEEDER 2005-11-03 30
1/010/1-8TCOWLFEEDER 2005-11-03 40
1/010/1-8TCOWLFEEDER 2005-11-09 10
1/010/1-8TCOWLFEEDER 2005-11-09 20
1/010/1-8TCOWLFEEDER 2005-11-09 30
1/010/1-8TCOWLFEEDER 2005-11-09 40

This is what I need:
PROCESS EFFECTIVE_ OPERATION_
1/010/1-8TCOWLFEEDER 2005-11-09 10
1/010/1-8TCOWLFEEDER 2005-11-09 20
1/010/1-8TCOWLFEEDER 2005-11-09 30
1/010/1-8TCOWLFEEDER 2005-11-09 40

Out of the records above I would only want to see the records with the 2005-11-09 date. The dates can vary based on processes so I really need something that selects the the latest date for each Process and Operation. I would like to do this in a query or multiple queries.

Thanks for any help.

 

[Murli]

I was able to get that using two queries.

Query1 : calculate the max of effective.
Query 2: use the base table and query 1 - use innerjoin to get all the fields for that max(effective).

Let me know if that works.

 

[Jon K]

I really need something that selects the latest date for each Process and Operation.

Build a Totals Query (by clicking on the Totals button on the toolbar in query design). In the Total: row that appears, use Group By for the Process and Operation fields and use Max for the Effective field.
.