Display only checked fields

[madctch]

What I have is a table with yes/no fields, with notes fields that correspond to each of the yes/no fields. These two fields are related to a Company name in another table. I am attempting to make a report to display the yes/no fields with their corresponding note boxes for each company, but I cannot figure out how to display only the ones that have a value of yes. Is it possible to only display the fields that are equal to yes for each company as well as the corresponding notes fields for each of the yes fields?

 

[dhookom]

I strongly suspect your table isn't normalized. However, you can check Allen Browne's page on using a standard text box to display (or not) your Yes values

http://www.allenbrowne.com/ser-52.html.

For the associated notes field, use a control source like:
=IIf([YesNoFieldA], [NotesFieldA],Null)


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[madctch]

Yes, I don't think it normalized either. What I am attempting to do is to be able to allow the user to enter notes for each check separately. I didn't know how to work the relationship so I just made a notes field for each checkbox.

 

[dhookom]

I expect each "checked" field should create a new record in a related table. This new table would have the primary key from the original table, a field the describes the form check box name, and the notes field.

For instance, if this was a table of people with their certifications:
[red][tt]
tblPeople
==========
PeopleID primary key
FirstName
LastName
CPR (yes/no)
CPRNotes
CDL
CDLNotes
FirstAid
FirstAidNotes
EMT
EMTNotes
.. etc ..
[/tt][/red]
You would remove all the certification fields and notes and create tables like:
[blue][tt]
tblCertifications
==================
CertID primary key
CertTitle values like CPR, First Aid,....
[/tt][/blue]
and
[blue][tt]
tblPeopleCertifications
=======================
PeopleCertID primary key
PeopleID links to tblPeople.PeopleID
CertID links to tblCertifications.CertID
CertNotes
[/tt][/blue]



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[madctch]

Ok, that works. But when it comes to the report how should I go about only displaying the fields that are not empty, the main problem being formatting. I could use the visible property and change it on print, but that would leave many of blank spaces on the report.

 

[dhookom]

If you change your table structure, then you would use a subreport to display the related "certifications" and notes. If there wasn't a related record, then none would show and you wouldn't have an issue.

If you don't change your structure, then create a normalizing union query based on your un-normalized table. The union query would look much like tblPeopleCertificats mentioned previously. Use the union query as the record source of a subreport.

If this doesn't work for you then you need to provide a more information about your table structure and desired layout on your report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[madctch]

Thanks for your help! I changed my table structure and am attempting to do a data entry form. Would I do a subform inside of another subform? The thing I cannot figure out is how to have the notes correspond to multiple fields on each certification. Right now I have one notes field per record.

 

[madctch]

Disregard that last reply. I think it would be alot easier if I just made a table with a notes field and a combo with all the certification values. Thanks for all of your help!