Multiple values in once cross-tab

J

Jumbo Shrimps, Jr.

Have 12 months of data,
most with nine groups.
Need to divide the sum of
all groups $ by
the sum of all Units
and array each month
as the column header.
Row header (not shown)
is the Office.
Data looks like this:

Dollar Unit $/Unit
Month GRP Value Value Value

Month1 Grp1 61295 115 533
Month1 Grp2 53295 95 561
Month1 Grp3 26055 45 579
Month1 Grp4 22163 37 599
Month1 Grp5 64785 105 617
Month1 Grp6 62426 98 637
Month1 Grp7 51903 79 657
Month1 Grp8 56700 84 675
Month1 Grp9 48774 66 739

Here is the current cross-tab expression
for value:
Expr1: SUM(([Dollar]/[Unit])) = 5597

However, the accurate result is:
Tot
Tot $ Units
SUM 447396 / 724 = 622

Expected Output:
Office Month1 Month2 Month3 Month4
New York 622 715 650 622 611 649
Oakland 642 745 350 421 522 929
etc.

There are always twelve months of data,
but not always nine groups to rolll up.
 
J

John Spencer

Pardon me but if you want to divide Sum Dollars by Sum Units I would think
the expression would be

Sum([Dollar]) / Sum([Unit])

Your expression is summing the average value for each group, while you want
the average value for the month

In the queries SQL view, you should have something like

TRANSFORM Sum([Dollar]) / Sum([Unit]) as AvgVal
SELECT OFFICE
FROM YourTable
GROUP BY Office
PIVOT Month

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Guest

I think 622 should be ~618. Try set your crosstab Value to:
TheValue: Sum([Dollar])/Sum([Unit])
and set the Totals line to Expression.
 
Top