Help with MS Access query that will number duplicate records

lthames used Ask the Experts™

I have a table full of Customer's additional ship to addresses that I am going to be importing into a ship to file that already has the 1st location for each company.

I need to add a field for location Number, which will start at 2 (since location # 1 is already in the file).  Then it needs to number the rest of the records for that customer (ie duplicate customer name).

Is this possible with a query?

Here is an example of hat I am looking for:

2     Allstate         111 Main Street
3     Allstate         243 Elm Street
4     Allstate         343 Hwy 34
2     Bay Photo    367 Pecan Ridge
2     First Bank     948 Rutherford
3     First Bank     236 Addison Avenue
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
try this query

SELECT a.CustomerName, a.Address, (Select Count(b.CustomerName) FROM myTable AS b WHERE b.CustomerName=a.CustomerName and b.Address<=a.Address) AS Ctr
FROM myTable AS a;
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Another way would be to use this function, which generates a "RankInGroup" value.  It currently starts at 1, but you can change the intRank = 0 to intRank = 1 when you run this.
Public Function RankInGroup(ParamArray Groups() As Variant) As Integer

    Dim intLoop As Integer
    Static intRank As Integer
    Static GroupValues() As Variant
    ReDim Preserve GroupValues(UBound(Groups()))
    For intLoop = LBound(Groups) To UBound(Groups)
        If GroupValues(intLoop) <> Groups(intLoop) Then
            GroupValues(intLoop) = Groups(intLoop)
            intRank = 0
            Exit For
        End If
    intRank = intRank + 1
    RankInGroup = intRank
End Function

It accepts a parameter array so you can actually group over multiple columns if you wanted to.  To use this, you would use:

SELECT RankInGroup([CustomerName]), CustomerName, Address
FROM your Table
ORDER BY [CustomerName]
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

In an overall sense what you seem to need is a single "Customer" table (Parent)
...and a "addresses" table (Child)

Table Name: tblCustomer
cID (PK)

Table Name: tblAddresses
aID (PK)
a_cID (FK)
aIsPrimary (Yes/No field)

Then you can create a query of the two tables joined where aIsPrimary=False

I don't know if your current system is etched in stone or not, just posting this as a suggestion of where you might want to think about moving this app...






Super simple to copy query and put to use . . . . . turned it into Make Table query.

Worked great

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial