MS-Access 2010 - Query to get Most Current Record

[KrazyKasper]

This should be so simple, but I cannot figure it out.
I have a list of machines, each of which has a MachineNumber, several PeriodDates, and Prices for each.</SPAN>
I need to select the record that has the most current PeriodDate for each machine. For example:</SPAN>
MachineNbr PeriodDate Price</SPAN>
Machine1 05/01/2010 $100</SPAN>
Machine1 08/10/2012 $200</SPAN>
Machine1 01/01/2015 $150</SPAN>
Machine2 11/01/2012 $100</SPAN>
Machine2 09/15/2014 $200</SPAN>

When I select “Max” PeriodDate, I still get multiple records for each machine. How can I get the most current record (based on Period Date) per machine?
Thanks.</SPAN>

 

[Joe4]

When you do an Aggregate Query, it groups on all the fields that aren't used in your Aggregate Function. So if you include the price field in your initial query, it is going to cause problems since the prices are different and you are grouping on them.

An easy way to do what you want is to do it in a series of two queries.

In the first query, you just want to return the latest date for each machine.
To do this, create a new query and add JUST the Machine Nbr and Period Date fields.
Click on the Totals icon to make this a Totals (Aggregate Query).
Change the Totals Row value under the Period Date field to Max.
This will "Group By" the Machine Nbr and return your latest Period Date for each grouping.
Save this query.

Now, create a new query, adding the original table and the query you just created above.
Join the table and query on BOTH the Machine Nbr and Period Date fields.
Then, return all the fields from your table, and this will give you what you are looking for.

 

[KrazyKasper]

Thanks very much.
Thread can now be closed.

 

[Joe4]

Glad it worked out for you.
We don't actually close threads. Your just saying it has been solved is enough.