Databases
StackOverflow2013
Postsdbo
POSelecting Time ranges from Date Time fields in Access
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelecting Time ranges from Date Time fields in Access
    primarykey
    data
    text
    <p>I have a table containing reports and the date/time they were created. I'd like to create a graph to tally the number of reports created during intervals of ten minutes between two time periods: 8:00AM-9:00AM and 1:00PM-2:00PM.</p> <p>Here's an example of a query I'd like to run:</p> <pre><code>SELECT s.StudyStartDateTime AS "8:00 - 8:10", s.StudyStartDateTime AS "8:10 - 8:20", s.StudyStartDateTime AS "8:20 - 8:30", s.StudyStartDateTime AS "8:30 - 8:40", s.StudyStartDateTime AS "8:40 - 8:50", s.StudyStartDateTime AS "8:50 - 9:00", s.StudyStartDateTime AS "1:00 - 1:10", s.StudyStartDateTime AS "1:10 - 1:20", s.StudyStartDateTime AS "1:20 - 1:30", s.StudyStartDateTime AS "1:30 - 1:40", s.StudyStartDateTime AS "1:40 - 1:50", s.StudyStartDateTime AS "1:50 - 2:00" FROM dbo_Study_ViewX211_Rpt AS s WHERE FORMAT("8:00 - 8:10", 'hh:mm:ss') BETWEEN "08:00:00" AND "08:09:59" AND FORMAT("8:10 - 8:20", 'hh:mm:ss') BETWEEN "08:10:00" AND "08:19:59" AND FORMAT("8:20 - 8:30", 'hh:mm:ss') BETWEEN "08:20:00" AND "08:29:59" AND FORMAT("8:30 - 8:40", 'hh:mm:ss') BETWEEN "08:30:00" AND "08:39:59" AND FORMAT("8:40 - 8:50", 'hh:mm:ss') BETWEEN "08:40:00" AND "08:49:59" AND FORMAT("8:50 - 9:00", 'hh:mm:ss') BETWEEN "08:50:00" AND "08:59:59" AND FORMAT("1:00 - 1:10", 'hh:mm:ss') BETWEEN "01:00:00" AND "01:09:59" AND FORMAT("1:10 - 1:20", 'hh:mm:ss') BETWEEN "01:10:00" AND "01:19:59" AND FORMAT("1:20 - 1:30", 'hh:mm:ss') BETWEEN "01:20:00" AND "01:29:59" AND FORMAT("1:30 - 1:40", 'hh:mm:ss') BETWEEN "01:30:00" AND "01:39:59" AND FORMAT("1:40 - 1:50", 'hh:mm:ss') BETWEEN "01:40:00" AND "01:49:59" AND FORMAT("1:50 - 2:00", 'hh:mm:ss') BETWEEN "01:50:00" AND "01:59:59" </code></pre> <p>Unfortunately, it seems that Access isn't able to tell that the field I'd like to format within the FORMAT function refers to the field of the same name in the query. In the above case, I assume this is because Access is treating this as a string literal. However, this doesn't work, either:</p> <pre><code>SELECT s.StudyStartDateTime AS first FROM dbo_Study_ViewX211_Rpt AS s WHERE FORMAT(first, 'hh:mm:ss') BETWEEN "08:00:00" AND "08:09:59" </code></pre> <p>Access is prompting me for the value for 'first'.</p> <p>I feel like I'm going about this the wrong way to begin with. Can anyone point me in the right direction?</p> <p>More info: I'm using the Jet engine through ODBC, but the data is actually stored in SQL Server 2005, so if necessary I suppose I could write a pass-through query. Ideally, however, I'd like it to work in Access.</p> <p>EDIT:</p> <p>To be clear, the following DOES work:</p> <pre><code>SELECT s.StudyStartDateTime AS first FROM dbo_Study_ViewX211_Rpt AS s WHERE FORMAT(StudyStartDateTime,'hh:mm:ss') Between "08:00:00" And "08:09:59"; </code></pre> <p>This, however, doesn't allow me to select the same field multiple times under multiple conditions.</p> <p>The first example at <a href="http://office.microsoft.com/en-us/access/HA012288391033.aspx#examples" rel="nofollow noreferrer">http://office.microsoft.com/en-us/access/HA012288391033.aspx#examples</a> suggests that wrapping the alias of the field in square braces is the correct way to reference a record source, but the following still results in a prompt for the value of 'first':</p> <pre><code>SELECT s.StudyStartDateTime AS first FROM dbo_Study_ViewX211_Rpt AS s WHERE FORMAT([first],'hh:mm:ss') Between "08:00:00" And "08:09:59"; </code></pre> <p>Edit II:</p> <p>An example of the contents of StudyStartDateTime of a few records:</p> <pre><code>29/11/2007 8:06:00 AM 30/11/2007 8:09:01 AM 05/12/2007 8:06:51 AM 06/12/2007 8:07:21 AM </code></pre> <p>Access sees this field as a "Date/Time" datatype.</p> <p>My end goal is to graph the tally for each interval. I think the easiest way of doing this would be to simply select each date/time as a different alias. For instance:</p> <pre><code>8:00-8:10 8:10-8:20 8:20:8:30 ... ======================================= 8:01 8:03 8:15 8:23 8:28 8:28 ... </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRichard
    1. USRichard
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COHave you looked into PARTITION()?
      singulars
      1. POSelecting Time ranges from Date Time fields in Access
      1. USDavid-W-Fenton
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload