prevent redundacy 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.

 

[Jeff Boyce]

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

 

[Stefan Hoffmann]

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.

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

 

[Amod]

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]

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

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

 

[Jeff Boyce]

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

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]

I tried doing that but still duplicate records gets appended.

 

[dandydan9]

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.

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

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

 

[JulieS]

Amod,

Were you ever able to figure this out?

Thanks!