Re: Crosstab or Pivot Frustration
- From: "Fred Zuckerman" <ZuckermanF@xxxxxxxxxxxxx>
- Date: Wed, 31 Oct 2007 14:58:24 GMT
"Hansen" <helgardh@xxxxxxxxxxx> wrote in message
news:1193823540.746907.84700@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
.
- References:
- Crosstab or Pivot Frustration
- From: Hansen
- Crosstab or Pivot Frustration
- Prev by Date: Re: Crosstab or Pivot Frustration
- Next by Date: Re: Package Installer
- Previous by thread: Re: Crosstab or Pivot Frustration
- Next by thread: "Enter parameter value" error when exporting to Excel (but not if to other formats)
- Index(es):
Relevant Pages
|
