Best to handle with 'import' or with a 'query'?

[warrensalsa]

My first time posting here, so i apologise in advance for any mistakes!

I want to import a file to Access, but only want the latest record in each case. I am not sure if this is better to handle this in the import step, or to import the file and then process this with a query. I need help, on which method is best, and then also the steps on how to do this.

My incoming file looks like this:

NUMBER	DATE	ID
15525888	04.02.1979	MMMMUO55907A9
15525888	31.12.2020	MMMMUO55907A9
15526013	01.01.2015	SPSNJTN54A18F0
15526948	30.06.1979	SPESTHTHVT58B13
15526948	31.12.9999	SPESTHTHVT58B13

What I would like to see in Access is only the latest records i.e:

NUMBER	DATE	ID
15525888	31.12.2020	MMMMUO55907A9
15526013	01.01.2015	SPSNJTN54A18F0
15526948	31.12.2020	SPESTHTHVT58B13

Thankyou kindly,
Warren.

[JoeM]

I would recommend importing into a Temporary Table, and then using an Append Query to just write the records you want from the Temporary Table to your Final Table.

You can clear/re-use your Temporary Table each time, if this is something that you will need to do over and over again.

[JoeM]

So you would use an Aggregate Query to get the values you want to import (Group By Number and ID and take the Max Date value), i.e.

SELECT TempTable.Number, TempTable.ID, Max(TempTable.Date) AS MaxOfDate
FROM TempTable
GROUP BY TempTable.Number, TempTable.ID;

And this is the query you use to write to your Final Table (by changing it to an Append Query).

Note that in order for this to work properly, your Date field will need to be a Date data type (and not Text), or else the Max function will not work as you expect.

BTW, I noticed something odd in your sample data. Your last record has a date of "31.12.9999", but your expected results shows it as "31.12.2020". Not sure if that was a typo, or there is something else going on there.

[warrensalsa]

Thank you for your answer, I will try this code. Good idea!
With the date of '31.12.2020' this was simply a cut and paste error when i was putting this question together! I.e. the last record of expected result should have 31.12.9999.
Thank you once again!