The first thing to understand is how a relational database stores data in
tables, each one of which represents an entity type, with the columns
(fields) of the table representing the attributes of the entity type. In
your case you have entity types Employees and Jobs to start with, so you
should have tables of those names. So that each row in each table can be
identified uniquely the tables each need a primary key, a unique value in a
column or set of values in more than one column. The easiest way to do this
is to give each table an autonumber column, EmployeeID and JobID; in the case
of jobs you could perhaps use a 'natural' key such as the job name, but
people's names can be duplicated, so a 'surrogate' numeric key is best for
employees.
The relationship type between Employees and Jobs is many-to-many, i.e. each
employee might be assigned to one or more jobs, and each job might have one
or more employees assigned to it. This type of relationship is modelled by
another table, Assignments say, with columns EmployeeID and JobID. These are
not autonumbers in this case but straightforward long integer number data
types. Each column is a foreign key which references the primary key of one
of the other tables, so the relationship is like this:
Employees----<Assignments>----Jobs
The Assignments table is a special type of entity type because it is also a
relationship type; in fact the latter is just a special kind of the former.
Consequently it can have its own attributes as other columns besides the two
key columns. You might for instance have date/time data type columns for
when an employee's assignment to a job starts and finishes.
The primary key of Assignments is a composite one made up of the EmployeeID
and JobID columns these will be unique in combination (unless you assign the
same employee to the same job more than once on different dates, in which
case you'd add the DateAssigned column to the primary key).
Having set up the tables you can then set up forms for data entry. You'll
probably have one form based on the Employees table for entering the employee
records, but the main form for your present task will be based on the Jobs
table, so start by creating that in single form view. Within this form you
will put a subform for assigning employees, so create a form based on the
Assignments table; this form should be in continuous form or datasheet view.
The form which you'll use as the subform does not need any control bound to
the JobID column as this has its values entered automatically via the link
with the main jobs form, but will need one bound to the EmployeeID column.
This should be a combo box and set up to show the employee names rather than
the underlying EmployeeID value. Assuming the Employees table has columns
FirstName and LastName, set up the combo box with properties as follows:
RowSource: SELECT EmployeeID, FirstName & " " & LastName AS FullName
FROM tblEmployees ORDER BY LastName, FirstName;
BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
Add any other controls bound to columns such as the start and end dates for
the employee's assignment to the form as necessary.
Once you've designed the form to be used as the subform embed it in the jobs
form you created earlier as a subform. You can do this in several ways, but
the easiest is to open the jobs form in design view and make sure its got
some empty space at the bottom for the subform. Arrange the screen so you
can see the form in design view and the database window, open at the forms
page, at the same time. Pick up the form to be used as the subform from the
database window with the mouse and drag and drop in onto the jobs form in the
required position. You can then adjust its position and size as necessary
with the mouse.
With the subform control (that's the one on the jobs form which now contains
the subform) selected in design view have a look at its LinkChildFields and
LinkMasterFields properties in its properties sheet. They should both say
JobID; if not then simply enter that in both. Save the form and open it.
You should now be able to page through the jobs in the main form, editing
and entering job data as necessary. In the subform you can select however
many employees you want to assign to a job by selecting from the combo box's
list. Add a new row (record) in the subform for each employee to be assigned
to the currently selected job.
Ken Sheridan
Stafford, England
