Removing rows from a query based on date

[ThomasLonsdale]

Hey,

I have a query that produces a set of rows that contain product IDs and dates (sent). What I want to do is filter out rows that share IDs with another row such that I keep only one row which has the most recent date. As an example my query might contain the following two rows:

DATE ID
01/01/2015 1
02/01/2015 1

What I want to do is filter my table and under the condition that ID #1 appears more than once only keep the row with the most recent date.



I'm unsure what the easiest way to do this would be (or how to do it at all).

I am eagerly awaiting your input .

[NoellaG]

Hi,

you can create 2 queries:
- one groupsquery: select [ID], max([DATE] from Mytable group by [ID].
- one query where you link the table data to the results of query 1, keeping only the rows of the most recent dates

[ThomasLonsdale]

Thanks! I left out the detail that there are also two other columns (Company sent to and Comments sections) so I added these to the group by part and I've managed to get something that works (using the max function) for me and produces the results I need to show for my colleague.