Question on Count in Date Range

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • rhonda6373
    New Member
    • Mar 2010
    • 35
    #1

    Question on Count in Date Range

    I am attempting to first calculate the number of days between a date and todays which I have accomplished: TimeElapsed: DateDiff("d",[DtDiscovered],Date()).

    I then need to count the number of records that have a calculated number of days (TimeElapsed) above as follows in one query:

    between 1-10, between 11-20, 21-30, 30-60, and over 60.

    How can I accomplish this?
    Tags: None
    • ADezii
      Recognized Expert Expert
      • Apr 2006
      • 8834
      #2
      Originally posted by rhonda6373
      Hello,

      I am attempting to first calculate the number of days between a date and todays which I have accomplished: TimeElapsed: DateDiff("d",[DtDiscovered],Date()).

      I then need to count the number of records that have a calculated number of days (TimeElapsed) above as follows in one query:

      between 1-10, between 11-20, 21-30, 30-60, and over 60.

      How can I accomplish this?

      Thanks for the help in advance!
      This can be done by using what is called a Frequency Distribution. Give me a little time, and I'll pu together a simple demo for you.

        Comment

        • rhonda6373
          New Member
          • Mar 2010
          • 35
          #3
          Originally posted by ADezii
          This can be done by using what is called a Frequency Distribution. Give me a little time, and I'll pu together a simple demo for you.
          Great, thank you!

            Comment

            • ADezii
              Recognized Expert Expert
              • Apr 2006
              • 8834
              #4
              Originally posted by rhonda6373
              Great, thank you!
              Rather than explain how a Frequency Distribution works, just download the Demo Attachment that I have created for you. Any questions, feel free to ask.
              Attached Files

                Comment

                • fdfjc
                  New Member
                  • May 2010
                  • 8
                  #5
                  Originally posted by rhonda6373
                  I am attempting to first calculate the number of days between a date and todays which I have accomplished: TimeElapsed: DateDiff("d",[DtDiscovered],Date()).

                  I then need to count the number of records that have a calculated number of days (TimeElapsed) above as follows in one query:

                  between 1-10, between 11-20, 21-30, 30-60, and over 60.

                  How can I accomplish this?
                  Hi Rhonda, Access makes it easy to do this in steps. In the first query you have figured the TimeElapsed. In the second query, you can use something like this: IIf([TimeElapsed] Between 29 And 59, 1 , 0) AS 30to60, IIf([TimeElapsed] Between 61 And 89, 1 , 0) AS 60to90 etc. Third query sums up each time period and gives you the total count. You call the last query in your code. I'm not great at explaining, so let me know if this isn't clear.
                  Brenda

                    Comment

                    • rhonda6373
                      New Member
                      • Mar 2010
                      • 35
                      #6
                      The Frequency Distribution worked great! Thank you so much! We have been working very long hours and this was something a director asked me for yesterday and I did not have the time or the brainpower to figure it out. This allowed me to create the report that was needed.

                      Brenda, thank you as well! I just now saw your post and will note this solution for future reference as well.

                        Comment

                        Previous template Next
                        Working...