Query design for Category and Hazard Selection
OBJECTS
In this sample database there are three tables:
1 – tblAHA
2 – tblHazard
3 – tblProject
CURRENT RELATIONSHIPS
Please see attached image
PROBLEM INFORMATION
The following two tables have the seven Categories of Hazards:
1 – tblAHA
2 – tblHazard
The seven Categories of Hazards are:
1 – Physical
2 – Chemical
3 – Biological
4 – Environmental
5 – Radiological
6 – Natural
7 – Ergonomic
PROBLEM
I am trying to create a query (no code, I don’t understand it) that displays “Hazards” from based on the Categories the user selects.
EXAMPLE:
A user selects Physical as the Hazard Category. Contained in the Physical Hazard Category are the following physical hazards:
1 – Slip
2 – Trip
3 – Fall
Based on this example, only the Physical Hazards (Slip, Trip & Fall) would be displayed.
RELATIONSHIPS-9-13-2011.jpg
In this sample database there are three tables:
1 – tblAHA
2 – tblHazard
3 – tblProject
CURRENT RELATIONSHIPS
Please see attached image
PROBLEM INFORMATION
The following two tables have the seven Categories of Hazards:
1 – tblAHA
2 – tblHazard
The seven Categories of Hazards are:
1 – Physical
2 – Chemical
3 – Biological
4 – Environmental
5 – Radiological
6 – Natural
7 – Ergonomic
PROBLEM
I am trying to create a query (no code, I don’t understand it) that displays “Hazards” from based on the Categories the user selects.
EXAMPLE:
A user selects Physical as the Hazard Category. Contained in the Physical Hazard Category are the following physical hazards:
1 – Slip
2 – Trip
3 – Fall
Based on this example, only the Physical Hazards (Slip, Trip & Fall) would be displayed.
RELATIONSHIPS-9-13-2011.jpg
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
We've been over this one before.
As plummet indicates (and as I said in your earlier question), you must implement a structure where you have tables to store both the Hazard and the Category, and you must relate your Hazard to the Category it belongs to. I personally would do it like this:
tblHazardCategory
--------------------------
ID
CatName
tblHazard
----------------------
ID
Hazard
HazardCatID
So you'd have data in tblHazardCategory like this:
And data in tblHazard like this:
You'd then relate ONLY the Hazard to the AHA record (using a Join table, as previously discussed).
In your form, you'd have a Subform based on the Join table. In that subform, you'd present a method where the user could filter for a specific HazardCategory (perhaps you'd have a combobox to do this, where the user would select a Category). After the user selects that Category, you'd then filter a second combobox so that only those Hazards associated with that Category would be presented. There are several different ways to do this, of course, but a simple datasheet form based on your Join table works well.
As plummet indicates (and as I said in your earlier question), you must implement a structure where you have tables to store both the Hazard and the Category, and you must relate your Hazard to the Category it belongs to. I personally would do it like this:
tblHazardCategory
--------------------------
ID
CatName
tblHazard
----------------------
ID
Hazard
HazardCatID
So you'd have data in tblHazardCategory like this:
ID CatName
--------------------
1 Physical
2 Chemical
3 Biological
etc etcAnd data in tblHazard like this:
ID Hazard HazardCatID
--------------------------------------
1 Slip 1
2 Fall 1
3 Spill 2
4 Rupture 2
etc etcYou'd then relate ONLY the Hazard to the AHA record (using a Join table, as previously discussed).
In your form, you'd have a Subform based on the Join table. In that subform, you'd present a method where the user could filter for a specific HazardCategory (perhaps you'd have a combobox to do this, where the user would select a Category). After the user selects that Category, you'd then filter a second combobox so that only those Hazards associated with that Category would be presented. There are several different ways to do this, of course, but a simple datasheet form based on your Join table works well.
ASKER
I made the changes you suggested. I am not sure if I did it correctly because things don't seem to work correctly. For example:
1. I can't get the "Category' field in the tblCategory table to showup
2. Not sure if I have the correct Primary Keys in the Category and Hazard tables.
3. No way for users to select the Pre-loaded Hazards that populate the various Hazard Categories.
Database1-9-11-2011.mdb
1. I can't get the "Category' field in the tblCategory table to showup
2. Not sure if I have the correct Primary Keys in the Category and Hazard tables.
3. No way for users to select the Pre-loaded Hazards that populate the various Hazard Categories.
Database1-9-11-2011.mdb
Are you referring to my comment? If so, I downloaded your file and looked, and your table structure isn't really what I suggested, nor is your form setup in a way where you could easily add Hazards to an AHA record.
What are "preloaded hazards"?
What are "preloaded hazards"?
ASKER
LSMConsulting:
I re-did the tables and tried to do them exactly as you said, but now I don't know what fields to connect to create the relationships.
Please advise.
Thanks,
CSSC1
Database9-14-2011.mdb
I re-did the tables and tried to do them exactly as you said, but now I don't know what fields to connect to create the relationships.
Please advise.
Thanks,
CSSC1
Database9-14-2011.mdb
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think LSM has got this well in hand, so unless I can be of any more assistance I'll leave it to him.
Cheers
John
Cheers
John
A statement like this will generally limit your options, as somethings can only be done via code.
In any event...
It is not clear if your 3 "Physical hazards" are stored in a related table...
But the concept you are looking for here seems to be "Cascading Lists"
(Note that this concept relies on some VBA code, so I can't tell you that you will ever find a solution that is "100% Query", because in most cases you will need to "requery" the second list, AFAIK, you can only do this via VBA)
See some examples here:
http://www.fontstuff.com/access/acctut10.htm
http://www.candace-tripp.net/download/CascadeCombo2007.zip
http://www.candace-tripp.net/download/cascadecombo2k.zip
http://www.candace-tripp.net/download/2table_cascadecombo2k.zip
JeffCoachman