asked on

Help with MS Access query that will number duplicate records

Greetings!

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

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
Next

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 Coachman

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

Table Name: tblCustomer
Fields:
cID (PK)
cName

Table Name: tblAddresses
Fields:
aID (PK)
a_cID (FK)
aAddress
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...

;-)

JeffCoachman

;-)

JeffCoachman

Lynn Thames

ASKER

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

Worked great