Fortunately Wayne's reply alerted me to the fact that I'd somehow omitted to
reply to you last post in the thread to which he referred. I have now done
so, but am repeating what I've said below for completeness:
If I've understood you correctly you seem to have three main entity types:
1. People, who I assume are employees so I'll call the table for this
Employees. It will have one row per employee.
2. Document, so I'll assume a table called Documents. One row per documnent.
3. Procedures, revisions etc; I'll call this table Procedures. One row per
type of procedure etc.
There is a many-to-many relationship between Employees and Procedures as
each employee is authorised to undertaken one or more type of procedure etc,
and each type of procedure etc can be undertaken by one or more employee. A
many-to-many relationship type is modelled by a table with two (sometimes
more, but two in this case) foreign key columns each referencing the primary
key of the two tables in the many-to-many relationship, so you'd have a table
Authorisations say with foreign key columns such as EmployeeID and
ProcedureID. Together these two columns constitute the composite primary key
of the table so should be designated as such in the table design (highlight
both fields in design view, right click and select primary key from the
shortcut menu).
To record all the procedures, revisions etc applied to a document you need a
table DocumentProcedures say. This will obviously have a foreign key
DocumentID column, but it also needs a EmployeeID and procedureID foreign key
columns so that a relationship between DocumentProcedures and Authorisations
can be created on both columns, and referential integrity enforced. This is
important as it’s the enforcement of this relationship which ensures that
only authorised employees can apply a particular procedure etc to a document.
Finally you have an entity type Distribution which is related to documents
in a many-to-one relationship, so you need a table Distribution with a
foreign key DocumentID column and an EmployeeID foreign key column (for the
employees its sent to), so you'll have multiple rows in this table for each
document. It will also have columns for DistributionDate, Status etc.
I hope I've understood your business model correctly and that the above
'logical model' reflects it accurately. If so then you should be able to
create forms/subforms for data entry of documents and procedures and
distributions applied to each. You can also create a report to show the
distribution/procedure history of each document by means of a report with
embedded subreports broadly reflecting the form/subform for data entry.
Ken Sheridan
Stafford, England
