Results 1 to 10 of 10
  1. #1
    BrockWade is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Posts
    63

    how to write a query that looks for differences between 2 tables

    I have two tables - 1 with about 9000 records and the other with about 8500 records... each has the same data structure, table names, etc.... what I need to do is write a query that will return only the records that are found in one table that are not found in the other... any ideas?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try the unmatched query wizard.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    BrockWade is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Posts
    63
    Thanks... I gave the Unmatched Query a try per the below. I'm dealing with about 8500 records in one table and 9000 records in the other so what I'm looking for is to return only those that do not have a match in a particular field... this is working to the extent that I'm getting "#Error" in that row in each field of the smaller table where there is no match... Is there a way to modify my query to just return those 500? The query takes about 5 minutes to run... I am querying off of other queries that I run rather than the table itself as I have to do some manipulation of the data... ignore the word "Concatenate" as it for now is just a name of a field, not being used as a key word.

    SELECT qryConcatenateBook.Concatenate, qryConcatenate.Concatenate
    FROM qryConcatenate LEFT OUTER JOIN qryConcatenateBook ON qryConcatenate.[Concatenate] = qryConcatenateBook.[Concatenate];

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You must not have used the wizard, which would have included a criteria along the lines of

    WHERE qryConcatenateBook.Concatenate Is Null
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    BrockWade is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Posts
    63
    I used the below (and followed the query wizard carefully for the duplicates model and got back "Invalid use of Null" when I ran it:

    SELECT qryConcatenate.StoreNum, qryConcatenate.Amount, qryConcatenate.Concatenate
    FROM qryConcatenate LEFT JOIN qryConcatenateBook ON qryConcatenate.[Concatenate] = qryConcatenateBook.[Concatenate]
    WHERE (((qryConcatenateBook.StoreNum) Is Null));

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The other queries run normally? Can you post the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    BrockWade is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Posts
    63
    Database2.accdbHere it is... the form is not usable... ignore it:

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Does eliminating the queries in the middle work for you (I may have reversed the join)?

    SELECT ExcelData.Store, ExcelData.Amount
    FROM ExcelData LEFT JOIN ExcelDataBook ON (ExcelData.Amount = ExcelDataBook.Amount) AND (ExcelData.[Store] = ExcelDataBook.[Store])
    WHERE (((ExcelDataBook.Store) Is Null));
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    BrockWade is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Posts
    63
    This works... Thank you!... I was making it way too complicated.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Help to write query?
    By mlandis in forum Queries
    Replies: 4
    Last Post: 02-17-2012, 09:42 AM
  2. Crosstab Query - Column differences conflict
    By JoshZulaica in forum Queries
    Replies: 1
    Last Post: 06-23-2011, 01:43 AM
  3. Replies: 9
    Last Post: 05-30-2011, 05:43 PM
  4. Help making a form write to multiple tables
    By shiphtfour in forum Forms
    Replies: 2
    Last Post: 01-15-2011, 04:12 PM
  5. Compare tables and tell me differences
    By cowboy in forum Programming
    Replies: 2
    Last Post: 08-11-2010, 08:32 AM

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