Sum of different categories with yes/no

[Guest]

I have a table that has columns TeamMember, ShirtSize, BlueShirt, WhiteShirt,
RoseShirt

The following is what the what the tlbMember would look like:

TeamMember ShirtSize BlueShirt WhiteShirt RoseShirt
Yes Small Yes No No
Yes Large No Yes Yes
No X-Large No No No
Yes XX-Large No No Yes
Yes Medium No No No
Yes Large No No No
Yes XX-Large No No Yes
Yes XX-Large No No No

The query I need to create will only give the number of shirts needed for
the TeamMember with Yes. The shirts marked with No are the ones needed.
So the results of the Query would look like the following:

ShirtSize CountOFBlueShirt CountOFWhiteShirt CountOFRoseShirt
Small 0 1
1
Medium 1 1 1
Large 2 1
1
X-Large 0 0 0
XX-Large 3 3 3

Thanks, Joe...

 

[Duane Hookom]

If the table is using Yes/No fields then you should be able to use a query
like:
SELECT ShirtSize, Sum(BlueShirt+1) as BlueShirts, Sum(WhiteShirt+1) as
WhiteShirts, Sum(RoseShirt+1) as RoseShirts
FROM tblUnNormalized
GROUP BY ShirtSize;

 

[Guest]

Duane, I tried the following:
SELECT ShirtSize, Sum(BlueShirt+1) AS BlueShirts, Sum(WhiteShirt+1) AS
WhiteShirts, Sum(RoseShirt+1) AS RoseShirts
FROM MemberTeam
GROUP BY ShirtSize;

Where MemberTeam is the name of the table. I get a "Data type mismatch in
criteria expression.

Also, in the original table I posted there is a column for TeamMember with
Yes/No. Did you forget this column? Thanks, Joe...

 

[Duane Hookom]

My first sentence in my reply suggested the query should work if the fields
were Yes/No. Are your shirt field yes/no or text or what?
This sql would limit the results to TeamMembers (again, if the fields are
yes/no).

SELECT ShirtSize, Sum(BlueShirt+1) as BlueShirts,
Sum(WhiteShirt+1) as WhiteShirts,
Sum(RoseShirt+1) as RoseShirts
FROM MemberTeam
WHERE TeamMember = True
GROUP BY ShirtSize;

 

[Guest]

Duane,
Thanks... yes you are right.. eventhough I said yes/no it is a text field. I
will change it to yes/no and I should work.
My apologies for the misunderstanding... Thanks !
Joe...