Do I use a loop for this?

[hansdevr]

I have two tables:

Of course, this is a simpified version of the original.

I have (inner) joined these tables to one new table, with the following contence:

For my project I have to 'distill' a new table from this one. as you can see, the Sequence_nr determines the number of times the %Id appears in the table. In my new table, there would be only one unique value for %Id. Second: the contence of 'Type' is very relevant. It can only be 'L', 'O',  'P' of 'W', in the same order of importance.

One %Id can have any number of sequence numbers, but when type 'L' is present in one of those, the resulting record would only contain the source record of type 'L'. All other records should be discarded. If no 'L' is present and an 'O' record is present, the resulting unique record should be the one that originally contained the 'O' - again the rest should be discarded, etcetera. Of course, if only 1 Sequence_nr was present, no records are discarded.

Which should result in the following table:

What would be the correct (and preferably fastest) way of achieving this? Should I use a loop on my concatenated table? And if yes, how? I am sure this would be an interesting case for anyone who wishes to create a table containing only unique values, determined by a value in one of the original tables...

[hansdevr]

Thnx, Gijsbert, I will try this with my original table!