Count criteria

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

Guest

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


Dale Fye said:
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.


Zb Kornecki said:
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
Click to expand...
Click to expand...
 
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

Zb Kornecki said:
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


Dale Fye said:
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.


Zb Kornecki said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top