Adding field from one table to another

[SusanCB]

Hi, all -- here's the situation:

1. I have a table called People with fields called ID (the primary key) and Employee_ID.

2. I have another, imported table called Enrollment that also has an Employee_ID field.

3. I'm trying to put the data from People.ID into a new Enrollment.ID field.

Since I'm still very much an Access newbie I'm not sure how to do it. I tried creating an Update query but got 0 records (hey, that's better than an error message, right?). Thanks for any help you can offer.

 

[John Big Booty]

Why not use a Combo box that is populated by your People table and simply use it to store the Employee_ID in your Enrolment table?

Additionally you might want to think about implementing a naming protocol for your various database object, something along the lines of TBL_TableName, FRM_FormName, QRY_QueryName etc. Limit yourself to alpha and numeric characters and the underscore(_) avoid spaces and other special characters at all costs. This will make identifying the various objects and referring to them once you start writing code a whole lot less traumatic.

 

[SusanCB]

Thanks, John. Good advice on the naming. I still want to add the ID numbers from the People table to the Enrollment table (linked by Employee_ID) -- long, convoluted story. Any thoughts?

 

[John Big Booty]

How do the PeopleID and the EmployeeID relate

 

[SusanCB]

Thanks again for the speedy assistance.

People.ID is the primary key from the People table. It identifies each person's record within the database.

Employee_ID is a six-digit employee number given by HR.

I want to put the People.ID field into the Enrollment table, which shows the classes each employee is enrolled in. Right now the Enrollment table has each employee's Employee_ID number but not their People.ID (primary key from the People table).

Hope this makes sense!

 

[John Big Booty]

You really only need to store one of those numbers in your Enrolment Table and the other can be collected using a query at any time you need to display it. This post explains how you can do it using a combo box.

You might also like to read up on Data Normalisation. You might also find this tutorial useful.

 

[SusanCB]

Great, thanks again! One of the problems: not everyone in People has an Employee_ID number. So I'm still posing my initial question.

 

[John Big Booty]

OK store the PeopleID it's a better number anyway (I'm assuming it's an Autonumber) as it is generated by the system there is no chance of an operator keying error occurring.

Now to fix this what you will need to do is add an additional field to your Enrolment table called PeopleID, now using an update query you should be able to insert the correct PeopleID based on the employeeID's that are already there. Once you have all the PeopleID's updated correctly you can delete the EmployeeID field from the table.

 

[SusanCB]

Yes, thanks, that is exactly what I'm trying to do. It's just that my update query is failing. Any advice/pointers on how to do it properly? Thank you again.

 

[Brianwarnock]

I don't mean to be rude but I have seen this done, did you run the query or view the results with the view icon.

Posting your SQL will help

Brian

 

[John Big Booty]

The way I normally do this is to start with a select query, and make sure it is picking up the appropriate records. Once I'm satisfied that it is collecting the data I'm after I convert that query to an Update query.

 

[SusanCB]

Oh, jeez -- just realized what I'd done wrong: I'd linked the wrong fields between the two tables. Once I fixed that, the query worked like a charm. Sorry for being a timewaster -- I really do appreciate your help and patience. This forum is awesome (believe me, I read about 100 previous posts for every question I ask).

 

[John Big Booty]

Glad you got it sorted