help with Duplicates

[keat63]

Folks



I'm not really a big access user.
I've somehow always managed to do what I need to do using Excell.

However, I have a very large database, that won't even open in excell.
So i have to use access.

I have a list of customer names, addresses, telephone, fax, postcode etc.
In the same database is a list of customers who havn't bought.

The two are indentifiable with a field named "type".
customers are "all", none buyers are "enq"

I've already run a duplicates query (using the find duplicates query wizzard), but I think I need to get a little more clever.

I'd like to run a duplicates query based on the first 3 letters of the company name and cross reference this with the first 3 letters in the postcode.

eg. Garage Works at LS27 12Z
Id like the query to use Gar and LS2

How would I modify the query to do this please ?

[orange]

Please show the names of the tables and the fields.
What have you tried? What results?

[keat63]

Please show the names of the tables and the fields.
What have you tried? What results?

If I run a duplicate query wizzard against the column "name" and column "postcode" and list the colmns "address" and "type" as additional info, it results me 71,600 duplicates. of which 93 are from the type "ENQ"
I can deduce from this that 93 entries with the column type "ENQ" are also listed as type "ALL"

I bet this isn't making much sense is it. ?

I'm currently changing the attributes of the columns "Name" and "postcode" to limit them to 4 and 3 characters resepctively.
I'm hoping that re-running the query will give a different set of results.

Which it does, so I can now run the report.
I think I may have sorted it, in one form or another.

[rpeare]

If the database found 71000 duplicates based on the entire company name and the entire postal code you have a ton of duplicates and chopping off the trailing information in your company name and postal code fields will do nothing to make that number go down, if anything it will make it increase

For example
company PostalCode
Garage 1 ABCD EFG
Garage 2 ABCD EFG

assume this is your entire data set. If you run a find duplicates on this you will end up with 0 results. If you do a find duplicates based on the first three letters of the company and the first three characters of the postal code you'll get 1 match.

Likewise if you have

company PostalCode
Garage1 ABCD EFG
Garage 1 ABCD EFG

Although these are the same company if you perform the find duplicates on the entire field you will find no matches but 1 if you check the first three digits. So you are likely to end up with *more* matches rather than fewer if you limit the string to the first x characters.

Are you trying to remove all duplicates from your tables? If so your best bet, most likely is to do it with code and to append all the 'deleted' items to a secondary table so you can review what was moved and retain it 'just in case'. I'd likely do that with VB code rather than a query.

[keat63]

I've just been into the sales office and questioned the 70,000 duplicates and understand why they are there.
The data is taken from a data file.
When ever a customer record is changed, rather than the info being deleted, it actually duplicated.
Rather silly if you ask me, but I didn't write the app.

This is the reason why my boss wanted to run the duplicates query based on cut down info.

I'm only really interested in the ones marked "ENQ".
These are customer enquiries, who may now be actual customers.
So by changing the fileds to contain less characters, has produced a report with more "ENQ" entries.. which is what I wanted.
It's now up to someone else to go through them on the app and remove or qualify them.