Finding two types of criteria in the one table

  • Thread starter Thread starter Les
  • Start date Start date
L

Les

I hava a database which is populated with pupils details. One of the tables
carries a list of thier qualifiations. What I need to do is to identify the
pupils with two qualifications say "Ab3" and "AB4" the criteria is entered
on a form in two fields "strQualifiaction1" and [strQualification2].

I need to list just those with both. At the moment I can only get the "Or"
selection to work. so I also finish up with those with just one or the
other.

The table name is [tblQualifications] with field name [strGrade]

My form name is [frmPupils_Qualifications] with fields "strQualifiaction1"
and [strQualification2].

I guess I need some form of "And" selection but I can' get one to work.

Any Ideas please.

Thanks

Les
 
I understand what you have, I think, but not what you want... do you want to
open a form, or a report on just the students with the two qualifications?
For a form (report would be same with OpenReport rather than OpenForm) if
the qualifications on the form are in text boxes "txtQ1" and "txtQ2".

DoCmd.OpenForm <yourstuform>,<viewdesired>,,"[strQualification1] = """ &
txtQ1 & """ AND [strQualification2] = """ & txtQ2 & """"

That should do what you describe.

If I didn't understand the problem or there's something I have overlooked,
post a followup in this thread.

Larry Linson
Microsoft Access MVP
 
I hava a database which is populated with pupils details. One of the tables
carries a list of thier qualifiations. What I need to do is to identify the
pupils with two qualifications say "Ab3" and "AB4" the criteria is entered
on a form in two fields "strQualifiaction1" and [strQualification2].

I need to list just those with both. At the moment I can only get the "Or"
selection to work. so I also finish up with those with just one or the
other.

The table name is [tblQualifications] with field name [strGrade]

My form name is [frmPupils_Qualifications] with fields "strQualifiaction1"
and [strQualification2].

I guess I need some form of "And" selection but I can' get one to work.
Click to expand...

I'm not sure that either Larry or I understand your question clearly!
Here's my GUESS at interpretation: you have a Form with to Textboxes
(not fields, fields are in tables not forms!) named strQualification1
and strQualification2. You have a table with - I'd guess - a PupilID
and a field strGrade; you want to identify pupils for whom one record
in tblQualifications contains the value in strQualification1 and some
other record contains the value in strQualification2. Is this close
to right?

IF SO... try

SELECT Pupils.*
FROM Pupils
WHERE PupilID IN
(SELECT PupilID FROM tblQualifications WHERE
tblQualifications.strGrade =
Forms!frmPupils_Qualifications!strQualification1)
AND PupilID IN
(SELECT PupilID FROM tblQualifications WHERE
tblQualifications.strGrade =
Forms!frmPupils_Qualifications!strQualification2)
 
Thanks John thats exactly what I wanted.

Sorry about the confusing way I wrote the question.

Les

John Vinson said:
I hava a database which is populated with pupils details. One of the tables
carries a list of thier qualifiations. What I need to do is to identify the
pupils with two qualifications say "Ab3" and "AB4" the criteria is entered
on a form in two fields "strQualifiaction1" and [strQualification2].

I need to list just those with both. At the moment I can only get the "Or"
selection to work. so I also finish up with those with just one or the
other.

The table name is [tblQualifications] with field name [strGrade]

My form name is [frmPupils_Qualifications] with fields "strQualifiaction1"
and [strQualification2].

I guess I need some form of "And" selection but I can' get one to work.
Click to expand...

I'm not sure that either Larry or I understand your question clearly!
Here's my GUESS at interpretation: you have a Form with to Textboxes
(not fields, fields are in tables not forms!) named strQualification1
and strQualification2. You have a table with - I'd guess - a PupilID
and a field strGrade; you want to identify pupils for whom one record
in tblQualifications contains the value in strQualification1 and some
other record contains the value in strQualification2. Is this close
to right?

IF SO... try

SELECT Pupils.*
FROM Pupils
WHERE PupilID IN
(SELECT PupilID FROM tblQualifications WHERE
tblQualifications.strGrade =
Forms!frmPupils_Qualifications!strQualification1)
AND PupilID IN
(SELECT PupilID FROM tblQualifications WHERE
tblQualifications.strGrade =
Forms!frmPupils_Qualifications!strQualification2)
Click to expand...
 
Larry sorry about the confusing way I asked that question.

I used Johns version in the end which now worked.

Thanks for the reply I will save your answer anyway as I think it will come
in usefull for another area.

Les


Larry Linson said:
I understand what you have, I think, but not what you want... do you want to
open a form, or a report on just the students with the two qualifications?
For a form (report would be same with OpenReport rather than OpenForm) if
the qualifications on the form are in text boxes "txtQ1" and "txtQ2".

DoCmd.OpenForm <yourstuform>,<viewdesired>,,"[strQualification1] = """ &
txtQ1 & """ AND [strQualification2] = """ & txtQ2 & """"

That should do what you describe.

If I didn't understand the problem or there's something I have overlooked,
post a followup in this thread.

Larry Linson
Microsoft Access MVP

Les said:
I hava a database which is populated with pupils details. One of the tables
carries a list of thier qualifiations. What I need to do is to identify the
pupils with two qualifications say "Ab3" and "AB4" the criteria is entered
on a form in two fields "strQualifiaction1" and [strQualification2].

I need to list just those with both. At the moment I can only get the "Or"
selection to work. so I also finish up with those with just one or the
other.

The table name is [tblQualifications] with field name [strGrade]

My form name is [frmPupils_Qualifications] with fields "strQualifiaction1"
and [strQualification2].

I guess I need some form of "And" selection but I can' get one to work.

Any Ideas please.

Thanks

Les
Click to expand...
Click to expand...
 
Back
Top