If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Not equal to entire other table?

How would you go about displaying records from one table missing out certain records if they are all on another table.

So I have a table which stores all members, and a table that store members who are down for each class. I wish to display all members minus the one from a certain class. Though some members are down for multiple classes, but I don't think that should affect it.

Not sure missed something obvious, but shouldn't this just display the Members table minus the names which appear on the Session 1 Members table?

Also after this works I want to have a user input prompt so I can enter their Class Preferences, so each member when they applied had other classes they'd like as first and second preference. These are stored on another table. So would like it to list the members who are not in Class 1 but had it as a preference or any other class.

But tats just if its simply, don't wanna be too much trouble with what I think is probably simple stuff, but I'm very new to this.

then right the the black line between the to tables change the join making it a injoin or out join and run the query and on of the ID coloumns should be blank

hope this help

StePhan McKillen
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment:Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010VB-NET based on my own environment started 2007SQL-2005 based on my own environment started 2008YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON

Errrm, not really got a clue what you're on about, but from what I gathered I right clicked on the relationship line between the 2 tables, and clicked properties, but there were only 3 options, one already selected. The other 2 just made either aa "RIGHT JOIN" or a "LEFT JOIN" with "INNER JOIN" being default, and "OUTTER"/"OUT" JOIN giving me an error.

And the left and right join either shows all the members or just the members from the class table, with the WHERE code I put in first post, it just displays nothing again

In the query designer, create the join between the tables from the master members list to the class members list. Right-click on the join and select "Join Properties". In the dialogue box that appears, select the second option ("Include ALL records from 'Members' and only those records from 'Class 1 members' where the joined fields are equal"). Pull down the fields that you want to see from Members, and the ID field from Class 1 members. Use the criterion "Is Null" for this field, and deselect the option to show it.
As an aside, you will find queries easier to manage if you design your tables without spaces in their names, and without spaces in the field names.