Count criteria

[Zb Kornecki]

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

 

[Dale Fye]

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

 

[Zb Kornecki]

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:

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

HTH
Dale

--
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
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

 

[Dale Fye]

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
Zb

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

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

--
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
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