Append query without duplicate rows

[Eric]

I am trying to design an append query that will not add duplicate
data. In my searching, all the information I have been able to find
has been in regards to finding duplicates in a single column, but I
need a more conditional query.

It is appending records to a joining table (t_SessionLearner), with
three fields (SessionLearnerID, SessionID, LearnerID). Currently, the
user can repeatedly add the same pair of SessionID and LearnerID
repeatedly. I want the query to avoid appending the record if that
pairing of SessionID and LearnerID already exist. Each SessionID can
be in the table multiple times, and each LearnerID can be in the table
multiple times. Just not together.

Any assistance is greatly appreciated.

 

[John Spencer]

The easiest way to do this is to add a multi-field unique index to
t_SessionLearner. This will generate a message that x records could not be added.


To create a multiple field unique index (Compound index)
--Open up the table in design mode
--Select View: Index from the menu
--Enter a name for the iIndex in first row under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
(Do NOT skip rows, do NOT enter the index name again)
--Continue moving down and selecting fieldnames until all needed fields are
included.
--Close the index window and close and save the table

You can also restrict this in a query. Post the SQL of your current query and
someone can suggest the needed modification.

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

 

[Eric]

For some reason I couldn't get the index method to work properly. I
need to be able to have duplicate values in a column (as long as the
values in the other column are different). It's the combination of
the two that needs to be unique. For instance:

SessionLearner ID - 1
SessionID - 6
PersonID - 1156

SessionLearner ID - 2
SessionID - 6
PersonID - 1276

SessionLearner ID - 3
SessionID - 7
PersonID - 1276

Here is the SQL I am using (I am defining the query in vba):


"INSERT INTO " & endTable & " ( PeopleID, SessionID ) " & _
"SELECT t_PeopleTemp.PeopleID, " & Forms.f_SessionEdit.SessionID & _
" FROM t_PeopleTemp " & _
"WHERE (((t_PeopleTemp.[Include In List?])=True));"

This works just fine except for allowing duplicate records.

Thanks for your response,

Eric

 

[Eric]

Sorry about the convoluted grammar in the first paragraph.  I started saying
one thing, and then changed tack to another in mid sentence!

Ken Sheridan
Stafford, England

Thanks very much, that worked perfectly. It's good to know about the
EXISTS condition!