Appending records and avoiding duplicate entries

[marcfl]

Hi,

I'm trying to copy the address detils from my customers table into my shipping table, i created an append query that transfers the addresses to a new table called companies where i made the address field "No Duplicates" so i only get a list of the companies. I then made another append query to transfer that information to the shipping table, but when i use it it copies all the entries even if theyre already in there.. How can i append only the information that is not in the shipping table?

Cheers,

Marc.

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SeeThru]

You need some kind of key - a field that is unique for each record.

Then, make sure the field where the key goes is seet either as 'primary key' or a Inexed(no duplicates)

When you import the data, duplicates will not be imported.

Addresses do NOT make good primary keys, as variations in spelling and punctuation prevent matches. A supplier ref, a ZIP code with something else, or a phone number all make quite good primary keys.

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[marcfl]

my Companies table doesnt have a primary key, but the shipping table does.

Company table:

CompanyName
Address
City
Region
PostalCode
Country
Notes

Shipping Table:

ShipmentID (PK)
ShipName
ShipAddress
ShipCity
ShipRegion
ShipPostalCode
ShipCountry
Notes

sql that transfers the data from Companies To Shipping :

INSERT INTO Shipping ( ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry, Notes )
SELECT DISTINCT Companies.CompanyName, Companies.Address, Companies.City, Companies.Region, Companies.PostalCode, Companies.Country, Companies.Notes
FROM Companies
ORDER BY Companies.CompanyName;

Cheers,

Marc.

 

[PHV]

What about this ?

INSERT INTO Shipping (ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry, Notes)
SELECT CompanyName, Address, City, Region, PostalCode, Country, Notes
FROM Companies
WHERE CompanyName Not In (SELECT ShipName FROM Shipping)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[marcfl]

haha nice one, been trying to do this for a couple days now was getting really frustrated.