Breaking Out 'TimeStamps & Total Duration' into 15 Minute Interval

[Data1234]

Hello. I am trying to figure out how to take a table of timestamps and activity duration and break it into intervals, where it groups the duration into the time spent in each 15 minute interval. I have included a sample of the data and output. Is there any way to pull this in Access (or SQL)?


Raw Data
Employee Activity TimeStampStart TimeStampEnd TotalDuration
Doe,John SomeActivity 12/16/13 9:06:02 AM 12/16/13 9:57:14 AM 0:51:12
Smith,Jane OtherActivity 12/16/13 9:22:15 AM 12/16/13 10:06:55 AM 0:44:40

Query to break out the total duration time in to the 15 minute interval it fell into
Employee Activity Interval IntervalDuration
Doe,John SomeActivity 12/16/13 9:00:00 AM 0:08:58
Doe,John SomeActivity 12/16/13 9:15:00 AM 0:15:00
Doe,John SomeActivity 12/16/13 9:30:00 AM 0:15:00
Doe,John SomeActivity 12/16/13 9:45:00 AM 0:12:14
Smith,Jane OtherActivity 12/16/13 9:15:00 AM 0:07:45
Smith,Jane OtherActivity 12/16/13 9:30:00 AM 0:15:00
Smith,Jane OtherActivity 12/16/13 9:45:00 AM 0:15:00
Smith,Jane OtherActivity 12/16/13 10:00:00 AM 0:06:55



Thanks!!!

 

[plog]

A query itself can't create records. So that leaves you 2 options to achieve this in Access:

1. Create a function that generates all the records you want. You would use VBA to read in each record from your initial table and then spit out new records to another table based on how many 15 minute periods each record occupies.

2. Create a Cartesian Product (http://en.wikipedia.org/wiki/Cartesian_product) with a table that contains all possible intervals. You would need to make a table with 96 records (1 for each 15 minute interval in a day). You would then bring that table and your data table into a query and not join them. You would create a calculated field to determine if the 15 minute interval table's data was between the data table's TimeStampStart and TimeStampEnd fields, if so, you show that record, if not, you don't.

 

[Mihail]

Welcome to the forum !

I think to one more solution (thanked to Geo... that helped me in other thread).
The solution is based on a table that store the time intervals:
00:00-00:15, 00:15-00:30, 00:30-00:45 ..... 23:45 - 24:00
A query based on this table + RawData table should select records in the time interval.

@Data1234
Is hard for my English to explain "en detail" what I think.
Give me a simple DB and I'll try to find a solution.

Use Access 2003 version.