Updating one table through another table

[probablyjoel]

I'm having an issue updating data in my database I'm creating.

Basically, we are using a linked table [txlsPayforceUpload] from our HRIS as the primary information. Since I cannot create a primary key on this linked table, I have an append query which moves the data to a table where I can identify [EmplID] as the Primary Key. I need this to have referrential integrity with the other data driven off this table.



My issue is that the feed which comes from our HRIS changes weekly, and the table to which I'm appending data [tblPayforceUpload], a) will not allow me to append the data, as the [EmplID]'s cannot duplicate, and b) I can't run a delete query and THEN append the data, as the other tables are linked to the [EmplID] with referrential integrity. I can't have it cascade delete, as it would delete everything.

Ideally, I would like to run an Update Query to simply update all fields in [tblPayforceUpload] based on what is in the linked table [txlsPayforceUpload]. I tried to run an expression through the update query for one table field to another, but it didn't work. P.S... I'm a n00b, so I'm totally stumped.

Any ideas?

[Robeen]

See if the information on this page helps:
http://www.techonthenet.com/access/queries/update2.php

[probablyjoel]

Thank you for your post. It works now!

I actually did try that exact method before posting and was getting errors. Apparently, my issue was that I was trying to update through a linked table. Once I created one extra step to take that data, append it to a new table, then run the update off that table, it worked perfectly.