Access Query

[Randall]

I have written a SQL query in Access 2007 which "counts" the number of shirt
sizes for a group of employees. Four sizes are involved, Small, Medium,
Large, Xl. The CountOfShirtSize returns the correct numbers with the correct
shirt size. The "count" for size Small returns correctly to 0 (none), but I
want the word "Small" to Return along with the count of 0. I stumped!!!

 

[Dirk Goldgar]

I have written a SQL query in Access 2007 which "counts" the number of
shirt
sizes for a group of employees. Four sizes are involved, Small, Medium,
Large, Xl. The CountOfShirtSize returns the correct numbers with the
correct
shirt size. The "count" for size Small returns correctly to 0 (none),
but I
want the word "Small" to Return along with the count of 0. I stumped!!!

What are you getting now? And what is the SQL of the query you're using?

 

[Randall]

Thanks for responding.. I return 0 for count,which is correct, but the word
"small' is not in the adjoining column...The SQL is
SELECT "Shirt Size" AS TYPE, Committee.[Shirt Size], Count(Committee.[Shirt
Size]) AS CountOfShirtSize, Committee.[Apparel-Men]
FROM Committee
GROUP BY "Shirt Size", Committee.[Shirt Size], Committee.[Apparel-Men]
HAVING (((Count(Committee.[Shirt Size]))>0) AND
((Committee.[Apparel-Men])=True)) OR (((Count(Committee.[Shirt Size]))=0));

The Query results are
TYPE Shirt Size CountOfShirtSize Apparel-Men
Shirt Size XXXXX 0 Yes
Shirt Size Large 7 Yes
Shirt Size Medium 1 Yes
Shirt Size XL 8 Yes

I want the word "small" to appear where the XXXXX's are an the
CountofShirtSize.

Hope this all makes sense.

Thanks,

 

[Dirk Goldgar]

Thanks for responding.. I return 0 for count,which is correct, but the
word
"small' is not in the adjoining column...The SQL is
SELECT "Shirt Size" AS TYPE, Committee.[Shirt Size],
Count(Committee.[Shirt
Size]) AS CountOfShirtSize, Committee.[Apparel-Men]
FROM Committee
GROUP BY "Shirt Size", Committee.[Shirt Size], Committee.[Apparel-Men]
HAVING (((Count(Committee.[Shirt Size]))>0) AND
((Committee.[Apparel-Men])=True)) OR (((Count(Committee.[Shirt
Size]))=0));

The Query results are
TYPE Shirt Size CountOfShirtSize Apparel-Men
Shirt Size XXXXX 0 Yes
Shirt Size Large 7 Yes
Shirt Size Medium 1 Yes
Shirt Size XL 8 Yes

I want the word "small" to appear where the XXXXX's are an the
CountofShirtSize.

That implies thart "XXXXX" is the value stored in the [Shirt Size] field for
small shirts (for some unknown reason), and you want to translate that value
to "Small". Is that correct, or am I misunderstanding you? You could
modify your query to look like this:

SELECT
"Shirt Size" AS TYPE,
IIf([Shirt Size] = "XXXXX", "Small", [Shirt Size]),
Count([Shirt Size]) AS CountOfShirtSize,
[Apparel-Men]
FROM Committee
GROUP BY
"Shirt Size",
IIf([Shirt Size] = "XXXXX", "Small", [Shirt Size]),
[Apparel-Men]
HAVING
(Count([Shirt Size])>0 AND [Apparel-Men]=True)
OR
Count([Shirt Size])=0;


Note: I took out what I believe were unnecessary elements and restructured
the SQL for readability. I may have made an error -- let me know if that
doesn't work

I don't know what you are intending to accomplish with your HAVING clause.
It seems a little odd to me -- do you mean to include only Men's shirts with
counts, but all shirts with no counts?

 

[Randall]

Dirk,

Sorry, I'm not explaining my problem very well. The query does not return
the "XXXXX"...it just returns that space as blank. I added the x's in the
email to show the spot where the word "small" should appear ... the 0 in the
adjoining column is the count for the "Small" shirt size...
The only way i could get the 0 count for the small size to return is by
adding the Having clause read to include those counts that = 0 or are
greater than 0.

I want the statement to return all sizes in one column and count in the next
column even if the count is 0 or no shirts that size. I intend to expand the
SQL statement to include [Apparel-Women] sizes also.
--
Randall

