update query with multiple keys

[lifter10]

This one seems so simple, but I just cannot figure it out. I have two tables, the first is a table listing all assets for a specific company, the second is a table of adjustment figures for those assets. So....

Table 1 Table 2

Company Company
Asset Code Asset Code
Value Adjustment to Value

Basically all I want to do is update Table 1 with the values of Table 2 by doing Value + Adjustment to Value. However it's got to match up with the proper Company and proper Asset Code (hence the multiple keys). I'm having a hell of a time trying to figure this out but it seems so simple.

Thanks in advance,

Chris

 

[JonFer]

Did you set the primary key for each table to be Company and Asset Code? That should allow you join the tables in the Query Design window and do the update.

 

[lifter10]

Unfortunately, it won't let me create a primary key on the Adjustments table because there are duplicate values and it won't accept the multiple key of Asset Code & Company.

 

[BNPMike]

You're going to have to clean up your data then.

 

[lifter10]

This is what I don't get about it. To me a composite key is two fields when put together form a unique ID, but separately they are not unique. So, why can I not have duplicate values for both fields?

Example: Account and Center combinations

Act Center
8137 79T39
8131 79T39
8137 79T37
8137 79T36
8131 79T36

Although there are duplicate values in both fields, when the two fields are used in conjunction with each other, they are unique. Am I thinking about this wrong?

 

[BNPMike]

No, you're thinking about it right.

Either you are putting the constraint on each field separately (which is obviously not the same) or you have some duplicates in there that shouldn't be there.

Do a query that counts records grouped by Center grouped by Act and see if any counts are greater than 1.