Help with sql which counts records

[TonyWilliams via AccessMonster.com]

Could someone help me to extend this sql to include:
1. a count of txtsole where the field is a YES/NO field and I want a count of
where the answer is YES
2. a count of txtmulti where the field is a YES/NO field and I want a count
of where the answer is YES
3. a count of txtsole where the field is a YES/NO field and I want a count of
where the answer is YES
4. a count of txtnbrparts where the field is a number field and I want a
count of where the answer greater than 1
I think I need to extend the WHERE statement?

SELECT tblhvdealspt1.txtablhybrid, Count(*) AS totals, tblhvdealspt1.txtmonth,
Count(tblhvdealspt1.txtdealnbr) AS CountOftxtdealnbr, Sum(tblhvdealspt1.
txtukline) AS SumOftxtukline
FROM tblhvdealspt1
WHERE (((tblhvdealspt1.txtablhybrid) In ("Pure ABL","HYBRID")))
GROUP BY tblhvdealspt1.txtablhybrid, tblhvdealspt1.txtmonth
HAVING (((tblhvdealspt1.txtmonth)=#3/1/2010#));


Thanks

 

[John Spencer]

SELECT tblhvdealspt1.txtablhybrid
, Count(*) AS totals
, tblhvdealspt1.txtmonth
, Count(tblhvdealspt1.txtdealnbr) AS CountOftxtdealnbr
, Sum(tblhvdealspt1.txtukline) AS SumOftxtukline
, Count(IIF(txtSole,1,Null)) as TxtSoleYes
, Count(IIF(txtMulti,1,Null)) as txtMultiYes
, Count(IIF(txtnbrParts>1,1,null) as txtNbrPartsOver1
FROM tblhvdealspt1
WHERE (((tblhvdealspt1.txtablhybrid) In ("Pure ABL","HYBRID")))
AND tblhvdealspt1.txtmonth=#3/1/2010#
GROUP BY tblhvdealspt1.txtablhybrid, tblhvdealspt1.txtmonth

Count counts the presence of a value other than null.

An alternative for counting is to use expressions like
Abs(Sum(txtSole))
or
Abs(Sum(txtnbrParts>1))

This alternative works since False (No) is always equal to zero and True is
equal to -1 (in Access) or 1 in many other systems.

If you want to count No (false) then
Count(IIF(txtSole,Null,1))
or
Count(IIF(txtSole=False,1,Null)
should work for you.

By the way I could not see any difference in you request for items 1 and 3.

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

 

[TonyWilliams via AccessMonster.com]

Thanks John that was really helpful. However I seem to have a problem with
part of the sql that deals with "Pure ABL" and "Hybrid". These values come
frpom a combo box based on defined values and not a table. What I want to do
is count how many records have the value "Pure ABL" and how many have
"HYBRID" as the value for each record. The sql I started with doesn't now
seem to give me that. So how do I change the sql to give me those totals?
Thanks a lot for your help.
Tony

SELECT tblhvdealspt1.txtablhybrid
, Count(*) AS totals
, tblhvdealspt1.txtmonth
, Count(tblhvdealspt1.txtdealnbr) AS CountOftxtdealnbr
, Sum(tblhvdealspt1.txtukline) AS SumOftxtukline
, Count(IIF(txtSole,1,Null)) as TxtSoleYes
, Count(IIF(txtMulti,1,Null)) as txtMultiYes
, Count(IIF(txtnbrParts>1,1,null) as txtNbrPartsOver1
FROM tblhvdealspt1
WHERE (((tblhvdealspt1.txtablhybrid) In ("Pure ABL","HYBRID")))
AND tblhvdealspt1.txtmonth=#3/1/2010#
GROUP BY tblhvdealspt1.txtablhybrid, tblhvdealspt1.txtmonth

Count counts the presence of a value other than null.

An alternative for counting is to use expressions like
Abs(Sum(txtSole))
or
Abs(Sum(txtnbrParts>1))

This alternative works since False (No) is always equal to zero and True is
equal to -1 (in Access) or 1 in many other systems.

If you want to count No (false) then
Count(IIF(txtSole,Null,1))
or
Count(IIF(txtSole=False,1,Null)
should work for you.

By the way I could not see any difference in you request for items 1 and 3.

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

Could someone help me to extend this sql to include:
1. a count of txtsole where the field is a YES/NO field and I want a count of

[quoted text clipped - 16 lines]

Thanks

 

[John Spencer]

Are you storing the values Pure Abl and Hybrid in a field? Or are you storing
a key value such as 1 or 2 that refers to the text values in another table? Or
are you storing a value that is something else.?

Perhaps you want
Count(txtablhybrid="Hybrid",1,0) As HybirdCount

or if the actual value is a number then
Count(txtablhybrid=1,1,0)

another option would

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

Thanks John that was really helpful. However I seem to have a problem with
part of the sql that deals with "Pure ABL" and "Hybrid". These values come
frpom a combo box based on defined values and not a table. What I want to do
is count how many records have the value "Pure ABL" and how many have
"HYBRID" as the value for each record. The sql I started with doesn't now
seem to give me that. So how do I change the sql to give me those totals?
Thanks a lot for your help.
Tony

SELECT tblhvdealspt1.txtablhybrid
, Count(*) AS totals
, tblhvdealspt1.txtmonth
, Count(tblhvdealspt1.txtdealnbr) AS CountOftxtdealnbr
, Sum(tblhvdealspt1.txtukline) AS SumOftxtukline
, Count(IIF(txtSole,1,Null)) as TxtSoleYes
, Count(IIF(txtMulti,1,Null)) as txtMultiYes
, Count(IIF(txtnbrParts>1,1,null) as txtNbrPartsOver1
FROM tblhvdealspt1
WHERE (((tblhvdealspt1.txtablhybrid) In ("Pure ABL","HYBRID")))
AND tblhvdealspt1.txtmonth=#3/1/2010#
GROUP BY tblhvdealspt1.txtablhybrid, tblhvdealspt1.txtmonth

Count counts the presence of a value other than null.

An alternative for counting is to use expressions like
Abs(Sum(txtSole))
or
Abs(Sum(txtnbrParts>1))

This alternative works since False (No) is always equal to zero and True is
equal to -1 (in Access) or 1 in many other systems.

If you want to count No (false) then
Count(IIF(txtSole,Null,1))
or
Count(IIF(txtSole=False,1,Null)
should work for you.

By the way I could not see any difference in you request for items 1 and 3.

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

Could someone help me to extend this sql to include:
1. a count of txtsole where the field is a YES/NO field and I want a count of

[quoted text clipped - 16 lines]

Thanks

 

[TonyWilliams via AccessMonster.com]

Hi John the values are stored in a field (txtablhybrid) as a text value and
are selected from a combox where the row source is a value list, there are
only the two values so I didn't think it was necessary to build a table for
these values. Currently my sql is this:
SELECT tblhvdealspt1.txtmonth, Count(tblhvdealspt1.txtdealnbr) AS
CountOftxtdealnbr, Sum(tblhvdealspt1.txtukline) AS SumOftxtukline, Count(IIf
(txtSole,1,Null)) AS TxtSoleYes, Count(IIf(txtMulti,1,Null)) AS txtMultiYes,
Count(IIf(txtnbrParts>1,1,Null)) AS txtNbrPartsOver1, Count(tblhvdealspt1.
txtdealnbr) AS PureABL2, Count(tblhvdealspt1.txtdealnbr) AS PureABL3,
tblhvdealspt1.txtablhybrid
FROM tblhvdealspt1
GROUP BY tblhvdealspt1.txtmonth, tblhvdealspt1.txtablhybrid
HAVING (((tblhvdealspt1.[txtablhybrid])="Pure ABL" Or (tblhvdealspt1.
[txtablhybrid])="Hybrid"));

This gives me a result on two lines like this:
March 2010 2 88 2 1 0 2 2 Hybrid
March 2010 4 253 3 1 0 4 4 Pure ABL

Whereasthe result I want is one line like this:
March 2010 6 391 5 2 0 4 (Hybrid) 2 (Pure ABL)

Hope I have explained that?
Thanks for your help.
Tony

Are you storing the values Pure Abl and Hybrid in a field? Or are you storing
a key value such as 1 or 2 that refers to the text values in another table? Or
are you storing a value that is something else.?

Perhaps you want
Count(txtablhybrid="Hybrid",1,0) As HybirdCount

or if the actual value is a number then
Count(txtablhybrid=1,1,0)

another option would

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

Thanks John that was really helpful. However I seem to have a problem with
part of the sql that deals with "Pure ABL" and "Hybrid". These values come

[quoted text clipped - 45 lines]

[quoted text clipped - 16 lines]
Thanks

 

[TonyWilliams via AccessMonster.com]

I've also tried this sql and it counts the total of Pure ABL and Hybrid for
the whole database, not just for each specific month:
SELECT tblhvdealspt1.txtmonth, Count(tblhvdealspt1.txtdealnbr) AS
CountOftxtdealnbr, Sum(tblhvdealspt1.txtukline) AS SumOftxtukline, Count(IIf
(txtSole,1,Null)) AS TxtSoleYes, Count(IIf(txtMulti,1,Null)) AS txtMultiYes,
Count(IIf(txtnbrParts>1,1,Null)) AS txtNbrPartsOver1, Count(IIf([txtnbrParts]
<1,1,Null)) AS txtNbrPartsUnder1, DCount("[txtablhybrid]","tblhvdealspt1","
[txtablhybrid]='Hybrid'") AS Hybrid, DCount("[txtablhybrid]","tblhvdealspt1",
"[txtablhybrid]='Pure ABL'") AS PurABL
FROM tblhvdealspt1
GROUP BY tblhvdealspt1.txtmonth;

Is that what DCount does?

Thanks
Tony

Hi John the values are stored in a field (txtablhybrid) as a text value and
are selected from a combox where the row source is a value list, there are
only the two values so I didn't think it was necessary to build a table for
these values. Currently my sql is this:
SELECT tblhvdealspt1.txtmonth, Count(tblhvdealspt1.txtdealnbr) AS
CountOftxtdealnbr, Sum(tblhvdealspt1.txtukline) AS SumOftxtukline, Count(IIf
(txtSole,1,Null)) AS TxtSoleYes, Count(IIf(txtMulti,1,Null)) AS txtMultiYes,
Count(IIf(txtnbrParts>1,1,Null)) AS txtNbrPartsOver1, Count(tblhvdealspt1.
txtdealnbr) AS PureABL2, Count(tblhvdealspt1.txtdealnbr) AS PureABL3,
tblhvdealspt1.txtablhybrid
FROM tblhvdealspt1
GROUP BY tblhvdealspt1.txtmonth, tblhvdealspt1.txtablhybrid
HAVING (((tblhvdealspt1.[txtablhybrid])="Pure ABL" Or (tblhvdealspt1.
[txtablhybrid])="Hybrid"));

This gives me a result on two lines like this:
March 2010 2 88 2 1 0 2 2 Hybrid
March 2010 4 253 3 1 0 4 4 Pure ABL

Whereasthe result I want is one line like this:
March 2010 6 391 5 2 0 4 (Hybrid) 2 (Pure ABL)

Hope I have explained that?
Thanks for your help.
Tony

Are you storing the values Pure Abl and Hybrid in a field? Or are you storing
a key value such as 1 or 2 that refers to the text values in another table? Or

[quoted text clipped - 18 lines]

[quoted text clipped - 16 lines]
Thanks

 

[John Spencer]

If you use DCount without limiting it to a specific month then it will count
the records for the entire period.

You should be able to use the following to limit the count to the current month.


DCount("[txtablhybrid]","tblhvdealspt1",
"[txtablhybrid]='Hybrid' and txtMonth=#" & txtMonth & "#")


Or use this modification of your query to get stuff all on one line.

SELECT Count(txtTblHybrid="Pure ABL",1,Null) as CountPure
, Count(txtTblHybrid="Hybrid",1,Null) as CountHybrid
, Count(*) AS totals
, tblhvdealspt1.txtmonth
, Count(tblhvdealspt1.txtdealnbr) AS CountOftxtdealnbr
, Sum(tblhvdealspt1.txtukline) AS SumOftxtukline
, Count(IIF(txtSole,1,Null)) as TxtSoleYes
, Count(IIF(txtMulti,1,Null)) as txtMultiYes
, Count(IIF(txtnbrParts>1,1,null) as txtNbrPartsOver1
FROM tblhvdealspt1
WHERE (((tblhvdealspt1.txtablhybrid) In ("Pure ABL","HYBRID")))
AND tblhvdealspt1.txtmonth=#3/1/2010#
GROUP BY tblhvdealspt1.txtmonth

AND If you want multiple months in one query. NOTE that the criteria has been
moved into a WHERE clause instead of in the HAVING clause. You should only
use a HAVING clause to apply criteria against values that use one of the
aggregate expressions (SUM, Count, Avg, Min, Max). If you are grouping by a
field, use the where clause for greater efficiency.

SELECT Format(TxtMonth,"yyyy-mm") as YearMonth
, Count(txtTblHybrid="Pure ABL",1,Null) as CountPure
, Count(txtTblHybrid="Hybrid",1,Null) as CountHybrid
, Count(*) AS totals
, Count(tblhvdealspt1.txtdealnbr) AS CountOftxtdealnbr
, Sum(tblhvdealspt1.txtukline) AS SumOftxtukline
, Count(IIF(txtSole,1,Null)) as TxtSoleYes
, Count(IIF(txtMulti,1,Null)) as txtMultiYes
, Count(IIF(txtnbrParts>1,1,null) as txtNbrPartsOver1
FROM tblhvdealspt1
WHERE (((tblhvdealspt1.txtablhybrid) In ("Pure ABL","HYBRID")))
AND tblhvdealspt1.txtmonth>#3/1/2009# AND txtMonth<= #3/1/2010#
GROUP BY Format(TxtMonth,"yyyy-mm")


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

I've also tried this sql and it counts the total of Pure ABL and Hybrid for
the whole database, not just for each specific month:
SELECT tblhvdealspt1.txtmonth, Count(tblhvdealspt1.txtdealnbr) AS
CountOftxtdealnbr, Sum(tblhvdealspt1.txtukline) AS SumOftxtukline, Count(IIf
(txtSole,1,Null)) AS TxtSoleYes, Count(IIf(txtMulti,1,Null)) AS txtMultiYes,
Count(IIf(txtnbrParts>1,1,Null)) AS txtNbrPartsOver1, Count(IIf([txtnbrParts]
<1,1,Null)) AS txtNbrPartsUnder1, DCount("[txtablhybrid]","tblhvdealspt1","
[txtablhybrid]='Hybrid'") AS Hybrid, DCount("[txtablhybrid]","tblhvdealspt1",
"[txtablhybrid]='Pure ABL'") AS PurABL
FROM tblhvdealspt1
GROUP BY tblhvdealspt1.txtmonth;

Is that what DCount does?

Thanks
Tony

Hi John the values are stored in a field (txtablhybrid) as a text value and
are selected from a combox where the row source is a value list, there are
only the two values so I didn't think it was necessary to build a table for
these values. Currently my sql is this:
SELECT tblhvdealspt1.txtmonth, Count(tblhvdealspt1.txtdealnbr) AS
CountOftxtdealnbr, Sum(tblhvdealspt1.txtukline) AS SumOftxtukline, Count(IIf
(txtSole,1,Null)) AS TxtSoleYes, Count(IIf(txtMulti,1,Null)) AS txtMultiYes,
Count(IIf(txtnbrParts>1,1,Null)) AS txtNbrPartsOver1, Count(tblhvdealspt1.
txtdealnbr) AS PureABL2, Count(tblhvdealspt1.txtdealnbr) AS PureABL3,
tblhvdealspt1.txtablhybrid
FROM tblhvdealspt1
GROUP BY tblhvdealspt1.txtmonth, tblhvdealspt1.txtablhybrid
HAVING (((tblhvdealspt1.[txtablhybrid])="Pure ABL" Or (tblhvdealspt1.
[txtablhybrid])="Hybrid"));

This gives me a result on two lines like this:
March 2010 2 88 2 1 0 2 2 Hybrid
March 2010 4 253 3 1 0 4 4 Pure ABL

Whereasthe result I want is one line like this:
March 2010 6 391 5 2 0 4 (Hybrid) 2 (Pure ABL)

Hope I have explained that?
Thanks for your help.
Tony

Are you storing the values Pure Abl and Hybrid in a field? Or are you storing
a key value such as 1 or 2 that refers to the text values in another table? Or

[quoted text clipped - 18 lines]

[quoted text clipped - 16 lines]
Thanks

 

[TonyWilliams via AccessMonster.com]

Thanks John. However when I run the sql I get an error message that says
wrong number of arguments in this line
Count(txtTblHybrid="Pure ABL",1,Null) as CountPure

Thanks for the tip on months. What I'm planning to do eventually is use a
form for the user to input a date so I will need to change the sql
(eventually) to define the month as being the control on that form, but I
thibk I can handle that.

Any ideas on the error message? I thought the Count expression could only
have one argument as in Count([txABLHybrid])

Thanks again
Tony

If you use DCount without limiting it to a specific month then it will count
the records for the entire period.

You should be able to use the following to limit the count to the current month.

DCount("[txtablhybrid]","tblhvdealspt1",
"[txtablhybrid]='Hybrid' and txtMonth=#" & txtMonth & "#")

Or use this modification of your query to get stuff all on one line.

SELECT Count(txtTblHybrid="Pure ABL",1,Null) as CountPure
, Count(txtTblHybrid="Hybrid",1,Null) as CountHybrid
, Count(*) AS totals
, tblhvdealspt1.txtmonth
, Count(tblhvdealspt1.txtdealnbr) AS CountOftxtdealnbr
, Sum(tblhvdealspt1.txtukline) AS SumOftxtukline
, Count(IIF(txtSole,1,Null)) as TxtSoleYes
, Count(IIF(txtMulti,1,Null)) as txtMultiYes
, Count(IIF(txtnbrParts>1,1,null) as txtNbrPartsOver1
FROM tblhvdealspt1
WHERE (((tblhvdealspt1.txtablhybrid) In ("Pure ABL","HYBRID")))
AND tblhvdealspt1.txtmonth=#3/1/2010#
GROUP BY tblhvdealspt1.txtmonth

AND If you want multiple months in one query. NOTE that the criteria has been
moved into a WHERE clause instead of in the HAVING clause. You should only
use a HAVING clause to apply criteria against values that use one of the
aggregate expressions (SUM, Count, Avg, Min, Max). If you are grouping by a
field, use the where clause for greater efficiency.

SELECT Format(TxtMonth,"yyyy-mm") as YearMonth
, Count(txtTblHybrid="Pure ABL",1,Null) as CountPure
, Count(txtTblHybrid="Hybrid",1,Null) as CountHybrid
, Count(*) AS totals
, Count(tblhvdealspt1.txtdealnbr) AS CountOftxtdealnbr
, Sum(tblhvdealspt1.txtukline) AS SumOftxtukline
, Count(IIF(txtSole,1,Null)) as TxtSoleYes
, Count(IIF(txtMulti,1,Null)) as txtMultiYes
, Count(IIF(txtnbrParts>1,1,null) as txtNbrPartsOver1
FROM tblhvdealspt1
WHERE (((tblhvdealspt1.txtablhybrid) In ("Pure ABL","HYBRID")))
AND tblhvdealspt1.txtmonth>#3/1/2009# AND txtMonth<= #3/1/2010#
GROUP BY Format(TxtMonth,"yyyy-mm")

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

I've also tried this sql and it counts the total of Pure ABL and Hybrid for
the whole database, not just for each specific month:

[quoted text clipped - 43 lines]

[quoted text clipped - 16 lines]
Thanks

 

[John Spencer]

DOH!!! My stupid error. I started to use one expression and switched to
another and failed to clean up

You should be able to use either of the following expressions
Count(IIF(txtTblHybrid="Pure ABL",1,Null)) as CountPure
OR
Abs(Sum(txtTblHybrid="Pure ABL")) as CountPure


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

Thanks John. However when I run the sql I get an error message that says
wrong number of arguments in this line
Count(txtTblHybrid="Pure ABL",1,Null) as CountPure

Thanks for the tip on months. What I'm planning to do eventually is use a
form for the user to input a date so I will need to change the sql
(eventually) to define the month as being the control on that form, but I
thibk I can handle that.

Any ideas on the error message? I thought the Count expression could only
have one argument as in Count([txABLHybrid])

Thanks again
Tony

If you use DCount without limiting it to a specific month then it will count
the records for the entire period.

You should be able to use the following to limit the count to the current month.

DCount("[txtablhybrid]","tblhvdealspt1",
"[txtablhybrid]='Hybrid' and txtMonth=#" & txtMonth & "#")

Or use this modification of your query to get stuff all on one line.

SELECT Count(txtTblHybrid="Pure ABL",1,Null) as CountPure
, Count(txtTblHybrid="Hybrid",1,Null) as CountHybrid
, Count(*) AS totals
, tblhvdealspt1.txtmonth
, Count(tblhvdealspt1.txtdealnbr) AS CountOftxtdealnbr
, Sum(tblhvdealspt1.txtukline) AS SumOftxtukline
, Count(IIF(txtSole,1,Null)) as TxtSoleYes
, Count(IIF(txtMulti,1,Null)) as txtMultiYes
, Count(IIF(txtnbrParts>1,1,null) as txtNbrPartsOver1
FROM tblhvdealspt1
WHERE (((tblhvdealspt1.txtablhybrid) In ("Pure ABL","HYBRID")))
AND tblhvdealspt1.txtmonth=#3/1/2010#
GROUP BY tblhvdealspt1.txtmonth

AND If you want multiple months in one query. NOTE that the criteria has been
moved into a WHERE clause instead of in the HAVING clause. You should only
use a HAVING clause to apply criteria against values that use one of the
aggregate expressions (SUM, Count, Avg, Min, Max). If you are grouping by a
field, use the where clause for greater efficiency.

SELECT Format(TxtMonth,"yyyy-mm") as YearMonth
, Count(txtTblHybrid="Pure ABL",1,Null) as CountPure
, Count(txtTblHybrid="Hybrid",1,Null) as CountHybrid
, Count(*) AS totals
, Count(tblhvdealspt1.txtdealnbr) AS CountOftxtdealnbr
, Sum(tblhvdealspt1.txtukline) AS SumOftxtukline
, Count(IIF(txtSole,1,Null)) as TxtSoleYes
, Count(IIF(txtMulti,1,Null)) as txtMultiYes
, Count(IIF(txtnbrParts>1,1,null) as txtNbrPartsOver1
FROM tblhvdealspt1
WHERE (((tblhvdealspt1.txtablhybrid) In ("Pure ABL","HYBRID")))
AND tblhvdealspt1.txtmonth>#3/1/2009# AND txtMonth<= #3/1/2010#
GROUP BY Format(TxtMonth,"yyyy-mm")

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

I've also tried this sql and it counts the total of Pure ABL and Hybrid for
the whole database, not just for each specific month:

[quoted text clipped - 43 lines]

[quoted text clipped - 16 lines]
Thanks

 

[TonyWilliams via AccessMonster.com]

It's a real shame that many of you guys who help are so far away John, I
can't tell you how much I appreciate what you've just done. If you were
closer it would really be worth a 5* meal or a crate of the finest!

Thank you, the amended sql worked and gave me exactly what I wanted.
Thanks again John and I wish you a very happy and prosperous New Year
Tony

DOH!!! My stupid error. I started to use one expression and switched to
another and failed to clean up

You should be able to use either of the following expressions
Count(IIF(txtTblHybrid="Pure ABL",1,Null)) as CountPure
OR
Abs(Sum(txtTblHybrid="Pure ABL")) as CountPure

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

Thanks John. However when I run the sql I get an error message that says
wrong number of arguments in this line

[quoted text clipped - 65 lines]

[quoted text clipped - 16 lines]
Thanks