prevent redundancy of records in append query

[Amod]

Can anyone help me...how to prevent redundancy of records in append query?
When I run my append query more than once, it adds all the old records as
well.
Thanks.

 

[Michael Gramelspacher]

Can anyone help me...how to prevent redundancy of records in append query?
When I run my append query more than once, it adds all the old records as
well.
Thanks.

If I run an append query more than once, it adds no records. It is supposed to
be impossible to add duplicate records to a table.

Your problem usually happens when there is an autonumber primary key with no
unique index on the natural key columns.

Maybe I just do not understand your problem.

 

[Amod]

Thanks Michael...yes you are right there is a auto number field but I can not
change the design. it does have a priomary key as well, it is working with
other table which just has a primary key. What should I do for auto number? I
also tried putting the SQL criterion but that also didnt help.

 

[Michael Gramelspacher]

Thanks Michael...yes you are right there is a auto number field but I can not
change the design. it does have a priomary key as well, it is working with
other table which just has a primary key. What should I do for auto number? I
also tried putting the SQL criterion but that also didnt help.

This is example is not original, but consider this table:

CREATE TABLE Ingredients (
ingredient_id IDENTITY(1,1) NOT NULL PRIMARY KEY,
ingredient_name VARCHAR (20) NOT NULL,
UNIQUE (ingredient_name));

with this data:

INSERT INTO Ingredients (ingredient_name) VALUES ('Potato');
INSERT INTO Ingredients (ingredient_name) VALUES ('Carrot');

now insert a new row:

INSERT INTO ingredients
(ingredient_name)
SELECT DISTINCT 'Tomato' AS Expr1
FROM Ingredients
WHERE NOT EXISTS (SELECT *
FROM ingredients
WHERE ingredient_name = 'Tomato');
If I run this query again, it tells me it could not insert the row. That is
good.

You need to determine which columns taken together make a row unique. Your
insert query will check that there is not already a row with those column values
and reject inserting that row. In other words, don't insert the row if it is
already there.

 

[Michael Gramelspacher]

This is example is not original, but consider this table:

CREATE TABLE Ingredients (
ingredient_id IDENTITY(1,1) NOT NULL PRIMARY KEY,
ingredient_name VARCHAR (20) NOT NULL,
UNIQUE (ingredient_name));

with this data:

INSERT INTO Ingredients (ingredient_name) VALUES ('Potato');
INSERT INTO Ingredients (ingredient_name) VALUES ('Carrot');

now insert a new row:

INSERT INTO ingredients
(ingredient_name)
SELECT DISTINCT 'Tomato' AS Expr1
FROM Ingredients
WHERE NOT EXISTS (SELECT *
FROM ingredients
WHERE ingredient_name = 'Tomato');
If I run this query again, it tells me it could not insert the row. That is
good.

You need to determine which columns taken together make a row unique. Your
insert query will check that there is not already a row with those column values
and reject inserting that row. In other words, don't insert the row if it is
already there.

Please disregard this query. It was only the unique constraint making it seem
to work for me. Sorry.

 

[John W. Vinson/MVP]

Can anyone help me...how to prevent redundancy of records in append query?
When I run my append query more than once, it adds all the old records as
well.
Thanks.

Please post a bit more information about your table and query. What is
the Primary Key of the table? Does it have any other unique indexes
(maybe it needs one)? Could you post the SQL of the append query?

You may be able to modify the append query to include a Join to the
target table, using the "unmatched query wizard" to select only those
records in the source table which do not exist in the target table.
Post some information about the two tables if you need help setting
this up.

 

[Michael Gramelspacher]

Please disregard this query. It was only the unique constraint making it seem
to work for me. Sorry.

Hopefully this will work. There is a subprogram, which will create two tables,
People and People2. People has 3 rows, and People2 has 2 rows, one of which
duplicates a row in People. Note that neither table has a primary key nor a
unique index.

Query1 inserts a new row into People. Query2 inserts only one new row into
People, because the other row would be a duplicate.

Sub CreateTables()
With CurrentProject.Connection

..Execute _
" CREATE TABLE People (" & _
" first_name VARCHAR (20)," & _
" last_name VARCHAR (20)," & _
" birth_date DATETIME," & _
" zip_code CHAR(5));"

..Execute "INSERT INTO People VALUES ('James', 'Nelson', #6/6/1962#, '47588');"
..Execute "INSERT INTO People VALUES ('Harvey', 'Little', #11/19/1968#,'47588');"
..Execute "INSERT INTO People VALUES ('Mary', 'Greer', #5/2/1973#,'47519');"

..Execute _
" CREATE TABLE People2 (" & _
" first_name VARCHAR (20)," & _
" last_name VARCHAR (20)," & _
" birth_date DATETIME," & _
" zip_code CHAR(5));"

..Execute "INSERT INTO People2 VALUES ('Philip', 'Schulz', #4/10/1978#,'47519');"
..Execute "INSERT INTO People2 VALUES ('Mary', 'Greer', #5/2/1973#,'47519');"


..Execute "CREATE TABLE Dummy (dummy_id INT NOT NULL PRIMARY KEY);"

..Execute "INSERT INTO Dummy VALUES (1);"

End With
End Sub

Query1: (inserts literal values into People table)

INSERT INTO People (first_name, last_name, birth_date, zip_code)
SELECT "Ralph", "Schmitt", "12/12/1979", "47518"
FROM Dummy
WHERE NOT EXISTS (SELECT *
FROM People
WHERE first_name = "Ralph"
AND last_name = "Schmitt"
AND birth_date =#12/12/1979#
AND zip_code = "47518");

Query2: (inserts values from People2 into People table)

INSERT INTO People ( first_name, last_name, birth_date, zip_code )
SELECT People2.first_name, People2.last_name, People2.birth_date,
People2.zip_code
FROM People2
WHERE NOT EXISTS (SELECT *
FROM People
WHERE People.first_name = People2.first_name
AND People.last_name = People2.last_name
AND People.birth_date = People2.birth_date
AND People.zip_code = People2.zip_code);