I remember being a user of these forums when I was but a wee A Level student, and many years later it seems I'm back
I'm working with a large data import for my company, and amongst other things need to locate and display some duplicate
I have a table called 'data' with 44000 records in it. There are a lot of fields, but the key fields I'm currently working
with are as follows...
*Account | Surname | DOB | Postcode
123451 | Smith | 17/07/1977 | AA11 1AA
123452 | Smith | 17/07/1977 | AA11 1AA
123453 | Jones | 11/01/1911 | BB11 1BB
123454 | Jones | 11/01/1911 | BB11 1BB
123455 | Jones | 11/01/1911 | BB11 1BB
123456 | Brown | 16/06/1966 | CC11 1CC
123457 | Brown | 16/06/1966 | CC11 1CC
The Account field is a unique identifier. I've currently got a query setup with the following SQL code to locate
customers who have more than one account based upon Surname, DOB and Postcode.
SELECT data.Surname, data.DOB, data.Postcode, Count(*) AS Multiples
GROUP BY data.Surname, data.DOB, data.Postcode
This is ok, as it gives me a Multiples field illustrating how many accounts each customer has. But the next step is to
be able to identify specifically which accounts each customer holds. For example something like:
SURNAME | Account1 | Account2 | Account3 | ...(upto 7)
Smith | 123451 | 123452 | | ...
Jones | 123453 | 123454 | 123455 | ...
Brown | 123456 | 123457 | | ...
At this point I'm completely stumped! Can anyone help...?
Ryan (your bewildered friend in need!)