[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

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

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

PROBLEM BACKGROUND
A.      My DB contains the seven (7) Hazard Categories listed below:
1.      Physical
2.      Chemical
3.      Ergonomic
4.      Environmental
5.      Natural
6.      Biological
7.      Radiological
This is the exhausted list of Hazard Categories and cannot be added to.
B.      The DB contains the two (2) tables listed below:
1.      tblAHA
2.      tblHazards
C.      1. The DB contains two (2) forms listed below:
1.      AHA-1of2
2.      AHA-2of2
PROBLEM DESCRIPTION
The problem I am having is trying getting form “AHA-2of2” populated with just the Hazards that are in the Categories that the user has selected in form “AHA-1of2”.

9-22-2011.mdb
0
cssc1
Asked:
cssc1
  • 5
  • 4
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
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


0
 
cssc1Author Commented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
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.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
cssc1Author Commented:

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
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
cssc1Author Commented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
I'll try to look at this this weekend
0
 
cssc1Author Commented:
boag2000:
 Thank you very much
0
 
Jeffrey CoachmanMIS LiasonCommented:
The design is still deeply flawed...
Your Hazard category names are the same as the Hazard names?
There are other linking oddities as well.

You have got to have *all* tables designed and related correctly *before* you ever think about making any forms...

here is how I would handle the design.
I then built a simple three level nested form with the form wizard.

Now since the design is correct (Again, as best as I can tell, from this example, which is based on your example) ...then you can go about designing an interface that will display the info in the manner you wish.

So here you see that most of your issue revolve around the tables not being designed properly, this leads to issue with your forms.
So *Never* even think about creating a single form until you make sure that *All* of the tables are designed, normalized and related properly.

JeffCoachman
Database39.accdb
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now