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

[BrockWade]

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);

[ItsMe]

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.

[BrockWade]

"consider additional queries or adding/subtracting fields that are concatenated." .... not sure of what SQL code I could try along this line

[ItsMe]

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.

[BrockWade]

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.

[ItsMe]

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

[BrockWade]

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);

[ItsMe]

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.

[BrockWade]

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

[ItsMe]

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.

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?

[BrockWade]

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.

[ItsMe]

..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.