Help with Sum/Counting a column in a Query

So I've got a table full of orders. I have a query that counts the
orders, no problem.

Now I've also got a query that tells me if the order is due or not.
Its a little more complicated, but basically says
"Due:Iif(Due_date>Today,"No","Yes"). Now I want to count all the
orders in that query that are due. I have Count_of_Due:
Count(Order_Due.Due) and it crashes. I try adding a column in the
Order_Due table with an Number_Due: Iif(Due="Yes",1,0) to make a
number. I tell it to count Number_Due. WTF? I try the same with the
sum function, same result. How do I make it count the number of times
a string appears in a query?

Re: Help with Sum/Counting a column in a Query

Scott269 wrote in
news:1f13bc34-58ea-4184-9943-
6805e36e096c@a70g2000hsh.googlegroups.co
m:

> So I've got a table full of orders. I have a query that counts
> the orders, no problem.
>
> Now I've also got a query that tells me if the order is due or
> not. Its a little more complicated, but basically says
> "Due:Iif(Due_date>Today,"No","Yes"). Now I want to count all the
> orders in that query that are due. I have Count_of_Due:
> Count(Order_Due.Due) and it crashes. I try adding a column in the
> Order_Due table with an Number_Due: Iif(Due="Yes",1,0) to make a
> number. I tell it to count Number_Due. WTF? I try the same with
> the sum function, same result. How do I make it count the number
> of times a string appears in a query?
>
Crashes? strange, If you mean "generates an error message" you
should say so.

All you need is one query to get both your counts.