Thanks for responding.. I return 0 for count,which is correct, but the
word
"small' is not in the adjoining column...The SQL is
SELECT "Shirt Size" AS TYPE, Committee.[Shirt Size],
Count(Committee.[Shirt
Size]) AS CountOfShirtSize, Committee.[Apparel-Men]
FROM Committee
GROUP BY "Shirt Size", Committee.[Shirt Size], Committee.[Apparel-Men]
HAVING (((Count(Committee.[Shirt Size]))>0) AND
((Committee.[Apparel-Men])=True)) OR (((Count(Committee.[Shirt
Size]))=0));

The Query results are
TYPE Shirt Size CountOfShirtSize Apparel-Men
Shirt Size XXXXX 0 Yes
Shirt Size Large 7 Yes
Shirt Size Medium 1 Yes
Shirt Size XL 8 Yes

I want the word "small" to appear where the XXXXX's are an the
CountofShirtSize.

That implies thart "XXXXX" is the value stored in the [Shirt Size] field for
small shirts (for some unknown reason), and you want to translate that value
to "Small". Is that correct, or am I misunderstanding you? You could
modify your query to look like this:

SELECT
"Shirt Size" AS TYPE,
IIf([Shirt Size] = "XXXXX", "Small", [Shirt Size]),
Count([Shirt Size]) AS CountOfShirtSize,
[Apparel-Men]
FROM Committee
GROUP BY
"Shirt Size",
IIf([Shirt Size] = "XXXXX", "Small", [Shirt Size]),
[Apparel-Men]
HAVING
(Count([Shirt Size])>0 AND [Apparel-Men]=True)
OR
Count([Shirt Size])=0;


Note: I took out what I believe were unnecessary elements and restructured
the SQL for readability. I may have made an error -- let me know if that
doesn't work

I don't know what you are intending to accomplish with your HAVING clause.
It seems a little odd to me -- do you mean to include only Men's shirts with
counts, but all shirts with no counts?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,

Sorry, I'm not explaining my problem very well. The query does not return
the "XXXXX"...it just returns that space as blank. I added the x's in the
email to show the spot where the word "small" should appear ... the 0 in
the
adjoining column is the count for the "Small" shirt size...

The question that remains unanswered is, what is in the [Shirt Size] column
for the small shirt sizes. Apparently it isn't "Small", or you wouldn't be
having this problem. Maybe it is Null -- no size recorded -- though that
would be a bad data design, since Null means "no data" or "not applicable",
and shouldn't really be used to represent a meaningful data value.

However, if [Shirt Size] is record as Null for small sizes, then you could
it is Null . If it is Null, then try this:

SELECT
"Shirt Size" AS TYPE,
Nz([Shirt Size], "Small"),
Count([Shirt Size]) AS CountOfShirtSize,
[Apparel-Men]
FROM Committee
GROUP BY
"Shirt Size",
Nz([Shirt Size], "Small"),
[Apparel-Men]
HAVING
(Count([Shirt Size])>0 AND [Apparel-Men]=True)
OR
Count([Shirt Size])=0;

The only way i could get the 0 count for the small size to return is by
adding the Having clause read to include those counts that = 0 or are
greater than 0.

I don't know if you have this right. It seems to me that you may be able to
do without the criterion on [Shirt Size] altogether, and just write this:

SELECT
"Shirt Size" AS TYPE,
Nz([Shirt Size], "Small"),
Count([Shirt Size]) AS CountOfShirtSize,
[Apparel-Men]
FROM Committee
GROUP BY
"Shirt Size",
Nz([Shirt Size], "Small"),
[Apparel-Men]
HAVING [Apparel-Men]=True;

But you should try that and see if you get what you want.

 

[Randall]

Dirk,

thank you!!! Now is can"see" the word "Small" in the query return even
though the countofshirtsize is "Null" or 0. One last question...I would like
to order to the shirt size in the query to small, medium, large, XL so it
look correct when i create a report with this quesry...it currently returns
alphabetically, or Large, Medium, Small, XL. Is that possible?

Regards,
--
Randall

Dirk,

Sorry, I'm not explaining my problem very well. The query does not return
the "XXXXX"...it just returns that space as blank. I added the x's in the
email to show the spot where the word "small" should appear ... the 0 in
the
adjoining column is the count for the "Small" shirt size...

