Group data based on Time in 15 minute interals 00, 15, 30, 45

[Evette]

I need to create a report that will let me group data based a time interval.

The result will look like this:

From To MRI CAT US Total
8:00 8:14 2 4 1 7
8:15 8:29 1 1 3 5
8:30 8:44 3 2 3 8
...and so on

I want a count by time by study.

Thanks

Evette

 

[grnzbra]

Create 3 calculated fields.

Hours: Hour(Time)
MinLow: IIf(Minute(Time)<15,&quot;00&quot;,IIf(Minute(Time)<30,&quot;15&quot;,IIf(Minute(Time)<45,&quot;30&quot;,&quot;45&quot))
MinHigh: IIf(Minute(Time)>45,&quot;59&quot;,IIf(Minute(Time)>30,&quot;44&quot;,IIf(Minute(Time)>15,&quot;29&quot;,&quot;14&quot))

 

[MichaelRed]

The classic process to solve this would be to create a timeslot calculated value and 'categorize' the time stamps in the intervals.

A somewhat minimalist response to your specific request might include something like:

Public Function basTimeSlot(MyTime As Date) As Integer

    'To Categorize times to fall within intervals
    'Minimize to only reflect the single discrete interval of 15 Minutes
    'Tek-tips thread181-363503
    'Michael Red 9/18/2002


    Dim MySlot As Integer
    Const MyInt = 1 / 96        '96 quarter haurs

    'Remove the Days part of the date
    MyTimeOfDay = CDbl(MyTime) - Fix(MyTime)

    MySlot = Int(MyTimeOfDay / MyInt)
    
    basTimeSlot = MySlot
End Function

Of course, even the minimalist will recognize (and correct) the ommission of error trapping.

The generalist would naturally elaborate the procedure to include at least the capablity to send the procedure information on the interval size / step (in addition to the error stuff). Other elaborations might include restricting the slots to certain (working?) hours.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[cmmrfrds]

The Partition function should do what you want. Here is a small blurb from the Access help. Also, you will probably need to use one of the datetime functions to reference your date. i.e. TimeSerial() or DatePart().

Partition Function Example
This example assumes you have an Orders table that contains a Freight field. It creates a select procedure that counts the number of orders for which freight cost falls into each of several ranges. The Partition function is used first to establish these ranges, then the SQL Count function counts the number of orders in each range. In this example, the arguments to the Partition function are start = 0, stop = 500, interval = 50. The first range would therefore be 0:49, and so on up to 500.

SELECT DISTINCTROW Partition([freight],0, 500, 50) AS Range,
Count(Orders.Freight) AS Count
FROM Orders
GROUP BY Partition([freight],0,500,50);

 

[cmmrfrds]

Here is an example using the datepart function to extract/group the time in 15 minute intervals.

SELECT DISTINCTROW Partition(datepart('n',dbo_Invoices.orderdate),0, 60, 15) AS Range,
Count(dbo_Invoices.orderdate) AS [Count]
FROM dbo_Invoices
GROUP BY Partition(datepart('n',dbo_Invoices.orderdate),0,60,15);