Update Table with FK

[VhbuiA]

Hi,

I am fairly new at VBA so please be nice =)
I am having problems updating a table with a foreign key if I already have a field that matches in the other table.

Please let me explain:

tblA
X (PK)
Y (FK)
Z (FK)

tblB
Y (PK)
Z (PK)

In table A, I already have data populated in Field Z. This field is populated from the original datasource. What I want to do is update tblA so that it contains the corresponding Field Y if tblA.z=tblB.z.

I've built the following SQL statement but it doesn't seem to be working. Please help!

DoCmd.RunSQL "Update tblA Set tblA.y=tblB.y where tblA.z=tblB.z"

I get an error message saying that it's missing something at the end of the statement.

Thanks!

 

[lupins46]

Update tblA inner join tblB
On tblA.z = tblB.z
set tblA.y = tblB.y

However,I can't see how this will produce a good result.
Given that Z is only part of the PK on tblB, it implies that there are multiple records on tblB with the same value of Z. These must all have different values of Y. So which value of Y are you expecting to end up with on tblA?

 

[VhbuiA]

Thanks lupins for your response.

I think I did not do a good job explaining. The problem is I get data from another source that gives me a ID code for an employee (X) and then an associated organization that the employee belongs to (Z). In order to normalize it, I had to create a reference table (tblB) that provides an ID code for that organization with Y being that code and Z being the name of the organization.

I basically want to update tblA to include the corresponding orgID (or Y) since I already have the orgname (z) provided to me.

Example
tblA
X Y Z
001 Alpha
002 Alpha
003 Beta

tblB
Y Z
1 Alpha
2 Beta

The resulting updated tblA would look like this:
tblA
X Y Z
001 1 Alpha
002 1 Aplha
003 2 Beta

Maybe if there's a better way to do this? I actually use tblB as a reference table in several other instances throughout the database.

 

[lupins46]

Then the query I gave in my previous post is what you want.