?
Solved

MS Access get count of incidents each hour of day

Posted on 2016-09-03
6
Medium Priority
?
50 Views
Last Modified: 2016-09-03
Dear Experts,

Okay...need some help with this one. I am trying to graph the number of incidents within each hour.

The image below shows my query as it is now. I can group the times but what the users want to see is what time during the day do more incidents occur. For example between 9:00am and 10:00am there were eight incidents and between 10:00am and 11:00am there were 3 incidents. I only need to track from 7:00am until 4:00pm each day.
screenshotscreenshot1Is this possible if and so how do I set my query up?

Thanks!
0
Comment
Question by:shogun5
  • 3
  • 2
6 Comments
 

Author Comment

by:shogun5
ID: 41782855
...and when I add the date and sort....
screenshot2
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 41782927
how about something like:

SELECT stdID, Hour(stdBehaviorTime) as BehHour, Count(ID) as Incidents
FROM tblStudentBehaviors
WHERE Hour(stdBehaviorTime) >= 7 and
AND Hour(stdBehaviorTime) < 16
AND stdID = "12345"
GROUP BY stdID, Hour(stdBehaviorTime)
0
 

Author Comment

by:shogun5
ID: 41782930
Thanks Dale but I am getting this....
screenshotDon't exactly know where the syntax error is....
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 53

Expert Comment

by:Gustav Brock
ID: 41782943
Don't exactly know where the syntax error is....

Are you serious? Read the error message again ("and" again if needed).

/gustav
0
 

Author Closing Comment

by:shogun5
ID: 41782944
Nevermind..found the extra .and.  Thanks again Dale! Worked great!
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 41783077
thanks, Gustav, for the assist.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question