Insert Query skipping duplicate values

[C Hayes]

I have two tables
-Individuals
-Participation

Participation table has two fields
IndividualID - related to the Individual table
ParticipationCode

it's a one to many relationship where the participation table has a
complex/dual primary key (the ID and the Code)

I need to run an insert query that will insert a participation code for an
individual ONLY if they do not have that code already (because of the
complex/dual key)

any ideas?

Thanks in advance.

Chris

 

[John Spencer]

Well, if you have made the primary key those two fields, then you won't
be able to add any existing combination - you will get an error.

Since you didn't post the SQL you wish to use - I will post one method
of avoiding the error.


Insert into Participation (IndividualID, ParticipationCode)
Values(123,"a24")
WHERE NOT EXISTS
(SELECT *
FROM Participation
WHERE IndividualId = 123
and ParticipationID ="a24")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Dale Fye]

John,

Would you actually use this method, or would you just run an Execute method,
and trap for the error that would occur if you attempted to insert a
duplicate into a set of fields with a unique index?

Dale

 

[John Spencer]

Depends on what day it is and where the data is stored (Jet, MS SQL,
Oracle) and how many records I was trying to insert and if the failure
to insert some records out of a group of records made a difference.

In other words, it depends.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================