How to populate fields in one table from data in another table

[jackrabidd]

I have a blank table named "Staff" which has fields "First name, Last name,
ID#, Branch, Address, City, State, Zip, Phone and Fax".

I have a 2nd table named "Offices" which has fields "Branch, Address, City,
State, Zip, Phone and Fax" - basically the address info of our 10 branch
offices a staff member may belong to.

When entering new data for new employees in the "Staff" table, what do I
need to do once I get to the "Branch" field in "Staff" to select the "Branch"
value via a lookup in the "Offices" table, and have all of the address info
automatically populate the address fields in the "Staff" table ?

Thanks in advance.

Jack

 

[Jerry Whittle]

You don't. You shouldn't have the same data in two different tables. Instead
you create a form based on the Staff table. For the Branch field you make it
a combo box. The record source for this combo box is the Offices table. From
it you select the Branch data. Then the two tables are linked. If you need to
see the Staff and their Office data, you create a query with those two tables
joined.

Suggestion: Add an autonumber field in your Offices table and make it the
primary key field. Use that field to join the two tables together. Why? If
someone renames a Branch, the link to the Staff table will most likely be
broken and no longer return the proper records.

If any of the above doesn't make sense to you, I highly recommend getting
some relational database training or reading "Database Design for Mere
Mortals" by Hernandez before proceeding any further on this database.