Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    1

    Unanswered: Query works on tables but not other queries

    Trying to find a list of all records in tableB that ARE NOT in table A. Here's the code I use:

    SELECT tblB.username, tblB.entity
    FROM tblB
    WHERE NOT EXISTS (select tblA.username, tblA.entity from tblA where tblA.username = tblB.username and tblA.entity = tblB.entity);

    That code works great as long as the data sources are tables. If I change it to qryA and qryB, it will lock up Access. qryA and qryB have the EXACT same structure and design as tblA and tblB.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The easiest approach would be to use the 'find unmatched' wizard as part of the query wizards.

  3. #3
    Join Date
    Jan 2005
    Posts
    31
    Quote Originally Posted by msellery
    Trying to find a list of all records in tableB that ARE NOT in table A. Here's the code I use:

    SELECT tblB.username, tblB.entity
    FROM tblB
    WHERE NOT EXISTS (select tblA.username, tblA.entity from tblA where tblA.username = tblB.username and tblA.entity = tblB.entity);

    That code works great as long as the data sources are tables. If I change it to qryA and qryB, it will lock up Access. qryA and qryB have the EXACT same structure and design as tblA and tblB.
    I have just tried running a couple of Difference Queries where query objects were used in place of table objects.

    They appeared to work just fine for me.

    Can you reproduce your DDL and query object SQL and some sample data here?

Posting Permissions

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