Grouping and counting records every 10 minutes

[MaHa]

In table, I have approx. 3000 lines with columns e.g. NAME, ARRIVAL_DATETIME and DEPARTURE_DATETIME. Data is added daily into this table.



NAME ARRIVAL_DATETIME DEPARTURE_DATETIME
Person 1 1/1/2011 0:23:12 AM 1/1/2011 5:30:12 AM
Person 2 1/1/2011 0:40:16 AM 2/1/2011 4:30:12 PM
Person 3 1/1/2011 2:34:16 AM 1/1/2011 3:21:09 PM
…
How I can count how many persons is present during in given timeframe in 10 minute intervals?
Timeframe is usually “yesterday”, “previous 7 days”, previous 14 days” and “previous 30 days”


e.g.
NAME PERSONS

1/1/2011 0:05 AM 6

1/1/2011 0:10 AM 6
1/1/2011 0:15 AM 6

1/1/2011 0:20 AM 6
1/1/2011 0:25 AM 7

1/1/2011 0:30 AM 7

1/1/2011 0:35 AM 7

1/1/2011 0:40 AM 7


Thanks for your help.

[maximus]

Tried to replicate you problem and this is what I have done.

I have a table (Table5)with the following Fields:
1) ID (PK Auto Number)
2) Arrival_Time (Date/Time)
3)Time_ Departure (Date/Time)

Assumptions:

1) A person will be considered to be departed if the Time_Departure is Not Null. e.g. if departure time is entered the person is considered to have left.

What Have I Done:

I have a Simple Form data Source is Table5. I have added three Text Box controls:

1) Text0
2) Text2
3) Text6


I have attached a code To the Open Event of the form which sets the value of Text0 with Current Time. I have then attached a Code to the Timer event.

Me.Text2 = Format(Time, "hh:mm:ss AMPM") This line of the code is not essential and I have used it to show the current time being updated. You can omit this line and not keep Text2 on your form.

The main aim is to create a interval of 10 minutes. Therefore when you open form the initialtime=Me.text0 which is the time at which the form is opened.

timediff is the difference between initial time and current time. When the difference = 10 the code updates the initial time with current time making the difference 0. In this way a continuous time period of 10 minutes interval is created.

When timediff=10 the procedure Total_present is called. This procedure uses a DAO.Recordset loops through Table5. A counter variable intCounter is introduced and +1 is done to it when ever the Do While Loop encounters the condition isNull(rst!Time_Departure) the result is then used to update Text6. This is done at an interval of 10 minutes. The codes used as follows:

Private Sub Form_Open(Cancel As Integer)
Me.Text0 = Format(Now, "hh:mm:ss AMPM")
End Sub

Private Sub Form_Timer()
Dim timeDiff As Integer
Dim initialTime As Date

Me.Text2 = Format(Time, "hh:mm:ss AMPM")

initialTime = Format(Me.Text0, "hh:mm:ss AMPM")
timeDiff = DateDiff("n", initialTime, Format(Now, "hh:mm:ss AMPM"))

If timeDiff = 1 Then
    initialTime = Format(Now, "hh:mm:ss AMPM")
    Me.Text0 = initialTime
    Call Total_Present
End If
End Sub

Private Sub Total_Present()
Dim rst As DAO.Recordset
Dim intCounter As Integer

Set rst = CurrentDb.OpenRecordset("Table5")
Do While Not rst.EOF
    If IsNull(rst!Time_Departure) Then
        intCounter = intCounter + 1
    End If
rst.MoveNext
Loop
Set rst = Nothing

Me.Text6 = intCounter
intCounter = 0
End Sub

[MaHa]

Excellent. Thanks alot fot the help.
This solved one of my many problems. Other problem related to this is, from same data, I am trying to extract with query to excel pivot table for others so they can tap to my database.

As I am looking history, how many were present at that time (e.g. 01:05 am,) I think I have to see how many perdons are with Arrival time < 01:05 am and Departure time is > 01:05 am. This list should be in 5-10 minute intervals. from 0000 -> 23.55.

Is there possibility to have query/table that is accessible from excel that I can set date/time range in excel and it gives me a table:
example

DateTime --- Persons
21/08/2011 00:00 AM --- 6
21/08/2011 00:10 AM ---  5
21/08/2011 00:20 AM ---  7
21/08/2011 00:30 AM ---  7
21/08/2011 00:40 AM ---  5
21/08/2011 00:50 AM ---  4
21/08/2011 01:00 AM ---  8
21/08/2011 01:10 AM ---  7
...,...
21/08/2011 23:50 AM ---  4

From there I and my colleagues can draw pretty graphs easily.

Thanks again for the help

Marko

(I couldn't get columns working for this table... embarrasing)

[MaHa]

What I did, I am counting with DCount all persons, who arrived before row's arrival time and departed after row's arrival time. This way I get everyone who are present. and able to pull number to excel pivot table.


This seems to be really inefficent way to calculate and for some days, I am having 0 as a result. I know that 0 is wrong result.
Is there more efficent way to do this kind of calculation?