query problem

[Guest_imported]

Hi

I have a basic student-class relational database.

I want to do a query which picks out all students in class X, but in addition it needs to pick out the other classes A,B,C they may also attend.

Any ideas as to how I may approach this?

TIA

 

[robmoors]

You're not very clear with your question, but let me take a stab at it. I believe your wanting to extract students from class x which is in one table, and class a,b, and c which are in seperate tables themselves. Many ways to skin a cat.
Here's one one:

Create a new make table query:
Create a left to right join from table Master to each class tables above.
In the query designer, create a Switch() statement. For example: Total Students:Switch([class x].[field1]="a","Found1",[class a].field1]="a","Found1",[class b].[field1]="a","Found1",[class c].[field1]="a","Found1&quot

When you run the query, from every Master record it looks in each of the tables for every accurance in field1="a". If it does equal "a", then it sends one record to your new table as found1.

When you're done with that, create a SELECT query from your new table and count the "Total Students" field with the criteria "Found1"