If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Grouping by people already in the waiting room
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 |
#2
|
|||
|
|||
Grouping by people already in the waiting room
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Zb Kornecki" zDOTkorneckiATcomcastDOTnet wrote in message ... 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 |
#3
|
|||
|
|||
Grouping by people already in the waiting room
(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]; "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Zb Kornecki" zDOTkorneckiATcomcastDOTnet wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|