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

> > Any assistance is greatly appreciated.- Hide quoted text -
>
> - Show quoted text -

[KenSheridan via AccessMonster.com]

Eric:

The easiest way to do this is simply delete the SessionLearner ID column from
the table and make SessionID and PersonID the composite primary key of the
table. You don't need The 'surrogate' SessionLearner ID primary key is
unnecessary in this case as the other two columns are a 'candidate key'.

You create the composite primary key in table design view by Ctrl-clicking
on each field, making sure you click on the field selector (the little grey
rectangle to the left of the field name), then right-click and select
'Primary key' from the shortcut menu.

To ensure that your SQL statement does not attempt to insert a duplicate row
you can amend it like this:

Dim strSQL As String

strSQL = _

"INSERT INTO " & endTable & " ( PeopleID, SessionID ) " & _

"SELECT PeopleID, " & Forms.f_SessionEdit.SessionID & _
" FROM t_PeopleTemp " & _
"WHERE [Include In List?] = TRUE " & _
"AND NOT EXISTS(" & _
"SELECT * " & _
"FROM " & endTable & _
" WHERE " & endTable & ".PeopleID = " & _
"t_PeopleTemp.PeopleID " & _
"AND " & endTable & ".SessionID = " & _
Forms.f_SessionEdit.SessionID & ")"

Even though this should prevent the insertion of duplicate rows by this SQL
statement you should nevertheless still amend the table design to prevent
this in any other way.

Ken Sheridan
Stafford, England

Eric wrote:
>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
>

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

>[quoted text clipped - 35 lines]

>>
>> - Show quoted text -

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1

[KenSheridan via AccessMonster.com]

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

[Eric]

On Dec 9, 6:06 pm, "KenSheridan via AccessMonster.com" <u51882@uwe>
wrote:

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