Multiple value fields in a crosstab query

[rahmad]

Dear All,
Hope there will be somebody help me to create a crosstab query
with multiple Value fields.

I want a datasheet like below

Reject Item Jan'07 Feb'07 Mar'07
Strike 5 0 6
Sensor 8 1 3
MalFunction 7 4 2
Broken 1 5 6
Dimension 6 8 9
Others 0 5 7

I have Table Reject Item Which is contain the name of reject.
One table Which have column Month and another 6 columns
( named with the name of reject ) with data type number.

I tried make a query
Reject Item as row heading
Expr1 ( year ) as column heading
and the value is ?????

Would any body help.

 

[Michel Walsh]

Probably COUNT(*), or, if there is a quantity in the original table,
SUM(qty). The SQL statement may look like:




TRANSFORM COUNT(*)
SELECT RejectItem
FROM originalTable
GROUP BY RejectItem
PIVOT Expr1



Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

So your table has fields that are something like

RejectItem
OccurenceMonth
Strike
Sensor
Malfunction
Broken
Dimension
Other

Good design would have been something more like
RejectItem
OccurenceMonth
RejectType (One of the above six fields)
RejectCount

With that design you crosstab would be easy to build.

You might try using a normalizing UNION query as the source of the crosstab

SELECT RejectItem, OccurenceMonth, Strike as RejCount, "Strike" as RType
FROM YourTable
UNION ALL
SELECT RejectItem, OccurenceMonth, Sensor, "Sensor"
FROM YourTable
UNION ALL
SELECT RejectItem, OccurenceMonth, Malfunction, "Malfunction"
FROM YourTable
....

Save that as qxTabSource

Then build your crosstab based on that query.

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