Crosstab or Pivot Frustration
am 31.10.2007 10:39:00 von Hansen
Hi
I have one table with the following design:
Fieldname Datatype
Region Text
Supply Number
Sales Number
Revision Number
Refused Number
SoldOut Number
I want to be able to report the data in the following format:
Total Region1 Region2 Region3 Region4
Supply
Sales
Revision
Refused
Soldout
I have been attempting this using crosstab queries and pivot tables to
no avail. I think I don't understand the concept. Can someone please
assis me?
Re: Crosstab or Pivot Frustration
am 31.10.2007 15:54:50 von none
"Hansen" wrote in message
news:1193823540.746907.84700@o38g2000hse.googlegroups.com...
> Hi
>
> I have one table with the following design:
> Fieldname Datatype
> Region Text
> Supply Number
> Sales Number
> Revision Number
> Refused Number
> SoldOut Number
>
> I want to be able to report the data in the following format:
> Total Region1 Region2 Region3 Region4
> Supply
> Sales
> Revision
> Refused
> Soldout
>
> I have been attempting this using crosstab queries and pivot tables to
> no avail. I think I don't understand the concept. Can someone please
> assis me?
>
Your table structure needs normalization.
Region InformationType Datavalue
-------- ---------------- ---------
Region1 Supply 1
Region1 Sales 2
Region1 Revision 3
Region1 Refused 4
Region1 SoldOut 5
Region2 Supply 6
....
With this table structure the Cross tab wizard could build a query to
produce your the required output.
Re: Crosstab or Pivot Frustration
am 31.10.2007 15:58:24 von Fred Zuckerman
"Hansen" wrote in message
news:1193823540.746907.84700@o38g2000hse.googlegroups.com...
> Hi
>
> I have one table with the following design:
> Fieldname Datatype
> Region Text
> Supply Number
> Sales Number
> Revision Number
> Refused Number
> SoldOut Number
>
> I want to be able to report the data in the following format:
> Total Region1 Region2 Region3 Region4
> Supply
> Sales
> Revision
> Refused
> Soldout
>
> I have been attempting this using crosstab queries and pivot tables to
> no avail. I think I don't understand the concept. Can someone please
> assis me?
Well, I'll get you started with one approach. There are probably others.
It uses two queries.
The first UNION query restructures you data into a normalized layout:
SELECT Region, "Supply" AS Category, Supply AS Result FROM Table1
UNION
SELECT Region, "Sales" AS Category, Sales AS Result FROM Table1
UNION
SELECT Region, "Revision" AS Category, Revision AS Result FROM Table1
UNION
SELECT Region, "Refused" AS Category, Refused AS Result FROM Table1
UNION
SELECT Region, "SoldOut" AS Category, SoldOut AS Result FROM Table1;
The second CROSSTAB query uses the first query:
TRANSFORM Sum(Result) AS SumOfResult
SELECT Category
FROM Query1
GROUP BY Category
PIVOT Region;
You'll have to do some more work if you want to show "totals".
Fred Zuckerman