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.

 

[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