Sequence Numbers in Access Query

Moxioron

Active Member
Joined
Mar 17, 2008
Messages
417
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"));
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,628
Office Version
2013
Platform
Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,095,919
Messages
5,447,282
Members
405,446
Latest member
morsecode

This Week's Hot Topics

Top