Multiple Tables into a table or form

[DavidRS]

I am currently designing a database to solve our organisation's training requirements. The db needs to be able to identify an individual member of staff at any particular site and list all the medical devices that they have training on and to what level of competency. So far I have five separate tables:

Staff details
Site
Devices
Competency/Risk
Equipment

By using unique identifiers within each table, we can, in theory, comply with the requirements.

Now here is the problem. Having talked it through with a colleague, we have two possible options.

A. Using a form based on the staff member, combine the salient details and, where needed, create additional fields (such as dates and tick boxes) to record odd items (this is my preference)

or

B. Based on the site, create a make-table query and form a table, sorted by site and then staff, listing all the equipment and competency levels. Given that some staff will work at multiple sites, the table will probably contain over 6500 records with lots of duplication (you can see my bias!)

Which solution is probably the best or should we be looking at solution that combines elements of both?

Thanks in advance

David

 

[FoFa]

Sounds like you are a table short.
How about a (for lack of a better term) cross ref. table.
Basically it needs to store a bunch of keys, and some unique data about the references.

PKey for this table (autonumber?)
FKey to Staff member
FKey to Site
FKey to Device
FKey to equipment (what's the diff. between device and equip?)
FKey to Competency/Risk
Additional Field like:
Dates required
Comments, check box etc.

Now you can have as many entries as you like.

 

[Pat Hartman]

Option b is really the other side of the relationship but is not properly described. I also don't know what the difference between Device and Equipment is. Based on your description of what you need, the relation table you are missing is between Staff and Device. Site is an attribute of Staff and has nothing to do with Device. Competency is an attribute of Staff + Device and so belongs in the relation table along with date and any other attributes of the relation.

tblStaffDevice
StaffDeviceID (autonumber primary key)
StaffID (unique index fld1, foreign key to tblStaff)
DeviceID (unique index fld2, foreign key to tblDevice)
CompetencyLevel
CertificationDate
etc.

Download my Many-to-Many example. It includes forms so you can see how the relation records are created. The example shows two forms each with a subform so you can manage the relation from either perspective.

In your case one side is straight forward - Staff to Device. The other side has an extra step - Site - Device - Staff. But the same relation table (tblStaffDevice) can be managed from either direction. My example is straight forward in that it shows a(b) and b(a). Your second form will be a main form (Site) - subform (device) - subsubform (staff).

 

[DavidRS]

Thanks to both of you and sorry for not getting back to you sooner. I got temporarily yanked off the project and put onto something else, so my focus has been elsewhere.

I'm sorry for the confusion about devices and equipment - they are one and the same. I have a bad habit of switching terms mid sentence!

Pat - your Many-to-Many solution seems to be the way to go and I am trying to make it work on a larger scale due to goalposts shifting!

Thanks once again

David