Results 1 to 6 of 6
  1. #1
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Unmatched values from two queries

    I know it’s fairly easy to get a list of values from one table, leaving out the repeated values in another but what if they are both queries.



    I have....


    Query1

    field1 auto
    field2 numeric
    field3 numeric (both foreign keys)

    query2

    similar but less data.


    All I want is the data from query1 where field2 and field3 don’t match records in query 2

    Can I use sql to get this?

    i tried unmatched query wizard but this only looks at one field for a match.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    i tried unmatched query wizard but this only looks at one field for a match.
    Easy way.

    Create the unmatched query, basing it on just one field (field2).
    Then simply open up the new query, and manually create the join for field3 (by clicking on the field3 table in one query and dragging and dropping to field3 in the other table).
    You will need to edit the Relationship type, setting it up so the arrow points in the exact same direction as the one for field 2 does.
    That's all you have to do!

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Thanks Joe. I'm trying to write the sql to complete this task.

    I've got



    Code:
    SELECT 1.* from (SELECT tblVIP_type.NameID, tblVIP_type.TypeID1, TblMeals.MealName, TblMeals.ID FROM tblVIP_type INNER JOIN TblMeals ON tblVIP_type.TypeID1 = TblMeals.TypeID WHERE (((tblVIP_type.NameID)=7))) as 1 where 1.ID Not in (select MealIDTried from (SELECT tblJunct.ID, tblJunct.ChoiceNumber, tblJunct.MealIDTried, tblJunct.VIPID_FK FROM tblJunct WHERE (((tblJunct.VIPID_FK)=7)))) order by MealName;
    but am getting an error "syntax error in 1.ID...…"

    Can anyone see where I've gone wrong?

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    that is not an unmatched query and you should not start table (or field) names with a number. Try

    Code:
    SELECT * 
    FROM (SELECT tblVIP_type.NameID, tblVIP_type.TypeID1, TblMeals.MealName, TblMeals.ID FROM tblVIP_type INNER JOIN TblMeals ON tblVIP_type.TypeID1 = TblMeals.TypeID) VIPM 
        LEFT JOIN tblJunct ON VIPM.NameID=tblJunct.VIPID_FK AND VIPM.ID=tblJunct.MealIDTried
    WHERE NameID=7 AND MealIDTried is Null
    ORDER BY MealName
    

  5. #5
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Thanks Ajax. Just what I wanted

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Note that to get the SQL code, you can simply create the unmatched query via the Query Builder (or Query Wizard), and then switch to SQL View.
    This will show you the SQL code of the query.
    That works for any and all queries in Access.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dynamic Lookup Value (unmatched values only)
    By awinterton in forum Database Design
    Replies: 1
    Last Post: 09-28-2017, 03:20 PM
  2. Replies: 4
    Last Post: 07-10-2014, 02:24 PM
  3. Referencing values within queries
    By sf827 in forum Queries
    Replies: 9
    Last Post: 12-19-2011, 05:49 PM
  4. Displaying Zero Values from Queries
    By seageath in forum Queries
    Replies: 2
    Last Post: 12-13-2011, 02:39 AM
  5. Replies: 1
    Last Post: 12-08-2011, 01:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums