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

    find records that don't have a match between 2 tables

    Does anyone have any ideas for writing a query that excludes the records in table to the left that match the table to the right? Thanks in advance!
    Notice that two records "236 500 236 500" are missing from the results off this query I tried:

    SELECT tmpBankDebits.Store, tmpBankDebits.Amount
    FROM tmpBankDebits LEFT JOIN tmpChangeOrdersWithManual ON
    tmpBankDebits.[ConcatenateBankDebits] = tmpChangeOrdersWithManual.[ConcatenateBank]
    WHERE (((tmpChangeOrdersWithManual.ConcatenateBank) Is Null));

    Results:
    Store Amount
    89 500
    95 500
    126 500
    1626 500
    712 500

    Store Amount ConcatenateBankDebits Store Amount ConcatenateBank
    64 500 64 500 64 500 64 500
    89 500 89 500 98 500 98 500
    95 500 95 500 108 500 108 500
    98 500 98 500 160 500 160 500
    108 500 108 500 189 500 189 500
    126 500 126 500 202 500 202 500
    160 500 160 500 209 500 209 500
    189 500 189 500 222 500 222 500
    202 500 202 500 222 500 222 500
    209 500 209 500 236 500 236 500
    222 500 222 500 236 500 236 500
    222 500 222 500 236 500 236 500
    236 500 236 500 236 500 236 500
    236 500 236 500 236 500 236 500
    236 500 236 500 236 500 236 500
    236 500 236 500 236 500 236 500
    236 500 236 500 236 500 236 500
    236 500 236 500 240 500 240 500


    236 500 236 500 252 500 252 500
    236 500 236 500 262 500 262 500
    236 500 236 500 265 500 265 500
    236 500 236 500 265 500 265 500
    240 500 240 500 703 500 703 500
    252 500 252 500 710 500 710 500
    262 500 262 500 721 500 721 500
    265 500 265 500 723 500 723 500
    265 500 265 500 1330 500 1330 500
    265 500 265 500 1602 500 1602 500
    703 500 703 500 1613 500 1613 500
    710 500 710 500 1645 500 1645 500
    712 500 712 500 1645 500 1645 500
    721 500 721 500
    723 500 723 500
    1330 500 1330 500
    1330 500 1330 500
    1602 500 1602 500
    1602 500 1602 500
    1613 500 1613 500
    1613 500 1613 500
    1626 500 1626 500
    1645 500 1645 500
    1645 500 1645 500

    I tried this and got the same results:

    SELECT tmpBankDebits.Store, tmpBankDebits.Amount,
    tmpBankDebits.ConcatenateBankDebits
    FROM tmpBankDebits WHERE ConcatenateBankDebits NOT IN
    (SELECT tmpChangeOrdersWithManual.ConcatenateBank
    FROM tmpChangeOrdersWithManual);
    Last edited by BrockWade; 02-06-2014 at 10:44 AM. Reason: hard to read, formatted

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    The following suggests you already have several fields concatenated that you then compare to find unmatched.
    WHERE (((tmpChangeOrdersWithManual.ConcatenateBank) Is Null));

    I would double check that the two "concatenated" results have identical fields and then consider additional queries or adding/subtracting fields that are concatenated.

    EDIT: You could include an alias in the query you have there. Concatenate those fields you want to test for null in an alias and include criteria to test for null.

  3. #3
    BrockWade is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Posts
    63
    "consider additional queries or adding/subtracting fields that are concatenated." .... not sure of what SQL code I could try along this line

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Check out my edit comment and see if creating an alias in your existing query will work.

    Because you are dealing with so many columns, there may be a need to nest queries or similar. It would be nice if you can get the function you need from adding an alias though.

  5. #5
    BrockWade is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Posts
    63
    I'm not too familiar with using Alias so I tried this just to see if we are on the same page (?):

    SELECT tmpBankDebits.Store, tmpBankDebits.Amount,
    tmpBankDebits.ConcatenateBankDebits AS Alias1,
    tmpChangeOrdersWithManual.ConcatenateBank AS Alias2
    FROM tmpBankDebits LEFT JOIN tmpChangeOrdersWithManual ON tmpBankDebits.[ConcatenateBankDebits] = tmpChangeOrdersWithManual.[ConcatenateBank]
    WHERE (((tmpChangeOrdersWithManual.ConcatenateBank) Is Null));

    Nothing appears in the Alias2 column.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I have to go to some meetings so I will be rather busy today. If you can, use the query builder. In a blank field in the designer, place

    NewAlias:

    This will be your alias and you will include the concatenated fields after the colon. Then, in the criteria filed you could type something like Not Is Null

  7. #7
    BrockWade is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Posts
    63
    I got totally lost with the query builder... any hints as to what the SQL would look like?

    Still not sure why this would not work either:

    SELECT tmpBankDebits.Store, tmpBankDebits.Amount,
    tmpBankDebits.ConcatenateBankDebits
    FROM tmpBankDebits WHERE ConcatenateBankDebits NOT IN
    (SELECT tmpChangeOrdersWithManual.ConcatenateBank
    FROM tmpChangeOrdersWithManual);

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    List out all of the field names for tmpBankDebits and all of the field names for tmpChangeOrdersWithManual

    Then explain which field names you want to compare to each other.

  9. #9
    BrockWade is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Posts
    63
    Table: tmpBankDebits Fields: Store, Amount, ConcatenateBankDebits
    Table: tmpChangeOrdersWithManual Fields: Store, Amount, ConcatenateBank

    I want to compare ConcatenateBankDebits to ConcatenateBank

    Table: tmpBankDebits has about 650 records
    Table: tmpChangeOrdersWithManual has about 250 records
    - the resulting query should yield about 425 records but it is leaving out about 125 records

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by BrockWade View Post
    Table: tmpBankDebits Fields: Store, Amount, ConcatenateBankDebits
    Table: tmpChangeOrdersWithManual Fields: Store, Amount, ConcatenateBank

    I want to compare ConcatenateBankDebits to ConcatenateBank

    Table: tmpBankDebits has about 650 records
    Table: tmpChangeOrdersWithManual has about 250 records
    - the resulting query should yield about 425 records but it is leaving out about 125 records
    The following SQL does what you describe here.
    Code:
    SELECT tmpBankDebits.Store, tmpBankDebits.Amount, tmpBankDebits.ConcatenateBankDebits
    FROM tmpBankDebits LEFT JOIN tmpChangeOrdersWithManual ON tmpBankDebits.[ConcatenateBankDebits] = tmpChangeOrdersWithManual.[ConcatenateBank]
    WHERE (((tmpChangeOrdersWithManual.ConcatenateBank) Is Null));
    What do you need to happen that this SQL does not do?

  11. #11
    BrockWade is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Posts
    63
    It is leaving out some records that it should not, e.g. the concatenation of the following:


    tmpBankDebits tmpChangeOrdersWithManual
    230 500 230 500
    230 500 230 500
    230 500


    Because there are only two records in the right table the query needs to
    return one records from the table on the left... this is happening sometimes but
    not all the time and I can't find a pattern.

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by BrockWade View Post
    ..the concatenation of the following...
    I am having a really hard time understanding what it is you are trying to say here. Nothing is getting concatenated in your query. You have a field called ConcatenateBankDebits that is being compared to a field called ConcatenateBank.

    All records in table tmpBankDebits are being returned as long as there is not a matching value in ConcatenateBank. That is the only pattern you should be observing. If the value in ConcatenateBankDebits does not match any one of the records in the other table's field named ConcatenateBank, then that record (in table tmpBankDebits) will be retrieved. there is only one comparison happening. The comparison of ConcatenateBankDebits, from any record/row, to ConcatenateBank, from any record/row.

    Consider what the query is doing and then think of the additional criteria needed. It seems you need to compare one or two other fields too. Maybe you need to compare the store to the store and then see if the ConcatenateBankDebits field matches or not.

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

Similar Threads

  1. Find an Exact Match in a Access Table column
    By raghavendran in forum Access
    Replies: 4
    Last Post: 10-12-2013, 11:57 AM
  2. Combo Box to find records from multiple tables
    By cbella625 in forum Access
    Replies: 3
    Last Post: 08-15-2013, 12:12 PM
  3. Match Dates from two tables
    By GabyArco in forum Queries
    Replies: 2
    Last Post: 06-02-2013, 06:03 PM
  4. Replies: 3
    Last Post: 08-08-2012, 03:15 AM
  5. Replies: 6
    Last Post: 02-10-2011, 07:09 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