Grouping by people already in the waiting room

[Zb Kornecki]

I would like to run a select query that groups the results by the number of
people already waiting. this could be done based on either 2 or 3 columns.

[arrival] (a D/T field), [depart] (a D/T field), and [Length_of Stay] in
minutes (a double data type field)

what I would like ideally is a calculated column that returns an number for
the total people waiting. I could then group or pivot table w/ this number.
In Excel I could do a sumproduct formula but my table out grew Excel. Is
there a way to do the same thing within access


A sample table
arrive,depart,LoS,#waiting
2100,2145,45,0
2110, 2150,40,1
2130,2150,20,2
2147,2155,8,2 (1st person left)
2150,2200,10,1(persons 1,2 & 3 left)

I hope this makes sense

Thank you

 

[Allen Browne]

Your table should not have fields for LoS and #waiting.

Use a query to calculate the length of stay:
DateDiff("n", [arrive], [depart])
Presumably depart is blank if they are still waiting, so you might want to
use:
DateDiff("n", [arrive], Nz([depart], Now())

The number of people waiting would be given by:
DCount("*", "Table1", "[depart] Is Null)

I'm not sure what you want for #waiting on each line: is this the number of
people waiting at the instant the person arrived? At the time They left? The
highest number waiting at any time during their wait?

You could use a subquery to get the number of people who were waiting at the
moment the person arrived by typing an expression like this into the Field
row in query design:
(SELECT Count(arrive) AS HowMany
FROM Table1 AS Dupe
WHERE Dupe.arrive < Table1.arrive
AND (Dupe.depart Is Null OR Dupe.depart > Table1.arrive))

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[Zb Kornecki]

(select count ([FC_Time_Stamps].[Arrival]) As HowMany
from FC_Time_Stamps as Dupe
where Dupe.Arrival < FC_Time_Stamps.[Arrival]
and Dupe.To Room > FC_Time_Stamps.[Arrival])
FROM FC_Time_Stamps
GROUP BY FC_Time_Stamps.[Pt Accnt];

Your table should not have fields for LoS and #waiting.

Use a query to calculate the length of stay:
DateDiff("n", [arrive], [depart])
Presumably depart is blank if they are still waiting, so you might want to
use:
DateDiff("n", [arrive], Nz([depart], Now())

The number of people waiting would be given by:
DCount("*", "Table1", "[depart] Is Null)

I'm not sure what you want for #waiting on each line: is this the number of
people waiting at the instant the person arrived? At the time They left? The
highest number waiting at any time during their wait?

You could use a subquery to get the number of people who were waiting at the
moment the person arrived by typing an expression like this into the Field
row in query design:
(SELECT Count(arrive) AS HowMany
FROM Table1 AS Dupe
WHERE Dupe.arrive < Table1.arrive
AND (Dupe.depart Is Null OR Dupe.depart > Table1.arrive))

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I would like to run a select query that groups the results by the number of
people already waiting. this could be done based on either 2 or 3
columns.

[arrival] (a D/T field), [depart] (a D/T field), and [Length_of Stay] in
minutes (a double data type field)

what I would like ideally is a calculated column that returns an number
for
the total people waiting. I could then group or pivot table w/ this
number.
In Excel I could do a sumproduct formula but my table out grew Excel. Is
there a way to do the same thing within access


A sample table
arrive,depart,LoS,#waiting
2100,2145,45,0
2110, 2150,40,1
2130,2150,20,2
2147,2155,8,2 (1st person left)
2150,2200,10,1(persons 1,2 & 3 left)

I hope this makes sense

Thank you