Any help would be appreciated.

[Always Learning]

Hi Guys,

This is my first posting here.
Could you please help me with this.
I have a table full of customer names & address's. Each record also has a
unique customer number.
What I need to do is pull out all the records including the customer number
but I do not want records with a duplicate Surname, Address Line 1 &
Postcode
I have tried various ways but because I need the Customer Number to be
included, when ever I include this field all records are considered unique
because the Customer Number is unique but there are duplicate (Surname,
Address1, Postcode)
Hope this makes sense.

Best Regards,

Steve.

 

[Steve Schapel]

Steve,

Would it serve your purpose, in the case of duplicated names/addresses,
to only show the details (including the Customer Number) for one of
them? Or do you mean you want to see the names and addresses once but
the Customer Number for all of them? If the first, try this... Make a
query including the Surname, Address1, Postcode, and Customer Number
fields. Make it a Totals Query (select Totals from the View menu).
Leave 'Group By' in the Totals row of the query design grid for the
first three fields, and put First or Last or Min or Max in the Totals
row of the Customer Number column.

 

[Always Learning]

Hi Steve,
Thanks for the response.
What I have is around 24000 names and address's. I know for a fact there is
one particular address that has the same Surname Address1 and Postcode 72
times but they all have a unique CustNumber. What I need to do is only have
one record per address so I need to drop the other 71 records that are
duplicated based on surname, address1, and postcode. this has to work for
all the other records too.

Any help would be appreciated.

Best Regards,

Steve Wilson.

 

[Guest]

I'm pretty sure you need to create an index. Open up the table in design view, right click on the title bar of the table and select indexes from the list. You should find that there is already an index (the primary key)
To create a new index, click in the first empty box under index name, making sure the corresponding feild name and sort order are empty. If they're not, go down a row and try again.

You now need to create a suitable name for the index(e.g Unique.......). Type it in in the index name column. Then in the field name choose the Surname field from the drop down box. Unless you want to sort the surname in descending order, leave the sort order as its default: ascending

Next you need to click in the empty box in the field name column under the surname field you've just chosen (leave the index name in this row blank). In this box you select the address line 1 from the drop down box. Again, leave the sort order as its default

Repeat the above step for the postcode
Lastly, click on the name you created for the index (Unique........) and 3 boxes will appear at the bottom. Change the box that says Unique from No to Yes
This has now ensured you don't get more than one record with the same surnae, address line1 and postcode
Let me know if it works

 

[Steve Schapel]

Sure, Steve. So you don't care which one of the 72 you keep? I would
do it like I suggested before, with the Totals Query. (Make sure you
have a backup copy of the database first When you have this query
returning the unique records you require (which you tried it already,
right?... and it does, right?), then make it into a Make-Table query
(select Make-Table from the Query menu), run the query, delete the
existing table, and rename the new table back to the original table
name. Hope that makes sense.

 

[John Vinson]

What I have is around 24000 names and address's. I know for a fact there is
one particular address that has the same Surname Address1 and Postcode 72
times but they all have a unique CustNumber. What I need to do is only have
one record per address so I need to drop the other 71 records that are
duplicated based on surname, address1, and postcode. this has to work for
all the other records too.

What do you want to do with all the other 71 Custnumbers? I would
guess that this table has a relationship to other tables - Orders,
Contacts, whatever - and that you want to keep this information, just
having it linked to a single customer number?

An added complication: might you have two customers (husband and wife,
father and son, two sisters) with the same surname and address? I'd
suggest including forename as well as surname - and being careful to
check for "Bob" and "Robert" and other such semi-duplicates!

One suggestion: create a new table by copying this table, structure
only. Open the new table in design mode and create a unique Index on
the combination of fields that reliably indicate a duplicate (say,
surname, forename, address and postcode). Run an Append query
appending the 24000 record table into this new table. You'll get a
warning message "xxx records not appended due to key errors" - these
will be the duplicates.

Then create one or more Update queries. Join each table related to
your old address table to the old table by CustNumber, and join the
old table to the new table by all of the unique fields you chose (NOT
including CustNumber). Update the related table's CustNumber field to

[Newtablename].[CustNumber]

to link the related table records to the one customer number you'll be
saving. MAKE A BACKUP of the database. Delete all relationships from
the old table. Rename (DON'T DELETE!!!!) the old table to some safe
name - Addresses_OLD for example - and the new table to the previous
name of the big table. Recreate all the relationships, now to the new
table. TEST EVERYTHING; check that you can retrieve all this
multiple-personality customer's records; when you're sure you have
everything, delete the old table.

 

[Always Learning]

Hi Steve,
What you have suggested works great and I will use this method. Thank you.
One other question is a few duplicates are getting through because although
the surname and post code are the same, if Address1 = "Buckingham Avenue"
And the other record has Address1 = "Buckingham Ave" it gets through. Is
there any way to work on just the first 12 or whatever characters of
Address1.

Thanks for taking time out for me I really appreciate it.

Best Regards,

Steve Wilson.

 

[Steve Schapel]

Steve,

Yes, to directly answer your question, instead of the Address1 field in
the query, you could use Left([Address1],12) instead. But with this
type of data manipulation there will always be anomalies, based on
misspellings, etc. What about two identical records apart from the
Postcode not entered for one of them. John Vinson has highlighted some
other potential problems in his reply. In other words, it is very
unlikely that you will get a simple method which will give you a 100%
correct result.

 

[Always Learning]

Hey thanks guys,
You really helped me a lot.
I really appreciate the time you have taken for me.

Best Regards,

Steve Wilson.

 

[Always Learning]

Hi Steve,
Kept getting errors with this but I managed to work my way round it. The
problem it causes though is that it only outputs 12 characters of Address1.
This is a problem because I need to mail out to the records and need the
full address.
Is there a way to dedupe on just the first 12 characters of Address1 but
output the full Address1.

Once again, thanks for your help.

All the best,

Steve Wilson.

Steve,

Yes, to directly answer your question, instead of the Address1 field in
the query, you could use Left([Address1],12) instead. But with this
type of data manipulation there will always be anomalies, based on
misspellings, etc. What about two identical records apart from the
Postcode not entered for one of them. John Vinson has highlighted some
other potential problems in his reply. In other words, it is very
unlikely that you will get a simple method which will give you a 100%
correct result.

--
Steve Schapel, Microsoft Access MVP

Hi Steve,
What you have suggested works great and I will use this method. Thank you.
One other question is a few duplicates are getting through because although
the surname and post code are the same, if Address1 = "Buckingham Avenue"
And the other record has Address1 = "Buckingham Ave" it gets through. Is
there any way to work on just the first 12 or whatever characters of
Address1.

Thanks for taking time out for me I really appreciate it.

Best Regards,

Steve Wilson.

 

[Steve Schapel]

Steve,

Yes, you can use a method based on the kind of idea I suggested to try
and get as close as You can to a unique set of records in a query. Then
you can make another query, which includes your original table, and the
first query, joined on the CustomerNumber field, and base your output
via a Make-Table query on this one.