splitting tables and removing duplicates

[raybas]

I have a table that has names and addresses. I have eleminated duplicates of names based on the listed compnay and address but now I want to split the table into a table with names and a table with just the company and addresses. I have done the split but I want to be able to get rid of duplicate listings for the company and address. This would seem not to be a big issue however if I do remove duplicates from this table and id's it deletes will be reflected in the names table. The recording is missing in the address table so the name table only shows a number that now reflects a record deleted in the address table. Is there a way around this or will I need to just manually update those records with the now missing address link?

Thank you,

Raymond Basaldua
Computer Systems Support
Arizona State University

 

[RickSpr]

If you still have a table that contains both the names and the addresses, you can create an Update query that will fill in the record numbers for you:

Let's say you have the original table, called NameAndAddress, with fields Name and Address in it. Let's further say that you've already created a table called Names with a Name field, and a table called Addresses with an Address field and an AddrNbr field. AddrNbr is the primary key for this latter table, and its data type is set to Autonumber (it could be Long Integer as well, but in that case you'll have to type in unique numbers manually for all the records.)

To set up the update query, create a new query and add the Names, NameAndAddress, and Addresses tables to it. Create joins between Names and NameAndAddress based on the Name field, and between NameAndAddress and Addresses based on the Address field. Then change the query type to Update. Drag the Names.AddrNbr field to the query grid. In the Update To: line for that field, enter [Addresses].[AddrNbr].

Then all you need to do is run the query, spot check the results for accuracy, and you're done!

------------

If you no longer have the names and addresses together in one table, I'm afraid the news is bad. There is no longer any association between a particular name and the corresponding address, so you'll have to create those associations manually by filling in the AddrNbr fields in the Names table. Rick Sprague

 

[johpje]

Here's how you can remove the duplicates if you already deleted the original table.

This example works with folowing tables:
Name(name (Text), addressid (Number))
Address(id (Number), address (Text), company (Text))

Example DATA:
Name Address
John, 1 1, Add1, Comp1
Peter, 2 2, Add2, Comp2
Rick, 3 3, Add1, Comp1
(where Rick should link to address 1 and address 3 should be deleted)

now make the following query (Query):
SELECT a.id, Min(b.id) AS use
FROM Address AS a, Address AS b
WHERE a.address=b.address AND a.company=b.company
GROUP BY a.address, a.company, a.id;

Note that the address and company fiels must be identical. however this should be no problem as the data comes from the same original table.

Now create a new table
Name2(name (Text), addressid (Number))

And run following Insert:
"INSERT INTO Name2 SELECT a.name, use FROM Name a, Query b WHERE a.addressid = b.id"

This should fill the new table with correct data.

Example Data after insert:
Name2 Address
John, 1 1, Add1, Comp1
Peter, 2 2, Add2, Comp2
Rick, 1 3, Add1, Comp1

Now replace Name with Name2 and you can delete duplicate records in Address.

 

[raybas]

RickSpr I have tried your soultion and it updates records but I think what is happening is the oppiste of what I need. I have the origial tabes and the split tables. I need to remove duplicate address while still having the names know which address they belon to. If this will be easier to do in the original table that will work as well... Maybe I have ocnfused myslef but I am no where closer than I was before.

johpje I do no even know how I would create that query based on the info you gave me. I have some knowledge of access but am merely doing this because I have been asked to figure it out. Would I create this query in a design view or is what you have stated to be done in code?

Thank you both for your help.

 

[johpje]

Raybas,

Its good to hear the problem's solved.

As for your last question: the query should be made as a query in Access. In design view you can right-click on the part where the tables are listed. If you select SQL from the menu, you can enter a SQL statement by yourself.

Regards,
Johpje