How To Fill In Multiple Fields From A Form

[SamanthaSamuels]

Hi.

Help!

I have a table with employees' names. Along with the employees' names, I have department and job for each employee. The table has only one employee.

I have a second table with training material. The training material is one field in the second table. The next field is department and the last field is job. This table is to decide which departments and jobs need to be trained on the training material.

Finally, I have a third table of training documentation. It documents the training each employee has received.

Now, I need to Query the following:



When an employee is selected from the first table using a drop down form, I need it to fill in the department and job for that employee so I can store the information in another query. In other words, there is only one Bob Miller. I need to be able to select Bob Miller's name and not only see his department and job but be able to store that information. I see that a combo box will allow me to view the information, but I don't know how to actually store that value for later use.

Help!

[jzwp11]

Welcome to the forum!

Before we get to your query/form question, it appears that your table structure needs some work, and for any successful relational database application, the table structure is critical. First I have a couple general guidelines. I would recommend that you have an autonumber primary key field in every table. This gives each record in the table a unique identifying number. Also, I would recommend that you not use spaces or special characters (!,#,$,&,* etc.) in your table or field names.

You said that you have an employee table

tblEmployee
-pkEmployeeID primary key, autonumber
-txtEmpFName text field for the employee's first name
-txtEmpLName text field for the employee's last name

If an employee can have only 1 job and 1 department, then you would have fields in tblEmployee to hold that information. However, you probably have multiple departments and multiple jobs, so instead of typing the department and job names over and over for each of your employees, it would be best to have separate tables to hold this information and then reference them in tblEmployees. You would use the primary key field of a table as a foreign key in another table to "reference" or relate the information in the tables.

So, create 2 new tables, one to hold the jobs and one to hold the departments:

tblJobs
-pkJobID primary key, autonumber
-txtJobTitle text field to hold the job title
(other fields related to the job)

tblDepartments
-pkDeptID primary key, autonumber
-txtDepartmentName text field that holds the name of the department
(other fields related to the job)


Now we can reference the job and department to the employee:

tblEmployee
-pkEmployeeID primary key, autonumber
-txtEmpFName text field for the employee's first name
-txtEmpLName text field for the employee's last name
-fkJobID foreign key field related to tblJobs, long number datatype field
-fkDeptID foreign key field related to tblDepartments, long number datatype field

I guess the next big question is what happens if a person changes jobs or departments? Do you want to track those changes over time? If so, then the tblEmployee I show above will not work because then a person can be associated with many jobs and/or departments (over time) which describes a one(employee)-to-many(jobs) relationship or a one(employee)-to-many(departments) relationship. In each case that would be handled with a separate but related table. You'll have to let us know what you plan to do regarding that issue since it will impact your table structure as well as any query, form or report associated with it.

Another way to handle jobs and departments is to associate the particular jobs with each department. This might make your training material issue a little easier to handle. In terms of relationships, a department can have many jobs associated with it (one-to-many relationship). But, you may have a job that is applicable to many departments (such as a secretary). This would also describe a one-to-many relationship. So when there are 2 one-to-many relationships between two tables (jobs and departments in your case) you have a many-to-many relationship which is handled with a junction table as follows

tblDepartmentJobs
-pkDeptJobsID primary key, autonumber
-fkDeptID foreign key to tblDepartments, long number field
-fkJobID foreign key to tblJobs, long number field

We can then use this relationship between departments and jobs in our employee table (assuming that we only want to record the employee's current job/department & we don't want to track their history over time)

tblEmployee
-pkEmployeeID primary key, autonumber
-txtEmpFName text field for the employee's first name
-txtEmpLName text field for the employee's last name
-fkDeptJobsID foreign key field related to tblDepartmentJobs (long number datatype field)



In terms of your training materials, I would probably put these in a separate table:

tblTrainingMaterials
-pkTrainMatID primary key, autonumber
-txtTrainingMaterialName
(other fields related to the training material)


Since many jobs/departments may need to receive the training material, you again have a one(training material)-to-many(jobs/departments) relationship. Also, since a job/department may require many training materials (another one-to-many relationship), we again have a many-to-many relationship which requires a junction table:

tblTrainingMaterialsJobDepts
-pkTrainMatJobDeptID primary key, autonumber
-fkTrainMatID foreign key to tblTrainingMaterials (long number datatype field)
-fkDeptJobsID foreign key to tblDepartmentJobs (long number datatype field)


I know that this is a lot to digest, so I will stop here.

To help you better structure your tables, we need to get a better understanding of the process you are trying to model. Just from your initial post it sounds like you are trying to capture employee training information. Can you provide more details as to what you hope to accomplish with the database?

[SamanthaSamuels]

Thanks for your feedback. I figured this out on my own.

[jzwp11]

Glad to hear it! Good luck with your project.