SQL Query Help - Only update/add records with no matches 2

[MattGreer]

I have a query that picks records out of a table. I want to take records from the query that do not have a match in a second table and add those records to the second table. I looked up various kinds of JOINS but I'm afraid I don't see what I need.

I tried just adding all the records and having one of the columns to be "no duplicates", but Access seems to refuse to do anything once it sees a duplicate. No records get added to the second table.

I apologize if there isn't enough detail or if this has been covered in the forums already. If you would kindly point me in the right direction it would be most appreciated.



Thanks!!


Matt

 

[dhookom]

There is an unmatched query wizard that you can use to identify the appropriate records. You must be able to identify the field or fields that would be used to determine matches.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

How would you know which records do or do not exsist in the second table? Do you have a Primay Key in both tables?

To find the 'missing' records in second table you may do:

SELECT PKFieldA from TableA
WHERE PKFieldA [blue]NOT IN [/blue]
(SELECT PKFieldB from TableB)

Have fun.

---- Andy

 

[MattGreer]

It's working now and I don't understand why it wasn't working before.

In the past I'd used Access to add records to another table, and it would skip over the duplicates (since I put "No Duplicates" on the appropriate field) and add just the new records.

For whatever reason, the database wasn't allowing me to do this.

I created an intermediate table to hold all the new records, rather than trying to update the target table by using several queries nested together. Either way, it's working now. :/

Thank you guys for your help! Stars for all of you.

Thanks!!


Matt