The question that remains unanswered is, what is in the [Shirt Size] column
for the small shirt sizes. Apparently it isn't "Small", or you wouldn't be
having this problem. Maybe it is Null -- no size recorded -- though that
would be a bad data design, since Null means "no data" or "not applicable",
and shouldn't really be used to represent a meaningful data value.

However, if [Shirt Size] is record as Null for small sizes, then you could
it is Null . If it is Null, then try this:

SELECT
"Shirt Size" AS TYPE,
Nz([Shirt Size], "Small"),
Count([Shirt Size]) AS CountOfShirtSize,
[Apparel-Men]
FROM Committee
GROUP BY
"Shirt Size",
Nz([Shirt Size], "Small"),
[Apparel-Men]
HAVING
(Count([Shirt Size])>0 AND [Apparel-Men]=True)
OR
Count([Shirt Size])=0;

The only way i could get the 0 count for the small size to return is by
adding the Having clause read to include those counts that = 0 or are
greater than 0.

I don't know if you have this right. It seems to me that you may be able to
do without the criterion on [Shirt Size] altogether, and just write this:

SELECT
"Shirt Size" AS TYPE,
Nz([Shirt Size], "Small"),
Count([Shirt Size]) AS CountOfShirtSize,
[Apparel-Men]
FROM Committee
GROUP BY
"Shirt Size",
Nz([Shirt Size], "Small"),
[Apparel-Men]
HAVING [Apparel-Men]=True;

But you should try that and see if you get what you want.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[John Spencer]

I believe that
the count of zero is the count of records that are null. The problem
with counting records that are null in the size field is that count does
not count nulls. You could try count(*) in place of Count([shirt Size])
that counts the number of records and not the number of records that
have a value in the field.

The fact that there are no shirts with the size small means that the
value is excluded from the results. Somehow you need to include that
into your results.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Dirk,

Sorry, I'm not explaining my problem very well. The query does not
return
the "XXXXX"...it just returns that space as blank. I added the x's in
the
email to show the spot where the word "small" should appear ... the 0
in the
adjoining column is the count for the "Small" shirt size...

The question that remains unanswered is, what is in the [Shirt Size]
column for the small shirt sizes. Apparently it isn't "Small", or you
wouldn't be having this problem. Maybe it is Null -- no size recorded
-- though that would be a bad data design, since Null means "no data" or
"not applicable", and shouldn't really be used to represent a meaningful
data value.

However, if [Shirt Size] is record as Null for small sizes, then you
could it is Null . If it is Null, then try this:

SELECT
"Shirt Size" AS TYPE,
Nz([Shirt Size], "Small"),
Count([Shirt Size]) AS CountOfShirtSize,
[Apparel-Men]
FROM Committee
GROUP BY
"Shirt Size",
Nz([Shirt Size], "Small"),
[Apparel-Men]
HAVING
(Count([Shirt Size])>0 AND [Apparel-Men]=True)
OR
Count([Shirt Size])=0;

The only way i could get the 0 count for the small size to return is by
adding the Having clause read to include those counts that = 0 or are
greater than 0.

I don't know if you have this right. It seems to me that you may be
able to do without the criterion on [Shirt Size] altogether, and just
write this:

SELECT
"Shirt Size" AS TYPE,
Nz([Shirt Size], "Small"),
Count([Shirt Size]) AS CountOfShirtSize,
[Apparel-Men]
FROM Committee
GROUP BY
"Shirt Size",
Nz([Shirt Size], "Small"),
[Apparel-Men]
HAVING [Apparel-Men]=True;

But you should try that and see if you get what you want.

 

[Dirk Goldgar]

I believe that
the count of zero is the count of records that are null. The problem with
counting records that are null in the size field is that count does not
count nulls. You could try count(*) in place of Count([shirt Size]) that
counts the number of records and not the number of records that have a
value in the field.

John, I think you're right. If the size "small" is represented in the
[Shirt Size] field by a value of Null, then Count([Shirt Size]) won't return
the real count, and we need Count(*) instead. Good catch!

 

[Dirk Goldgar]

Dirk,

thank you!!! Now is can"see" the word "Small" in the query return even
though the countofshirtsize is "Null" or 0. One last question...I would
like
to order to the shirt size in the query to small, medium, large, XL so it
look correct when i create a report with this quesry...it currently
returns
alphabetically, or Large, Medium, Small, XL. Is that possible?

