query yes for two fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a query that needs to say find anyone that took an access class
and passed it as well as find anyone that took an excel class and passed it.
That work fine. But now I want to say find anyone that took both classes and
passed both.
 
Without knowing your data structure, this would be very hard to answer.

You SHOULD have a separate record for each class taken. Your query should
include a class name field and a grade field. You should have criteria
under those fields to say "If the class is excel and if the grade is greater
than 70, return the record".

You simply need to change this to say "if the class is excel or access, and
if the grade is over 70, then return the record".

Many people do not design their databases properly and might have a field
called "Access Class Grade" and "Excel Class Grade". If that is the case,
you'd just put criteria of ">70" under each column. BUT... you would do
this on a separate row in your query design grid. Criteria all on one row
creates an "AND" condition. Criteria on separate rows creates an "OR"
criteria. If this solution matches your data, then your tables are not
designed properly. The field name should never contain data (like the name
of the class, the month of the year, the location name, etc.)

Again, without knowing your data structure and field names this would be
hard to answer for you.
 
Hi Rick

My course table has the field description and passed, and another table has
lastname, etc...

I created a query that said find any *access* class and yes for passed. The
second row for my or part was find any *excel* class and yes for passed. So
it shows me anyone who took an excel or access class and passed it. Now I
want it to say show me only those people that took any excel and any access
and passed the class.

Thanks!
 
One method

SELECT PeopleTable.*
FROM PeopleTable
WHERE PeopleId in
(SELECT Classtable.PeopleID
FROM ClassTable
WHERE Class = "Access" AND Score < 70)
AND PeopleId in
(SELECT Classtable.PeopleID
FROM ClassTable
WHERE Class = "Excel" AND Score < 70)
 
Back
Top