Count Queries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karen53 - 15 May 2005 17:47 GMT

Hello,

I have been going around and around with this. From my query I want to be
able to count the number of items recieved, the number shipped and the number
not shipped. One field is the item name received, the other a check box
indicating whether it has been shipped. I have been unable to get it to work
in one query. So, I tried separating them into their own query. The trouble
is, I can't combine them for a report. They all work fine individually, but
when I try to combine them into one query it multiplies the values. If I
link the Item names, it filters to the shipped items only. I would like to
be able to do it in one query. Is there a way? I tried various expressions
but they did not work. Any suggestions?

Thanks

Reply to this Message

Karen53 - 15 May 2005 19:53 GMT

I am getting a separate line item for each item depending on the shipped
checkbox being true or false, even though I am linked by item.

> Hello,
>
[quoted text clipped - 10 lines]
>
> Thanks

Reply to this Message

Dale Fye - 16 May 2005 03:09 GMT

Karen,

Can you give us an example of the field in your table, a couple of records
from that table, and what you would expect the results of the query to
return, given only the rows of data you give us?

Without knowing your table structure, but with your description below, I
would guess the query would look something like:

SELECT ItemReceived,
SUM(1) as Received,
SUM(ABS(Shipped)) as Shipped,
SUM(1-ABS(Shipped)) as NotShipped
From yourTable
GROUP BY ItemReceived

HTH
Dale

> Hello,
>
[quoted text clipped - 16 lines]
>
> Thanks

Reply to this Message