Crosstab or Pivot Frustration

[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?

[paii, Ron]

"Hansen" <he******@hotmail.comwrote in message
news:11*********************@o38g2000hse.googlegro ups.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.

[Fred Zuckerman]

"Hansen" <he******@hotmail.comwrote in message
news:11*********************@o38g2000hse.googlegro ups.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

[Hansen]

Thank you Fred.

Your approach has given me insight into crosstab queries and works
like a charm.

I was busy using case statements to select records by Region and then
populating unbound text boxes on the report. a Lot more work than this
approach!