I have a form with a listbox where a user can select multiple
questions. The questions come from a table called
[CompetenciesAndQuestions] which has an autonumber primary key for
each question. When a user saves their question selection on the form,
it saves to another table in one field. It formats like:
6,8,10,12,14
(meaning they selected questions 6, 8, 10, etc.). Other data is also
saved regarding who will be asked these questions. This is a table
called [CandidateQuestons]
Now, I need to write a query which takes each row in
[CandidateQuestons] and joins back to the [CompetenciesAndQuestions]
table so that it gives me each candidate and the questions selected
for asking. How can I do this?
Appreciate any help!!
missuje...@hotmail.com wrote:
> I have a form with a listbox where a user can select multiple
> questions. The questions come from a table called
> [CompetenciesAndQuestions] which has an autonumber primary key for
> each question. When a user saves their question selection on the form,
> it saves to another table in one field. It formats like:
> 6,8,10,12,14
You shouldn't do that. It violates the rules of normalization. Instead
of a table like
[CandidateQuestons]: Candidate_ID(PK), Questions
store it as
[CandidateQuestons]: Candidate_ID(PK), Question_ID(PK)
You need the same effort to store it in the second form as in the first
as long as you don't use multi-value fields.
mfG
--> stefan <--
On Nov 11, 10:43 am, Stefan Hoffmann <ste...@ste5an.de> wrote:
> hi,
You indicate that your table structure contains two fields (Candidate and
Questions), and that the values you get from your listbox are being
concatenated into the Questions field as a string "6, 8, 10, 12, 14 ".
Stefan's point is that you should only have one piece of information in each
field of each record (but you are storing multiple values). Instead of a
single record, you should have 5 records. This is actually just as easy to
do as concatenating the values from the list box, but instead of
concatenating, you just execute an insert query for each item selected in
the list.
Candidate_ID Question_ID
1 6
1 8
1 10
1 12
1 14
With this structure, you could then join your CandidateQuestions table to
your questions table on the Question ID to get the actual questions for each
candidate.
Doing it they way you have, you will have to parse the Question field into
its various values, store that data, and then join it to the questions table
(not very efficient, is it?).
HTH
Dale
<missuje...@hotmail.com> wrote in message
news:3da811b5-7c6c-48c7...@n33g2000pri.googlegroups.com...
I am not sure how to write the code to do that insert though, eep
<missuje...@hotmail.com> wrote in message
news:a9f0148a-8db3-42c1...@o40g2000prn.googlegroups.com...
Private Sub testmultiselect_Click()
Dim oItem As Variant
Dim sTemp As String
Dim iCount As Integer
iCount = 0
If Me!QuestionsList.ItemsSelected.Count <> 0 Then
For Each oItem In Me!QuestionsList.ItemsSelected
If iCount = 0 Then
sTemp = sTemp & Me!QuestionsList.ItemData(oItem)
iCount = iCount + 1
Else
sTemp = sTemp & "," & Me!QuestionsList.ItemData(oItem)
iCount = iCount + 1
End If
Next oItem
Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If
Me!mySelections.Value = sTemp
End Sub
something like:
Private Sub testmultiselect_Click()
On Local Error GoTo LocalError
Dim db As DAO.Database
Dim SQL As String
Dim oItem As Variant
Set db = CurrentDb
SQL = "DELETE FROM CandidateQuestons " & _
"WHERE Candidate_ID = " & Me![Candidate_ID]
db.Execute SQL, dbFailOnError
If Me!QuestionsList.ItemsSelected.Count <> 0 Then
For Each oItem In Me!QuestionsList.ItemsSelected
SQL = "INSERT INTO CandidateQuestons(Candidate_ID, Question_ID) " & _
"VALUES (" & Me![Candidate_ID] & ", " & _
Me!QuestionsList.ItemData(oItem) & ")"
db.Execute SQL, dbFailOnError
Next oItem
End If
Set db = Nothing
Exit Sub
LocalError:
' do error handling...
End Sub
mfG
--> stefan <--