Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2012

    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.

    I've tried:
    WHERE ((([Members].[ID])<>[Class 1 Members].[Member ID]));

    It just runs and displays nothing.

    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.

  2. #2
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 10
    create a New Query SHowing Join the Tables buy the Member ID and ID

    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

    See clear as mud

    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/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

  3. #3
    Join Date
    Nov 2012
    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
    Last edited by Veréonix; 12-02-12 at 20:58.

  4. #4
    Join Date
    Apr 2004
    metro Detroit
    since the tables are joined be memberID, your where clause will always return an empty record set

    you have two tables, tableA and tableB

    create a query using both tables joined on memberID

    right click the join and choose include all records from tableA and matching records from tableB

    bring down memberID from both tables

    in the criteria for memberID from tableB type null

  5. #5
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 10
    You need the results of an outer join for this.

    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.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts