Summarizing Weighted Averages in Access

[dewole]

Hello,

The following is an example of fields from a data set that I am trying
to manipulate and summarize with Ms-Access.

*Line of Business, *Business Unit, *Customer Segment, **Year,
**Quarter, **Month, **Week,Order, *Brand(where more than one brand can
be on an order), BrandUnits, BrandList Revenue, BrandCost

My desired output is a final report, form or chart that would be
flexible enough to display the Weighted Average Margin for any
combination of the fields which have * on the Y-Axis with the fields
which have ** on the X-Axis.

Eg. Weighted Average Margin for each Business Unit by Week
Weigthted Average Margin for each Line of Business by Month

The only way I know to do this know would involve building a series of
make table queries for all possible combinations of the data. I am
trying to avoid this approach because my core data set already has
more than 100,000 records.

I am using the Access for Dummies as a reference but so far it has not
helped me with this particular problem.

My appologies in advance if my request was unclear and thank you for
taking the time to help.

Dewole
Happy New Year to all.

 

[Tom Ellison]

Dear Dewole:

I suggest you write a query that performs any one of the combinations
you want to do. Assuming you are using Jet for your database engine,
it will be a crosstab query, so try the crosstab query wizard. Next,
create a different combination, and compare the SQL of the two
queries. This will help you learn how the varying parameters you need
"plug in" to the query.

You can create the varying SQL strings with VBA code on the event that
opens your report, form, or chart.

This may or may not be particularly easy, depending on your skill at
programming this. I suggest you take a look at it and come back when
you have some specific questions about implementing it.

I do not rule out the possibility of writing a single query that
references the controls on your form and figures out how to perform
the crosstab for all these combinations. We can try to persue that as
well, up to the point where you make a decision which route seems
best.

At a minimum, post back the crosstab queries to a couple of the
combinations that you perform, assuming you can get that far.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts