prevent redundacy in append query

  • Thread starter Thread starter Amod
  • Start date Start date
A

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.
 
Amod

One approach would be to pick out the fields/columns for which you do not
wish duplicate records, then use the table design view to set a Unique index
on that set of fields.

Access can still attempt to append, but will reject the subsequent
(non-Unique) copies.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
hi 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.
Click to expand...
You need a candidate key to identify those records. In the worst case
you have to compare each field (without identity), e.g.:

INSERT INTO destinationTable (field1, ..., fieldN)
SELECT field1, ..., fieldN
FROM sourceTable s
WHERE NOT EXISTS(
SELECT *
FROM destinationTable d
WHERE s.field1 = d.field1 ...
AND s.fieldN = d.fieldN
)



mfG
--> stefan <--
 
Thanks Stefan,
I am trying the same syntax as you did but still records get
duplicated...here is my query where testTbl is source table & New testTbl is
Destination table.

INSERT INTO [New testTbl] ( ID, ID1, [First], [Last], Age, DOB, Employed,
Experience )
SELECT testTbl.ID, testTbl.ID1, testTbl.First, testTbl.Last, testTbl.Age,
testTbl.DOB, testTbl.Employed, testTbl.Experience
FROM testTbl
WHERE NOT Exists (SELECT * FROM [New testTbl] WHERE [testTbl].[ID] = [NEW
testTbl].[ID] AND [testTbl].[ID1] = [NEW testTbl].[ID1]);
 
Jeff, I tried doing that even:) but I can not make the design change because
the destination table is not a normal access table but a linked Sharepoint
table.

--
Amod Goyal
IT System Developer
NSK Precision America


Jeff Boyce said:
Amod

One approach would be to pick out the fields/columns for which you do not
wish duplicate records, then use the table design view to set a Unique index
on that set of fields.

Access can still attempt to append, but will reject the subsequent
(non-Unique) copies.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Amod

If you don't have a way to modify the table structure, the only other way
I'm familiar with is what Stefan H. suggested.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Amod said:
Jeff, I tried doing that even:) but I can not make the design change
because
the destination table is not a normal access table but a linked Sharepoint
table.
Click to expand...
 
If ID and ID1 are both autonumber fields, that won't work since the
INSERT will result in new autonumber rows that don't match the existing
autonumber rows.

Instead, you need to join on static data columns like [First], [Last], Age,
DOB.

Amod said:
Thanks Stefan,
I am trying the same syntax as you did but still records get
duplicated...here is my query where testTbl is source table & New testTbl is
Destination table.

INSERT INTO [New testTbl] ( ID, ID1, [First], [Last], Age, DOB, Employed,
Experience )
SELECT testTbl.ID, testTbl.ID1, testTbl.First, testTbl.Last, testTbl.Age,
testTbl.DOB, testTbl.Employed, testTbl.Experience
FROM testTbl
WHERE NOT Exists (SELECT * FROM [New testTbl] WHERE [testTbl].[ID] = [NEW
testTbl].[ID] AND [testTbl].[ID1] = [NEW testTbl].[ID1]);

--
Amod Goyal
IT System Developer
NSK Precision America


Stefan Hoffmann said:
hi Amod,

You need a candidate key to identify those records. In the worst case
you have to compare each field (without identity), e.g.:

INSERT INTO destinationTable (field1, ..., fieldN)
SELECT field1, ..., fieldN
FROM sourceTable s
WHERE NOT EXISTS(
SELECT *
FROM destinationTable d
WHERE s.field1 = d.field1 ...
AND s.fieldN = d.fieldN
)



mfG
--> stefan <--
Click to expand...
Click to expand...
 
Back
Top