Right Join Update Query not Appending

[hext2003]

RIGHT JOIN Update Query will NOT append new records.

I thought by doing a right join update query, this would update any records currently in my table, and also append any new records.

I keep getting

1 record failed due to key violation.

(I only added one new record to test the append)

what am I missing?

TIA

 

[SoCalAccessPro]

1 record failed due to key violation.

Could be a duplicate key, or the table with the foreign key may not have a matchng record.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[hext2003]

Thanks for the Reply

I finally got it to work.

I had to turn off Referential Integrity

Then it works like a charm!

Thanks!

 

[PHV]

I had to turn off Referential Integrity
So, you really wanted to create orphan records ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hext2003]

Well yes and no. I am working on pulling together information from several departments and their computer systems. To do this I am pulling in 4 tables (one from each department) in one case I have a one to one relationship with two tables. Each night I run the update query pulling in the 4 tables and update (and append) any new records. The update isn't a problem. It's when I need to add a new acct.

With a one to one relationship no matter which way I try and do the append I get a KEY VIOLATION error and no records appended. (normally you do the one side of a one to many relationship first) If I turn off Referential Integrity then run the two queries I can add this acct to both tables. Then I turn it back on. So really I don't want orphan records but to get the info into both tables I have to have an orphan record for the minute or two it takes the queries to run.

If you have a different way of doing this please enlighten. These two tables probably really shouldn't be separate tables, but since they are coming from different sources it just seemed easier to pull them in separate.