Report to list Values on Variable1 based on Yes/No on Variable2

[Pascale]

I have a large Union query with, among others, Tasks and some 20 associated Competencies (let's call them Comp1 to Comp20). Tasks are text boxes while Competencies are Yes/No fields. I would want a report with:

1. Total number of Yes for each Competency (This, I can do!)
2. For each Competency, a list of tasks for which the given competency was checked (Yes answer)

For example, 4 tasks require Competency 1, leading to "4" for #1 above. The remaining 16 Competencies are not counted as they got a No answer. I would like the report to list the 4 tasks for which Competency 1 was checked.



Could someone help me with this?

[ranman256]

if Competencies is a YES/NO value ,then you need a query that counts the Tasks given the competency.
on a form, show a list of competencies (your query) and the count
click on the list item, to run a query to show the count of tasks.

the query, docmd.openquery "qsTasks1Comp" , will look at the form list box clicked....
select * from tasks where [compency] = forms!myForm!lstboxComps

[Pascale]

Thanks. Let me try that.

[Pascale]

Hum... Tasks is not a list box. My Union query rather looks like the example below.

Task	Comp1	Comp2
Task1	0	0
Task2	1	0
Task3	1	1
Task4	0	0
Total	2	1

In that example, I would want my Report to show:

Comp1	Comp2
Total=2 Total=1
Tasks:	Tasks:
Task2	Task3
Task3

[aytee111]

Use subreports - one for each type of what you are trying to see. Each subform would have its own record source/query showing exactly what you want to see.

[Pascale]

Use subreports - one for each type of what you are trying to see. Each subform would have its own record source/query showing exactly what you want to see.

Thanks again. I had tried that but was not able to assign different record source to subreports.

[aytee111]

Every subreport has to have its own record source - how were you not able to accomplish that?

[Pascale]

OK. I figured it out. It works fine. Thanks!