Access fjerne dubletter men behold den ældste

Here is a similar example

https://www.computerworld.dk/eksperten/spm/1015214

Hi Terry,

Thanks for your prompt reply. It works - Great :)

Just a supplementary question. I am new beginning in SQL. There are several columns. If for example I would like to have content from ColD + ColE list in the view, how SQL commands then be?

SELECT Min (tblTest.ColA) cola, tblTest.ColB, tblTest.ColC
FROM tblTest
GROUP BY tblTest.ColB, tblTest.ColC

Not exactly sure what your after, could you show some data and also the result you expect?

Oh, and please mark my answer as correct, if it works of course ;-)

Hi Terry,

Thanks again.

I added example of data I want to.

No | User | Date | City | Note
100 | NN | 2017-03-27 | Randers | På Citytur
100 | NN | 2017-03-30 | Glostrup | En is tur
100 | NN | 2017-03-25 | Hillerød | Skovtur <--
100 | TT | 2017-03-26 | Odense | I ZOO <--
200 | TT | 2017-03-30 | Padborg | Over grænsen
200 | NN | 2017-03-25 | Flensborg | Shopping <--
200 | NN | 2017-03-27 | Rønne | Ud at sejle
200 | TT | 2017-03-30 | København | Arbejde <--

The result I want is the following:
No | User | Date | City | Note
100 | NN | 2017-03-25 | Hillerød | Skovtur <--
100 | TT | 2017-03-26 | Odense | I ZOO <--
200 | NN | 2017-03-25 | Flensborg | Shopping <--
200 | TT | 2017-03-30 | København | Arbejde <--

The following works,
SELECT Min (tblTest.Date) AS Date, tblTest.No, tblTest.User
FROM tblTest
GROUP CITY tblTest.No, tblTest.User

But if I try to add the col. Like "City" and "Note" like the below:

SELECT Min (tblTest.Date) AS Date, tblTest.No, tblTest.User, tblTest.City, tblTest.Note
FROM tblTest
GROUP CITY tblTest.No, tblTest.User

I get a Access promp with following message:

"Din forespørgsel indeholder ikke det angivne udtryk "City" som en del af en aggregatfuntion"

What do I wrong?

In the link I gave we delete the records we dont want
https://www.computerworld.dk/eksperten/spm/1015214

So the table only contains the records you want in result IE

No | User | Date | City | Note
100 | NN | 2017-03-25 | Hillerød | Skovtur <--
100 | TT | 2017-03-26 | Odense | I ZOO <--
200 | NN | 2017-03-25 | Flensborg | Shopping <--
200 | TT | 2017-03-30 | København | Arbejde <--

Are you using the second SQL to delete them?

You asked for "fjerne dubletter men behold den ældste"!

Are you using the second SQL to delete those you dont want?

No, I use the query only for Excel, where I make data connection between Excel and Access.

So your original questions didn't include delete of records?

OK, I think I can find a solution but first tomorrow as I'm a bit busy right now....

Not sure that your query works

"The following works,
SELECT Min (tblTest.Date) AS Date, tblTest.No, tblTest.User
FROM tblTest
GROUP CITY tblTest.No, tblTest.User"

But this does. I have changed name of field Date to Dato. Date is a reserved word ;.)

Any, make a query using this SQl and name it qry1

SELECT tblTest.[No], tblTest.User, Min(tblTest.Dato) AS MinOfDato
FROM tblTest
GROUP BY tblTest.[No], tblTest.User;


Then make another query
SELECT tblTest.ID, tblTest.[No], tblTest.User, tblTest.Dato, tblTest.City, tblTest.Note, *
FROM tblTest INNER JOIN qry1 ON (tblTest.Dato = qry1.MinOfDato) AND (tblTest.User = qry1.User) AND (tblTest.[No] = qry1.[No]);

Thanks Terry,

It works now :)

Super !