Count criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Zb Kornecki - 24 Sep 2007 19:10 GMT

Hi I have table that has (among others) three date-time fields: Arrive,
Complete, Leave. I would like to create a query field that counts, for each
arrival, how many pt's are completed but waiting to leave. I did this w/ a
sum product line in Excel but haven't been able to port that to Access when
my DB out grew Excel.

Thank- you
Zb

Reply to this Message

Dale Fye - 24 Sep 2007 19:24 GMT

Your query might look something like:

SELECT Arrive, Count(*) as WaitingToLeave
FROM yourTable
WHERE [Complete] IS NOT NULL
AND [Leave] IS NULL
GROUP BY Arrive

HTH
Dale

Signature

Email address is not valid.
Please reply to newsgroup only.

> Hi I have table that has (among others) three date-time fields: Arrive,
> Complete, Leave. I would like to create a query field that counts, for each
[quoted text clipped - 4 lines]
> Thank- you
> Zb

Reply to this Message

Zb Kornecki - 26 Sep 2007 13:22 GMT

Dale since niether time can be null Would the following work?
TY
Zb

SELECT Arrive, Count(*) as WaitingToLeave
FROM yourTable
WHERE [Arrive] IS BETWEEN [Complete] AND [Leave]
GROUP BY WaitingToLeave

> Your query might look something like:
>
[quoted text clipped - 15 lines]
> > Thank- you
> > Zb

Reply to this Message

Dale Fye - 26 Sep 2007 22:36 GMT

Are you trying to count everyone that is waiting to leave?

If so, I would think the [leave] field would still be null, but you would
not need to include the Arrive in the Select or in the Group By.

It might look like:

SELECT Count(*) as WaitingToLeave
FROM yourTable
WHERE [Complete] IS NOT NULL
AND [Leave] IS NULL

HTH
Dale

> Dale since niether time can be null Would the following work?
> TY
[quoted text clipped - 27 lines]
>> > Thank- you
>> > Zb

Reply to this Message