Query Question

[ielmrani via AccessMonster.com]

Hi everyone,
I have the following table:

Client D1 D2 D3 D4 D5

A Yes Yes No No No
B No No Yes No No
C No Yes Yes Yes No
D No No Yes No No
E Yes No No Yes Yes
F Yes No No No No
G Yes Yes Yes No Yes
H Yes Yes Yes No Yes
J Yes No No No No
M Yes No No No No

I would like to generate:



A report to give me all shared department

1 D1 and D2 (D1 and D2 are shared: meaning they both have Yes and all the
other have No))
1 D2, D3 and D4
1 D1, D4 and D5
2 D1, D2, D3 and D5

I hope my question is clear, if not please let me know.
Thank you so much in advance.

 

[John Spencer]

You could try a query that looks like:

SELECT D1, D2, D3, D4, D5, Count(*) as TheCount
FROM TheTable
WHERE Abs( D1 + D2 + D3 + D4 + D5 )>1
GROUP BY D1, D2, D3, D4, D5

You could then use that in a report and show/hide controls based on the
values of the fields D1 to D5

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[ielmrani via AccessMonster.com]

Worked Great. I managed to do it but using several queries. This is done in
one step.

Thank you very much.

You could try a query that looks like:

SELECT D1, D2, D3, D4, D5, Count(*) as TheCount
FROM TheTable
WHERE Abs( D1 + D2 + D3 + D4 + D5 )>1
GROUP BY D1, D2, D3, D4, D5

You could then use that in a report and show/hide controls based on the
values of the fields D1 to D5

Hi everyone,
I have the following table:

[quoted text clipped - 24 lines]

I hope my question is clear, if not please let me know.
Thank you so much in advance.