If CheckBox is checked, display field name

[Nathan-bfhd]

I have a database that tracks medical facilities. Each facility has at least
one Specialty classification (ex. surgery, pediatrics, cardiology, etc), and
many of them have several Specialty classifications. The facility name is
the Primary key for the table that stores the info and each Specialty is a
field that is either checked or not depending on whether it applies or not.
What I would like is a report that displays the field names of the
Specialties that are True for each given Medical Facility. I would love it
like the following:

Medlin Medical Associates: psychiatry, physical_therapy, surgery, neurology


I think I already have a way to list the field names in a single line
seperated by a comma, but what I can't figure out is how to list the field
names and only the field names that are true for each Facility.

Please Help.

 

[Duane Hookom]

Is there any chance that you could normalize your table structure so that
specialties each created a new record in a related table? If you can't
change the structure, the first step I would take is to create a union
query:

SELECT FacilityID, "Surgery" as Specialty
FROM tblNoNameGiven
WHERE Surgery = True
UNION ALL
SELECT FacilityID, "pediatrics"
FROM tblNoNameGiven
WHERE pediatrics = True
UNION ALL
--- etc ---
FROM tblNoNameGiven
WHERE LastField = True;

You can then use a multiple column subreport to display the specialties or
the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

You really should take the time to normalize so you can add new specialties
without having to change table structures or forms or reports or other
objects.

 

[Nathan-bfhd]

Duane,
How could I normalize the table structure? The snag I'm running into is
that each facility has at least 1 specialty, but some have several, maybe as
many as 5 or so. Since each Facility has a different number of specialties,
I couldn't think of a different way to do it than the current way (which is
to have a seperate Specialty Table where each specialty is its own field and
is tied to the other tables by the Facility ID). Have any suggestions?

As for your current recommendation on the code, I can give that a try. I'm
not that familiar with how Unions actually work, but I can mess around with
your idea -- it's just going to be a lot of work because there are over 50
different specialties - that's a lot of code. I was hoping there was maybe
some generic code that would return its field name if its check box is
checked. Or maybe something with stepping through a recordset...?

 

[Duane Hookom]

Regarding the normalization, consider tables

tblFacilities
====================
FacilityID
FacilityName
.....

tblSpecialites (1 record for each different specialty)
====================
SpecialtyID autonumber primary key
Specialty values like "Surgery", "Pediatrics","Colonoscopy"

tblFacilitySpecialties (1 record per facilty per specialty)
==================
FacilityID links to tblFacilities.FacilityID
SpecialtyID links to tblSpecialties.SpecialtyID

A facility with 3 specialties would have three records in
tblFacilitySpecialites.

 

[Nathan-bfhd]

That's ingenious! I was just having a hard time wrapping my head around
how to change it (I've inherited this project). Now, I just have to figure
out how much work it's going to be to update 400 Facilities .
Theoretically, with it set up the way you mentioned, I should be able to run
a simple query selecting FacilitiyIDs and Specialties from the
tblFacilitySpecialties to get what I'm looking for right?

Any recommendations for me on how best to change the Database table
structure with all that data already in there? I'm going to go take a break
and see if steam is coming out of my ears.

 

[Duane Hookom]

I would use code to loop through the fields to create and run append
queries. Roger Carlson has a small sample database that might be of help at
http://www.rogersaccesslibrary.com/TableOfContents3.asp#N.