By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,953 Members | 1,109 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,953 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Sep 23 '08 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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.

Sep 23 '08 #2

P: n/a
On Sep 23, 3:08*pm, Salad <o...@vinegar.comwrote:
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
Sep 24 '08 #3

P: n/a
magickarle wrote:
On Sep 23, 3:08 pm, Salad <o...@vinegar.comwrote:
>>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
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.

Sep 24 '08 #4

P: n/a
On Sep 24, 3:45*pm, Salad <o...@vinegar.comwrote:
magickarlewrote:
On Sep 23, 3:08 pm, Salad <o...@vinegar.comwrote:
>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

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 YTDAvgofGrade666666
ManagerID 7777777 YTDAvgofGrade777777
ManagerID 8888888 YTDAvgofGrade888888
ManagerID 9999999 YTDAvgofGrade999999

Average Quarter 1
ManagerID 6666666 Q1AvgofGrade666666
ManagerID 7777777 Q1AvgofGrade777777
ManagerID 8888888 Q1AvgofGrade888888
ManagerID 9999999 Q1AvgofGrade999999

Average Quarter 2
ManagerID 6666666 Q2AvgofGrade666666
ManagerID 7777777 Q2AvgofGrade777777
ManagerID 8888888 Q2AvgofGrade888888
ManagerID 9999999 Q2AvgofGrade999999

Average Quarter 3
ManagerID 6666666 Q3AvgofGrade666666
ManagerID 7777777 Q3AvgofGrade777777
ManagerID 8888888 Q3AvgofGrade888888
ManagerID 9999999 Q3AvgofGrade999999
....

Average Month January
ManagerID 6666666 JanAvgofGrade666666
ManagerID 7777777 JanAvgofGrade777777
ManagerID 8888888 JanAvgofGrade888888
ManagerID 9999999 JanAvgofGrade999999

Average Month Feb
ManagerID 6666666 FebAvgofGrade666666
ManagerID 7777777 FebAvgofGrade777777
ManagerID 8888888 FebAvgofGrade888888
ManagerID 9999999 FebAvgofGrade999999
....

Average Week Januaray 6th
ManagerID 6666666 Jan6AvgofGrade666666
ManagerID 7777777 Jan6AvgofGrade777777
ManagerID 8888888 Jan6AvgofGrade888888
ManagerID 9999999 Jan6AvgofGrade999999

Average Week Januaray 13th
ManagerID 6666666 Jan13AvgofGrade666666
ManagerID 7777777 Jan13AvgofGrade777777
ManagerID 8888888 Jan13AvgofGrade888888
ManagerID 9999999 Jan13AvgofGrade999999
....

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
Sep 25 '08 #5

P: n/a
magickarle wrote:
On Sep 24, 3:45 pm, Salad <o...@vinegar.comwrote:
>>magickarlewrote:
>>>On Sep 23, 3:08 pm, Salad <o...@vinegar.comwrote:
>>>>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

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 YTDAvgofGrade666666
ManagerID 7777777 YTDAvgofGrade777777
ManagerID 8888888 YTDAvgofGrade888888
ManagerID 9999999 YTDAvgofGrade999999

Average Quarter 1
ManagerID 6666666 Q1AvgofGrade666666
ManagerID 7777777 Q1AvgofGrade777777
ManagerID 8888888 Q1AvgofGrade888888
ManagerID 9999999 Q1AvgofGrade999999

Average Quarter 2
ManagerID 6666666 Q2AvgofGrade666666
ManagerID 7777777 Q2AvgofGrade777777
ManagerID 8888888 Q2AvgofGrade888888
ManagerID 9999999 Q2AvgofGrade999999

Average Quarter 3
ManagerID 6666666 Q3AvgofGrade666666
ManagerID 7777777 Q3AvgofGrade777777
ManagerID 8888888 Q3AvgofGrade888888
ManagerID 9999999 Q3AvgofGrade999999
...

Average Month January
ManagerID 6666666 JanAvgofGrade666666
ManagerID 7777777 JanAvgofGrade777777
ManagerID 8888888 JanAvgofGrade888888
ManagerID 9999999 JanAvgofGrade999999

Average Month Feb
ManagerID 6666666 FebAvgofGrade666666
ManagerID 7777777 FebAvgofGrade777777
ManagerID 8888888 FebAvgofGrade888888
ManagerID 9999999 FebAvgofGrade999999
...

Average Week Januaray 6th
ManagerID 6666666 Jan6AvgofGrade666666
ManagerID 7777777 Jan6AvgofGrade777777
ManagerID 8888888 Jan6AvgofGrade888888
ManagerID 9999999 Jan6AvgofGrade999999

Average Week Januaray 13th
ManagerID 6666666 Jan13AvgofGrade666666
ManagerID 7777777 Jan13AvgofGrade777777
ManagerID 8888888 Jan13AvgofGrade888888
ManagerID 9999999 Jan13AvgofGrade999999
...

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.
Sep 25 '08 #6

