Find Unmatched records using AND criteria

[Gavx]

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]

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

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

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]

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

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]

Gavx

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

 

[isladogs]

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]

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]

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]

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]

Sorted and thanks for asking.

 

[theDBguy]

Sorted and thanks for asking.

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