Query Help 2

[istone]

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 containing Department alone (Department not shared) Example below is based on the above table:

3 D1 alone (client F, J and M are not shared)
0 D2 alone
2 D3 alone (Clinet B and D are not shared)
0 D4 alone
0 D5 alone

Then another 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.

 

[lespaul]

maybe something like:

SELECT COUNT(*), "D1 alone" WHERE D1 And SUM(D2 + D3 + D4 + D5) = 0
UNION SELECT COUNT(*), "D2 alone" WHERE D2 And SUM(D1 + D3 + D4 + D5) = 0 
UNION SELECT COUNT(*), "D3 alone" WHERE D3 And SUM(D1 + D2 + D4 + D5) = 0
UNION SELECT COUNT(*), "D4 alone" WHERE D4 And SUM(D1 + D2 + D3 + D5) = 0
UNION SELECT COUNT(*), "D5 alone" WHERE D5 And SUM(D1 + D2 + D3 + D4) = 0

not sure how to tackle the all shared department query, but I think this one will solve your first question.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[istone]

thank you leslie for your quick response. I am getting an error message:

syntax error (missing operator) in query expression " Group alone" where Group and SUM(Pension + Life + CHRD + RMS) = 0

I tried in vain to correct it.
my query looks like this now:

SELECT COUNT(*), "Group alone" WHERE Group And SUM(Pension + Life + CHRD + RMS) = 0
UNION SELECT COUNT(*), "Pension alone" WHERE Pension And SUM(Group + Life + CHRD + RMS) = 0
UNION SELECT COUNT(*), "Life alone" WHERE Life And SUM(Group + Pension+ CHRD + RMS) = 0
UNION SELECT COUNT(*), "CHRD alone" WHERE CHRD And SUM(Group + Pension +Life + RMS) = 0
UNION SELECT COUNT(*), "RMS alone" WHERE RMS And SUM(Group + Pension +CHRD + Life) = 0;

thanks

 

[lespaul]

you're missing the table name! There's no FROM clause

SELECT COUNT(*), "Group alone" [b]FROM TableName[/b] WHERE Group And SUM(Pension + Life + CHRD + RMS) = 0
UNION SELECT COUNT(*), "Pension alone" [b]FROM TableName[/b] WHERE Pension And SUM(Group + Life + CHRD + RMS) = 0
UNION SELECT COUNT(*), "Life alone" [b]FROM TableName[/b] WHERE Life And SUM(Group + Pension+ CHRD + RMS) = 0
UNION SELECT COUNT(*), "CHRD alone" [b]FROM TableName[/b] WHERE CHRD And SUM(Group + Pension +Life + RMS) = 0
UNION SELECT COUNT(*), "RMS alone" [b]FROM TableName[/b] WHERE RMS And SUM(Group + Pension +CHRD + Life) = 0;

 

[istone]

thanks, but it's still doesn't like it. It gives me a total count if I remove the: Wher group and...etc

error:

Syntax in where clause

query:

SELECT COUNT(*), "Group alone" FROM Customers WHERE Group And SUM(Pension + Life + CHRD + RMS) = 0
UNION SELECT COUNT(*), "Pension alone" FROM Customers WHERE Pension And SUM(Group + Life + CHRD + RMS) = 0
UNION SELECT COUNT(*), "Life alone" FROM Customers WHERE Life And SUM(Group + Pension+ CHRD + RMS) = 0
UNION SELECT COUNT(*), "CHRD alone" FROM Customers WHERE CHRD And SUM(Group + Pension +Life + RMS) = 0
UNION SELECT COUNT(*), "RMS alone" FROM Customers WHERE RMS And SUM(Group + Pension +CHRD + Life) = 0;

what I am doing wrong?

 

[PHV]

Group is a reserved word:
SELECT COUNT(*), "Group alone" FROM Customers WHERE [!][[/!]Group[!]][/!] And SUM(Pension + Life + CHRD + RMS) = 0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

well, yeah if you remove the WHERE clause you'll get a total of all the records in the table for each UNION.

Have you tried just the first query alone to see what you need to do to get one of them to work and then you can apply the fix to all of them.

When you show the 'Yes' 'No' fields in your example, are those boolean or text fields? If they are text then you'll need to change the query to something like this:

SELECT COUNT(*), "Group alone" FROM Customers WHERE Group = "Yes" And SUM(CBool(Pension) + CBool(Life) + CBool(CHRD) + CBool(RMS)) = 0

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

BTW, I'd don't use the SUM aggregate.
Assumption: the fields are Boolean.
SELECT Count(*), "Group alone" FROM Customers WHERE [Group] AND Not (Pension OR Life OR CHRD OR RMS)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[istone]

Great, I tried the last one by PHV and it It worked perfectly. thank you so much.
Any suggestion on the second question?

Then another 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

 

[istone]

Just want to let you know that I solved the second question using the same query removing the NOT

SELECT Count(*) as [Total Count], "Group Shared" as [Department Name] FROM Customers WHERE [Group] AND (Pension OR Life OR CHRD OR RMS)
UNION SELECT COUNT(*), "Pension Shared" FROM Customers WHERE [Pension] And (Group OR Life OR CHRD OR RMS)
UNION SELECT COUNT(*), "Life Shared" FROM Customers WHERE [Life] And (Pension OR Group OR CHRD OR RMS)
UNION SELECT COUNT(*), "CHRD Shared" FROM Customers WHERE [CHRD] And (Pension OR Life OR Group OR RMS)
UNION SELECT COUNT(*), "RMS Shared" FROM Customers WHERE [RMS] And (Pension OR Life OR CHRD OR Group);

Thanks again for your help