Restrict Append to Non-Duplicated Records on Concatenated Fields

[Dana F. Brewer]

I have a database that I will be merging new records into from time to time.
I want to only append records where data matching address+address2 (highly
unlikely that I will have same address+address2 in different cities) from the
source database (new data to be imported) does not exist in the existing
database.

SourceDB:
Name
Address
Address2
City
I can have a resultant combined field in the SourceDB (i.e., FullAddress or
something) but I do not want to add any fields to the ExistingDB if at all
possible.


ExistingDB:
Name
Address
Address2
City

Thanks!

 

[John W. Vinson]

I have a database that I will be merging new records into from time to time.
I want to only append records where data matching address+address2 (highly
unlikely that I will have same address+address2 in different cities) from the
source database (new data to be imported) does not exist in the existing
database.

SourceDB:
Name
Address
Address2
City
I can have a resultant combined field in the SourceDB (i.e., FullAddress or
something) but I do not want to add any fields to the ExistingDB if at all
possible.


ExistingDB:
Name
Address
Address2
City

Thanks!

You can use an "unmatched query" to do this:

INSERT INTO ExistingDB([Name], Address, Address2, City)
SELECT SourceDB.[Name], SourceDB.Address, SourceDB.Address2, SourceDB.City
FROM SourceDB LEFT JOIN ExistingDB
ON SourceDB.Address = ExistingDB.Address
AND SourceDB.Address2 = ExistingDB.Address2
WHERE ExistingDB.Address IS NULL
AND ExistingDB.Address2 IS NULL;

Note that Name is a reserved Name, since a table already has a Name property,
as does a field...

 

[Dana F. Brewer]

You are awesome John! I left out one detail. I am including the CityID code
from yet another table. I tried to add another join statement to the mix but
I get an error. Can you see what is wrong with my syntax or placement please?

INSERT INTO tblSurveyData ( FullName, Address, Address2, CityID, Telephone )
SELECT tblImportFixedUp.FullName, tblImportFixedUp.Address,
tblImportFixedUp.Address2, tblCity.CityID, tblImportFixedUp.Telephone
FROM tblCity INNER JOIN (tblImportFixedUp LEFT JOIN tblSurveyData ON
(tblImportFixedUp.Address2 = tblSurveyData.Address2) AND
(tblImportFixedUp.Address = tblSurveyData.Address)) ON tblCity.CityID =
tblSurveyData.CityID
WHERE (((tblSurveyData.Address) Is Null) AND ((tblSurveyData.Address2) Is
Null));

....Dana ;-)

I have a database that I will be merging new records into from time to time.
I want to only append records where data matching address+address2 (highly
unlikely that I will have same address+address2 in different cities) from the
source database (new data to be imported) does not exist in the existing
database.

SourceDB:
Name
Address
Address2
City
I can have a resultant combined field in the SourceDB (i.e., FullAddress or
something) but I do not want to add any fields to the ExistingDB if at all
possible.


ExistingDB:
Name
Address
Address2
City

Thanks!

You can use an "unmatched query" to do this:

INSERT INTO ExistingDB([Name], Address, Address2, City)
SELECT SourceDB.[Name], SourceDB.Address, SourceDB.Address2, SourceDB.City
FROM SourceDB LEFT JOIN ExistingDB
ON SourceDB.Address = ExistingDB.Address
AND SourceDB.Address2 = ExistingDB.Address2
WHERE ExistingDB.Address IS NULL
AND ExistingDB.Address2 IS NULL;

Note that Name is a reserved Name, since a table already has a Name property,
as does a field...