Peremeter Query

P

paulinec

I have a training database and I am trying to get students who have certain
qualifications, eg: a Blue Card and A Dozer Ticket and maybe a Scraper
Ticket. I only want to see those students who have ALL of the required
tickeds
I have tried using the functions that I found on this site:
Function GetToken(stLn, stDelim) and Function InParam(Fld, Param)
I have typed the following SQL statement in a query:
SELECT TBStudentDetails.StudentFirstName, TBStudentDetails.StudentLastName,
TBStudentCompetency.ModuleNo, InParam([ModuleNo],
[Type a list like "CA, NY, NJ"]) AS Expr3
FROM TBStudentDetails INNER JOIN TBStudentCompetency ON
TBStudentDetails.StudentID = TBStudentCompetency.StudentId
WHERE (((InParam([ModuleNo],[Type a list like "CA, NY, NJ"]))=True));
It keeps putting the Expr3 in even though I don't type it in and it isn't
showing any data.
The two samples I am querying on are Students who have the following
qualifications - sometimes it could be students that have three or four
qualifications:
RUAAG1100EOA and RUAAG2101EOA. I just want to be able to type in ag1100 and
ag2101.
Can anyone help me with this. I thought it was easy till I tried to do it.
Pauline C
 
J

Jeff Boyce

Pauline

How is your data structured? Building a query to find something presupposes
we know what we're looking at.

Do each of your "qualifications" get recorded as separate fields (a very
spreadsheetly way to do it), or as separate rows in a "many" table, related
1:m to the students?
 
P

paulinec

