Find Unmatched records using AND criteria (1 Viewer)

Gavx · Dec 1, 2019

I would like to find all records in tblImport that does not have matching records in tblAccountTransactions.

In both tables I have Amount, Date and Description. I need to use an AND statement in the criteria.
That is, using all 3 fields (Amount AND Date AND Description) from tblImport find non matching records using Amount AND Date AND Decription to find all non matching records.
It seems like it is a compound index situation.

How can this be done?

isladogs · Dec 1, 2019

Have you tried using the unmatched query wizard?
Although the wizard only allow you to join on one field, you can add the other two fields yourself in design view

BTW Date is a reserved word in Access and should not be used for field names. Use e.g. TransactionDate

The result will be something like this

Code:

SELECT tblImport.*
FROM tblImport LEFT JOIN tblAccountTransactions ON tblImport.[TransactionDate] = tblAccountTransactions.[TransactionDate]
WHERE (((tblAccountTransactions.TransactionDate) Is Null) AND ((tblAccountTransactions.Description) Is Null) AND ((tblAccountTransactions.Amount) Is Null));

You can then change this into an append query to add the unmatched records to tblAccountTransactions

Code:

INSERT INTO tblAccountTransactions ( TransactionDate, Description, Amount )
SELECT DISTINCT tblImport.TransactionDate, tblImport.Description, tblImport.Amount
FROM tblImport LEFT JOIN tblAccountTransactions ON tblImport.[TransactionDate] = tblAccountTransactions.[TransactionDate]
WHERE (((tblAccountTransactions.TransactionDate) Is Null) AND ((tblAccountTransactions.Description) Is Null) AND ((tblAccountTransactions.Amount) Is Null));

Hope that helps

Gavx · Dec 1, 2019

isladogs said:
Have you tried using the unmatched query wizard?
Although the wizard only allow you to join on one field, you can add the other two fields yourself in design view

BTW Date is a reserved word in Access and should not be used for field names. Use e.g. TransactionDate

Yes have used the unmatched query wizard without success.
My mistake, the field Date is really TransactionDate.

The result will be something like this

Code:

SELECT tblImport.*
FROM tblImport LEFT JOIN tblAccountTransactions ON tblImport.[TransactionDate] = tblAccountTransactions.[TransactionDate]
WHERE (((tblAccountTransactions.TransactionDate) Is Null) AND ((tblAccountTransactions.Description) Is Null) AND ((tblAccountTransactions.Amount) Is Null));

This is asking me for Parameter values for tblAccountTransactions.Description and tblAccountTransactions.Amount.

bob fitz · Dec 1, 2019

Gavx

Can you post a copy of the db with just a few fictitious record that illustrate the problem

isladogs · Dec 1, 2019

It should work. See attached for a quick example

Check you have the same data types for each field in the two tables
Also that you haven't asked for parameters in the query design

theDBguy · Dec 1, 2019

Hi Gavx. Where is the data from tblImport coming from? I'm just curious why you have two tables with the same data. Also, "matching" on Description (assuming it's a memo field) will probably be slow and only match the first 255 chars (just a guess).

Gavx · Dec 1, 2019

theDBguy said:
Where is the data from tblImport coming from? I'm just curious why you have two tables with the same data. Also, "matching" on Description (assuming it's a memo field) will probably be slow and only match the first 255 chars (just a guess).

tblImport is created from a text file import and holds superfluous transactions that need to be weeded out - the balance of the transactions are appended to the tblAccountTransactions.

Thanks for the pointer re memo fields. Fortunately it isn't.

theDBguy · Dec 2, 2019

Gavx said:
tblImport is created from a text file import and holds superfluous transactions that need to be weeded out - the balance of the transactions are appended to the tblAccountTransactions.

Thanks for the pointer re memo fields. Fortunately it isn't.

Hi. Thanks for the clarification. So, were you able to get it to work?

Gavx · Dec 2, 2019

Sorted and thanks for asking.

theDBguy · Dec 2, 2019

Gavx said:
Sorted and thanks for asking.

Congratulations! Glad to hear you got it sorted out. Good luck with your project.

Find Unmatched records using AND criteria (1 Viewer)

Gavx

Registered User.

isladogs

MVP / VIP

Gavx

Registered User.

bob fitz

AWF VIP

isladogs

MVP / VIP

Attachments

theDBguy

I’m here to help

Gavx

Registered User.

theDBguy

I’m here to help

Gavx

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread