Update a field from one table to another?

  • Thread starter Thread starter CW
  • Start date Start date
C

CW

In my Corporates table I have CorpName and CorpID.
In my related table CorpContacts I have CorpName already but have only just
added the field CorpID and now I need to input all those values.
I'm guessing I can use an Update Query to do this (?) i.e. to say that where
the CorpNames are the same, the CorpName should be copied from Corporates to
CorpContacts. But how, please?
Many thanks
CW
 
If you are trying to keep both CorpID and CorpName in two tables, stop now!
You'd do something like that if you were using a spreadsheet, but Access is
a relational database. Access' features/functions are optimized to work
with well-normalized data, not 'sheet data.

If "relational" and "normalization" aren't familiar, plan to brush up.
Otherwise, you will be always coming up with work-arounds to try to
compensate for the fact that Access has to work harder if it doesn't have
'good' data.

What is the relationship between Corporations and CorporateContacts? ... in
YOUR situation. Can you have multiple contacts at one corporation? ... or
only one?

You can pay now (make sure your data is optimized for Access) or pay later
(keep coming up with work arounds).

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
In my Corporates table I have CorpName and CorpID.
In my related table CorpContacts I have CorpName already but have only just
added the field CorpID and now I need to input all those values.
I'm guessing I can use an Update Query to do this (?) i.e. to say that where
the CorpNames are the same, the CorpName should be copied from Corporates to
CorpContacts. But how, please?
Many thanks
CW
Click to expand...

I'd do this in several steps. BACK UP YOUR DATABASE FIRST!!!

I'll assume that the CorpName is currently the Primary Key of corporates and
is linked to a CorpName field in CorpContacts; and that you've added and
populated an Autonumber CorpID field in Corporates and added a (now empty)
Long Integer CorpID field to CorpContacts. If these assumptions are wrong post
back.

Create a Query joining CorpContacts to Corporates by CorpName (which I gather
is the current linking field). Change it to an Update query and Update the
(newly added) CorpID field in CorpContacts to

[Corporates].[CorpID]

The brackets are required (or it will try to update the Long Integer field to
a text string "Corporates.CorpID" and fail).

Now open the Relationships window and (if there is a relationship on CorpName)
select the join line and press the delete key. Then drag the CorpID field from
Corporates to CorpContacts, and enforce referential integrity.

You'll probably then need to change all the master/child link fields in your
form/subforms to use the ID rather than the name. You'll also need to fix some
combo boxes I'd expect.

Once you have everything linked by ID instead of by name, test everything
thoroughly; if it all works, open CorpContacts, take a deep calming breath,
and delete the CorpName field. Test everything again (you might need to
replace some textboxes showing the corpname with combo boxes, and/or change
some reports to join both tables rather than using the CorpContacts name
field).
 
Back
Top