Subreport Listing Fields in Rows

[Connie]

Hi. I am using Access 2003. My main table (tblAnalysis) has a one-to-one
relationship with tblHazard through the AnalysisID. There are 78 fields in
tblHazard including number fields HazardID and AnalysisID, and checkbox
fields A, B, C, etc. on up to FF and text fields AText, BText, CText, etc. on
up to FFText.

I’ve created a report pulling data from tblAnalysis. (The report already has
a subreport from another one-to-one relationship with tblSCL).

For another section of the report, I need to insert the related record from
tblHazard, but I don’t want all 76 checkboxes and text boxes listed. I want
to list only the checkbox fields that are “True†along with the corresponding
text field. The “True†checkbox fields and their text fields need to be
listed under each other. For example:

Letter Description
A AText (text describing the letter A)
C CText (text describing the letter C)
L LText (text describing the letter L)

The “Letter†will be the field’s name rather than the checkmark and the
“Description†may have text or it could be blank.

I appreciate the help I’ve received on a previous question concerning this
database and am hoping that someone will have an answer for this one, too.
Thanks.

 

[Duane Hookom]

Is there any chance that you could normalize the 78 fields from tblHazard
into a table that might have up to 38 records for each record in
tblAnalysis? If you can't change the structure, you could consider creating
a union query that normalizes tblHazard.

 

[Connie]

Thanks for responding, Duane.
I am not totally sure of what you are asking, but if by normalizing you
mean can I change my tblHazard to include just the 38 A, B, C, etc. checkbox
fields and then create a second table (tblHazardText) to include a key field
(HTextID) and the 38 AText, BText, CText, etc. text fields with an AnalysisID
field relating this table back to tblAnalysis—then yes I think that would
work. I will probably have to go back and make changes to some other queries
and forms, but I think that is possible.
I’ve seen a database with a union query, but I have never created one myself.
After I break the tables down, can you tell me what I would need to do then
to get the report to display only the checkbox fields that are “True†and not
all 38 of them? Plus list each field on a separate row (along with its
corresponding text field) rather than in columns?

 

[Duane Hookom]

You aren't understanding normalization. Are your tblHazard fields actually
A, B, C,... AText, BText, CText,...?
If you were selling 38 products to your customers, you wouldn't create pairs
of 38 "fields" to store the purchase information. You create one record per
Sales Order for each product purchased. I would expect you to use the same
exact idea when creating your tblHazard.

 

[Connie]

The fields are actually called A, B,…AText, BText. Let me explain how I setup
the database.

TblAnalysis has 7 fields including AnalysisID, PrebriefingNo, FacilityName,
Supervisor, Date, LocationOfWork, and TaskActivity.

TblEquipment (they also call their equipment “Location of Workâ€) has an
EquipID, EquipName, CategoryID, EquipNo1…EquipNo8,
DefaultHazard1….DefaultHazard8, and DefaultSCL1…DefaultSCL8 (there being 8
default EquipNos, Hazards, and SCLs per Equipment). I set it up so in
DefaultHazard1…Hazard8 they could type A, B, M, AA or whatever 8 hazards
should default for the selected equipment. Some equipment may have only 1
default hazard, others may have more, up to 8.

TblHazard has HazardID, AnalysisID, 32 checkbox fields called A, B,…FF, and
32 text fields called AText, BText…FFText.

TblSCL has SCLID, AnalysisID, and about 45 checkbox fields with lengthier
names than tblHazard has.

On the paper report I was given to use to create this database, listed in
columns at the top strictly for reference are the 32 lettered Hazards and
their descriptions, for example:
A – Electrocution I – Machinery Q – Lead Paint Y – Poor Lighting
B – Arc Flash J – Hot Surfaces R – Silica Dust X – Heat Stress
and so on.

Underneath this reference text is where the employee has been manually
writing the Hazard letter under a column titled “Letter†and then writing the
corrective action for the Hazard in a column to the right of “Letterâ€. The A,
B…fields in tblHazard are what I setup so they could check the ones they need
and then eventually see those checked fields listed under the “Letterâ€
column. The AText, BText…fields in tblHazard are what I setup to record what
they would write for the “corrective action†which is not the same as the
words listed to the right of the letter in the above reference text example.

I then created a main form (frmAnalysis) based on a query pulling fields
from tblAnalysis, including “LocationOfWork†which is a combo box pulling
records from tblEquipment. This main form has a subform that lists the
checkboxes and text fields from tblHazard. (It also has another subform based
on tblSCL.)

Upon first opening the main form, the subform (sfHazard) is not enabled.
The employee needs to click a HAZARD button which opens form sfHazard2
(basically a copy of sfHazard). Through code in the button’s On Click event,
sfHazard2 opens with the checkboxes for the default hazards for the selected
equipment already marked and allows them to select other checkboxes and fill
in other corrective actions as needed. Closing sfHazard2 then enables
sfHazard on the main form.

This setup appears to be working well thus far, but pulling the selected
hazards and the text to the report has got me stumped.

If I’ve been able to give you a good enough explanation of the table setup,
can you give me suggestions on a better way of setting up the tables to get
the report I need? Currently, tblAnalysis has a one-to-one relationship with
tblHazard and with tblSCL.
I appreciate your help.

 

[Duane Hookom]

You should read up on table structures and normalization
http://www.datamodel.org/NormalizationRules.html
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

Having field1,2,3,... is not the way store normalized data.

 

[Connie]

Duane,
Thank you so much for your help. I don't know what I was thinking when I set
up my tables. I guess you could say I wasn't thinking at all. I knew better
than that but just blanked out. I think the checkboxes threw me. I've
restructured my tables to where they now have probably 4 or 5 fields at the
most. I have to redo most of my forms and reports yet, but I think I am on
the right path now, thanks to you sticking with me and telling me to get my
stuff together. : )

 

[Duane Hookom]

Sometimes this is a tough pill to swallow once you have headed off in the
wrong direction. My opinion is it's never too late to normalize.

"Friends don't let ther friends commit spreadsheet"