Building an Access 2010 Report to capture "yes" selections

[ecalvert47462]

Hello All, I have a quick question. I will try to explain the best I can without sounding silly.



I am working in Access 2010.

I have a user form with multiple checkboxes on it, like around 30 or so. It's a review form that an end user can bring up the Job # and then select if the job had any deficiencies or not...if no then they just hit enter and go to the next job # and if it does then they check a box and then check the areas where there were deficiencies. This all gets stored in a table that records a yes or no in the value, under each field name that they specifically checked or didn't check next to the deficient area.

And I have a query set up to look at if the job had issues then it brings me a list of all the jobs that had problems and leaves out the ones that do not.

What I don't know how to do is to design a report that will just bring me the Job # and a list of the deficiencies with only a yes in the field value...is there a way to design it so it just puts in the deficiencies and ignores the ones with no values.

thank you.

[CJ_London]

I would think the recordsource to your report will be something like

select Job, deficiency from mytable where myfield=true

[June7]

Sounds like data structure is not normalized - the 30 or so checkboxes is an indication.

Normalize the data structure.

tblReviewers
ReviewerID (primary key)
LastName
FirstName

tblJobs
JobNo (primary key)
JobDescription
JobDate

tblDeficiencies
DefID (primary key)
DefDescription

tblJobDeficiencies
JobNumber (foreign key)
DefNumber (foreign key)
ReviewerNumber (foreign key)


Otherwise, use a UNION query to rearrange the 30 or so yes/no fields into normalized structure and apply filter criteria that retrieves only the Yes records. Use this as the report RecordSource.

[orange]

I think you really want a Form to capture your data.
Tables are where data is stored. Forms are the means by which users interact with the data and the application.
Reports are typically used to display results of manipulations and/or summaries of data.

Do you have a data model representing your tables and relationships?