Lynn Thames
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
...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
ASKER
Super simple to copy query and put to use . . . . . turned it into Make Table query.
Worked great
Worked great
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]
FROM your Table
ORDER BY [CustomerName]