Select with Max clause

[goofychump]

Hi there!

I have a table as such:

TableName
StartPlace Text <-- Primary Key
EffDate Date <-- Primary Key
X
Y

Need to construct a query which will take 'StartPlace' as input and retreive all information as per the most recent EffDate. I guess I need to use a Max on EffDate - but just don't know how - in AccessSQL!

Thanks a bunch for your help!

 

[kathryn]

The easiest way I have found to do this is to sort on EffDate and then set the query to return only the Top 1 record.

To get the Top X number of records use the drop down box in query design that usually says &quot;All&quot;. Just type in 1.

Will this do what you need? Kathryn

 

[goofychump]

Thanks for the prompt reply.

But I must clarify - I was thinking of the latest date within a &quot;StartPlace&quot;. I f the values are:
'Boston', '2/15/2001', ....
'Boston', '2/25/2001', ....
'San Fransisco', '2/20/2001', ....
'San Fransisco', '2/25/2001', ....

then I must pick 'Boston', '2/25/2001', ....
(Input parameter being 'Boston')
Sorry for my incomplete question earlier.

thanks!

 

[kathryn]

OK that isn't too difficult.

Create a Totals Query.

Open a query design window and add your table. Click the Totals icon on the toolbar. It is the Sigma(??) directly to the left of the combobox that says &quot;All&quot; on the toolbar.

Now add your StartPlace and make sure that in the Total line of the query design grid, it says group by. Add your data, and in the total line use the drop down to select Max.

Run the query and see if that is what you want.

Kathryn

 

[goofychump]

Hello ...

Thank You again!

That query definitely worked; however I needed more than those two columns (X, Y, etc.) which form the table.

So... I created a query (say Q1) along the lines that you have suggested and created another query joining my original table with Q1. I was initially trying a sub-query but was not very successful with that.

But now I have a solution - and thanks a lot to you for that!