Need Help Bulding a Complex QBF using list boxes

[Simply Devine]

I built form to store data in a particular table from this website
http://www.daiglenet.com/MSAccess.htm , (The Select Records Download), I
need help to build a search form using a multi select list box that will have
an “and†criteria. For Example:



Student Classes

John Math
John English
John Spanish
Wendy Math
Wendy English





If I run a query and want all the students that take math and spanish, I
would only get the result of John back.



Thank you,

 

[Allen Browne]

Your (correct) design means that John's Math class and John's Spanish class
are different records. You will therefore need to use different queries to
test the multiple records.

Simplest way to do that is to add subqueries to test if the subsequent
records also exist. This kind of thing:

SELECT Student FROM Table1
WHERE Classes = 'Math'
AND EXISTS (SELECT Student FROM Table1 AS Dupe
WHERE Dupe.Student = Table1.Student
AND Dupe.Classes = 'Spanish');

If subqueries are new, here's a starting point:
http://allenbrowne.com/subquery-01.html

 

[Douglas J. Steele]

Another approach (albeit less efficient) is

SELECT Student FROM Table1
WHERE Classes IN ('Math', 'Spanish')
GROUP BY Student
HAVING Count(*) = 2

 

[Allen Browne]

Doug, that's a great suggestion.

It could be *more* efficient (not to mention easier to build) where there
are more than 2 subjects selected in the list box.

 

[Douglas J. Steele]

It's definitely easier when you want to build the query based on a list box.

Dim lngCount As Long
Dim strSQL As String
Dim strSubjects As String
Dim varSelected As Variant

lngCount = 0
With Me!MyForm!MyListBox
For Each varSelected in .ItemsSelected
lngCount = lngCount + 1
strSubjects = strSubjects & "'" & .ItemData(varSelected) & "', "
Next varSelected
End With
If Len(strSubjects) > 0 Then
strSubjects = Left$(strSubjects, Len(strSubjects) - 2)
strSQL = "SELECT Student FROM Table1 " & _
"WHERE Classes IN (" & strSubjects & ") " & _
"GROUP BY Student " & _
"HAVING Count(*) = " & lngCount
Else
MsgBox "Nothing Selected"
End If

 

[Simply Devine]

Thank you,

I will try both tonight

It's definitely easier when you want to build the query based on a list box.

Dim lngCount As Long
Dim strSQL As String
Dim strSubjects As String
Dim varSelected As Variant

lngCount = 0
With Me!MyForm!MyListBox
For Each varSelected in .ItemsSelected
lngCount = lngCount + 1
strSubjects = strSubjects & "'" & .ItemData(varSelected) & "', "
Next varSelected
End With
If Len(strSubjects) > 0 Then
strSubjects = Left$(strSubjects, Len(strSubjects) - 2)
strSQL = "SELECT Student FROM Table1 " & _
"WHERE Classes IN (" & strSubjects & ") " & _
"GROUP BY Student " & _
"HAVING Count(*) = " & lngCount
Else
MsgBox "Nothing Selected"
End If