Remove duplicates and mark field in master record

[parksjsn]

I have a database with 15 tables(aprox) I have combined all tables into one tabe and assigned a separate primary key prefix for each original table. Is it possible to create a query that would search for duplicates and remove duplicates based on name and address and also add a character to a field that represents additional tables the master record was in prior to deletion?
for example if I had the same record in table 1,2 and 3, the query would delete the records from table 2 and three and place a 1 in a field representing table 2 and also a 1 in a field representing table 3? This way I can query all the records with a 1 in field 27(for example) and pull out my original table records?

 

[SUnderwood]

Hi, I hope I can help.

If you don't mind can you send to me either a zipped copy of the database before its been merged, or a definition of the tables.

Also can I ask, what is the purpose of joining the tables in such a way? What is the desired final outcome you want to achieve?

Many thanks
Sean

 

[parksjsn]

The Database is to large to send very quickly,the tables are essentially the same, they are for a mailing list. The fields are the same in all tables, fname,lname,add1,add2,city,st,zip,zip+4 etc. The list is owned by one company but the tables are specific to branch locations. The problem is that duplicates are contained between tables and some branches use tables from other branches. I would like to be able to remove all duplicates and mark the single unique record indicating what other tables that record has been deleted from. This way I can run a query that pulls all records with a key code for the tables I need as well as all records that have been deleted from those tables but are contained in the database under a different keycode.

 

[SUnderwood]

My friend, i think you need to go back to the start and redesign your tables. You may have to put up with duplicates of entries across company branches if, from the point of view of the company branch, the individual "works" there - eg John Smith work for EDF Romania branch, and EDF France branch, so has two entries.

OR (*brainwave*)

You could be more clever and use three tables
1 - Company and branch info
2 - Individual details
3 - Company Individuals

So that
(1) would contains Company-Branch name and address
(2) would contains individual name and tels
(3) contains keys of company-branch and individuals

How to get there?

1 - Add to each table fields "company name", and "branch Name" and fill them in accordingly with a update query
2 - Create table (1) and populate it with all company-branch details, there are only 15 so it doesn't take long
3 - Create tables (2) and (3)
4 - In this order and one mailing list table at a time
4.1 - Populate table (2) with individual details from a mailing list table
4.2 - Populate table (3) with the individuals PK and the mailing list tables company-branch PK from table (1)
4.3 - Return 4.1-3 until all 15 tables have been converted

When performing 4.1 duplicate entries will be weeded out by Access because duplicate individual records will not be made. But ensure this is desireable (two different John Smiths may have the same name and one will be wiped out by step 4.1)

Try that and see what happens.

Sean

Business and Data Analyst
Database & Web Applications
International Applications Development
VB,VBA,ASP,SQL,Java,Pascal,ADA,SSADM,UML
Interested parties please email: seanunderwood1@hotmail.com

 

[pdldavis]

Hi,

Well, table design aside you could eliminate the dups pretty quickly by doing something like:

Create one field that concatenates the name and address - something to make the records more unique - [Sears - Chicago].

Create one or more fields to store prior table names.

Make a copy of the table - definition only.
Assign the primary key in the copied table to the concatenated field.
Append the original table to the copied table. It will append only unique companies - No duplicates.

The other part:

When you bring this tables in you can first append the table and then use an update query to store your table numbers in the new table.
Either that, or create a new field in your older tables and populate that field with your table number and append it directly in.

Hope that helps.

 

[parksjsn]

I think I am confusing the subject. I have 15 tables all identical in layout. They all contain Mailing addresses with a layout like this.
List_ID,Fname,Lname,Title,Firm,Address,city,st,zip,Phone,Email
Some records might have a firm, others wont same with title email and phone.
I want to create a database comprised of all unique records and document every list a duplicate of that record was deleted from. So my new DB will be laid out like this..
List_ID,Fname,Lname,Title,Firm,Address,city,st,zip,phone,email,list1,list2,list3 etc.. list15

then a record would look like this
p123,Joe,schmoe,,,123 anywhere,city,st,zip,,,x,,,x,,,x
with the x representing me removing duplicates from list 1,4 and 7. I dont really care which record is kept and which one is deleted just so the list id fields representing the duplicates is appended to the record that remains. I have other software I can depend on for the removal of duplicates and I can output all the dupes into a seperate file but I cant figure out how to append the list data without doing it manually. If I import one list at a time is there a way to write a query that says if list1 name,address,zip=list2 name,address,zip then delete list2 record and append an x in field 15 of list1 record?