Apologies for cross/re-posting but I got no joy in the 'reports' forum.
Here's my original post:
Not sure if I should spread this across two threads but here goes.
I report sales from a database I have built, and have created a crosstab, which groups sales value per week by employee. I want to create a report which will calculate an average for the value of an employees sales over the previous four weeks for every week of the period I am reporting.
e.g:
week 6 sales are 300
week 5 sales are 200
week 4 sales are 100
week 3 sales are 200
week 2 sales are 700
week 1 sales are 600
so:
The rolling average figure for week 6 is 200
(week6+week5+week4+week3)/4)
The average for week 5 is 300
((week5+week4+week3+week2)/4)
And the average for week 4 is 400
((week4+week3+week2+week1)/4)
I then want to put those average values into a chart, to track the employees' performance over a period of time in a way which shows the trends much more accurately than by following the ups and downs of weekly variations. However the problem is I have no idea of how to get Access to do this. I can create a report based on my original crosstab no problem, but I can't calculate the average for each period without some serious manual field property setting. Also, it doesn't put fields into the report for future weeks, so once the week turns over things go awry anyway.
The other problem is that sometimes the weekly value for the employee may be 0, which as you know returns a null value in the report. This the buggers up the averaging. I know that I need to use the Nz function to convert the null value to 0, but am not at all familiar with how to do it (or whether I can in a query or report).
Apologies for being long winded. Any help would be greatly appreciated, Iain. [end original post]
I have since managed a workaround which saves me doing too much manual entry work. This involves building a report with just the last four weeks' sales on it and re-naming the report fields, so that the calculated field which displays the average is always correct. I still have to change the data source for the four report fields (one for each week) manually when the week rolls over (e.g. from 9,8,7 & 6 to 10,9,8 & 7) and I am also having to take the average value and put this into a seperate spreadsheet. This means that I can not do a historical analysis, without changing the period the report covers manually and I just don't have the time...
I am sure that it must be possible to get Access to do it all for me, but it's a bit out of my league if I'm honest.
Again, apologies for long windedness! Thanks in advance, Iain.
BTW I run Access 2k on Win 2k Robbo ;-)
Here's my original post:
Not sure if I should spread this across two threads but here goes.
I report sales from a database I have built, and have created a crosstab, which groups sales value per week by employee. I want to create a report which will calculate an average for the value of an employees sales over the previous four weeks for every week of the period I am reporting.
e.g:
week 6 sales are 300
week 5 sales are 200
week 4 sales are 100
week 3 sales are 200
week 2 sales are 700
week 1 sales are 600
so:
The rolling average figure for week 6 is 200
(week6+week5+week4+week3)/4)
The average for week 5 is 300
((week5+week4+week3+week2)/4)
And the average for week 4 is 400
((week4+week3+week2+week1)/4)
I then want to put those average values into a chart, to track the employees' performance over a period of time in a way which shows the trends much more accurately than by following the ups and downs of weekly variations. However the problem is I have no idea of how to get Access to do this. I can create a report based on my original crosstab no problem, but I can't calculate the average for each period without some serious manual field property setting. Also, it doesn't put fields into the report for future weeks, so once the week turns over things go awry anyway.
The other problem is that sometimes the weekly value for the employee may be 0, which as you know returns a null value in the report. This the buggers up the averaging. I know that I need to use the Nz function to convert the null value to 0, but am not at all familiar with how to do it (or whether I can in a query or report).
Apologies for being long winded. Any help would be greatly appreciated, Iain. [end original post]
I have since managed a workaround which saves me doing too much manual entry work. This involves building a report with just the last four weeks' sales on it and re-naming the report fields, so that the calculated field which displays the average is always correct. I still have to change the data source for the four report fields (one for each week) manually when the week rolls over (e.g. from 9,8,7 & 6 to 10,9,8 & 7) and I am also having to take the average value and put this into a seperate spreadsheet. This means that I can not do a historical analysis, without changing the period the report covers manually and I just don't have the time...
I am sure that it must be possible to get Access to do it all for me, but it's a bit out of my league if I'm honest.
Again, apologies for long windedness! Thanks in advance, Iain.
BTW I run Access 2k on Win 2k Robbo ;-)