What you have here is a many-to-many relationship between Applicants and
Steps. To model this you need three tables:
1. Applicants; with columns ApplicantID, FirstName, LastName etc,
2. Steps: with columns Step and StepNumber (there'll be 10 rows in this
table).
3. ApplicantProgress: with columns ApplicantID and Step. These are
foreign keys referencing the primary keys of Applicants and Steps. You might
have other columns such as DateAcheived etc. in this table.
Presumably the applicants are applying for something, so you'll have
another table for this, Positions say with a column Position and other
columns as necessary. This will mean that the ApplicantProgress table would
need another column Position as a foreign key referencing the primary key of
Positions. The primary key of ApplicantProgress would therefore be a
composite one of the ApplicantID, Step and Position columns.
For data entry purposes you'd have an Applicants form based on the
Applicants table. This would be in single form view. Within that form you'd
have a subform based on the ApplicantProgress table; this would be in
continuous form view. The subform would be linked to the main parent form on
ApplicantID, i.e. this would be both its LinkMasterFields and LinkChildFields
property.
In the subform you'd have combo boxes bound to the Step and Position
columns. The RowSource properties of these combo boxes would be:
SELECT Step FROM Steps ORDER BY StepNumber;
and:
SELECT Position ORDER BY Position;
Along with these combo boxes you'd have controls bound to any other columns
in the table, such as DateAcheived. You don't need a control for the
ApplicantID column, however, as that's inserted automatically into the
underlying table by the linking mechanism. To record an applicant's progress
therefore you simply insert rows in the subform, one for each step achieved,
selecting the relevant step and position from the combo box in each case.
It would in fact be possible to set up a form along the lines you have in
mind, with 10 check-boxes, one for each step, rather than using a subform.
These would be unbound controls, however, so you'd need to write code in the
form's module to update the ApplicantProgress table when the boxes are
checked, and to assign values to the boxes from the data in the table as you
navigate to each applicant. This is not unduly difficult for someone
reasonably familiar with VBA programming, but I suspect might be beyond your
current level of experience. A bound form/subform as described above is far
simpler to implement.
Ken Sheridan
Stafford, England