SELECT count(Due_Date) as OrderCount, sum(iif(duedate > date(),1,0)
AS orderDueCount FROM Orders;

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Re: Help with Sum/Counting a column in a Query

On Apr 2, 4:25=A0pm, Scott269 wrote:
> So I've got a table full of orders. =A0I have a query that counts the
> orders, no problem.
>
> Now I've also got a query that tells me if the order is due or not.
> Its a little more complicated, but basically says
> "Due:Iif(Due_date>Today,"No","Yes"). =A0Now I want to count all the
> orders in that query that are due. =A0I have Count_of_Due:
> Count(Order_Due.Due) and it crashes. =A0I try adding a column in the
> Order_Due table with an Number_Due: Iif(Due=3D"Yes",1,0) to make a
> number. =A0I tell it to count Number_Due. =A0WTF? =A0I try the same with t=
he
> sum function, same result. =A0How do I make it count the number of times
> a string appears in a query?

This tends to work for me:

Count(iif(Due=3D"Yes",Due,null))

Count does not include null values.

- GL

Re: Help with Sum/Counting a column in a Query

On Apr 2, 3:56=A0pm, Bob Quintal wrote:
> Scott269 wrote in
> news:1f13bc34-58ea-4184-9943-
> 6805e36e0...@a70g2000hsh.googlegroups.co
> m:
>
> > So I've got a table full of orders. =A0I have a query that counts
> > the orders, no problem.
>
> > Now I've also got a query that tells me if the order is due or
> > not. Its a little more complicated, but basically says
> > "Due:Iif(Due_date>Today,"No","Yes"). =A0Now I want to count all the
> > orders in that query that are due. =A0I have Count_of_Due:
> > Count(Order_Due.Due) and it crashes. =A0I try adding a column in the
> > Order_Due table with an Number_Due: Iif(Due=3D"Yes",1,0) to make a
> > number. =A0I tell it to count Number_Due. =A0WTF? =A0I try the same with=

> > the sum function, same result. =A0How do I make it count the number
> > of times a string appears in a query?
>
> Crashes? strange, If you mean "generates an error message" you
> should say so.
>

Nope, no error, Access just locks up and I have to ctrl alt delete to
close it. Like it was stuck in an infinte loop or just taking a
really long time to calculate. I've got 20,000 records, but like I
said, it counts the number of orders no problem, it just gets stuck
counting the number of orders that are due.

Re: Help with Sum/Counting a column in a Query

Scott269 wrote in news:1f13bc34-58ea-4184-9943-
6805e36e096c@a70g2000hsh.googlegroups.com:

> So I've got a table full of orders. I have a query that counts the
> orders, no problem.
>
> Now I've also got a query that tells me if the order is due or not.
> Its a little more complicated, but basically says
> "Due:Iif(Due_date>Today,"No","Yes"). Now I want to count all the
> orders in that query that are due. I have Count_of_Due:
> Count(Order_Due.Due) and it crashes. I try adding a column in the
> Order_Due table with an Number_Due: Iif(Due="Yes",1,0) to make a
> number. I tell it to count Number_Due. WTF? I try the same with the
> sum function, same result. How do I make it count the number of times
> a string appears in a query?
>

COUNT(*) As NumberDue WHERE Due_Date < Today

OR

if you don't have the field Due_Date in your query:

COUNT(*) As NumberDue WHERE Due = "Yes"

(Access/JET/Ace cannot optimize Count(FieldName))

Have you considered that Iif(Due_date>Today,"No","Yes") does not treat
DueDates of 2004-04-02 and 2004-04-02 01:00:00 similarily returning "No"
for the first and "yes" for the second.

If there is the possibility of a time value greater than zero in the
Due_Date Field then probably
IIf(Due_date>=DateAdd("d", 1, Date),"No","Yes")

Re: Help with Sum/Counting a column in a Query

Guillermo_Lopez wrote in news:f3931884-754a-4e7c-817b-
69553228eed9@8g2000hsu.googlegroups.com:

> On Apr 2, 4:25 pm, Scott269 wrote:
>> So I've got a table full of orders.  I have a query that counts the
>> orders, no problem.
>>
>> Now I've also got a query that tells me if the order is due or not.
>> Its a little more complicated, but basically says
>> "Due:Iif(Due_date>Today,"No","Yes").  Now I want to count all the
>> orders in that query that are due.  I have Count_of_Due:
>> Count(Order_Due.Due) and it crashes.  I try adding a column in the
>> Order_Due table with an Number_Due: Iif(Due="Yes",1,0) to make a
>> number.  I tell it to count Number_Due.  WTF?  I try the same with t
> he
>> sum function, same result.  How do I make it count the number of times
>> a string appears in a query?
>
> This tends to work for me:
>
> Count(iif(Due="Yes",Due,null))

This could never be optimized. It's very bad form.

I hope you won't insulted and resentful; there might be a bright young
person reading this who could benefit from learning that COUNT(*) ... WHERE
is de rigeur for counting.

Re: Help with Sum/Counting a column in a Query

On Apr 2, 8:18=A0pm, lyle fairfield wrote:
> Guillermo_Lopez wrote in news:f3931884-754a-4e7c-817b-=

> 69553228e...@8g2000hsu.googlegroups.com:
>
>
>
>
>
> > On Apr 2, 4:25=A0pm, Scott269 wrote:
> >> So I've got a table full of orders. =A0I have a query that counts the
> >> orders, no problem.
>
> >> Now I've also got a query that tells me if the order is due or not.
> >> Its a little more complicated, but basically says
> >> "Due:Iif(Due_date>Today,"No","Yes"). =A0Now I want to count all the
> >> orders in that query that are due. =A0I have Count_of_Due:
> >> Count(Order_Due.Due) and it crashes. =A0I try adding a column in the
> >> Order_Due table with an Number_Due: Iif(Due=3D"Yes",1,0) to make a
> >> number. =A0I tell it to count Number_Due. =A0WTF? =A0I try the same wit=
h t
> > he
> >> sum function, same result. =A0How do I make it count the number of time=
s
> >> a string appears in a query?
>
> > This tends to work for me:
>
> > =A0Count(iif(Due=3D"Yes",Due,null))
>
> This could never be optimized. It's very bad form.
>
> I hope you won't insulted and resentful; there might be a bright young
> person reading this who could benefit from learning that COUNT(*) ... WHER=
E
> is de rigeur for counting.- Hide quoted text -
>
> - Show quoted text -

No resentment. I'm guessing Scott only needs to count the orders and
that's it. count(*) ... Where is the way to go.

still, i'd like to point out the usefullness of the Count(iif(...))
and why i disagree that it can never be optimized and its bad form.

I agree that Count(*) ... where is the best way to count anything.
However, it only works when you are particularly grouping and no other
agregate functions need to be estimated. Say if i want to count the
number of due's i have, Number of orders with dollar amount greater
than 100, Total sum of Orders that are due, and Total sum of all
Orders.

Obviously this might not be what Scott requieres but it validates my
point. by using count(*) ... where then you need 3 queries.
Count(iif(...)) and Sum(iif(...) works for me in one query.

- GL