Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-22
9
Medium Priority
?
353 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

877 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