How to display Hazards based on a finite number of Hazard Categories?

This is a fairly basic One to many relationship, so you need a HazardCategory table

tblHazardCategory
HazardCategoryID
HazardCategoryName

tblHazards
HazardID
HazardCategoryID
HazardName


Then you can display the corresponding Hazards for a category with something like this:

SELECT tblHazardCategory.HazardCategoryID, tblHazardCategory.HazardCategoryName, tblHazards.HazardID, tblHazards.HazardName
FROM tblHazardCategory INNER JOIN tblHazards ON tblHazardCategory.HazardCategoryID= tblHazards.HazardCategoryID
WHERE tblHazardCategory.HazardCategoryID=2

So in your case you could create a main form of categories, and a subform of hazards.
Or use one of the many "Cascading Lists" techniques.


I am sure you can apply this logic to your design

JeffCoachman

boag2000:
   I have attached the work I did based on your suggestion. However, I am confused about how to relate the fields in the table?
Did I do that part correctly?
How will the user know what AHA the categories and hazards they are selecting belong to?

Also, the Hazard Categories and Hazards do not display?
9-22-2011.mdb

I really don't know the full scope of the purpose of this syetem is, so I cant tell you what to do.

Some notes:
The Hazards table do not have the HazardsCategoryID's listed...?
You have a "Selected" field in both tables.  I am not sure what these fields are doing...
But if you are using them to "select" categories and/or Hazards, I can't see how this would ever give you a "history".
Also with a system like this you have to keep running code to "Reset" the selections.

Again, if you created a simple main form of HazardCategories and a subform of Hazards, this should work, so do that first as a test.

boag2000:

1. Q - Full scope of project is:
    A - What you see is what it is.

2. Q - The Hazards table do not have the HazardsCategoryID's listed...?
    A - Done

3. Q - You have a "Selected" field in both tables.  I am not sure what these fields are doing...
    A - So user can select the Categories and Hazards within the selected categories.

4. Q - But if you are using them to "select" categories and/or Hazards, I can't see how this would ever give you a "history".
    A - Sorry, I do not understand the question.

5. Q - Also with a system like this you have to keep running code to "Reset" the selections.
    A - Yes, but I do not know how to write code to do this. Is this a separate question?

6. Q - Again, if you created a simple main form of HazardCategories and a subform of Hazards, this should work, so do that first as a test.
    A - Done, please see attached DB

9-22-2011.mdb

1. Q - Full scope of project is:
    A - What you see is what it is.
...That's the point, I am confused as to why this form is designed in this way, or what this data is being used for...
I am not saying that it is wrong, only that I cant make a recommendation, without knowing a bit more about the usage/purpose, or how any changes might affect the existing functionality of the DB.


2. Q - The Hazards table do not have the HazardsCategoryID's listed...?
    A - Done
Not in the sample db you just posted, ...see the attached screenshot

3. Q - You have a "Selected" field in both tables.  I am not sure what these fields are doing...
    A - So user can select the Categories and Hazards within the selected categories.
4. Q - But if you are using them to "select" categories and/or Hazards, I can't see how this would ever give you a "history".
    A - Sorry, I do not understand the question.
Once a user selects a Category(s) where are these selections used... Do they need to be stored?
In other words, why are they selecting categories/category types?
Just give me a simple example...

Yes, this is a separate question, but it needs to be considered  here at some level to address this issue...


JeffCoachman


untitled.JPG

boag2000:
  I will try my best to provide the accurate information:
Q - Full scope of project is:
1. The purpose of this db is so I can create Activity Hazard Analysis. My job requires me to create theses AHA's. I just thought that this DB would make it easier and faster for me to create the AHA's.
To provide you more on what an AHA is I have provided attached a sample report. Basically, an AHA really consists of 4 areas:
1. Header Information
2. Step Information
3. Hazard Information
4. Control Information
______________________________________________________________
2. Q - The Hazards table do not have the HazardsCategoryID's listed...?
    1. The field is their however, I don’t know how it will update? I see the image you attached, but nothing is in the field? Why? I do not know?
________________________________________________________________
3. Q - You have a "Selected" field in both tables. I am not sure what these fields are doing...
A - So user can select the Categories and Hazards within the selected categories.
4. Q - But if you are using them to "select" categories and/or Hazards, I can't see how this would ever give you a "history".
1. You are right. I will need a history, I never thought about that. What do you suggest?
A - Sorry, I do not understand the question.
Once a user selects a Category(s) where are these selections used... Do they need to be stored?
In other words, why are they selecting categories/category types?
Just give me a simple example...
1.      I see what you mean now. The Categories and Hazards both need to be stored so they can be used on different AHA’s and to make the reports based on the user selecting the name of the aha they want printed.  Also, see the attached AHA
Thanks so much.

AHA---P213---Crane-Rail-Installa.doc
9-22-2011.mdb

I'll try to look at this this weekend

boag2000:
 Thank you very much