|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
i have a table listing customer names and addresses, along with unique customer id numbers.
I have a simple query to identify the duplicates, and this lists customer names and customer numbers. What I now want is a table with 2 columns of customer numbers - one for the original (lowest) customer number, and one for the duplicate. Any easy way to do this? all ideas welcome! thanks 4 reading... |
|
#2
|
|||
|
|||
|
got a fix from a friend in the meantime:
SELECT Dupstha2_1.FNAME, Dupstha2_1.LNAME, Dupstha2_1.[Cust No], Dupstha2_2.[Cust No] FROM Dupstha2 AS Dupstha2_1, Dupstha2 AS Dupstha2_2 WHERE Dupstha2_1.[Cust No] < [Dupstha2_2].[Cust No] AND Dupstha2_1.FirstNAME = Dupstha2_2.FirstNAME AND Dupstha2_1.LastNAME = Dupstha2_2.LastNAME AND Dupstha2_1.STREET = Dupstha2_2.STREET; Basically what is does is that it joins the Dupstha2 table with itself on the FNAME column and the STREET column. The Dupstha2_1.[Cust No] < [Dupstha2_2].[Cust No] makes sure that the two [Cust No] combinations is only shown once. |
![]() |
| Viewing: Dev Shed Forums > Databases > Database Management > Access 97 - sorting numbers |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|