Date\Time to hourly slots

[sightsound]

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]

Hi. Have you tried adding a calculated column in your query using either the Hour() or Format() function?

 

[Mark_]

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]

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.

 

[isladogs]

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

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

 

[sightsound]

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

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

 

[isladogs]

You're welcome. Glad it worked for you