Update Query With 3 Tables

[Excel_Kid1081]

Hello-

I have a question regarding doing an Update query from one table to another when the table relationships look like this:


tblProjects
Project_Code-Primary Key
Project_Description

tblSecurities
Security_ID-Primary Key
Project_Code

tblHoldings
Security_ID-Primary Key
IssueType


What I am trying to do is update the IssueType in tblHoldings with the Project_Description from tblProjects. When I run a SELECT query the results work (i.e. I put in the tblHoldings.Security_ID,tblProject.Project_Description for the output fields), however, when I run an update query the Issue_Type shows up blank despite putting tblProjects.Project_Description in UPDATE TO:

Here it is in SQL if this helps:
UPDATE (tblProjects INNER JOIN tblSecurities ON tblProjects.Project_Code = tblSecurities.Project_Code) INNER JOIN tblAllHoldings ON tblSecuities.Security_ID = tblAllHoldings.Security_ID SET tblAllHoldings.IssuerType = tblProjects.Project_Description;

Are my table relationships just not correct or can you not use three tables in an update query?

Thanks!!

EK

 

[RainLover]

Your table relationships are not correct.

What is the relationship of tblHoldings to the other tables. I can't see one.

 

[Excel_Kid1081]

Security_ID is what links tblHoldings to tblSecurities. The reason they are separated is that tblSecurities will be a historic list of securities while tblHoldings will only be the most current and have numerous other attributes as well. Would this not be the best way to set-up a relational table?

 

[Pat Hartman]

Securities and Holdings have the same primary key. That gives them a 1-1 relationship. However, your description indicates a 1-m relationship so you would need to add a different PK to securities and leave the existing PK as a FK.

Your update query is using a different table name than your text indicates for tblHoldings.

And finally, why would you have the same piece of data in two places?

 

[RainLover]

And finally, why would you have the same piece of data in two places?

I agree with Pat. Your table structure is incorrect. Instead of two tables simply add an additional field and mark it as "Old". or something more meaningfull.