Preventing duplicate entries with an Append Query

[OEB]

I have an append query that is appending records from a table that I have
imported from a spreadsheet to a table that exists in the database. The
existing table has many similar records (name of project) in it. I want to
append the existing table with the imported table only if the names are
different to the existing table.

In my design view of my append query, what criteria do I enter to accomplish
this? I tried [Project Title] <> [ProjectName] but that didn't work.

Thanks.

 

[John Spencer (MVP)]

Base your append query on a not In query.

INSERT INTO YourTable [Project Title]
SELECT ProjectName
FROM ImportTable LEFT JOIN YourTable
ON ImportTable.ProjectName = YourTable.[Project Title]
WHERE YourTable.[Project Title] is null

If you don't know how to do this use the unmatched query wizard to find
records in your import table that are not in your destination table. Then use
the Unmatched query as the source the insert.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

In your append query design view add the table that you are appending to.

Click in the input table fields that you do not want to duplicate and drag
to the 'append to' table field that matches. This creates a connecting line.

Double click on the connecting line to open a popup window with three
choses. Select the one that says all records from the input table. Close
popup.

Drag the 'append to' table field to the FIELD row of the grid. Click on the
little square of the SHOW: row to remove the check mark.
In the CRITERIA row type --
Is Null

This will append only records where the content of the field does not exist
in the 'append to' table.

 

[OEB]

Thanks! That worked great.

In your append query design view add the table that you are appending to.

Click in the input table fields that you do not want to duplicate and drag
to the 'append to' table field that matches. This creates a connecting line.

Double click on the connecting line to open a popup window with three
choses. Select the one that says all records from the input table. Close
popup.

Drag the 'append to' table field to the FIELD row of the grid. Click on the
little square of the SHOW: row to remove the check mark.
In the CRITERIA row type --
Is Null

This will append only records where the content of the field does not exist
in the 'append to' table.

--
KARL DEWEY
Build a little - Test a little

I have an append query that is appending records from a table that I have
imported from a spreadsheet to a table that exists in the database. The
existing table has many similar records (name of project) in it. I want to
append the existing table with the imported table only if the names are
different to the existing table.

In my design view of my append query, what criteria do I enter to accomplish
this? I tried [Project Title] <> [ProjectName] but that didn't work.

Thanks.