Yearly, Quartly and Weekly report on the same report sheet. Possible?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • magickarle
    #1

    Yearly, Quartly and Weekly report on the same report sheet. Possible?

    Hi, I have a database in access with the following columns:
    Day
    AgentID
    ManagerID
    Grade

    They got information about agents and some grades.

    I would like to have ONE form with several grouping (yearly quarterly
    and weekly) with the respective average.
    So I need to create a query that would convert the Day in Year,
    Quarter, Week
    Then do an average of Grade

    Then create a report using the query.
    So is this possible?

    IE:
    January 2 2008
    123456 6666666 95
    345678 7777777 85
    345678 8888888 100

    January 3 2008
    123456 6666666 100
    345678 7777777 60
    345678 8888888 100
    Average week of December 30th: 90

    February 5 2008
    123456 7777777 30
    986543 9999999 80
    ....
    Average Quarterly 1: 69.38
    Average Yearly of 2008: 69.38
    Tags: None
    • Salad
      #2
      Re: Yearly, Quartly and Weekly report on the same report sheet. Possible?

      magickarle wrote:
      Hi, I have a database in access with the following columns:
      Day
      AgentID
      ManagerID
      Grade
      >
      They got information about agents and some grades.
      >
      I would like to have ONE form with several grouping (yearly quarterly
      and weekly) with the respective average.
      So I need to create a query that would convert the Day in Year,
      Quarter, Week
      Then do an average of Grade
      >
      Then create a report using the query.
      So is this possible?
      >
      IE:
      January 2 2008
      123456 6666666 95
      345678 7777777 85
      345678 8888888 100
      >
      January 3 2008
      123456 6666666 100
      345678 7777777 60
      345678 8888888 100
      Average week of December 30th: 90
      >
      February 5 2008
      123456 7777777 30
      986543 9999999 80
      ...
      Average Quarterly 1: 69.38
      Average Yearly of 2008: 69.38
      I'm not sure where the 69.38 value came from...I do understand the
      90...anyway, you might want to review RunningSum in online help. You'd
      want a group band for week, quarter, and year and use the running sum
      values to determine the avg. How it breaks on week and quarter is
      something you need to consider.

        Comment

        • magickarle
          #3
          Re: Yearly, Quartly and Weekly report on the same report sheet.Possible?

          On Sep 23, 3:08 pm, Salad <o...@vinegar.c omwrote:
          magickarlewrote :
          Hi, I have a database in access with the following columns:
          Day
          AgentID
          ManagerID
          Grade
          >
          They got information about agents and some grades.
          >
          I would like to have ONE form with several grouping (yearly quarterly
          and weekly) with the respective average.
          So I need to create a query that would convert the Day in Year,
          Quarter, Week
          Then do an average of Grade
          >
          Then create a report using the query.
          So is this possible?
          >
          IE:
          January 2 2008
                123456        6666666      95
                345678        7777777      85
                345678        8888888      100
          >
          January 3 2008
                123456        6666666      100
                345678        7777777      60
                345678        8888888      100
          Average week of December 30th: 90
          >
          February 5 2008
                123456        7777777      30
                986543        9999999      80
          ...
          Average Quarterly 1:        69.38
          Average Yearly of 2008: 69.38
          >
          I'm not sure where the 69.38 value came from...I do understand the
          90...anyway, you might want to review RunningSum in online help.  You'd
          want a group band for week, quarter, and year and use the running sum
          values to determine the avg.  How it breaks on week and quarter is
          something you need to consider.- Hide quoted text -
          >
          - Show quoted text -
          Isn't Running Sum a cumulative sum function?
          I'm not sure how Running Sum can help me (I didn't know about this
          option so I'm not too familiar with it)
          Thank you

            Comment

            • Salad
              #4
              Re: Yearly, Quartly and Weekly report on the same report sheet.Possible?

              magickarle wrote:
              On Sep 23, 3:08 pm, Salad <o...@vinegar.c omwrote:
              >
              >>magickarlewro te:
              >>
              >>>Hi, I have a database in access with the following columns:
              >>>Day
              >>>AgentID
              >>>ManagerID
              >>>Grade
              >>
              >>>They got information about agents and some grades.
              >>
              >>>I would like to have ONE form with several grouping (yearly quarterly
              >>>and weekly) with the respective average.
              >>>So I need to create a query that would convert the Day in Year,
              >>>Quarter, Week
              >>>Then do an average of Grade
              >>
              >>>Then create a report using the query.
              >>>So is this possible?
              >>
              >>>IE:
              >>>January 2 2008
              >> 123456 6666666 95
              >> 345678 7777777 85
              >> 345678 8888888 100
              >>
              >>>January 3 2008
              >> 123456 6666666 100
              >> 345678 7777777 60
              >> 345678 8888888 100
              >>>Average week of December 30th: 90
              >>
              >>>February 5 2008
              >> 123456 7777777 30
              >> 986543 9999999 80
              >>>...
              >>>Average Quarterly 1: 69.38
              >>>Average Yearly of 2008: 69.38
              >>
              >>I'm not sure where the 69.38 value came from...I do understand the
              >>90...anyway , you might want to review RunningSum in online help. You'd
              >>want a group band for week, quarter, and year and use the running sum
              >>values to determine the avg. How it breaks on week and quarter is
              >>something you need to consider.- Hide quoted text -
              >>
              >>- Show quoted text -
              >
              >
              Isn't Running Sum a cumulative sum function?
              I'm not sure how Running Sum can help me (I didn't know about this
              option so I'm not too familiar with it)
              Thank you
              It is cumulative if you set it to "Over All". But you can break on
              group. So if you have bands to print week (break), qtr (break), year
              (overall) you should be OK.

                Comment

                • magickarle
                  #5
                  Re: Yearly, Quartly and Weekly report on the same report sheet.Possible?

                  On Sep 24, 3:45 pm, Salad <o...@vinegar.c omwrote:
                  magickarlewrote :
                  On Sep 23, 3:08 pm, Salad <o...@vinegar.c omwrote:
                  >
                  >magickarlewrot e:
                  >
                  >>Hi, I have a database in access with the following columns:
                  >>Day
                  >>AgentID
                  >>ManagerID
                  >>Grade
                  >
                  >>They got information about agents and some grades.
                  >
                  >>I would like to have ONE form with several grouping (yearly quarterly
                  >>and weekly) with the respective average.
                  >>So I need to create a query that would convert the Day in Year,
                  >>Quarter, Week
                  >>Then do an average of Grade
                  >
                  >>Then create a report using the query.
                  >>So is this possible?
                  >
                  >>IE:
                  >>January 2 2008
                  >     123456        6666666      95
                  >     345678        7777777      85
                  >     345678        8888888      100
                  >
                  >>January 3 2008
                  >     123456        6666666      100
                  >     345678        7777777      60
                  >     345678        8888888      100
                  >>Average week of December 30th: 90
                  >
                  >>February 5 2008
                  >     123456        7777777      30
                  >     986543        9999999      80
                  >>...
                  >>Average Quarterly 1:        69.38
                  >>Average Yearly of 2008: 69.38
                  >
                  >I'm not sure where the 69.38 value came from...I do understand the
                  >90...anyway, you might want to review RunningSum in online help.  You'd
                  >want a group band for week, quarter, and year and use the running sum
                  >values to determine the avg.  How it breaks on week and quarter is
                  >something you need to consider.- Hide quoted text -
                  >
                  >- Show quoted text -
                  >
                  Isn't Running Sum a cumulative sum function?
                  I'm not sure how Running Sum can help me (I didn't know about this
                  option so I'm not too familiar with it)
                  Thank you
                  >
                  It is cumulative if you set it to "Over All".  But you can break on
                  group.  So if you have bands to print week (break), qtr (break), year
                  (overall) you should be OK.- Hide quoted text -
                  >
                  - Show quoted text -
                  Thank you for your help!

                  Now what I'm trying to do is (still on one report):
                  Group per TM their average per
                  Year, Quarter, Month and Week.

                  If I group per year, quarter, month, week, I'm not able to display all
                  the ManagerID

                  The kind of layout I'm trying to do is:
                  Average Year to date: AvgofGrade
                  ManagerID 6666666 YTDAvgofGrade66 6666
                  ManagerID 7777777 YTDAvgofGrade77 7777
                  ManagerID 8888888 YTDAvgofGrade88 8888
                  ManagerID 9999999 YTDAvgofGrade99 9999

                  Average Quarter 1
                  ManagerID 6666666 Q1AvgofGrade666 666
                  ManagerID 7777777 Q1AvgofGrade777 777
                  ManagerID 8888888 Q1AvgofGrade888 888
                  ManagerID 9999999 Q1AvgofGrade999 999

                  Average Quarter 2
                  ManagerID 6666666 Q2AvgofGrade666 666
                  ManagerID 7777777 Q2AvgofGrade777 777
                  ManagerID 8888888 Q2AvgofGrade888 888
                  ManagerID 9999999 Q2AvgofGrade999 999

                  Average Quarter 3
                  ManagerID 6666666 Q3AvgofGrade666 666
                  ManagerID 7777777 Q3AvgofGrade777 777
                  ManagerID 8888888 Q3AvgofGrade888 888
                  ManagerID 9999999 Q3AvgofGrade999 999
                  ....

                  Average Month January
                  ManagerID 6666666 JanAvgofGrade66 6666
                  ManagerID 7777777 JanAvgofGrade77 7777
                  ManagerID 8888888 JanAvgofGrade88 8888
                  ManagerID 9999999 JanAvgofGrade99 9999

                  Average Month Feb
                  ManagerID 6666666 FebAvgofGrade66 6666
                  ManagerID 7777777 FebAvgofGrade77 7777
                  ManagerID 8888888 FebAvgofGrade88 8888
                  ManagerID 9999999 FebAvgofGrade99 9999
                  ....

                  Average Week Januaray 6th
                  ManagerID 6666666 Jan6AvgofGrade6 66666
                  ManagerID 7777777 Jan6AvgofGrade7 77777
                  ManagerID 8888888 Jan6AvgofGrade8 88888
                  ManagerID 9999999 Jan6AvgofGrade9 99999

                  Average Week Januaray 13th
                  ManagerID 6666666 Jan13AvgofGrade 666666
                  ManagerID 7777777 Jan13AvgofGrade 777777
                  ManagerID 8888888 Jan13AvgofGrade 888888
                  ManagerID 9999999 Jan13AvgofGrade 999999
                  ....

                  The Query result would be:
                  Year Quat Month Week ManagerID Grade
                  -------------------------------------------------------------
                  2008 Q1 January January 6 666666666 95
                  2008 Q1 January January 6 777777777 85
                  2008 Q1 January January 6 888888888 88
                  2008 Q1 January January 6 999999999 62
                  2008 Q1 January January 13 666666666 98
                  2008 Q1 January January 13 777777777 100
                  2008 Q1 January January 13 888888888 78
                  2008 Q1 January January 13 999999999 84
                  2008 Q1 February February 3 666666666 84
                  2008 Q1 February February 3 777777777 54
                  2008 Q1 February February 3 888888888 95
                  2008 Q1 February February 3 999999999 62
                  2008 Q2 April April 6 666666666 84
                  2008 Q2 April April 6 777777777 62
                  2008 Q2 April April 6 888888888 32
                  2008 Q2 April April 6 999999999 62
                  2008 Q2 April April 20 666666666 64
                  2008 Q2 April April 20 777777777 68
                  2008 Q2 April April 20 888888888 84
                  2008 Q2 April April 20 999999999 74
                  2008 Q2 May April 27 666666666 52
                  2008 Q2 May April 27 777777777 65
                  2008 Q2 May April 27 888888888 85
                  2008 Q2 May April 27 999999999 84
                  2008 Q3 July July 13 666666666 96
                  2008 Q3 July July 13 777777777 85
                  2008 Q3 July July 13 888888888 83
                  2008 Q3 July July 13 999999999 74
                  2008 Q3 July July 20 666666666 95
                  2008 Q3 July July 20 777777777 92
                  2008 Q3 July July 20 888888888 91
                  2008 Q3 July July 20 999999999 75

                    Comment

                    • Salad
                      #6
                      Re: Yearly, Quartly and Weekly report on the same report sheet.Possible?

                      magickarle wrote:
                      On Sep 24, 3:45 pm, Salad <o...@vinegar.c omwrote:
                      >
                      >>magickarlewro te:
                      >>
                      >>>On Sep 23, 3:08 pm, Salad <o...@vinegar.c omwrote:
                      >>
                      >>>>magickarlew rote:
                      >>
                      >>>>>Hi, I have a database in access with the following columns:
                      >>>>>Day
                      >>>>>AgentID
                      >>>>>ManagerI D
                      >>>>>Grade
                      >>
                      >>>>>They got information about agents and some grades.
                      >>
                      >>>>>I would like to have ONE form with several grouping (yearly quarterly
                      >>>>>and weekly) with the respective average.
                      >>>>>So I need to create a query that would convert the Day in Year,
                      >>>>>Quarter, Week
                      >>>>>Then do an average of Grade
                      >>
                      >>>>>Then create a report using the query.
                      >>>>>So is this possible?
                      >>
                      >>>>>IE:
                      >>>>>January 2 2008
                      >>>> 123456 6666666 95
                      >>>> 345678 7777777 85
                      >>>> 345678 8888888 100
                      >>
                      >>>>>January 3 2008
                      >>>> 123456 6666666 100
                      >>>> 345678 7777777 60
                      >>>> 345678 8888888 100
                      >>>>>Average week of December 30th: 90
                      >>
                      >>>>>February 5 2008
                      >>>> 123456 7777777 30
                      >>>> 986543 9999999 80
                      >>>>>...
                      >>>>>Average Quarterly 1: 69.38
                      >>>>>Average Yearly of 2008: 69.38
                      >>
                      >>>>I'm not sure where the 69.38 value came from...I do understand the
                      >>>>90...anyway , you might want to review RunningSum in online help. You'd
                      >>>>want a group band for week, quarter, and year and use the running sum
                      >>>>values to determine the avg. How it breaks on week and quarter is
                      >>>>something you need to consider.- Hide quoted text -
                      >>
                      >>>>- Show quoted text -
                      >>
                      >>>Isn't Running Sum a cumulative sum function?
                      >>>I'm not sure how Running Sum can help me (I didn't know about this
                      >>>option so I'm not too familiar with it)
                      >>>Thank you
                      >>
                      >>It is cumulative if you set it to "Over All". But you can break on
                      >>group. So if you have bands to print week (break), qtr (break), year
                      >>(overall) you should be OK.- Hide quoted text -
                      >>
                      >>- Show quoted text -
                      >
                      >
                      Thank you for your help!
                      >
                      Now what I'm trying to do is (still on one report):
                      Group per TM their average per
                      Year, Quarter, Month and Week.
                      >
                      If I group per year, quarter, month, week, I'm not able to display all
                      the ManagerID
                      >
                      The kind of layout I'm trying to do is:
                      Average Year to date: AvgofGrade
                      ManagerID 6666666 YTDAvgofGrade66 6666
                      ManagerID 7777777 YTDAvgofGrade77 7777
                      ManagerID 8888888 YTDAvgofGrade88 8888
                      ManagerID 9999999 YTDAvgofGrade99 9999
                      >
                      Average Quarter 1
                      ManagerID 6666666 Q1AvgofGrade666 666
                      ManagerID 7777777 Q1AvgofGrade777 777
                      ManagerID 8888888 Q1AvgofGrade888 888
                      ManagerID 9999999 Q1AvgofGrade999 999
                      >
                      Average Quarter 2
                      ManagerID 6666666 Q2AvgofGrade666 666
                      ManagerID 7777777 Q2AvgofGrade777 777
                      ManagerID 8888888 Q2AvgofGrade888 888
                      ManagerID 9999999 Q2AvgofGrade999 999
                      >
                      Average Quarter 3
                      ManagerID 6666666 Q3AvgofGrade666 666
                      ManagerID 7777777 Q3AvgofGrade777 777
                      ManagerID 8888888 Q3AvgofGrade888 888
                      ManagerID 9999999 Q3AvgofGrade999 999
                      ...
                      >
                      Average Month January
                      ManagerID 6666666 JanAvgofGrade66 6666
                      ManagerID 7777777 JanAvgofGrade77 7777
                      ManagerID 8888888 JanAvgofGrade88 8888
                      ManagerID 9999999 JanAvgofGrade99 9999
                      >
                      Average Month Feb
                      ManagerID 6666666 FebAvgofGrade66 6666
                      ManagerID 7777777 FebAvgofGrade77 7777
                      ManagerID 8888888 FebAvgofGrade88 8888
                      ManagerID 9999999 FebAvgofGrade99 9999
                      ...
                      >
                      Average Week Januaray 6th
                      ManagerID 6666666 Jan6AvgofGrade6 66666
                      ManagerID 7777777 Jan6AvgofGrade7 77777
                      ManagerID 8888888 Jan6AvgofGrade8 88888
                      ManagerID 9999999 Jan6AvgofGrade9 99999
                      >
                      Average Week Januaray 13th
                      ManagerID 6666666 Jan13AvgofGrade 666666
                      ManagerID 7777777 Jan13AvgofGrade 777777
                      ManagerID 8888888 Jan13AvgofGrade 888888
                      ManagerID 9999999 Jan13AvgofGrade 999999
                      ...
                      >
                      The Query result would be:
                      Year Quat Month Week ManagerID Grade
                      -------------------------------------------------------------
                      2008 Q1 January January 6 666666666 95
                      2008 Q1 January January 6 777777777 85
                      2008 Q1 January January 6 888888888 88
                      2008 Q1 January January 6 999999999 62
                      2008 Q1 January January 13 666666666 98
                      2008 Q1 January January 13 777777777 100
                      2008 Q1 January January 13 888888888 78
                      2008 Q1 January January 13 999999999 84
                      2008 Q1 February February 3 666666666 84
                      2008 Q1 February February 3 777777777 54
                      2008 Q1 February February 3 888888888 95
                      2008 Q1 February February 3 999999999 62
                      2008 Q2 April April 6 666666666 84
                      2008 Q2 April April 6 777777777 62
                      2008 Q2 April April 6 888888888 32
                      2008 Q2 April April 6 999999999 62
                      2008 Q2 April April 20 666666666 64
                      2008 Q2 April April 20 777777777 68
                      2008 Q2 April April 20 888888888 84
                      2008 Q2 April April 20 999999999 74
                      2008 Q2 May April 27 666666666 52
                      2008 Q2 May April 27 777777777 65
                      2008 Q2 May April 27 888888888 85
                      2008 Q2 May April 27 999999999 84
                      2008 Q3 July July 13 666666666 96
                      2008 Q3 July July 13 777777777 85
                      2008 Q3 July July 13 888888888 83
                      2008 Q3 July July 13 999999999 74
                      2008 Q3 July July 20 666666666 95
                      2008 Q3 July July 20 777777777 92
                      2008 Q3 July July 20 888888888 91
                      2008 Q3 July July 20 999999999 75
                      Hmmm...I'd probably create a report for each breakdown and then start
                      futzing with subreports. Af first I thought you were simply wanting
                      averages for the week, month, and qtr.

                        Comment

                        • magickarle
                          #7
                          Re: Yearly, Quartly and Weekly report on the same report sheet.Possible?

                          On Sep 25, 11:41 am, Salad <o...@vinegar.c omwrote:
                          magickarle wrote:
                          On Sep 24, 3:45 pm, Salad <o...@vinegar.c omwrote:
                          >
                          >magickarlewrot e:
                          >
                          >>On Sep 23, 3:08 pm, Salad <o...@vinegar.c omwrote:
                          >
                          >>>magickarlewr ote:
                          >
                          >>>>Hi, I have a database in access with the following columns:
                          >>>>Day
                          >>>>AgentID
                          >>>>ManagerID
                          >>>>Grade
                          >
                          >>>>They got information about agents and some grades.
                          >
                          >>>>I would like to have ONE form with several grouping (yearly quarterly
                          >>>>and weekly) with the respective average.
                          >>>>So I need to create a query that would convert the Day in Year,
                          >>>>Quarter, Week
                          >>>>Then do an average of Grade
                          >
                          >>>>Then create a report using the query.
                          >>>>So is this possible?
                          >
                          >>>>IE:
                          >>>>January 2 2008
                          >>>    123456        6666666      95
                          >>>    345678        7777777      85
                          >>>    345678        8888888      100
                          >
                          >>>>January 3 2008
                          >>>    123456        6666666      100
                          >>>    345678        7777777      60
                          >>>    345678        8888888      100
                          >>>>Average week of December 30th: 90
                          >
                          >>>>February 5 2008
                          >>>    123456        7777777      30
                          >>>    986543        9999999      80
                          >>>>...
                          >>>>Average Quarterly 1:        69.38
                          >>>>Average Yearly of 2008: 69.38
                          >
                          >>>I'm not sure where the 69.38 value came from...I do understand the
                          >>>90...anywa y, you might want to review RunningSum in online help.  You'd
                          >>>want a group band for week, quarter, and year and use the running sum
                          >>>values to determine the avg.  How it breaks on week and quarter is
                          >>>something you need to consider.- Hide quoted text -
                          >
                          >>>- Show quoted text -
                          >
                          >>Isn't Running Sum a cumulative sum function?
                          >>I'm not sure how Running Sum can help me (I didn't know about this
                          >>option so I'm not too familiar with it)
                          >>Thank you
                          >
                          >It is cumulative if you set it to "Over All".  But you can break on
                          >group.  So if you have bands to print week (break), qtr (break), year
                          >(overall) you should be OK.- Hide quoted text -
                          >
                          >- Show quoted text -
                          >
                          Thank you for your help!
                          >
                          Now what I'm trying to do is (still on one report):
                          Group per TM their average per
                          Year, Quarter, Month and Week.
                          >
                          If I group per year, quarter, month, week, I'm not able to display all
                          the ManagerID
                          >
                          The kind of layout I'm trying to do is:
                          Average Year to date: AvgofGrade
                          ManagerID 6666666   YTDAvgofGrade66 6666
                          ManagerID 7777777   YTDAvgofGrade77 7777
                          ManagerID 8888888   YTDAvgofGrade88 8888
                          ManagerID 9999999   YTDAvgofGrade99 9999
                          >
                          Average Quarter 1
                          ManagerID 6666666   Q1AvgofGrade666 666
                          ManagerID 7777777   Q1AvgofGrade777 777
                          ManagerID 8888888   Q1AvgofGrade888 888
                          ManagerID 9999999   Q1AvgofGrade999 999
                          >
                          Average Quarter 2
                          ManagerID 6666666   Q2AvgofGrade666 666
                          ManagerID 7777777   Q2AvgofGrade777 777
                          ManagerID 8888888   Q2AvgofGrade888 888
                          ManagerID 9999999   Q2AvgofGrade999 999
                          >
                          Average Quarter 3
                          ManagerID 6666666   Q3AvgofGrade666 666
                          ManagerID 7777777   Q3AvgofGrade777 777
                          ManagerID 8888888   Q3AvgofGrade888 888
                          ManagerID 9999999   Q3AvgofGrade999 999
                          ...
                          >
                          Average Month January
                          ManagerID 6666666   JanAvgofGrade66 6666
                          ManagerID 7777777   JanAvgofGrade77 7777
                          ManagerID 8888888   JanAvgofGrade88 8888
                          ManagerID 9999999   JanAvgofGrade99 9999
                          >
                          Average Month Feb
                          ManagerID 6666666   FebAvgofGrade66 6666
                          ManagerID 7777777   FebAvgofGrade77 7777
                          ManagerID 8888888   FebAvgofGrade88 8888
                          ManagerID 9999999   FebAvgofGrade99 9999
                          ...
                          >
                          Average Week Januaray 6th
                          ManagerID 6666666   Jan6AvgofGrade6 66666
                          ManagerID 7777777   Jan6AvgofGrade7 77777
                          ManagerID 8888888   Jan6AvgofGrade8 88888
                          ManagerID 9999999   Jan6AvgofGrade9 99999
                          >
                          Average Week Januaray 13th
                          ManagerID 6666666   Jan13AvgofGrade 666666
                          ManagerID 7777777   Jan13AvgofGrade 777777
                          ManagerID 8888888   Jan13AvgofGrade 888888
                          ManagerID 9999999   Jan13AvgofGrade 999999
                          ...
                          >
                          The Query result would be:
                          Year Quat Month  Week         ManagerID    Grade
                          -------------------------------------------------------------
                          2008 Q1 January  January 6     666666666   95
                          2008 Q1 January  January 6     777777777   85
                          2008 Q1 January  January 6     888888888   88
                          2008 Q1 January  January 6     999999999   62
                          2008 Q1 January  January 13   666666666   98
                          2008 Q1 January  January 13   777777777   100
                          2008 Q1 January  January 13   888888888   78
                          2008 Q1 January  January 13   999999999   84
                          2008 Q1 February February 3   666666666   84
                          2008 Q1 February February 3   777777777   54
                          2008 Q1 February February 3   888888888   95
                          2008 Q1 February February 3   999999999   62
                          2008 Q2 April       April 6          666666666   84
                          2008 Q2 April       April 6          777777777   62
                          2008 Q2 April       April 6          888888888   32
                          2008 Q2 April       April 6          999999999   62
                          2008 Q2 April       April 20        666666666   64
                          2008 Q2 April       April 20        777777777   68
                          2008 Q2 April       April 20        888888888   84
                          2008 Q2 April       April 20        999999999   74
                          2008 Q2 May       April 27        666666666   52
                          2008 Q2 May       April 27        777777777   65
                          2008 Q2 May       April 27        888888888   85
                          2008 Q2 May       April 27        999999999   84
                          2008 Q3 July        July 13        666666666   96
                          2008 Q3 July        July 13        777777777   85
                          2008 Q3 July        July 13        888888888   83
                          2008 Q3 July        July 13        999999999   74
                          2008 Q3 July        July 20        666666666   95
                          2008 Q3 July        July 20        777777777   92
                          2008 Q3 July        July 20        888888888   91
                          2008 Q3 July        July 20        999999999   75
                          >
                          Hmmm...I'd probably create a report for each breakdown and then start
                          futzing with subreports.  Af first I thought you were simply wanting
                          averages for the week, month, and qtr.- Hide quoted text -
                          >
                          - Show quoted text -
                          It was the case at first but then wanted to add more details (grouping
                          per TM).
                          I learned about the running sum. That's a great feature to keep in
                          mind.
                          Ya, I think I'll have to create sub reports. It's been couple of hours
                          trying to group per TM based on Year, Quarter, ... but nothing yet.
                          I'm wondering if I'll have to create seperate queries for each
                          SubReports?

                            Comment

                            • Salad
                              #8
                              Re: Yearly, Quartly and Weekly report on the same report sheet.Possible?

                              magickarle wrote:
                              On Sep 25, 11:41 am, Salad <o...@vinegar.c omwrote:
                              >
                              >>magickarle wrote:
                              >>
                              >>>On Sep 24, 3:45 pm, Salad <o...@vinegar.c omwrote:
                              >>
                              >>>>magickarlew rote:
                              >>
                              >>>>>On Sep 23, 3:08 pm, Salad <o...@vinegar.c omwrote:
                              >>
                              >>>>>>magickarl ewrote:
                              >>
                              >>>>>>>Hi, I have a database in access with the following columns:
                              >>>>>>>Day
                              >>>>>>>AgentI D
                              >>>>>>>ManagerI D
                              >>>>>>>Grade
                              >>
                              >>>>>>>They got information about agents and some grades.
                              >>
                              >>>>>>>I would like to have ONE form with several grouping (yearly quarterly
                              >>>>>>>and weekly) with the respective average.
                              >>>>>>>So I need to create a query that would convert the Day in Year,
                              >>>>>>>Quarte r, Week
                              >>>>>>>Then do an average of Grade
                              >>
                              >>>>>>>Then create a report using the query.
                              >>>>>>>So is this possible?
                              >>
                              >>>>>>>IE:
                              >>>>>>>Januar y 2 2008
                              >>>>>> 123456 6666666 95
                              >>>>>> 345678 7777777 85
                              >>>>>> 345678 8888888 100
                              >>
                              >>>>>>>Januar y 3 2008
                              >>>>>> 123456 6666666 100
                              >>>>>> 345678 7777777 60
                              >>>>>> 345678 8888888 100
                              >>>>>>>Averag e week of December 30th: 90
                              >>
                              >>>>>>>Februa ry 5 2008
                              >>>>>> 123456 7777777 30
                              >>>>>> 986543 9999999 80
                              >>>>>>>...
                              >>>>>>>Averag e Quarterly 1: 69.38
                              >>>>>>>Averag e Yearly of 2008: 69.38
                              >>
                              >>>>>>I'm not sure where the 69.38 value came from...I do understand the
                              >>>>>>90...anyw ay, you might want to review RunningSum in online help. You'd
                              >>>>>>want a group band for week, quarter, and year and use the running sum
                              >>>>>>values to determine the avg. How it breaks on week and quarter is
                              >>>>>>somethi ng you need to consider.- Hide quoted text -
                              >>
                              >>>>>>- Show quoted text -
                              >>
                              >>>>>Isn't Running Sum a cumulative sum function?
                              >>>>>I'm not sure how Running Sum can help me (I didn't know about this
                              >>>>>option so I'm not too familiar with it)
                              >>>>>Thank you
                              >>
                              >>>>It is cumulative if you set it to "Over All". But you can break on
                              >>>>group. So if you have bands to print week (break), qtr (break), year
                              >>>>(overall) you should be OK.- Hide quoted text -
                              >>
                              >>>>- Show quoted text -
                              >>
                              >>>Thank you for your help!
                              >>
                              >>>Now what I'm trying to do is (still on one report):
                              >>>Group per TM their average per
                              >>>Year, Quarter, Month and Week.
                              >>
                              >>>If I group per year, quarter, month, week, I'm not able to display all
                              >>>the ManagerID
                              >>
                              >>>The kind of layout I'm trying to do is:
                              >>>Average Year to date: AvgofGrade
                              >>>ManagerID 6666666 YTDAvgofGrade66 6666
                              >>>ManagerID 7777777 YTDAvgofGrade77 7777
                              >>>ManagerID 8888888 YTDAvgofGrade88 8888
                              >>>ManagerID 9999999 YTDAvgofGrade99 9999
                              >>
                              >>>Average Quarter 1
                              >>>ManagerID 6666666 Q1AvgofGrade666 666
                              >>>ManagerID 7777777 Q1AvgofGrade777 777
                              >>>ManagerID 8888888 Q1AvgofGrade888 888
                              >>>ManagerID 9999999 Q1AvgofGrade999 999
                              >>
                              >>>Average Quarter 2
                              >>>ManagerID 6666666 Q2AvgofGrade666 666
                              >>>ManagerID 7777777 Q2AvgofGrade777 777
                              >>>ManagerID 8888888 Q2AvgofGrade888 888
                              >>>ManagerID 9999999 Q2AvgofGrade999 999
                              >>
                              >>>Average Quarter 3
                              >>>ManagerID 6666666 Q3AvgofGrade666 666
                              >>>ManagerID 7777777 Q3AvgofGrade777 777
                              >>>ManagerID 8888888 Q3AvgofGrade888 888
                              >>>ManagerID 9999999 Q3AvgofGrade999 999
                              >>>...
                              >>
                              >>>Average Month January
                              >>>ManagerID 6666666 JanAvgofGrade66 6666
                              >>>ManagerID 7777777 JanAvgofGrade77 7777
                              >>>ManagerID 8888888 JanAvgofGrade88 8888
                              >>>ManagerID 9999999 JanAvgofGrade99 9999
                              >>
                              >>>Average Month Feb
                              >>>ManagerID 6666666 FebAvgofGrade66 6666
                              >>>ManagerID 7777777 FebAvgofGrade77 7777
                              >>>ManagerID 8888888 FebAvgofGrade88 8888
                              >>>ManagerID 9999999 FebAvgofGrade99 9999
                              >>>...
                              >>
                              >>>Average Week Januaray 6th
                              >>>ManagerID 6666666 Jan6AvgofGrade6 66666
                              >>>ManagerID 7777777 Jan6AvgofGrade7 77777
                              >>>ManagerID 8888888 Jan6AvgofGrade8 88888
                              >>>ManagerID 9999999 Jan6AvgofGrade9 99999
                              >>
                              >>>Average Week Januaray 13th
                              >>>ManagerID 6666666 Jan13AvgofGrade 666666
                              >>>ManagerID 7777777 Jan13AvgofGrade 777777
                              >>>ManagerID 8888888 Jan13AvgofGrade 888888
                              >>>ManagerID 9999999 Jan13AvgofGrade 999999
                              >>>...
                              >>
                              >>>The Query result would be:
                              >>>Year Quat Month Week ManagerID Grade
                              >>>-------------------------------------------------------------
                              >>>2008 Q1 January January 6 666666666 95
                              >>>2008 Q1 January January 6 777777777 85
                              >>>2008 Q1 January January 6 888888888 88
                              >>>2008 Q1 January January 6 999999999 62
                              >>>2008 Q1 January January 13 666666666 98
                              >>>2008 Q1 January January 13 777777777 100
                              >>>2008 Q1 January January 13 888888888 78
                              >>>2008 Q1 January January 13 999999999 84
                              >>>2008 Q1 February February 3 666666666 84
                              >>>2008 Q1 February February 3 777777777 54
                              >>>2008 Q1 February February 3 888888888 95
                              >>>2008 Q1 February February 3 999999999 62
                              >>>2008 Q2 April April 6 666666666 84
                              >>>2008 Q2 April April 6 777777777 62
                              >>>2008 Q2 April April 6 888888888 32
                              >>>2008 Q2 April April 6 999999999 62
                              >>>2008 Q2 April April 20 666666666 64
                              >>>2008 Q2 April April 20 777777777 68
                              >>>2008 Q2 April April 20 888888888 84
                              >>>2008 Q2 April April 20 999999999 74
                              >>>2008 Q2 May April 27 666666666 52
                              >>>2008 Q2 May April 27 777777777 65
                              >>>2008 Q2 May April 27 888888888 85
                              >>>2008 Q2 May April 27 999999999 84
                              >>>2008 Q3 July July 13 666666666 96
                              >>>2008 Q3 July July 13 777777777 85
                              >>>2008 Q3 July July 13 888888888 83
                              >>>2008 Q3 July July 13 999999999 74
                              >>>2008 Q3 July July 20 666666666 95
                              >>>2008 Q3 July July 20 777777777 92
                              >>>2008 Q3 July July 20 888888888 91
                              >>>2008 Q3 July July 20 999999999 75
                              >>
                              >>Hmmm...I'd probably create a report for each breakdown and then start
                              >>futzing with subreports. Af first I thought you were simply wanting
                              >>averages for the week, month, and qtr.- Hide quoted text -
                              >>
                              >>- Show quoted text -
                              >
                              >
                              It was the case at first but then wanted to add more details (grouping
                              per TM).
                              I learned about the running sum. That's a great feature to keep in
                              mind.
                              Ya, I think I'll have to create sub reports. It's been couple of hours
                              trying to group per TM based on Year, Quarter, ... but nothing yet.
                              I'm wondering if I'll have to create seperate queries for each
                              SubReports?
                              It might be best. However, if you design a report thru the wizard and
                              group on something (like acct mgr) you can get summary options live avg,
                              sum, etc. Maybe that will help in using the same query

                                Comment

                                • magickarle
                                  #9
                                  Re: Yearly, Quartly and Weekly report on the same report sheet.Possible?

                                  It might be best.  However, if you design a report thru the wizard and
                                  group on something (like acct mgr) you can get summary options live avg,
                                  sum, etc.  Maybe that will help in using the same query- Hide quoted text -
                                  >
                                  - Show quoted text -
                                  It's me again.
                                  I get exactly what I wanted.
                                  Now I'm trying to optimize the report (with the sub-reports).
                                  What's happening: the report got to run 4 times the same query (since
                                  I got 4 sub reports with same record source).
                                  Is there a way to "cache" in memory the query result and use it in the
                                  sub reports (so it doens't have to run the same query 4 times)?

                                  Updates: Well I've found a way to make it faster:
                                  I've converted the query to a make table query and I changed the
                                  record source of the sub reports to the table created.
                                  So this way, it runs one a make query.
                                  Is there another way?
                                  Thanks again!

                                    Comment

                                    • Salad
                                      #10
                                      Re: Yearly, Quartly and Weekly report on the same report sheet.Possible?

                                      magickarle wrote:
                                      >>It might be best. However, if you design a report thru the wizard and
                                      >>group on something (like acct mgr) you can get summary options live avg,
                                      >>sum, etc. Maybe that will help in using the same query- Hide quoted text -
                                      >>
                                      >>- Show quoted text -
                                      >
                                      >
                                      It's me again.
                                      I get exactly what I wanted.
                                      Now I'm trying to optimize the report (with the sub-reports).
                                      What's happening: the report got to run 4 times the same query (since
                                      I got 4 sub reports with same record source).
                                      Is there a way to "cache" in memory the query result and use it in the
                                      sub reports (so it doens't have to run the same query 4 times)?
                                      Not that I know of. Each report is "separate" and should be able to run
                                      by itself. If the query is slow, then displaying the report will be slow.
                                      >
                                      Updates: Well I've found a way to make it faster:
                                      I've converted the query to a make table query and I changed the
                                      record source of the sub reports to the table created.
                                      So this way, it runs one a make query.
                                      Is there another way?
                                      Thanks again!
                                      If you have to do that. I don't know what your query looks like or what
                                      it is filtering on.

                                      If you are using SubSelects then I'd assume/expect it would be slow as
                                      molasses. Ex:
                                      SELECT Field From Table Where Field In (select ...)

                                      If your tables are large and you are filtering on fields/columns that
                                      aren't indexed, or the linked fields in the tables aren't indexed, it
                                      might be slow.

                                      I've never had to worry about speed in my reports so I've never had to
                                      do a MakeTable first. If you actually have to do that do get some speed
                                      then make sure you do that before you call the report.

                                        Comment

                                        Previous template Next
                                        Working...