Max date query

[tmcrouse]

I always thought when using max it would take the maxdate as well as the item that correlates with the max date. I have the following example

taxid eff_dt market
11111 1/1/1990 new hampshire
11111 1/1/1997 delaware

22222 1/1/1993 maine
22222 1/1/2010 new england

There are more rows but this is the way the table looks. If I run this query

SELECT CLEANING3.Taxid, Max(CLEANING3.EFF_DT) AS MaxOfEFF_DT, CLEANING3.market
FROM CLEANING3
GROUP BY CLEANING3.Taxid, CLEANING3.market
HAVING (((CLEANING3.Market)<>"UNKNOWN"));

It will return the same as in the rows. It should be returning

taxid eff_dt market
11111 1/1/1997 delaware
22222 1/1/2010 new england

How do I create the query to do this?

 

[jzwp22]

You would first get rid of the market field in your query since the markets are different for each taxID. If you get an error since the market field is no longer in the Max query, create a query that does the market filtering first and then base the Max query on that query.

SELECT CLEANING3.Taxid, Max(CLEANING3.EFF_DT) AS MaxOfEFF_DT
FROM CLEANING3
GROUP BY CLEANING3.Taxid
HAVING (((CLEANING3.Market)<>"UNKNOWN"));


You will then create another new query that joins the max query back to the table (or filtered query) and join via both the taxID AND the date (max date to eff_DT)

 

[tmcrouse]

THNX. I see do a tin with max of the date first and then connect that back to original table and join the tin and the date in the 2 tables and run the query. Worked great.

 

[jzwp22]

You're welcome. Glad that worked for you.