Sequence Numbers in Access Query

[Moxioron]

Hello.

Please see my SQL below. Everything is working fine, except I have to account for duplicates.

What I want to do: If there are duplicate Member Number fields, then sequentially use L25 for the first one, L26 for the second, so on and so forth.

Any ideas? Thanks.


SELECT [tbl_Credit Card Detail for Card Conversion_Updated].[Card Number], [tbl_Credit Card Detail for Card Conversion_Updated].[Member Number], [Member Number] & "C" & [Card Number] AS Expr1, [Member Number] & "L125" AS Expr2, IIf([External Status Code] In ("Z","B","E","A","L","U","I") And [Account Balance Total Amount]>0,"X",IIf([External Status Code] Is Null,"X","")) AS Expr3, [tbl_Credit Card Detail for Card Conversion_Updated].[External Status Code], [tbl_Credit Card Detail for Card Conversion_Updated].[Account Balance Total Amount], [tbl_Credit Card Detail for Card Conversion_Updated].ID
FROM [tbl_Credit Card Detail for Card Conversion_Updated]
WHERE ((([tbl_Credit Card Detail for Card Conversion_Updated].[Member Number]) Is Not Null And ([tbl_Credit Card Detail for Card Conversion_Updated].[Member Number])<>"0000000" And ([tbl_Credit Card Detail for Card Conversion_Updated].[Member Number])<>"1" And ([tbl_Credit Card Detail for Card Conversion_Updated].[Member Number])<>"000000" And ([tbl_Credit Card Detail for Card Conversion_Updated].[Member Number])<>"0000001") AND ((IIf([External Status Code] In ("Z","B","E","A","L","U","I") And [Account Balance Total Amount]>0,"X",IIf([External Status Code] Is Null,"X","")))="X"));

 

[xenou]

Since L125 is hardcoded into your SQL -- assuming you mean L125 and not L25 -- , this is not possible. In general, with Access SQL its not something you can do even with ordinary sequential numbers (1,2,3...). First question is why do you have duplicates that you want to treat as not being duplicates - perhaps you can avoid this at an early stage with better data (for instance, by getting the PRIMARY KEYS along with the rest of the data).