table relations & primary key

[mwabbe]

just a quick question about relationships and primary keys. i am working on an employee directory database including tables such as employee info, benefits, training and so on. there is an employee id field that im trying to get so that when it is entered on the first form that it will create a record in every table with that id so the tables are linked for thta record. any help? please also i was wondering about each table having an id field with an autonumber value, why would i do that?

thanks!

[jzwp11]

Having a primary key field in each table allows Access to assign a unique value to each record in the table. I generally have my primary key fields as autonumber. A primary key field should have no significance to the user; in fact the user should never see the key value. If you build the relationships between the main table (employee table) and the detail tables in the relationship window and then build a form based on the employee table that has a subform for each detail table, Access will automatically populate the foreign key values in the detail tables when you add a record in each subform.

[mwabbe]

ok awesome thanks. what about the employee id field i want that, once its entered on the main employee table to be auto entered into the field or text box in the subforms? is that possible?

thanks.

[jzwp11]

As I said in my earlier post, if you have the relationships set up properly and then build your main form with the subforms, Access should autopopulate the employeeID value of the main form into appropriate control in the subforms.

If I were structuring this, my tables would look something like this:

tblEmployees
-pkEmployeeID primary key, autonumber
-txtFName
-txtLName

tblTraining (just a table to hold the training courses)
-pkTrainingID primary key, autonumber
-txtTrainingCourseName

tblEmployeeTraining
-pkEmpTrainingID primary key, autonumber
-fkEmployeeID foreign key to tblEmployees, long number field
-fkTrainingID foreign key to tblTraining, long number field
-dteTraining (date field to hold the date when the training occurred for the employee)

You would build the relationships in the relationship window as indicate above (primary key-->to corresponding foreign key one-to-many relationship)

You would then create a form based on tblEmployee. You would also create a form based on tblEmployeeTraining (use a combo box to populate the training info). You would then drag and drop the form based on tblEmployeeTraining into your main form based on the tblEmployee. Access should automatically link the subform to the main form based on the relationships you created. You would add a record to the main form and then when you add a record to the subform, Access will autopopulate the control that is bound to the fkEmployeeID field.

[mwabbe]

is there any way to make a link to open the subform on the mainform

[mwabbe]

appreciate the help btw

[mwabbe]

i have

tblEmployee
-(pk)ID
-(fk)BenefitID
-(fk)TrainingID
-LN
-FN

tblBenefits
-(pk)BenefitID
-medicalPlan....and so on

tblTraining
-(pk)TrainingID
-...fields...


i have the relationship set up so that the (pk) in tblBenefits is matched with the (fk) in tblEmployee and same for tblTraining. with a one-to-many relationship.

does this sound right?

thanks!

[jzwp11]

is there any way to make a link to open the subform on the mainform

I'm not sure I understand what you are asking. You would actually drag and drop the subform into the main form (in design view) and then when you open the main form, you should see the subform

Assuming that an employee can have many benefits and main training items, your current table structure is not correct. With your current structure an employ can have only 1 benefit and only 1 training item.

[mwabbe]

tblEmployee
-(pk)ID
-LN
-FN

tblBenefits
-(pk)BenefitID
-(fk)ID
-medicalPlan....and so on

tblTraining
-(pk)TrainingID
-(fk)ID
-...fields...


how about that

[jzwp11]

If an employee can have many benefits, and a benefit can apply to many employees, then you need a junction table. Similarly, if an employee can have many training items, and a training item can apply to many employees, then you need another junction table for that.

tblEmployees
-pkEmployeeID primary key, autonumber
-LN
-FN

The following table only holds the list of benefits (1 record for each benefit):

tblBenefits
-pkBenefitID primary key, autonumber
-txtBenefitName

The following table only holds the list of training items (1 record for each training item):

tblTraining
-pkTrainingID primary key, autonumber
-txtTrainingName

Now the juction tables....

The following joins each employee with their benefits:

tblEmployeeBenefits
-pkEmpBenefitsID primary key, autonumber
-fkEmployeeID foreign key to tblEmployees, long number datatype field
-fkBenefitID foreign key to tblBenefits, long number datatype field


The following joins each employee with their training:

tblEmployeeTraining
-pkEmpTrainingID primary key, autonumber
-fkEmployeeID foreign key to tblEmployees, long number datatype field
-fkTrainingID foreign key to tblTraining, long number datatype field