Access report question

[SeanCly10]

I'm having some trouble with one of my reports.

What I want to try to do is to have a field in my report that displays
all the yes/no fields in a certain table that are checked "yes". For
example, 5 medications are set up as seperate fields (columns) in a
table, each with a yes/no datatype. If a patient has had 2 out of 5
available medications given, then the 2 corresponding columns have been
checked as "yes". I want to use a report to display the column names in
the table that were checked "yes", but inside a single report field.

Thanks for any help.

 

[Allen Browne]

Sean, you are working in a relational database, so the right way to store
this data is with one-to-many relations.

Create a table of Medications, with MedicationID primary key (text field if
you like.) Enter one record for each medication.

You already have a Patient table. Make sure it has a PatientID primary key
(probably an AutoNumber.) No medication info goes in this table.

Now create a 3rd table with fields:
PatientID
MedicationID
If Jo Smith has 3 medications, he appears 3 times in this table.
The interface will be a main form bound to the Patient table, with a subform
bound to this 3rd table. The subform will have a combo for selecting the
medication, and you enter as many rows as apply for this patient.

With that design, the report you want is very simple, and so is everything
else you might want to query.