Using a Query to Select the latest update to a data series 1

[postlethwaited]

Good Morning, I have a table of data where the same system number may occur more than once. I would like to select only the rows which have the highest life-cycle reference for each system number. Is this possible?

 

[rjoubert]

Yes, it's possible, but you will need to give more info. What is the structure of the table you want to get this data from? Which field in said table is the "life-cycle reference", and which one is the "system number"?

 

[Remou]

Perhaps something like:

[tt]SELECT Table1.Field1, Table1.Field2
FROM Table1
WHERE Table1.Field2 =
(SELECT Max(Field2) from Table1 X
WHERE Table1.Field1 = X.Field1);[/tt]

There is a dedicated forum for queries:
Microsoft: Access Queries and JET SQL Forum
forum701

 

[postlethwaited]

thanks guys, to be honest i was trying not to code this and use just one query - i am not sure if it is possible. the structure of the table is as follows:

FIELD ONE FIELD TWO ALL OTHER DATA
(SYSTEM NO) (LIFE CYCLE)

445566 10345678
445566 10345679 **
436574 10456493
436574 10456496 **

I would want to select the two marked records - like i say not sure if it possible, but appreciate your efforts!

 

[Remou]

What I posted is one query, SQL view, that selects the two marked rows.

 

[postlethwaited]

thanks very much! excellent!