If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Append query without duplicate rows
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. |
#2
|
|||
|
|||
Append query without duplicate rows
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 wrote: 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. |
#3
|
|||
|
|||
Append query without duplicate rows
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 On Dec 9, 2:23*pm, John Spencer wrote: 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 wrote: 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.- Hide quoted text - - Show quoted text - |
#4
|
|||
|
|||
Append query without duplicate rows
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/For...eries/200912/1 |
#5
|
|||
|
|||
Append query without duplicate rows
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200912/1 |
#6
|
|||
|
|||
Append query without duplicate rows
On Dec 9, 6:06*pm, "KenSheridan via AccessMonster.com" u51882@uwe
wrote: 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 -- Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1 Thanks very much, that worked perfectly. It's good to know about the EXISTS condition! |
Thread Tools | |
Display Modes | |
|
|