AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
Expert: Scottgem Date: 7/4/2008 Subject: Setting up a database with multiple choices
Question I've been tackling this problem for weeks and gone down many dead ends. I've been tasked with designing a database to store job hazard information and I'm having trouble getting started. I'm hoping you can send me down the right road with the design aspect and how best to store the information in tables and establish the correct relationships.
Here is the structure:
1) We have many work units at our location.
2) Each work unit performs many activities.
3) Each activity can have a variety of inherent hazards (ie. biological, asbestos, etc.) The number of hazards can range from 1-30.
4) For each activity there are associated losses that can occur (ie. building damage, eye damage, etc.) Again, there can be 1-30 of these.
5) For each activity there needs to be control measures in place. There are about 15 to choose from, and an activity can have 1-15 of the controls in place.
This database will be available to a large number of users, so I need to make it very user friendly. I envisioned a form with various check boxes on tab sheets so users can just look at the page and tick off the hazards, etc. that apply to their particular activity. On one hand, I think I should have a parent table of “work unit” and child tables of “Activity”, “Hazard”, “Risk”, and “Control”. However, for the best user interface, I have created a large table (I know, bad!) with yes/no fields that are each hazard, risk, etc. In your words, I believe I am wrongly “using field names to define data rather than using the Entity/Attribute concept”.
I’m totally stuck. Help!
I'll give you some information
Answer Yep those are my words ;) And yes it does sound like that's what you are doing.
So lets first take table design, I would do it like this:
tblWorkUnit
WorkUnitID (PK Autonumber)
details about Work unit
tblActivities
ActivityID (PK Autonumber)
WorkUnitID (FK)
detials about activity
While I agree that the form you envision will be user friendly its much harder to maintain especially with a proper database design. For example; if you change the list of hazards, you have to redo all your tables, forms, reports, queries, etc. With the structure (and interface I'm going to suggest, all you would need to do is add a record to one of the lookup (tlu) tables.
So the way I would do this is with a mainform bound to tblActivity and subforms on a tab control for Hazards, Losses and Controls. The subforms would all be continous forms with comboboxes to select the hazard, loss or control. You can setup the combo so a user can't select anything twice. I maintain that this is just as user friendly as multiple checkboxes.
If you still want to use checkboxes you can, but you will have to use unbound forms. You will then have to write VBA code to add a record for each checked item and to populate the form when retrieving a record.
Creating a normalized database will pay dividends when you want to retrieve data.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
