Results 1 to 4 of 4
  1. #1
    Galactus is offline Novice
    Windows 10 Access 2013
    Join Date
    Dec 2015
    Posts
    2

    COmpare two queries - find the ones missing in one query

    Hi everybody.
    I have two queries with identical structure.


    They show customers with revenue in 2014 and customers with revenue in 2015.
    I want to find the customers that had revenue in 2014 but NOT in 2015.
    I tried all I could think of but no success.
    How would you do that?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    It's an OUTER JOIN.
    Make a query, bring in both 2014 and 2015 queries.
    join them on customerID
    dbl-click the join line
    set it to All items in 2014, some in 2015
    bring down fields from both queries,
    but under the qs2015.CustomerID, put: IS NULL under its criteria
    run

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Create a new query using the query wizard, selecting the unmatched query wizard option.
    Use the two queries for 2014 & 2015.

  4. #4
    Galactus is offline Novice
    Windows 10 Access 2013
    Join Date
    Dec 2015
    Posts
    2
    Thank you Ranman for your response.
    I get a "forbidden operation" error though (or similar - I'm using a German version of Access, so I'm guessing the English wording).
    The SQL looks like this after I followed your instructions:
    SELECT KundenMitUmsatz2014.Kundennummer, KundenMitUmsatz2015.Kundennummer
    FROM KundenMitUmsatz2014 LEFT JOIN KundenMitUmsatz2015 ON KundenMitUmsatz2014.Kundennummer = KundenMitUmsatz2015.Kundennummer
    WHERE (((KundenMitUmsatz2015.Kundennummer) Is Null));
    - "Kundennummer" is the customerID

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

Similar Threads

  1. Replies: 10
    Last Post: 11-05-2015, 02:22 PM
  2. Query to Find a Missing Record
    By KCC47 in forum Queries
    Replies: 8
    Last Post: 05-06-2014, 01:25 PM
  3. Need query to find missing field data
    By narendrabr in forum Queries
    Replies: 3
    Last Post: 01-22-2013, 12:48 PM
  4. Find Query Wizard Missing
    By Edgy in forum Queries
    Replies: 1
    Last Post: 03-26-2011, 09:37 AM
  5. How to find missing date query
    By twhite in forum Queries
    Replies: 8
    Last Post: 09-02-2010, 02:42 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