5 Replies Latest reply on Jan 8, 2014 5:11 PM by cdcreek

    Count by Month?

    cdcreek

      I have a single table DB and want to create a report that counts the reords by month and by week within the month. Is there a custom function available for this?

      Any other suggestions appreciated.

        • 1. Re: Count by Month?
          BobGossom

          Hi,

           

          The month is pretty easy. Create a calc that concatenates the year and the month. I like to pad a leading zero on the month so it sorts nicely and avoids some potential oddities:

          YearDate = Year(date) & Right("00" & Month(date);2)

           

          Create a relationship on this field and count the records across the relationship and you are done.

           

          One reason the week is trickier is that there aren't 4 weeks in a month. Assuming that you want to include everything after the 28th in the 4th week, this should do it:

          Use the above field and concatenate on to it:

          YearDate &

          Case(

          Day(date) ≤ 7; "01";

          Day(date) ≤ 14; "02";

          Day(date) ≤ 21; "03";

          "04")

           

          Again, create a realationship on this field and count the records accross the relationship. You could fancy these up with Let statements, but if the Date field is a stored value it shouldn't make a noticiable difference. In fact for the relationships mentioned, the date would have to be stored.

           

          Bob Gossom

          1 of 1 people found this helpful
          • 2. Re: Count by Month?
            cdcreek

            Bob;

            Thanks I will give that a try!

            Regards,

            Cdcreek

            • 3. Re: Count by Month?
              cdcreek

              Bob:

              Thanks, I will give it a try.  I don't have any relationships since there is only one table but I think I can set up something that would allow me to try what you suggest.  

              Again, much appreciated!

               

              Regards,

               

              Christian M. Deutsch

              Acuity Systems, Inc.

              Strategic Advisor

               

               

               

              Please note that this e-mail and any files transmitted with it may be privileged, confidential and protected from disclosure under applicable law. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reading, dissemination, distribution, copying or other use of this communication or any of its attachments is strictly prohibited. If you have received this communication in error, please notify the sender immediately by replying to this message, and deleting this message, any attachments, and all copies and backups from your computer.The information included is from sources deemed reliable. No representation is made as to the accuracy thereof and is submitted subject to errors and omissions.

              • 4. Re: Count by Month?
                BobGossom

                Christian,

                 

                Note that a table can be related to itself.

                 

                Bob G.

                • 5. Re: Count by Month?
                  cdcreek

                  Bob

                  Thanks again!

                  Chris

                   

                  Sent from my iPhone

                  Chris Deutsch