L
lin qin
I encounter a problem between pivot table and access.
In layout of pivot table, i put region,country to
the "page" area i put model number to the "row" area and
fisical week to the "column" area and revenue and quantity
to the "data" area, and i insert a calculated item call
revenue per unit(p.Rev) in the pivot table.
for example, revenue is 100 and quantity is 20 and i
get per unit revenue to be 50. However, when i use access
database, I design the following query: select
rev,qty,rev/qty as P.Rev from table group by
mod,region,country then the P.Rev I get from the query is
different from P.Rev from the pivot table calculated field,
the reason is P.Rev can't be aggregated, if we have two
records: 1.mod=1234, region=asia, country=china, rev=100,
qty=10,2.mod=1234, region=asia, country=china, rev=200,
qty=20, then calculated field P.Rev will return (100+200)/
(10+20)=10 and above database query will return
100/10+200/20=20.
can we redesign the query, so the query will return the
same result with calculated fields. thanks.
In layout of pivot table, i put region,country to
the "page" area i put model number to the "row" area and
fisical week to the "column" area and revenue and quantity
to the "data" area, and i insert a calculated item call
revenue per unit(p.Rev) in the pivot table.
for example, revenue is 100 and quantity is 20 and i
get per unit revenue to be 50. However, when i use access
database, I design the following query: select
rev,qty,rev/qty as P.Rev from table group by
mod,region,country then the P.Rev I get from the query is
different from P.Rev from the pivot table calculated field,
the reason is P.Rev can't be aggregated, if we have two
records: 1.mod=1234, region=asia, country=china, rev=100,
qty=10,2.mod=1234, region=asia, country=china, rev=200,
qty=20, then calculated field P.Rev will return (100+200)/
(10+20)=10 and above database query will return
100/10+200/20=20.
can we redesign the query, so the query will return the
same result with calculated fields. thanks.