Update field in one table with value from another

  • Thread starter Thread starter Ann Scharpf
  • Start date Start date
A

Ann Scharpf

I have two related tables:

VendorFundedHrs
EmployeeVendorAssignment

The key to the VendorFundedHrs (ProjectCode) is a longish alphanumeric
string which I thought would be unwieldy to work with. So I created another
three-digit field, VendorID, which I added to both tables.

Now I would like to link the tables the way I SHOULD have in the first place
.... with the ProjectCode. I'm trying to add the field to the
EmployeeVendorAssignment and then use an update query to insert the correct
value (from the VendorFundedHrs) into the employee's record. I'm not really
proficient in VBA - I can copy from stuff here but I can't write it from
scratch. So, I'm trying to use the graphic interface in the query to tell
Access to take the VendorFundedHrs:[ProjectCode] and insert it into the new
field in the EmployeeVendorAssignments table.

I figure there must be a way to do this but I haven't been able to figure
out how to do it; and so far I'm not seeing quite this type of update in the
Community Newsgroups posts. Can someone please point me in the right
direction?

Thanks very much for your help.
 
Ann:

Crteate a query joining both tables on the VendorID columns. Add the
ProjectCode column from the EmployeeVendorAssignment table to a column in
the design grid. Change the query type to an 'update query' and in the
'update to' row of the column put.

[VendorFundedHrs].[ProjectCode]

Run the query and then try creating a query in which the two tables are
joined on the ProjectCode columns. Once you are satisfied that the result is
correct you can delete the redundant VendorID columns from the tables and
create a new relationship between them on ProjectCode.

Ken Sheridan
Stafford, England
 
PS: Make sure you also index the new ProjectCode column in
EmployeeVendorAssignment non-uniquely (duplicates allowed).

Ken Sheridan
Stafford, England
 
Thanks! I had tried that but I had the wrong syntax for the field name. I
think I did VendorFundedHrs:[ProjectCode] or something like that!

Using the correct field name – ahem! – it worked like a charm.
 
The update to needs to be the field that you update from. It is backwards on
access. Update to means make it look like this one. Also, the format is
![field]

Ann Scharpf said:
Thanks! I had tried that but I had the wrong syntax for the field name. I
think I did VendorFundedHrs:[ProjectCode] or something like that!

Using the correct field name – ahem! – it worked like a charm.

--
Ann Scharpf


Ken Sheridan said:
Ann:

Crteate a query joining both tables on the VendorID columns. Add the
ProjectCode column from the EmployeeVendorAssignment table to a column in
the design grid. Change the query type to an 'update query' and in the
'update to' row of the column put.

[VendorFundedHrs].[ProjectCode]

Run the query and then try creating a query in which the two tables are
joined on the ProjectCode columns. Once you are satisfied that the result is
correct you can delete the redundant VendorID columns from the tables and
create a new relationship between them on ProjectCode.

Ken Sheridan
Stafford, England

"Ann Scharpf" wrote:
Click to expand...
Click to expand...
 
Back
Top