Query/AND operator/Passing values of many list boxes to a query

[Denis]

This post is submitted in two related parts:

Part One -
Two tables: tblPersonnel has a one-to-many relationship
with tblQualifications. I want to create a query, which
includes both tables, looking for say, all personnel with
any number of qualifications. I.e. I may want to search
for someone with a "PhD" AND with experience in "Project
Management" AND "Access 2000". Another time, I may be
looking for someone with 2, 3, 4...etc separate
qualifications. I realize that as the number of required
qualifications increases, the results will be limited

While I would almost expect that such a query (of which
the qualifications will be selected from a form-based
multi-select listbox) 'should' be pretty straight
forward, I am experiencing difficulties trying to come up
with the proper SQL statement. Can you help?

Part 2 -
Once Part One is solved, the next question has to do with
capturing the values of 'several' (say 10) form-based
multi-select listboxes and concatenating these values in
a textbox, whose value will serve as RowSource to yet
another Listbox. At this point I have been able to pass
multiple values from a 'single' listbox to a query (using
the "OR" operator) with effective results. Do all 10
listboxes have to somehow have the focus set on them to
capture their values? I would like to be able to pass,
say one or more values from one or more listboxes to a
query.

Any assistance is appreciated and welcomed. I hope I was
clear enough and apologize if I wasn't.

Denis

 

[Allen Browne]

Hi Denis

You are asking the question:
Show me the People how have a Qualification of 'PhD' AND have a
Qualification of 'ProjectMgr' AND ... You will need a subquery to read each
of the qualifications, and return only those who have them all.

The basic subquery works like this:

SELECT tblPersonnel.*
FROM tblPersonnel
WHERE EXISTS ( SELECT tblQualifications.ID
FROM tblQualifications
WHERE tblQualifications.PersonID = tblPersonnel.PersonID
AND tblQualifications.Qualification = 'PhD' );

To check if multiple qualifications exist for the person:
SELECT tblPersonnel.*
FROM tblPersonnel
WHERE (EXISTS ( SELECT tblQualifications.ID
FROM tblQualifications
WHERE tblQualifications.PersonID = tblPersonnel.PersonID
AND tblQualifications.Qualification = 'PhD' ))
AND (EXISTS ( SELECT tblQualifications.ID
FROM tblQualifications
WHERE tblQualifications.PersonID = tblPersonnel.PersonID
AND tblQualifications.Qualification = 'ProjectMgr' ))
AND (EXISTS (SELECT ...

So, you will loop through the ItemsSelected in your listbox, and generate a
SQL string like that.

In answer to part 2 of your question, you will only be able to generate the
RowSource by creating this string dynamically. You will not be able to have
an existing query read the values from multi-select list boxes directly.

Maybe not as simple as you envisaged, but quite doable and the performance
should be quite okay.

 

[Denis]

Hi Allen,
Yes indeed, this is slightly more complicated than I
originally thought, and I look forward to working with
what you have provided me.
Thank you for your assistance.
Denis