Query latest user

[JiTS]

Hi,

I am working on a report and I am stuck with the following problem:

Here at work we use two tables. One for creating records and for the updates of the created records. In the attachment you see a part of the table 'updated'. What you see is one ServiceNumber (1-551747091) which is changed users (Eigenaar.) three times.

I want to query the latest user of each ServiceNumber. So that the result is:

ServiceNumber | User
1-551747091 | MKB_VERHUIZEN
etc.

How to do that?

Thanks!

 

[RV]

Look up the function Max in Access Help.

Don't use reserved words such as Date, Field, User.
They will cause mayhem.

RV

 

[JiTS]

I found something about 'TOP 1'. Isn't that a better solution?
Because with MAX I only get one record, but I need to get all the unique records with the latest user!?!

ServiceNumber | User
1-551747091 | MKB_VERHUIZEN
1-551747092 | HOCKSC
1-551747093 | GRAALMANT
etc.

BTW I don't use the reserved words because I translated them from Dutch to English.

 

[RV]

I found something about 'TOP 1'. Isn't that a better solution?
Because with MAX I only get one record, but I need to get all the unique records with the latest user!?!

Using TOP is not really a good idea.
Stick with the Max function instead.
You need to include your user column in your query if you want to retrieve rows per user.

PS Eigenaar should read Owner, User stands for Gebruiker

RV