Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Query design for Category and Hazard Selection

Posted on 2011-09-13
9
274 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:cssc1
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36533642
<I am trying to create a query (no code, I don’t understand it)>
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
0
 
LVL 10

Assisted Solution

by:plummet
plummet earned 200 total points
ID: 36534572
HI cssc1

It seems to me that your data structure is not appropriate for your requirements. From what I understand I think you need 2 tables: tblProject and tblHazardCategory

tblProject would be as you currently have it.
tblHazardCategory would have a HazardID, CategoryName and subCategoryName:
ID  CategoryName   SubCategoryName
1    Physical              Slip
2    Physical              Trip
3    Physical              Fall
4    Chemical            (etc, etc)

tblProject would simply link to the selected hazard.

However, you may want more than one hazard to be linked to a project, in which case you could have a 3rd table which contains a link to the project, and to the hazardcategory. That will allow as many hazards as the user requires to be linked to the project.

eg tblHazardProjectLink
ProjectID
HazardID

Does that help? I hope so.

Regards
0
 
LVL 84
ID: 36535135
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:
ID    CatName
--------------------
1       Physical
2       Chemical
3       Biological
etc etc

Open in new window


And data in tblHazard like this:
ID   Hazard    HazardCatID
--------------------------------------
1       Slip           1
2       Fall           1
3       Spill          2
4       Rupture    2
etc etc

Open in new window


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.

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:cssc1
ID: 36536259
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
0
 
LVL 84
ID: 36536651
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"?
0
 

Author Comment

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

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 100 total points
ID: 36536950
(I'll let plummet and LSM take it from here to avoid confusion...)
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 36537002
I made some changes to this, and added a form (Form1) to show you how the relationship works.

If you need an interface utility that would allow users to select/deselect Hazards for each AHA record, then you'll probably be best advised to make use of a Temporary table where you can implement a checkbox sort of thing. This is a UI consideration that many users expect to see, and it's not too hard to do in Access (however, you must be somewhat fluent in VBA to impelment it).

At the end of the day, you want to relate your AHA record with multiple Hazards, and that is done through the AHA_Hazards table I've included. How you get that data into the table is the real trick!
Database1-9-11-2011.mdb
0
 
LVL 10

Expert Comment

by:plummet
ID: 36538666
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
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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 …

856 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