Solved

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

Posted on 2011-09-22
9
321 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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
 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

815 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

9 Experts available now in Live!

Get 1:1 Help Now