Sequence Numbers in Access Query (1 Viewer)

[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"));

 

[plog]

Can you demonstrate your issue with data? Provide 2 sets:

A. Starting data from your table. Include field and table names and enough data to cover all cases.

B. Expected results of A. Show what you expect to end up with when you feed your query the data in A.

 

[arnelgp]

yes its possible, BUT
first you need to ADD
sort to your Original query (the one you posted)
Sort it on [Member Number], [Card Number]

next change this in your query:

[Member Number] & "L25" As Expr2

TO:

'if member number is numeric:
([Member Number] & "L" & 24 & DCount("*",","[tbl_Credit Card Detail for Card Conversion_Updated]", "[Member Number]=" & [Member Number] & " And [Card Number]<=" & [Card Number])) As Expr2

'if member number is string:
([Member Number] & "L" & 24 & DCount("*",","[tbl_Credit Card Detail for Card Conversion_Updated]", "[Member Number]='" & [Member Number] & "' And [Card Number]<=" & [Card Number])) As Expr2