Append query and duplicate records 1

[r00tcanal]

Hello all,
I'm attempting to construct and append query that appends data from an imported data table (tblImport) to the main transaction table (tblTransactions); however, I only want to import records that do not already appear in tblTransactions. Both tblImport and tblTransactions have a unique TransactionID field (not autonumber). The tblTransactions has two relationships, one with CustID and one with DeptID and is "enforcing referential integrity". The tblImport table is stand-alone. I have both tables added to the query and have setup the criteria as follows for the tblImport.TransactionID column:

<>[tblTransactions]![TransactionID]

There are 132 records in tblTransactions and 139 in tblImport. When I run the query it tells me it will append 18216 rows. I say yes. Access then says it couldn't add 18209 records due to key violations (referential integrity). The seven remaining new records get imported.

My question is why is Access trying to add 18000+ records based on the criteria? Am I missing something? How might I fix this?

Any advice/comments you can offer would be appreciated.

Thanks,

-Jeff Lush

 

[RickSpr]

You must not have a join line between the tables. When you just add two tables without a join line, your result table (the output of your query) consists of every possible combination of a row from one table with a row from the other. That's 132 x 138 = 18216 (you must have 138 rows in tblImport, not 139).

What you want to do is draw a join line between the TransactionID fields in each table. The double click on the line and select the option &quot;Include ALL records from tblImport and only those records from tblTransactions where the joined fields are equal.&quot; Then click OK.

Back in the design grid, delete your &quot;<>&quot; criteria. Instead, drag TransactionID from tblTransactions to the grid, clear its Show check box, and enter [red]Is Null[/red] in the criteria line. This will tell Access to include only rows where no matching TransactionID is found in tblTransactions. Rick Sprague

 

[r00tcanal]

That did it! Thanks very much for the assistance. It is really appreciated!

-Jeff Lush