How do I return the field name when the check box = Yes

[Guest]

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 query that returns the field names of the
Specialties that are True for each given Medical Facility.

Please Help

 

[Guest]

You should revise your table structure to have two tables - Medical Facility
& Specialty. Have them in a one-to-many relation.

But for now use this in your query (Untested) --
SELECT Facility, IIf([surgery] = -1, "Surgery ", Null), IIf([pediatrics] =
-1, "Pediatrics ", Null), IIf([cardiology] = -1, "Cardiology ", Null),
etc

 

[Guest]

Thanks for your response.
I believe the database is currently set up the way you recommended -- a
Facility table that holds basic info about the Facilities and a Specialty
Table linked by the Facility Id. I would love to simplify the Table
structure even more, but 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
actually already came up with something similar and was hoping I might find a
way to accomlish it more efficiently-- it's 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...?

You should revise your table structure to have two tables - Medical Facility
& Specialty. Have them in a one-to-many relation.

But for now use this in your query (Untested) --
SELECT Facility, IIf([surgery] = -1, "Surgery ", Null), IIf([pediatrics] =
-1, "Pediatrics ", Null), IIf([cardiology] = -1, "Cardiology ", Null),
etc

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 query that returns the field names of the
Specialties that are True for each given Medical Facility.

Please Help

 

[Guest]

Facility table --
FacilityID - autonumber - primary key
Name - text
Location - text
ADDR1 - text
ADDR2 - text
City - text
State - text
ZIP - text
Phone - text
FAX - text
POC - text
Etc -

FacilitySpec table --
FacilitySpecID - autonumber - primary key
FacilityID - number - integer - foreign key
Specility - text
Size - number - integer
Rating - text
CertifiedBy - text
CertificateExp - DateTime
Etc.


Set a one-to-many relationship between the Facility and FacilitySpec tables.

Thanks for your response.
I believe the database is currently set up the way you recommended -- a
Facility table that holds basic info about the Facilities and a Specialty
Table linked by the Facility Id. I would love to simplify the Table
structure even more, but 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
actually already came up with something similar and was hoping I might find a
way to accomlish it more efficiently-- it's 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...?

You should revise your table structure to have two tables - Medical Facility
& Specialty. Have them in a one-to-many relation.

But for now use this in your query (Untested) --
SELECT Facility, IIf([surgery] = -1, "Surgery ", Null), IIf([pediatrics] =
-1, "Pediatrics ", Null), IIf([cardiology] = -1, "Cardiology ", Null),
etc

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 query that returns the field names of the
Specialties that are True for each given Medical Facility.

Please Help

 

[John Vinson]

I believe the database is currently set up the way you recommended -- a
Facility table that holds basic info about the Facilities and a Specialty
Table linked by the Facility Id. I would love to simplify the Table
structure even more, but 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?

Yes; you have a perfectly classic many to many relationship. This
requires three tables:

Facilities
FacilityID
FacilityName
<other information about the facility>

Specialties
SpecialtyID
SpecialtyName
<other information about the specialty>

FacilitySpecialties
FacilityID <<<< link to Facilities
SpecialtyID <<<< link to Specialties
<any information about THIS facility with regard to THIS specialty>

If a given facility has eight specialties, there would be eight
records in this table, one for each specialty, all with the same
FacilityID.

The preferred data entry technique would be to have a Form based on
the Facilities table (or the Specialties table if you prefer, or
both!) with a Subform based on the FacilitySpecialties table. This
Subform would have a combo box bound to the Specialties table (or the
Facilities table, if the mainform displays a specialty).


John W. Vinson[MVP]