I have a student's table and a StudentCompetency Table with a StudentId in it
- this is a one to many join with the Student Table. The Qualifications are
recorded as a separate row in the StudentCompetency Table. The Fields in
this table are StudentId (1:M to Student Table), ModuleNo ((1:M to the
ModuleDetails table), Competent Y/N plus a couple more that aren't relevant
to this query.
We are now adding an employment section to this DB and need to find students
who have certain qualifications - might be two or three or more but they must
have ALL the qualifications that are required for a certain job.
Jeff Boyce said:
Pauline

How is your data structured? Building a query to find something presupposes
we know what we're looking at.

Do each of your "qualifications" get recorded as separate fields (a very
spreadsheetly way to do it), or as separate rows in a "many" table, related
1:m to the students?

--
More info, please ...

Jeff Boyce
<Access MVP>

paulinec said:
I have a training database and I am trying to get students who have certain
qualifications, eg: a Blue Card and A Dozer Ticket and maybe a Scraper
Ticket. I only want to see those students who have ALL of the required
tickeds
I have tried using the functions that I found on this site:
Function GetToken(stLn, stDelim) and Function InParam(Fld, Param)
I have typed the following SQL statement in a query:
SELECT TBStudentDetails.StudentFirstName, TBStudentDetails.StudentLastName,
TBStudentCompetency.ModuleNo, InParam([ModuleNo],
[Type a list like "CA, NY, NJ"]) AS Expr3
FROM TBStudentDetails INNER JOIN TBStudentCompetency ON
TBStudentDetails.StudentID = TBStudentCompetency.StudentId
WHERE (((InParam([ModuleNo],[Type a list like "CA, NY, NJ"]))=True));
It keeps putting the Expr3 in even though I don't type it in and it isn't
showing any data.
The two samples I am querying on are Students who have the following
qualifications - sometimes it could be students that have three or four
qualifications:
RUAAG1100EOA and RUAAG2101EOA. I just want to be able to type in ag1100 and
ag2101.
Can anyone help me with this. I thought it was easy till I tried to do it.
Pauline C
Click to expand...
Click to expand...
 
P

paulinec

It has given me an idea but how did you match the criteria in the new table
to the students. That is where I seem to be having trouble - getting a query
to work involving AND where you are asking for more than one input field and
using the * wildcard. I can get students who have any of the qualifications
but not the ones who satisfy ALL the qualifications. The reason I want to do
this is Employers phone in wanting someone who has anything from two to four
qualifications.

Jeff Boyce said:
I had to deal with a similar requirement in a "Certification" database
application. The solution I settled on was to build a table that listed ALL
requirements for each particular certification. Then I built queries to
find folks whose satisfied qualifications matched ALL the requirements (and
those who were "short").

Does this give you any ideas?

--
Good luck

Jeff Boyce
<Access MVP>

paulinec said:
I have a student's table and a StudentCompetency Table with a StudentId in it
- this is a one to many join with the Student Table. The Qualifications are
recorded as a separate row in the StudentCompetency Table. The Fields in
this table are StudentId (1:M to Student Table), ModuleNo ((1:M to the
ModuleDetails table), Competent Y/N plus a couple more that aren't relevant
to this query.
We are now adding an employment section to this DB and need to find students
who have certain qualifications - might be two or three or more but they must
have ALL the qualifications that are required for a certain job.
Jeff Boyce said:
Pauline

How is your data structured? Building a query to find something presupposes
we know what we're looking at.

Do each of your "qualifications" get recorded as separate fields (a very
spreadsheetly way to do it), or as separate rows in a "many" table, related
1:m to the students?

--
More info, please ...

Jeff Boyce
<Access MVP>

I have a training database and I am trying to get students who have
certain
qualifications, eg: a Blue Card and A Dozer Ticket and maybe a Scraper
Ticket. I only want to see those students who have ALL of the required
tickeds
I have tried using the functions that I found on this site:
Function GetToken(stLn, stDelim) and Function InParam(Fld, Param)
I have typed the following SQL statement in a query:
SELECT TBStudentDetails.StudentFirstName,
TBStudentDetails.StudentLastName,
TBStudentCompetency.ModuleNo, InParam([ModuleNo],
[Type a list like "CA, NY, NJ"]) AS Expr3
FROM TBStudentDetails INNER JOIN TBStudentCompetency ON
TBStudentDetails.StudentID = TBStudentCompetency.StudentId
WHERE (((InParam([ModuleNo],[Type a list like "CA, NY, NJ"]))=True));
It keeps putting the Expr3 in even though I don't type it in and it isn't
showing any data.
The two samples I am querying on are Students who have the following
qualifications - sometimes it could be students that have three or four
qualifications:
RUAAG1100EOA and RUAAG2101EOA. I just want to be able to type in ag1100
and
ag2101.
Can anyone help me with this. I thought it was easy till I tried to do
it.
Pauline C
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

Take a look at the Unmatched query wizard. The basic concept I used was to
find all folks (participants) for whom their list of (satisfied)
requirements did NOT include all the base requirements. This is an
"unmatched" situation. I had to wrestle a bit with multiple, "chained
together" queries, but was able to end up with queries that generated the
rows I needed for a report that showed one page per participant, and the
requirements "satisfied" and "unsatisfied".

Good luck!

Jeff Boyce
<Access MVP>

paulinec said:
It has given me an idea but how did you match the criteria in the new table
to the students. That is where I seem to be having trouble - getting a query
to work involving AND where you are asking for more than one input field and
using the * wildcard. I can get students who have any of the qualifications
but not the ones who satisfy ALL the qualifications. The reason I want to do
this is Employers phone in wanting someone who has anything from two to four
qualifications.

Jeff Boyce said:
I had to deal with a similar requirement in a "Certification" database
application. The solution I settled on was to build a table that listed ALL
requirements for each particular certification. Then I built queries to
find folks whose satisfied qualifications matched ALL the requirements (and
those who were "short").

Does this give you any ideas?

--
Good luck

Jeff Boyce
<Access MVP>

paulinec said:
I have a student's table and a StudentCompetency Table with a
Click to expand...
StudentId in
it
- this is a one to many join with the Student Table. The
Click to expand...
Qualifications
are
recorded as a separate row in the StudentCompetency Table. The Fields in
this table are StudentId (1:M to Student Table), ModuleNo ((1:M to the
ModuleDetails table), Competent Y/N plus a couple more that aren't relevant
to this query.
We are now adding an employment section to this DB and need to find students
who have certain qualifications - might be two or three or more but
Click to expand...
they
must
have ALL the qualifications that are required for a certain job.
:

Pauline

How is your data structured? Building a query to find something presupposes
we know what we're looking at.

Do each of your "qualifications" get recorded as separate fields (a very
spreadsheetly way to do it), or as separate rows in a "many" table, related
1:m to the students?

--
More info, please ...

Jeff Boyce
<Access MVP>

I have a training database and I am trying to get students who have
certain
qualifications, eg: a Blue Card and A Dozer Ticket and maybe a Scraper
Ticket. I only want to see those students who have ALL of the required
tickeds
I have tried using the functions that I found on this site:
Function GetToken(stLn, stDelim) and Function InParam(Fld, Param)
I have typed the following SQL statement in a query:
SELECT TBStudentDetails.StudentFirstName,
TBStudentDetails.StudentLastName,
TBStudentCompetency.ModuleNo, InParam([ModuleNo],
[Type a list like "CA, NY, NJ"]) AS Expr3
FROM TBStudentDetails INNER JOIN TBStudentCompetency ON
TBStudentDetails.StudentID = TBStudentCompetency.StudentId
WHERE (((InParam([ModuleNo],[Type a list like "CA, NY, NJ"]))=True));
It keeps putting the Expr3 in even though I don't type it in and
Click to expand...
it
isn't
showing any data.
The two samples I am querying on are Students who have the following
qualifications - sometimes it could be students that have three or four
qualifications:
RUAAG1100EOA and RUAAG2101EOA. I just want to be able to type in ag1100
and
ag2101.
Can anyone help me with this. I thought it was easy till I tried
Click to expand...
to
do
it.
Pauline C
Click to expand...
Click to expand...
Click to expand...
 
Top