Query

[JMarcus]

I have a query with multiple dates for the same person and ID # so it shows up several times. I have to filter by the most recent date. Tried the max and last but multiple are still showing up. Any suggestions. Thanks

 

[plog]

Can you post data to demonstrate what you want to achieve? Post 2 sets of data:

A. Starting sample data from all relevant tables. Include table and field names and enough data to cover all cases.

B. Expected results based on A. Show what you want your query to return when you feed it data from A.

 

[JMarcus]

Sample
A: # Name Start Date Code
101 John 12/1/2012 HU

101 John 1/1/2014 BC
101 John 6/6/2016 EE

Expected Result
B 101 John 6/6/2016 EE

 

[plog]

That's a super horrible table. Is # a field name? Name and Date shouldn't be, those are reserved words and will make writing code/queries difficult, I suggest you change them. Also, you should have an autonumber primary key to uniquely identify records in your table--which you kind of need for what you are asking.

With that said, there's the general approach:

Create a max query (http://www.techonthenet.com/access/functions/numeric/max.php) to get the maximum Date value for each # or Name. Save this query, let's call it 'sub1'

Then build a new query using sub1 and your table. Link them via # or Name fields and also the Date to MaximumDate. Then bring all the values from the table into the query and that will show you what you want.

 

[JMarcus]

sorry that was just an example. the table is more complex. thanks

 

[JMarcus]

i figured it out anyways. thanks

 

[arnelgp]

if you use Last() and your dates are entered in random dates, you will likely get the wrong result.