Solved

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

Posted on 2011-09-22
9
298 Views
Last Modified: 2012-05-12
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
Comment
Question by:cssc1
  • 5
  • 4
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36583801
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
 

Author Comment

by:cssc1
ID: 36584575
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36585122
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
 

Author Comment

by:cssc1
ID: 36587228

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36587575
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
 

Author Comment

by:cssc1
ID: 36592766
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36592926
I'll try to look at this this weekend
0
 

Author Comment

by:cssc1
ID: 36711826
boag2000:
 Thank you very much
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 36714429
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now