P: n/a
On Sep 25, 11:41*am, Salad <o...@vinegar.comwrote:
magickarle wrote:
On Sep 24, 3:45 pm, Salad <o...@vinegar.comwrote:
>magickarlewrote:
>>On Sep 23, 3:08 pm, Salad <o...@vinegar.comwrote:
>>>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
>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 * YTDAvgofGrade666666
ManagerID 7777777 * YTDAvgofGrade777777
ManagerID 8888888 * YTDAvgofGrade888888
ManagerID 9999999 * YTDAvgofGrade999999
Average Quarter 1
ManagerID 6666666 * Q1AvgofGrade666666
ManagerID 7777777 * Q1AvgofGrade777777
ManagerID 8888888 * Q1AvgofGrade888888
ManagerID 9999999 * Q1AvgofGrade999999
Average Quarter 2
ManagerID 6666666 * Q2AvgofGrade666666
ManagerID 7777777 * Q2AvgofGrade777777
ManagerID 8888888 * Q2AvgofGrade888888
ManagerID 9999999 * Q2AvgofGrade999999
Average Quarter 3
ManagerID 6666666 * Q3AvgofGrade666666
ManagerID 7777777 * Q3AvgofGrade777777
ManagerID 8888888 * Q3AvgofGrade888888
ManagerID 9999999 * Q3AvgofGrade999999
...
Average Month January
ManagerID 6666666 * JanAvgofGrade666666
ManagerID 7777777 * JanAvgofGrade777777
ManagerID 8888888 * JanAvgofGrade888888
ManagerID 9999999 * JanAvgofGrade999999
Average Month Feb
ManagerID 6666666 * FebAvgofGrade666666
ManagerID 7777777 * FebAvgofGrade777777
ManagerID 8888888 * FebAvgofGrade888888
ManagerID 9999999 * FebAvgofGrade999999
...
Average Week Januaray 6th
ManagerID 6666666 * Jan6AvgofGrade666666
ManagerID 7777777 * Jan6AvgofGrade777777
ManagerID 8888888 * Jan6AvgofGrade888888
ManagerID 9999999 * Jan6AvgofGrade999999
Average Week Januaray 13th
ManagerID 6666666 * Jan13AvgofGrade666666
ManagerID 7777777 * Jan13AvgofGrade777777
ManagerID 8888888 * Jan13AvgofGrade888888
ManagerID 9999999 * Jan13AvgofGrade999999
...
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?
Sep 25 '08 #7

P: n/a
magickarle wrote:
On Sep 25, 11:41 am, Salad <o...@vinegar.comwrote:
>>magickarle wrote:
>>>On Sep 24, 3:45 pm, Salad <o...@vinegar.comwrote:
>>>>magickarlewrote:
>>>>>On Sep 23, 3:08 pm, Salad <o...@vinegar.comwrote:
>>>>>>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
>>>>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 YTDAvgofGrade666666
ManagerID 7777777 YTDAvgofGrade777777
ManagerID 8888888 YTDAvgofGrade888888
ManagerID 9999999 YTDAvgofGrade999999
>>>Average Quarter 1
ManagerID 6666666 Q1AvgofGrade666666
ManagerID 7777777 Q1AvgofGrade777777
ManagerID 8888888 Q1AvgofGrade888888
ManagerID 9999999 Q1AvgofGrade999999
>>>Average Quarter 2
ManagerID 6666666 Q2AvgofGrade666666
ManagerID 7777777 Q2AvgofGrade777777
ManagerID 8888888 Q2AvgofGrade888888
ManagerID 9999999 Q2AvgofGrade999999
>>>Average Quarter 3
ManagerID 6666666 Q3AvgofGrade666666
ManagerID 7777777 Q3AvgofGrade777777
ManagerID 8888888 Q3AvgofGrade888888
ManagerID 9999999 Q3AvgofGrade999999
...
>>>Average Month January
ManagerID 6666666 JanAvgofGrade666666
ManagerID 7777777 JanAvgofGrade777777
ManagerID 8888888 JanAvgofGrade888888
ManagerID 9999999 JanAvgofGrade999999
>>>Average Month Feb
ManagerID 6666666 FebAvgofGrade666666
ManagerID 7777777 FebAvgofGrade777777
ManagerID 8888888 FebAvgofGrade888888
ManagerID 9999999 FebAvgofGrade999999
...
>>>Average Week Januaray 6th
ManagerID 6666666 Jan6AvgofGrade666666
ManagerID 7777777 Jan6AvgofGrade777777
ManagerID 8888888 Jan6AvgofGrade888888
ManagerID 9999999 Jan6AvgofGrade999999
>>>Average Week Januaray 13th
ManagerID 6666666 Jan13AvgofGrade666666
ManagerID 7777777 Jan13AvgofGrade777777
ManagerID 8888888 Jan13AvgofGrade888888
ManagerID 9999999 Jan13AvgofGrade999999
...
>>>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
Sep 25 '08 #8

P: n/a
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!
Sep 29 '08 #9

P: n/a
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.
Sep 29 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.