Find Max date in a query

[smiley1978]

I have a table that is updated by an Excel Macro. The data is stored vertically. When the macro runs, one of the fields is given a date and time. This date and time is generated by the system clock and is stored in a variable in Excel. Every record that is updated by the macro is given the same date and time for each run. When I update this table, I have a field for State and one for Channel. I need to return the Max Date (the most recent run) for each combination of State and Channel. When I use the Totals option in the query, it returns everything. Can anyone help?

 

[MyBoo]

Have you tried using the MAX option when using Totals? Under Group By.

 

[smiley1978]

Yes I have. It returns everything. When I add State and Channel parameters to narrow the results to the Max date of that State Channel combo, it returns one of every date for the combo that is stored. That is the problem I am running into. For each state channel combo in the table, I need to return the most recent date. I will use this date in combination with the State Channel combo in another query to return the results that I am looking for.

 

[MyBoo]

Can you post an example?

 

[smiley1978]

State Channel DateUpdated Measure Value
NC Ch1 10/10/2006 Premium 100
NC Ch1 10/10/2006 Premium 120
NC Ch1 10/15/2006 Premium 132
TX Ch1 10/16/2006 Premium 150
NC Ch2 10/14/2006 Premium 160
TX Ch1 10/15/2006 Premium 140
TX Ch2 10/19/2006 Premium 190

In this example I need to pull NC Ch1 date of 10/15, NC Ch2 date of 10/14, TX Ch1 date of 10/16 and TX Ch2 date of 10/19. For each combination of State and Channel, there is a max date. I need that date so that I can run it in another query. If I use the Max on the entire table, I will only receive the Max date, which is the last st to be updated. I need the most recent for each possible State Channel combo.

 

[Norie]

This worked for me.

SELECT Sheet11.State, Sheet11.Channel, Max(Sheet11.DateUpdated) AS MaxOfDateUpdated
FROM Sheet11
GROUP BY Sheet11.State, Sheet11.Channel;

Note Sheet11 is the table name, which I copied from Excel.

 

[smiley1978]

Thanks. That worked.