Query to extract selected yes fields from table

[Archie1]

I have a "Risk Assessment" table with Contractor information and then a few yes/no fields. One or multiple fields could be = yes which would change with each risk assessment
I would like a report to show the Contractor Info fields and then only the fields = yes and omit fields = no. Presuming I would need to do this first in a query, whats the best way to proceed?

 

[plog]

Bad news, good news, worse news: Bad: Queries don't work that way. You can't control visibility of a field based on the value of an individual record.

Good: I think you can achieve the query you want--that is showing just the risks a contractor has...

Worse news: I think your tables are set up incorrectly. I think your risk assesement fields need to go into a new table.

Instead of those check boxes, I believe you need a new table which will hold the contractor ID and the risk they have. I don't know your data, so please see the forest and not the trees of this example, This is what your risk table should look like:

ContractorId, Risk
17, Bankruptcy
19, Licensing Issues
19, New Contractor
25, Licensing Issues
31, New Contractor
...

Instead of having a checkbox named for the risk, you would put the actual risk as a value into the [Risk] field of your RiskAssesment table. This way, you have no 'No' values. If a risk exists for a contractor it is listed, if not, its not listed. So when you run a query for a contractor you see all their risks and none of the no's--because you don't track the nos.

That's how this should probably be set up.

 

[Archie1]

Thanks for your reply. Sorry, I do have 2 tables. TblContractor containing Contractor name and ID. TblRisks containing the yes/no fields

 

[plog]

TblRisks should be laid out per my prior post, not with the Risk as a field name, but as a value contained in a field.