Date\Time to hourly slots (1 Viewer)

sightsound

New member
Local time
Today, 10:36
Joined
Jul 3, 2019
Messages
4
Hi All,

I’m back again with another question, this time in regard to sorting Date\Time date in a table.


I have a table which logs when jobs were added Date\Time and I’m trying to break this down into a chart of hourly slots, to see which the busiest time period over the year.


In the chart the date range needs to be the whole of the year, but broken down to the time.


The data in the table is stored as below.

08/07/2019 08:10:00
08/07/2019 14:15:00
08/07/2019 17:10:00
09/07/2019 08:56:00
09/07/2019 17:56:00


What I’m trying to do is have the chart dispaly like the bad example below.
I have created a Query, but unsure of the criteria.


__2____ 0_____ 0_____0_____1_____1________1
<9am __9am__10am__11am__2pm__5pm__ > 5:30pm


many thanks
James
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:36
Joined
Oct 29, 2018
Messages
21,357
Hi. Have you tried adding a calculated column in your query using either the Hour() or Format() function?
 

Mark_

Longboard on the internet
Local time
Today, 03:36
Joined
Sep 12, 2017
Messages
2,111
I have a table which logs when jobs were added Date\Time and I’m trying to break this down into a chart of hourly slots, to see which the busiest time period over the year.

I have had to do this in the past. How many records are you looking at? Will job type also need to be included? What are the rules for "Hour" as you show "Before 9am"?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:36
Joined
May 7, 2009
Messages
19,169
Code:
SELECT Distinct (Select Top 1 Count("1") From [yourTable] As T1 Where DatePart("h", T1.yourDateField) < 9) As [<9 am], 
(Select Top 1 Count("1") From [yourTable] As T1 Where DatePart("h", T1.yourDateField) = 9) As [9 am],  
(Select Top 1 Count("1") From [yourTable] As T1 Where DatePart("h", T1.yourDateField) = 10) As [10 am], 
(Select Top 1 Count("1") From [yourTable] As T1 Where DatePart("h", T1.yourDateField)  = 11) As [11 am], 
(Select Top 1 Count("1") From [yourTable] As T1 Where DatePart("h", T1.yourDateField)  = 14) As [2 pm], 
(Select Top 1 Count("1") From [yourTable] As T1 Where  Format(T1.yourDateField, "hh:nn")  Between "17:00" And "17:30") As [5 pm], 
(Select Top 1 Count("1") From [yourTable] As T1 Where Format(T1.yourDateField, "hh:nn")  > "17:30") As [>5:30 pm] 
FROM [yourTable]
replace yourTable with correct table name and yourDateField with correct date/time fieldname.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 10:36
Joined
Jan 14, 2017
Messages
18,186
I believe this simple query will do what you want
I've assumed you have an ID field and the datetime field is called EventDateTime

Code:
SELECT Count(TableName.ID) AS TotalCount, Hour([EventDateTime]) AS EventHour
FROM TableName
GROUP BY Hour([EventDateTime]);

Use the query as the source for your chart

See simple example attached
 

Attachments

  • HourlyCountTest.zip
    38.3 KB · Views: 153
Last edited:

sightsound

New member
Local time
Today, 10:36
Joined
Jul 3, 2019
Messages
4
Thanks to All for helping.


I haven't had time to try all the expamles as yet, However the one by isladogs works a treat. Many thanks for attaching the example this really helped as i'm new to access.


great forum, thats two for two problems i was having fixed in super quick time.


once again thanks for all the help.



I believe this simple query will do what you want
I've assumed you have an ID field and the datetime field is called EventDateTime

Code:
SELECT Count(TableName.ID) AS TotalCount, Hour([EventDateTime]) AS EventHour
FROM TableName
GROUP BY Hour([EventDateTime]);
Use the query as the source for your chart

See simple example attached
 

Users who are viewing this thread

Top Bottom