Results 1 to 7 of 7
  1. #1
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94

    Count number of records <=4 hours

    Hello!


    I am trying to count the number of records where the "BusinessHours" field equals a certain timeframe. My timeframes are <=4 hours, Between 4:01 And 5 hours, Between 5:01 And 10 Hours and >10 Hours.

    Something is off and I am counting too many records. For example, I have 178 <=4 but I am calculating 188. The field is formatted in the table as Date/Time - Short Time.

    Here is my current SQL
    Code:
    SELECT Count([tbl_HomeOwner Claims].Claim) AS CountOfClaim, Count(IIf([tbl_HomeOwner Claims]![BusinessHours]<=#12/30/1899 4:0:0#,[Claim],Null)) AS [Contact <4 Hours], [Contact <4 Hours]/[CountOfClaim] AS [Percent < 4 Hours], Count(IIf([tbl_HomeOwner Claims]![BusinessHours] Between #12/30/1899 4:1:0# And #12/30/1899 5:0:0#,[Claim],Null)) AS [Contact Between 4-5 Hours], [Contact Between 4-5 Hours]/[CountOfClaim] AS [Percent Between 4-5 Hours], Count(IIf([tbl_HomeOwner Claims]![BusinessHours] Between #12/30/1899 5:1:0# And #12/30/1899 10:0:0#,[Claim],Null)) AS [Contact Between 5-10 Hours], [Contact Between 5-10 Hours]/[CountOfClaim] AS [Percent Between 5-10 Hours], Count(IIf([tbl_HomeOwner Claims]![BusinessHours]>#12/30/1899 10:0:0#,[Claim],Null)) AS [Contact >10 Hours], [Contact >10 Hours]/[CountOfClaim] AS [Percent >10 Hours], Count(IIf([tbl_HomeOwner Claims]![DaysReportedToClosed] Is Null,[Claim],Null)) AS [Open Claims]
    FROM [tbl_HomeOwner Claims];
    
    Thank you for whatever help you can offer!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    1. dont use time/date for get an integer sum of an hour.

    2. use 2 queries:
    Q1: sum the BusHrs (as TotalHrs) for all
    Q2: use Q1 & the master table ,join, to get those w the TotalHrs<4

  3. #3
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Sorry Im a little confused.
    For point 1 - I need to change the format in the table from Date/Time to something else? I am not actually summing the hours, the hours are already provided in the table.

    This query is counting the number of records that fall into each timeframe and then it also gives a percentage for each timeframe against the overall total records.

  4. #4
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you try this:

    SELECT Count([tbl_HomeOwner Claims].Claim) AS CountOfClaim,sum(IIf([tbl_HomeOwner Claims]![BusinessHours]<=#12/30/1899 4:0:0#,1,0)) AS [Contact <4 Hours], [Contact <4 Hours]/[CountOfClaim] AS [Percent < 4 Hours], sum(IIf([tbl_HomeOwner Claims]![BusinessHours] Between #12/30/1899 4:1:0# And #12/30/1899 5:0:0#,1,0)) AS [Contact Between 4-5 Hours], [Contact Between 4-5 Hours]/[CountOfClaim] AS [Percent Between 4-5 Hours], Sum(IIf([tbl_HomeOwner Claims]![BusinessHours] Between #12/30/1899 5:1:0# And #12/30/1899 10:0:0#,1,0)) AS [Contact Between 5-10 Hours], [Contact Between 5-10 Hours]/[CountOfClaim] AS [Percent Between 5-10 Hours], Sum(IIf([tbl_HomeOwner Claims]![BusinessHours]>#12/30/1899 10:0:0#,1,0)) AS [Contact >10 Hours], [Contact >10 Hours]/[CountOfClaim] AS [Percent >10 Hours], Sum(IIf([tbl_HomeOwner Claims]![DaysReportedToClosed] Is Null,1,0)) AS [Open Claims]
    FROM [tbl_HomeOwner Claims];

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Thank you Gicu - I tried this and I get the same result.
    The results I get are:
    Count of <= 4 Hours = 188 and Percent 75.2
    Count of Between 4-5 hours = 18 and Percent 7.20 (This one is actually accurate)
    Count of Between 5-10 hours = 85 and percent 34
    Count of >10 hours = 126 and Percent 50.40

    Apart from the c
    ount of Between 4-5 hours, all the rest are counting too many.

  6. #6
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you upload a small sample with some data?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    My timeframes are <=4 hours, Between 4:01 And 5 hours, Between 5:01 And 10 Hours and >10 Hours.
    to start with, your time frames are wrong. you have no option for a time between 4:00 and 4:01, or for a time between 5:00 and 5:01.

    so, the first thing is to amend your time frames so that every possible time is covered.

    1. <= 4:00
    2. > 4:00 And <= 5:00
    3. > 5:00 And <= 10:00
    4. > 10:00

    when you've fixed that you might find it gives correct results.


    good luck with your project.



    Cottonshirt

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Count number of records
    By udigold1 in forum Queries
    Replies: 5
    Last Post: 02-28-2018, 08:37 AM
  2. Replies: 2
    Last Post: 02-19-2013, 10:36 AM
  3. Replies: 1
    Last Post: 01-24-2013, 05:50 PM
  4. Replies: 6
    Last Post: 07-25-2011, 01:54 PM
  5. count the number of records from a query
    By Nixx1401 in forum Queries
    Replies: 4
    Last Post: 05-24-2011, 06:45 PM

Tags for this Thread

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