I'm going to combine my answer to this with the necessary correction pointed
out by John Spencer. Try this SQL:

SELECT
"Shirt Size" AS TYPE,
Nz([Shirt Size], "Small"),
Count(*) AS CountOfShirtSize,
[Apparel-Men]
FROM Committee
GROUP BY
"Shirt Size",
Nz([Shirt Size], "Small"),
[Apparel-Men]
HAVING [Apparel-Men]=True
ORDER BY
Switch([Shirt Size] Is Null, 0,
[Shirt Size] = "Medium", 1,
[Shirt Size] = "Large", 2,
[Shirt Size] = "XL", 3);

 

[Randall]

Dirk and John,

I inserted the SQL statement as you suggested, using the Count(*) for the
shirt count by size. The query returned the count of small shirts to 1 even
though there are no small shirts listed in the accompanying table. There is,
however, one null field because one member does not want a shirt. So, the
proper return with 17 fields in the table should include:
Small =0
Medium = 1
Large = 7
XL = 8
When I change the SLQ back to Count(Committee.[Shirt.Size] I do get the
above return.
I then added the ORDER BY clause and received error message"...does not
include the specified expression Switch([Shirt Size] Is Null, 0, [Shirt
Size] = "Medium", 1,
[Shirt Size] = "Large", 2, [Shirt Size] = "XL", 3); as part of an aggregate
function."

We're getting closer and thanks so much for your expertise!!!
--
Randall

I believe that
the count of zero is the count of records that are null. The problem with
counting records that are null in the size field is that count does not
count nulls. You could try count(*) in place of Count([shirt Size]) that
counts the number of records and not the number of records that have a
value in the field.

John, I think you're right. If the size "small" is represented in the
[Shirt Size] field by a value of Null, then Count([Shirt Size]) won't return
the real count, and we need Count(*) instead. Good catch!

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[John Spencer (MVP)]

Right, you have ZERO records with a shirt size of small. Therefore there is
NO record being returned for small size. No record means there is NOTHING to
count.

Do you have a table of sizes?

If so, you could include that in your query with an outer join and then you
could get the count. Actually since you are restricting the records returned
based on the Committee table you need something a bit more complex.

SELECT "Shirt Size" AS TYPE
, Committee.[Shirt Size]
,Committee.[Apparel-Men]
FROM Committee
WHERE Committee.[Apparel-Men]=True

Now use that saved query in another query

SELECT q.Type
, TableSizes.Size
, Count(TableSizes.Size) as CountShirts
, q.[Apparel-Men]
FROM TableSizes LEFT JOIN qAboveSavedQuery as Q
ON TableSizes.Size = Q.[Shirt Size]
GROUP BY q.Type
, TableSizes.Size
, q.[Apparel-Men]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dirk Goldgar]

Dirk and John,

I inserted the SQL statement as you suggested, using the Count(*) for the
shirt count by size. The query returned the count of small shirts to 1
even
though there are no small shirts listed in the accompanying table. There
is,
however, one null field because one member does not want a shirt. So, the
proper return with 17 fields in the table should include:
Small =0
Medium = 1
Large = 7
XL = 8
When I change the SLQ back to Count(Committee.[Shirt.Size] I do get the
above return.
I then added the ORDER BY clause and received error message"...does not
include the specified expression Switch([Shirt Size] Is Null, 0, [Shirt
Size] = "Medium", 1,
[Shirt Size] = "Large", 2, [Shirt Size] = "XL", 3); as part of an
aggregate
function."

I think I've finally arrived at an understanding of what's in your table.
Contrary to my previous interpretation, you do *not* intend that Null should
be interpreted as "Small". Rather, Null means "no size selected (or no
shirt wanted)". The problem is that you have no orders in your table for
"Small" shirts. In that case, in order to have "Small" listed in the
results of your totals query, you *MUST* have a table that lists the
available sizes. This is what John Spencer -- who is obviously smarter than
I am -- has been getting at. Has his answer resolved your question?

 

[Randall]

Dirk,

Thanks fo responding. With following SQL statement i get the correct of
shirt sizes.
SELECT "Shirt Size" AS TYPE, Nz([Shirt Size], "Small"), Count([Shirt Size])
AS CountOfShirtSize, [Apparel-Men]
FROM Committee
GROUP BY "Shirt Size", Nz([Shirt Size], "Small"), [Apparel-Men]
HAVING [Apparel-Men]=True

The results are listed ascending or I canchange it to descending. I would
like to ORDER BY Small, Medium, Large, XL.

Tried John's suggestion of two queries, but no success.

Thoughts and thanks in advance.
--
Randall

Dirk and John,

I inserted the SQL statement as you suggested, using the Count(*) for the
shirt count by size. The query returned the count of small shirts to 1
even
though there are no small shirts listed in the accompanying table. There
is,
however, one null field because one member does not want a shirt. So, the
proper return with 17 fields in the table should include:
Small =0
Medium = 1
Large = 7
XL = 8
When I change the SLQ back to Count(Committee.[Shirt.Size] I do get the
above return.
I then added the ORDER BY clause and received error message"...does not
include the specified expression Switch([Shirt Size] Is Null, 0, [Shirt
Size] = "Medium", 1,
[Shirt Size] = "Large", 2, [Shirt Size] = "XL", 3); as part of an
aggregate
function."

I think I've finally arrived at an understanding of what's in your table.
Contrary to my previous interpretation, you do *not* intend that Null should
be interpreted as "Small". Rather, Null means "no size selected (or no
shirt wanted)". The problem is that you have no orders in your table for
"Small" shirts. In that case, in order to have "Small" listed in the
results of your totals query, you *MUST* have a table that lists the
available sizes. This is what John Spencer -- who is obviously smarter than
I am -- has been getting at. Has his answer resolved your question?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,

Thanks fo responding. With following SQL statement i get the correct of
shirt sizes.
SELECT "Shirt Size" AS TYPE, Nz([Shirt Size], "Small"), Count([Shirt
Size])
AS CountOfShirtSize, [Apparel-Men]
FROM Committee
GROUP BY "Shirt Size", Nz([Shirt Size], "Small"), [Apparel-Men]
HAVING [Apparel-Men]=True

The results are listed ascending or I canchange it to descending. I would
like to ORDER BY Small, Medium, Large, XL.

Tried John's suggestion of two queries, but no success.

Randall, I think you only *think* this is giving you the correct results.
Or, rather, if it happens to be giving you the correct result now, it won't
in the future when the data are slightly different. John's suggestion was
the way to go, and I think you should direct your efforts to making it work.
You don't actually need two queries, I think (though I could be wrong), but
you do need a simple table of shirt sizes. With that you can build an
appropriate query that will give you the count of each shirt size regardless
of whether anyone has ordered that size or not.

Do you have a ShirtSizes table? If not, it would be a trivial matter to
create one and populate it with the appropriate set of records.

 

[Randall]

Dirk,

No, i don't have a shirts table. While i'm just an Access beginner, it's
amazing how powerful the Access program can be. On the other hand, a few
years ago, an Access programmer friend said to me and I paraphrase "Keep it
Simple, STUPID." That said, "your right"...i need to break down my one table
into smaller tables with relationship to my commitee names table. I know
then I can pull appropriate queries when needed and not try to gather so much
information in one form.

This has been a good lesson for me...i certainly appreciate the help and
suggestions from you and John...i will drop a send a quick email when i have
revised by table list.

Thanks again,
--
Randall

Dirk,

Thanks fo responding. With following SQL statement i get the correct of
shirt sizes.
SELECT "Shirt Size" AS TYPE, Nz([Shirt Size], "Small"), Count([Shirt
Size])
AS CountOfShirtSize, [Apparel-Men]
FROM Committee
GROUP BY "Shirt Size", Nz([Shirt Size], "Small"), [Apparel-Men]
HAVING [Apparel-Men]=True

The results are listed ascending or I canchange it to descending. I would
like to ORDER BY Small, Medium, Large, XL.

Tried John's suggestion of two queries, but no success.

Randall, I think you only *think* this is giving you the correct results.
Or, rather, if it happens to be giving you the correct result now, it won't
in the future when the data are slightly different. John's suggestion was
the way to go, and I think you should direct your efforts to making it work.
You don't actually need two queries, I think (though I could be wrong), but
you do need a simple table of shirt sizes. With that you can build an
appropriate query that will give you the count of each shirt size regardless
of whether anyone has ordered that size or not.

Do you have a ShirtSizes table? If not, it would be a trivial matter to
create one and populate it with the appropriate set of records.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)