parsing a field for a join

  • Thread starter Thread starter missujerr_junk
  • Start date Start date
M

missujerr_junk

Hi, this seems like it should be easy but I have a newborn and no
sleep :)

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!!
 
hi,

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
Click to expand...
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 <--
 
Thanks for replying!! It is stored by the question ID - now, how do I
get the questions associated with each question id? I need to print a
report for each candidate with the questions to ask. Thanks!

hi,

[email protected] said:
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
Click to expand...

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 <--
Click to expand...
 
You missed Stefan's point.

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

Thanks for replying!! It is stored by the question ID - now, how do I
get the questions associated with each question id? I need to print a
report for each candidate with the questions to ask. Thanks!

hi,

[email protected] said:
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
Click to expand...

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 <--
Click to expand...
 
ohhhh, I see, that makes perfect sense now! Thanks!

I am not sure how to write the code to do that insert though, eep
 
Thanks!

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
 
hi,

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 <--
 
Back
Top