Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    TrulyVisceral is offline Total N00B
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Deimos
    Posts
    102

    Smile Create a query that shows how many entries were entered at each hour.

    Something like this:
    Hour Incidents
    1 12
    2 32
    3 43
    4 16

    I asked something similar in reports, but I got nowhere. Maybe it was me not getting something super obvious (actually, it might as well be the most likely reason).

    The "Hour of entry" field is in the numbers that count from the year 1899.
    I tried putting the hour of entry in the first column of the query, I changed it's format property to display as Short Hour, so it gives me military hours.


    In the other post I made (in reports), I got told to Group By Hour... how? Sorry if this seems like a very dumb question, but I tend to miss extremely obvious stuf, or just forget things I used like minutes ago.
    And then sum the incidents field... well, there's Incident ID, do I sum that?

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Here is an explanation of the Group By Functionality in an Access query

    https://www.techonthenet.com/access/...umeric/sum.php

  3. #3
    TrulyVisceral is offline Total N00B
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Deimos
    Posts
    102
    Quote Originally Posted by alansidman View Post
    Here is an explanation of the Group By Functionality in an Access query

    https://www.techonthenet.com/access/...umeric/sum.php
    That's the SUM function, but thanks anyways. I'll check ORDER BY.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You need to use the count function instead: https://www.techonthenet.com/access/...eric/count.php
    When I said sum the incidents in the other thread, I meant Count!

    AND because I'm kind ... here's how you do the whole thing:

    Code:
    SELECT Hour([DateTimeField]) AS IncidentHour, Count(IncidentField) AS IncidentsFROM TableName
    GROUP BY Hour([DateTimeField]);
    Substitute your own field & table names
    NOTE: Don't use Hour as a query field name - it's a reserved word in Access
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    TrulyVisceral is offline Total N00B
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Deimos
    Posts
    102
    Click image for larger version. 

Name:	1.png 
Views:	13 
Size:	4.3 KB 
ID:	33532
    Click image for larger version. 

Name:	2.png 
Views:	13 
Size:	10.4 KB 
ID:	33533Welp. I guess "group by" is the thing I need?
    I got this as well https://stackoverflow.com/questions/...-by-10-minutes

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Looks like Colin is offline. You haven't wrapped your date/time field in the Hour() functions as he showed.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    TrulyVisceral is offline Total N00B
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Deimos
    Posts
    102
    He added that after my reply. I hadn't seen it.
    As for not using "hour" as field names, it's fine, I'm working in spanish. Not too much to worry about there.
    ALSO: Now I know his name is Colin

  8. #8
    TrulyVisceral is offline Total N00B
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Deimos
    Posts
    102
    Quote Originally Posted by pbaldy View Post
    You haven't wrapped your date/time field in the Hour() functions as he showed.
    Attachment 33534Cool. Almost all there. Now, it doesn't show those hours without entries, but I need it to. What then?

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    My first thought is creating a table with records for hours 0-23, and join to that table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi
    Yes I added the query SQL after posting - the clue to my name is in the signature line!
    Even if working in Spanish, the reserved words shouldn't be used as they are part of Access functions etc
    If you mean you're using 'Hora' or 'Horas' then of course that's fine.

    One way of getting the hours with no incidents is as follows

    Save the first query I gave you as qryCountIncidentsByHour or similar
    Create a table tblHours with one field HourValue (integer) and populate it with values 0 through to ....23

    Now create a second query using both of the above with an outer join

    Code:
    SELECT tblHours.HourValue AS IncidentHour, Nz([Incidents],0) AS TotalIncidents FROM tblHours LEFT JOIN qryCountIncidentsByHour ON tblHours.HourValue = qryCountIncidentsByHour.IncidentHour;
    The Nz function will give a value 0 where there are no incidents
    QED! HTH! BOL!

    EDIT: Hadn't seen Paul's reply but great minds think alike as that's what the above does
    Last edited by isladogs; 04-12-2018 at 11:52 AM. Reason: Fixed missing space in code
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    TrulyVisceral is offline Total N00B
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Deimos
    Posts
    102
    Quote Originally Posted by ridders52 View Post
    "Really helpful stuff I keep misunderstanding"
    God, working with 2 different languages is messing me up. Getting syntax error.

    TBLTNum is where the 0-23 are, saved as HNum.
    the first query is QRYHoraEntrada, where "Hora" (from column "Hora de entrada") and "Cantidad" (from column "ID de incidente") are taken from the table DCEne2018. The raw data I'm working with.

    Thus:
    Code:
    SELECT TBLTNum.HNum AS IncidentHour, Nz([ID de incidente],0) AS TotalIncidents FROM DCEne2018 LEFT JOIN QRYHoraEntrada ON TBLTNum.HNum = QRYHoraEntrada.Hora;
    From:
    Code:
    SELECT tblHours.HourValue AS IncidentHour, Nz([Incidents],0) AS TotalIncidentsFROM tblHours LEFT JOIN qryCountIncidentsByHour ON tblHours.HourValue = qryCountIncidentsByHour.IncidentHour;
    Is that FROM after TotalIncidents a different word? Or not?

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I don't recall saying anything like that quote!

    Yes. It's a separate word. Sometimes happens when code posted to forum
    I'll correct the previous post
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    TrulyVisceral is offline Total N00B
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Deimos
    Posts
    102
    Code:
    SELECT TBLTNum.HNum AS Hora, Nz([Cantidad],0) AS TotalFROM TBLTNum LEFT JOIN QRYHoraEntrada ON TBLTNum.HNum = QRYHoraEntrada.Hora;
    Click image for larger version. 

Name:	1.png 
Views:	11 
Size:	9.1 KB 
ID:	33539
    Got it!
    AND YET THE PROBLEMS DON'T STOP
    Because I'm supposed to use these in a line graph. But apparently "Total" isn't formated as a number field. So when I try to do this:Click image for larger version. 

Name:	4.png 
Views:	11 
Size:	4.7 KB 
ID:	33540Then It's automatically set as "Count" and can't change because "It's not a number field".
    Last edited by TrulyVisceral; 04-12-2018 at 12:47 PM. Reason: Grammar

  14. #14
    TrulyVisceral is offline Total N00B
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Deimos
    Posts
    102
    Alright. Since the main Query problem was solved, should I move the next problem in the "Report" Thread?

    Well, for the time being, I did this:
    Click image for larger version. 

Name:	3.png 
Views:	10 
Size:	7.0 KB 
ID:	33541
    It doesn't change the data, and it lets me work with the graph. Hopefully it doesn't break the entire DB.

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You could try this in the query:

    CLng(Nz([Cantidad],0))
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Trying to Create a Query that shows Percentages
    By KingOf206 in forum Queries
    Replies: 4
    Last Post: 10-07-2016, 09:10 AM
  2. Replies: 9
    Last Post: 03-15-2015, 10:37 PM
  3. Replies: 1
    Last Post: 10-20-2013, 10:04 AM
  4. Replies: 12
    Last Post: 12-17-2010, 05:35 PM
  5. create a query to find names not entered
    By tomClark in forum Queries
    Replies: 4
    Last Post: 02-24-2010, 10:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums