MS Access 2003 - Calculating an average based on qty sold/per site with supply %

Question

Here is another question I have about being able to calculate this scenario in Access, or even at all for that matter:

I have a query that find the TOP 5 items sold in a given timeframe, and it groups by site. I use this to create a comparative chart between the site for ppt presentations. I do a lot of these but I have a problem with the presentation that I foresee they will have a problem with and it makes for bad metrics:

Some stores are bigger than others, and get much more supply. So a straight aggregate total of just qty of toping selling items, and comparing the locations is stacking the deck a little.

So if Site A gets 80% of the supply, and sells 500, Site B gets 15% supply and sell 75, and site C get 5% supply and sells 50 items, then Site C actually has the best sales for their size. I have exactly what I need in terms in the first chart (from my queries and such) to show the aggregate total, but what do I need to represent the idea mentioned above.

The factors that I have that go into this are:

ItemID - group by Item - group by qty sold - sum/descending (which is the variable that determines the Top 5) Store/Location - Group By

and then I run a seperate query to get the total deliveries (supply) to each site

I realize that this may just be a lack of mathmatical understanding on my part, but can anyone help with this?

thanks

Answer 1

The first issue that I see isn't about SQL savvy; it's how to serve your data customer. What does he or she want to see? Metrics is a term with a holy ring, and for good reason: it's supposed to be what is used for the big business decisions, and it's scary easy to measure the wrong thing.

So I'd make sure I know what my customer wants. If you can't model it on a spreadsheet, you won't be able to develop your reporting effectively.

Every deck of cards is loaded. You have to know how they want it loaded.