sql in MS Access - Ranking Query and running YTD Calculation

[GrantHarrison]

I have Data (scores) for Shops (Sites) across the country.

These Sites are assessed by Mystery Shoppers who submit scores for each of the areas within a Site e.g.
Customer Toilets, Shop Layout, Availabality of Staff, etc...

Each month I have to rank these sites according to their overall performance. The problem I have is
that Sites can join the program - this assessment of their various components - at any month throughout
the year.

Fortunately the program runs from January to December in 2001, but as I said, sites can join at any
time - this means I have to create a Query that will calculate their Average Performance (Year To Date) divisable
by the number of months they have been in the program.

Further more, each area within the Sites is not measure wevery month. For example, Site 1 will have 7
areas that are assessed but only 3 are assessed every month, the rest ALTERNATE with each other according to a set
schedule, thereby causing some areas to be assessed for a total of 6 times throughout the year.

I need a Query that will "do the stuff" - do all of the above and I am using Access so I can use the Design Query
view but at the moment I am stumped... have I made it clear enough what is required? I call it "Rank and Position,
Year To Date" analysis. Or simply "Ranking YTD".

Many thanks

 

[MichaelRed]

Without the specific schema of the tables involved, only a very general process can be described.

I would use seperate queries to calculate the YTD score based on the number of entries. Of couorse, that presupposes that you have/can segregate the individual 'stores?' bu the number of scores entered for the store. this is reasonable done with the aggregate count function, which (coonveniently?) also yields the divisor for the average.

However, depending on the schema, it MAY be possible to do the entire thing with a simple (AVG) aggregate query.

MichaelRed
m.red@att.net

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