How Do I Select Max Value in Query?

[S Jackson]

I have two tables. Table1 has a one to many relationship with Table2. I
want to create a query that selects fields from Table1, and one field (a
date field) from Table2, but I want it to select only the newest field (date
field) from Table 2. Maybe an example would help:

Currently, I get this:

CaseName1, 02/14/04
CaseName1, 03/14/04
CaseName1, 04/14/04

But, I only want the last row, CaseName1, 04/14/04.

Does this make sense?

TIA
S. Jackson

 

[Douglas J. Steele]

Convert your query to a Totals query. You can do this either by clicking on
the Sigma icon on the tool bar, or by selecting View | Totals from the menu
bar.

Once you've done that, a new row (Total) will appear in the grid, with each
cell set to Group By. Under the date field, change Group By to Max.

 

[DDM]

A totals query will do it. Set up your query, then click the Totals button
(the Greek Sigma) from the toolbar at the top of the query design screen.
Set CaseName to "Group by," and Date (your date field) to "Max." Then run
the query.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

 

[S Jackson]

Thank you! I knew the solution would be simple (especially after I spent
all morning on it!)
S. Jackson

 

[PC Datasheet]

Hi Shelly,

I see you got your answer. Just wanted to say Hi!

Steve
PC Datasheet