Graphing time intervals in Access

Question

I have a table that looks like the following:

StudyId  StudyStartDateTime
============================
1        01/01/2009 08:45 AM
2        01/01/2009 08:53 AM
...

I'd like to return a query that contains the StudyId, as well as the ten-minute interval during which it was started:

StudyId   Interval
==================
1         8:40 AM
2         8:50 AM

This is because I'd like to create a graph displaying the distribution of the studies across ten minute intervals.

The intervals are every ten minutes between 8AM-9AM, and 1PM-2PM. I've gotten this far:

SELECT s.studyid,
       s.studystartdatetime,
       Iif(Format(s.studystartdatetime,'hh:nn:ss AM/PM') BETWEEN "08:00:00 AM" AND "08:09:59 AM",
           "8:00 - 8:10",0) AS ["8:00 - 8:10"],
       Iif(Format(s.studystartdatetime,'hh:nn:ss AM/PM') BETWEEN "08:10:00 AM" AND "08:19:59 AM",
           "8:10 - 8:20",0) AS ["8:10 - 8:20"],
       Iif(Format(s.studystartdatetime,'hh:nn:ss AM/PM') BETWEEN "08:20:00 AM" AND "08:29:59 AM",
           "8:20 - 8:30",0) AS ["8:20 - 8:30"],
       Iif(Format(s.studystartdatetime,'hh:nn:ss AM/PM') BETWEEN "08:30:00 AM" AND "08:39:59 AM",
           "8:30 - 8:40",0) AS ["8:30 - 8:40"],
       Iif(Format(s.studystartdatetime,'hh:nn:ss AM/PM') BETWEEN "08:40:00 AM" AND "08:49:59 AM",
           "8:40 - 8:50",0) AS ["8:40 - 8:50"],
       Iif(Format(s.studystartdatetime,'hh:nn:ss AM/PM') BETWEEN "08:50:00 AM" AND "08:59:59 AM",
           "8:50 - 9:00",0) AS ["8:50 - 9:00"],
       Iif(Format(s.studystartdatetime,'hh:nn:ss AM/PM') BETWEEN "01:00:00 PM" AND "01:09:59 PM",
           "1:00 - 1:10",0) AS ["1:00 - 1:10"],
       Iif(Format(s.studystartdatetime,'hh:nn:ss AM/PM') BETWEEN "01:10:00 PM" AND "01:19:59 PM",
           "1:10 - 1:20",0) AS ["1:10 - 1:20"],
       Iif(Format(s.studystartdatetime,'hh:nn:ss AM/PM') BETWEEN "01:20:00 PM" AND "01:29:59 PM",
           "1:20 - 1:30",0) AS ["1:20 - 1:30"],
       Iif(Format(s.studystartdatetime,'hh:nn:ss AM/PM') BETWEEN "01:30:00 PM" AND "01:39:59 PM",
           "1:30 - 1:40",0) AS ["1:30 - 1:40"],
       Iif(Format(s.studystartdatetime,'hh:nn:ss AM/PM') BETWEEN "01:40:00 PM" AND "01:49:59 PM",
           "1:40 - 1:50",0) AS ["1:40 - 1:50"],
       Iif(Format(s.studystartdatetime,'hh:nn:ss AM/PM') BETWEEN "01:50:00 PM" AND "01:59:59 PM",
           "1:50 - 2:00",0) AS ["1:50 - 2:00"]
FROM   dbo_study_viewx211_rpt AS s

Which results in something like this:

StudyId   StudyStartDateTime    8:00 - 8:10   8:10 - 8:20  ...
==================================================================
1         28/11/2007 8:15:22 AM           0   8:10 - 8:20    0   ...
...

How can I group all of the intervals into a single "Interval" column?

Answer 1

I think it would be easier to have your query just output the StudyStartDateTime,

SELECT s.studyid,       
       s.studystartdatetime,
FROM   dbo_study_viewx211_rpt AS s

then switch to "Pivot Table" view and use the grouping features of the pivot table to do your formatting and grouping.

Answer 2

See if it is easier to do what you want by creating a TimeIntervals table, with istart and iend as Date/Time data type.

istart     iend       ilabel
8:00:00 AM 8:09:59 AM 8:00-8:10
8:10:00 AM 8:19:59 AM 8:10-8:20
8:20:00 AM 8:29:59 AM 8:20-8:30
8:30:00 AM 8:39:59 AM 8:30-8:40
8:40:00 AM 8:49:59 AM 8:40-8:50
8:50:00 AM 8:59:59 AM 8:50-9:00
9:00:00 AM 9:09:59 AM 9:00-9:10

Then try a query like this:

SELECT
    s.StudyId
    , s.StudyStartDateTime
    , t.istart
    , t.iend
    , t.ilabel
FROM
    dbo_study_viewx211_rpt AS s
    , TimeIntervals AS t
WHERE
    TimeValue(s.StudyStartDateTime) Between t.istart And t.iend;

Sample output from query:

StudyId StudyStartDateTime  istart     iend       ilabel
1       1/1/2009 8:45:00 AM 8:40:00 AM 8:49:59 AM 8:40-8:50
2       1/1/2009 8:53:00 AM 8:50:00 AM 8:59:59 AM 8:50-9:00

You probably won't want the StudyStartDate, istart, and iend fields displayed in your query results. I left them in here to hopefully make it easier to understand how it works.