Here's one for the Access masters out there.

[Robbo1974]

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 ;-)

 

[MichaelRed]

I fell asleep somewhere in paragrapg 3 (I think). It was never clear to me where the various values were calculated, or wheather they were part of the same report / output object. In a VERY general sense, I would probably do the averages in a seperate query which would alais the fields to be used for the Chart. Use the querry for the chart?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Robbo1974]

The weekly sales values are calculated in my original crosstab.

I can't at present calculate the averages, without manually setting field properties which reference the week numbers in the original crosstab.

If I did that, the field data source property bit would look something like this i suppose:

[Average[sub]n[/sub]] = ([week[sub]n[/sub]]+[week[sub]n-1[/sub]]+[week[sub]n-2[/sub]]+[week[sub]n-3[/sub]])/4

Where n is the week number in my original crosstab.

I want to export a value of Average[sub]n[/sub] for each value of n to a seperate report/chart object.

Any clearer?

Robbo ;-)

 

[MichaelRed]

Some, not enough.

It would be 'better' if I knew the ACTUAL Fields in the source table and the SQL for the crosstab query.

It would be 'more better' if I had a SAMPLE of the source table and the SQL for the crosstab query.

It is possible to alais the Week names (or otherwise force them) to a consistient set of names for use in an additional query to get the resuults, bt without knowing the field names and their derivation, I am to foggey to really try.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Robbo1974]

Thanks for your patience Michael, here's the info:

SQL for Crosstab:

[/code]TRANSFORM Sum(qryRollingAverage1.Value) AS SumOfValue
SELECT qryRollingAverage1.Employee, Sum(qryRollingAverage1.Value) AS [Total Of Value]
FROM qryRollingAverage1
GROUP BY qryRollingAverage1.Employee
PIVOT Format([Date],"ww"[/code]

The crosstab references a select query (qryRollingAverage1) which selects the following fields from three tables:

tblEmployees: [Employee] 'Employee's Name

tblOrders1: [Date] 'Date of Order, limited to last six months

tblOrderDetails: [Value] 'Sales value of Order

I can email sample data and query if you'd prefer.

Thanks again, Iain Robbo ;-)

 

[AMeyer]

I haven't worked with cross tab queries, but I can get what I think you want from a standard query. If I'm reading your post correctly this query should give you the starting point:

Select Employee, Format([Date],"ww&quot AS Week,
sum(Value) As ValueSum From qryRollingAverage1
Group by Employee, Format([Date],"ww"

This should give you The total for each employee for each week. Now for the good part, You self join the table and use each record from the first instance of the table to pick up each record from the second instance of the table that is within the last four records. It would look something like this if the query above was "base".

Select base.employee, base.week, base_1.week, base_1.valuesum
from base, base as base_1
where base.empoloyee=base_1 employee and base_1.week between (base.week-3) and base.week

This should give you a list of each employee, a week, and a week and value for the included weeks. It is an "exploded" view of what I think you're wanting. From there just average the base_1.valuesum column and you should get a "sliding window" rolling average with a window of 4 weeks.

I'll admit that my SQL is quite rusty, so if I've made a mistake in there I'll apologize in advance. I think that the concept is good, if a bit warped. If you'd like, mail me sample data and I'll send back a sample db. (Access 2K/Win 2K)

Andy Meyer
meyer@weatherwiseusa.com

 

[MichaelRed]

Robbo,

If the above is not enough, you could sen me a sample db to work with.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over