Trying to pull data from one table to another

[mrhartwig]

Hi there. I am trying to build a table ("Hours Worked") that will
have 3 fields: EE Name, Supervisor, and Hours. Instead of entering
each employee & supervisor name I was hoping to somehow pull this
information from my other table ("Roster") that contains all this
information. That way when changes are made (supervision changes) I
won't have to remember to make the change in 2 places.

I have tried to do an update query but it doesn't appear to be
working? This is what I've tried:

Field: Supervisor
Table: Hours Worked
Update to: [Roster].[Supervisor]

I have the same done for EE Name as well. Am I missing something?
Hope I'm making sense. Thank you so much for your help!

--Michelle

 

[John W. Vinson/MVP]

Hi there. I am trying to build a table ("Hours Worked") that will
have 3 fields: EE Name, Supervisor, and Hours. Instead of entering
each employee & supervisor name I was hoping to somehow pull this
information from my other table ("Roster") that contains all this
information. That way when changes are made (supervision changes) I
won't have to remember to make the change in 2 places.

I have tried to do an update query but it doesn't appear to be
working? This is what I've tried:

Field: Supervisor
Table: Hours Worked
Update to: [Roster].[Supervisor]

I have the same done for EE Name as well. Am I missing something?
Hope I'm making sense. Thank you so much for your help!

--Michelle

The solution is to not store anybody's name, EE or Supervisor, in the
Hours Worked table AT ALL. That's not how relational databases work!

The names should be stored in a Roster table, which should have a
primary key (EmployeeID perhaps). This might be an Autonumber, a Long
Integer, or (probably better) a text or numeric unique, unambiguous,
programmatically or manually assigned employee identifier (whatever
your HR department uses in its paper files perhaps).

The HoursWorked table (don't use blanks in tablenames) would have a
field of the same datatype as the EmployeeID for the EE and another
for the Supervisor; both fields would be linked to the Roster table in
the relationships window. To see the name you would create a Query
joining the HoursWorked table to two instances of the roster.

 

[pietlinden]

Hi there.  I am trying to build a table ("Hours Worked") that will
have 3 fields:  EE Name, Supervisor, and Hours.  Instead of entering
each employee & supervisor name I was hoping to somehow pull this
information from my other table ("Roster") that contains all this
information.  That way when changes are made (supervision changes) I
won't have to remember to make the change in 2 places.

I have tried to do an update query but it doesn't appear to be
working?  This is what I've tried:

Field: Supervisor
Table:  Hours Worked
Update to: [Roster].[Supervisor]

I have the same done for EE Name as well.  Am I missing something?
Hope I'm making sense.  Thank you so much for your help!

--Michelle

This is in Elmasri & Navathe "Fundamentals of Database Systems". Nice
explanation of normalization and proper database design.

 

[mrhartwig]

Okay, here is my problem. My roster is linked to a spreadsheet in
excel so I am unable to define a primary key in access. I would
prefer to keep my access roster a linked table, however, I did try to
import my data to an unliked table then run an update query but it
didn't work. I get the message that I won't be able to undue the
changes, I select yes and then it states I am about to update 789 rows
(which is 1 less than the total number of records) and asks if I am
sure...but nothing ever changes. Any other ideas? I would greatly
appreciate any feedback. Thanks!

 

[mrhartwig]

Nevermind! I figured it out!!! I wasn't able to make a primary key
on the roster table but I was able to make a primary key on the hours
worked table and that did the trick!!! Thanks anyway!