Getting a Count for a range of values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to make a query that will give me a count of all the values in a
column that are <=25 broken up by month. At the moment for the count I have a
query that looks like this:
ShowCount: Sum(IIf([MyTBL]![Tot Chargeback]<=25,1,0))
but it only returns a count of about 15 total, yet the true number is over
5000

Any suggestions?

Thanks,
Pete
 
Actually Sum(boolean expression) is probably more appropriate. Pete didn't
suggest anything about the "by month".

Are you expecting to sum the TotChargeback field values or just count the
number of times the TotChargeback is <=25?

--
Duane Hookom
MS Access MVP


Lynn Trapp said:
Well, you might try the Count() function, instead of the Sum() function.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Pete said:
I am trying to make a query that will give me a count of all the values in
a
column that are <=25 broken up by month. At the moment for the count I
have a
query that looks like this:
ShowCount: Sum(IIf([MyTBL]![Tot Chargeback]<=25,1,0))
but it only returns a count of about 15 total, yet the true number is
over
5000

Any suggestions?

Thanks,
Pete
Click to expand...
Click to expand...
 
I just would like to count the number of times the TotChargeback is <=25, as
far as the "by month" part of the query I used the summary function in the
query wizard to get my "Accepted" dates grouped by month

Expr1: Year([MyTBL].[Accepted])*12+DatePart('m',[MyTBL].[Accepted])-1


Duane Hookom said:
Actually Sum(boolean expression) is probably more appropriate. Pete didn't
suggest anything about the "by month".

Are you expecting to sum the TotChargeback field values or just count the
number of times the TotChargeback is <=25?

--
Duane Hookom
MS Access MVP


Lynn Trapp said:
Well, you might try the Count() function, instead of the Sum() function.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Pete said:
I am trying to make a query that will give me a count of all the values in
a
column that are <=25 broken up by month. At the moment for the count I
have a
query that looks like this:
ShowCount: Sum(IIf([MyTBL]![Tot Chargeback]<=25,1,0))
but it only returns a count of about 15 total, yet the true number is
over
5000

Any suggestions?

Thanks,
Pete
Click to expand...
Click to expand...
Click to expand...
 
Maybe you should show us your full SQL view.

--
Duane Hookom
MS Access MVP


Pete said:
I just would like to count the number of times the TotChargeback is <=25,
as
far as the "by month" part of the query I used the summary function in the
query wizard to get my "Accepted" dates grouped by month

Expr1: Year([MyTBL].[Accepted])*12+DatePart('m',[MyTBL].[Accepted])-1


Duane Hookom said:
Actually Sum(boolean expression) is probably more appropriate. Pete
didn't
suggest anything about the "by month".

Are you expecting to sum the TotChargeback field values or just count the
number of times the TotChargeback is <=25?

--
Duane Hookom
MS Access MVP


Lynn Trapp said:
Well, you might try the Count() function, instead of the Sum()
function.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


I am trying to make a query that will give me a count of all the values
in
a
column that are <=25 broken up by month. At the moment for the count I
have a
query that looks like this:
ShowCount: Sum(IIf([MyTBL]![Tot Chargeback]<=25,1,0))
but it only returns a count of about 15 total, yet the true number is
over
5000

Any suggestions?

Thanks,
Pete
Click to expand...
Click to expand...
Click to expand...
 
Sorry, I should have thought of that from the start. Here ya go:

SELECT DISTINCTROW Format$([MyTBL].[Accepted],'mmmm yyyy') AS [Accepted By
Month], Year([MyTBL].[Accepted])*12+DatePart('m',[MyTBL].[Accepted])-1 AS
Expr1, Sum(IIf([MyTBL]![Tot Chargeback]<=25,1,0)) AS ShowCount
FROM MyTBL
GROUP BY Format$([MyTBL].[Accepted],'mmmm yyyy'),
Year([MyTBL].[Accepted])*12+DatePart('m',[myTBL].[Accepted])-1
ORDER BY Year([MyTBL].[Accepted])*12+DatePart('m',[MyTBL].[Accepted])-1;

Thank you so much for your help,
Pete

Duane Hookom said:
Maybe you should show us your full SQL view.

--
Duane Hookom
MS Access MVP


Pete said:
I just would like to count the number of times the TotChargeback is <=25,
as
far as the "by month" part of the query I used the summary function in the
query wizard to get my "Accepted" dates grouped by month

Expr1: Year([MyTBL].[Accepted])*12+DatePart('m',[MyTBL].[Accepted])-1


Duane Hookom said:
Actually Sum(boolean expression) is probably more appropriate. Pete
didn't
suggest anything about the "by month".

Are you expecting to sum the TotChargeback field values or just count the
number of times the TotChargeback is <=25?

--
Duane Hookom
MS Access MVP


Well, you might try the Count() function, instead of the Sum()
function.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


I am trying to make a query that will give me a count of all the values
in
a
column that are <=25 broken up by month. At the moment for the count I
have a
query that looks like this:
ShowCount: Sum(IIf([MyTBL]![Tot Chargeback]<=25,1,0))
but it only returns a count of about 15 total, yet the true number is
over
5000

Any suggestions?

Thanks,
Pete
Click to expand...
Click to expand...
Click to expand...
 
I would remove the group by, add Tot ChargeBack, and paste the datasheet
view into Excel to count the records where <=25.

--
Duane Hookom
MS Access MVP


Pete said:
Sorry, I should have thought of that from the start. Here ya go:

SELECT DISTINCTROW Format$([MyTBL].[Accepted],'mmmm yyyy') AS [Accepted By
Month], Year([MyTBL].[Accepted])*12+DatePart('m',[MyTBL].[Accepted])-1 AS
Expr1, Sum(IIf([MyTBL]![Tot Chargeback]<=25,1,0)) AS ShowCount
FROM MyTBL
GROUP BY Format$([MyTBL].[Accepted],'mmmm yyyy'),
Year([MyTBL].[Accepted])*12+DatePart('m',[myTBL].[Accepted])-1
ORDER BY Year([MyTBL].[Accepted])*12+DatePart('m',[MyTBL].[Accepted])-1;

Thank you so much for your help,
Pete

Duane Hookom said:
Maybe you should show us your full SQL view.

--
Duane Hookom
MS Access MVP


Pete said:
I just would like to count the number of times the TotChargeback is
<=25,
as
far as the "by month" part of the query I used the summary function in
the
query wizard to get my "Accepted" dates grouped by month

Expr1: Year([MyTBL].[Accepted])*12+DatePart('m',[MyTBL].[Accepted])-1


:

Actually Sum(boolean expression) is probably more appropriate. Pete
didn't
suggest anything about the "by month".

Are you expecting to sum the TotChargeback field values or just count
the
number of times the TotChargeback is <=25?

--
Duane Hookom
MS Access MVP


Well, you might try the Count() function, instead of the Sum()
function.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


I am trying to make a query that will give me a count of all the
values
in
a
column that are <=25 broken up by month. At the moment for the
count I
have a
query that looks like this:
ShowCount: Sum(IIf([MyTBL]![Tot Chargeback]<=25,1,0))
but it only returns a count of about 15 total, yet the true number
is
over
5000

Any suggestions?

Thanks,
Pete
Click to expand...
Click to expand...
Click to expand...
 
Back
Top