Deleting Duplicates Thoughout Multiple Tables 1

[leadpro]

I have several tables because my record volume is well over 10 million records. I would like to dedup based on Homephone thoughout all of my databses. Any suggestions?

Leadpro

 

[PHV]

Create a saved union query reading all your tables.
Then create an aggregate query based on this union query grouped by homephone having count(*)>1.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[leadpro]

Ah...a union query creates unique record set from all tables and I can pull from there..is this what you're saying?

 

[PHV]

No, not really.
My suggestion was to find the duplicates (with UNION ALL).
I've misread your post, sorry.
Create a table with the desired structure and and index not allowing duplicates on the relevant fields.
Then run an append query based on the union query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[leadpro]

Hmmm...what if the result is millions of records? Will Access handle this in one results table?

 

[PHV]

The limitation is that the size of the db must be less than 2 Gb.
You may have many mdb playing with linked tables.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[leadpro]

OK, I'll play around with this, however, I suspect that once the appendage to the new table exceeds this, I will have a problem.

In other words, I create my union query to join 10 linked tables of data 20 gb and the result is a table 15 gb. Not sure what happens there unles there is a way to split this up?

 

[PHV]

No go.
Take a look at MSDE.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[leadpro]

how about this...can we only include the phone numbers...this would dedup all of the records. Export those phone numbers, then match them up with the respective records in each table...export those into multiple databases.

Question...what is the sql for finding matched records of another table?

 

[PHV]

SELECT your fields list
FROM Table1 INNER JOIN Table2 ON Table1.PhoneNum = Table2.PhoneNum

But, are all the phone numbers stored with the same format ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[leadpro]

Almost there...

my phone number table is phoneonly with field HomePhone
table with fields including HomePhone field is HomeOwner

Please show me the INNER JOIN with this in mind.

Thanks so much!

LeadPro

 

[leadpro]

BTW...Yes...all stored as text

 

[PHV]

all stored as text
In a consistent manner ?

SELECT A.*, B.*
FROM phoneonly A INNER JOIN HomeOwner ON A.HomePhone = B.HomePhone

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[leadpro]

hmm...consistent...sorted ascending in both tables...still picking up 2 records when they match. Just need unique full records in a query result that matches the phoneonly table.

LeadPro...thanks!

 

[PHV]

Just need unique full records
SELECT DISTINCT your fields list
FROM phoneonly A INNER JOIN HomeOwner ON A.HomePhone = B.HomePhone

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244