Help Transposing Columns into rows in access

[memnac via AccessMonster.com]

Hello All,
Here is a sample data: -
Groups Centers Avail Set Rem
A JP 10 5 5
A PA 20 2 18
A WA 30 10 20
B JP 15 10 5
B PA 25 14 11
..

I would like to end up with this:
For Center JP
GrpA GrpB ... GrpZ
Avail 10 15 Havail
Set 5 10 Hset
Rem 5 5 Hrem

For Center PA
GrpA GrpB
Avail 20 25
Set 2 14
Rem 18 11

and so on ...

I have tried creating a crosstab query for each (Avail, Set and Rem) over
group range (could be A-Z) but don't know how to bring all together.
I have also looked at the microsoft forum on transposing into a table but
ideally can i achieve the goal in a query / sql without creating tables? Any
help will be much appreciated.

 

[Daryl S]

Memnac -

The data is not in the format you need for the crosstab to work as is. You
can still do this with two queries. The first will put the data in a format
that a crosstab can use, and the second is the crosstab you want.

The first query is a union query, since you need to have a field to store
the 'category' of the item (e.g. Avail, Set, Rem). It will be something
like this (put in your table name instead of GroupCenter):

SELECT GroupCenter.Groups, GroupCenter.Centers, "Avail" AS Category,
GroupCenter.Avail AS Amount
FROM GroupCenter
UNION ALL
SELECT GroupCenter.Groups, GroupCenter.Centers, "Set" AS Category,
GroupCenter.Set AS Amount
FROM GroupCenter
UNION ALL SELECT GroupCenter.Groups, GroupCenter.Centers, "Rem" AS Category,
GroupCenter.Rem AS Amount
FROM GroupCenter;

The crosstab is then based on this query, and will look something like this:

TRANSFORM Sum(GroupCenterUnionQry.Amount) AS SumOfAmount
SELECT GroupCenterUnionQry.Centers, GroupCenterUnionQry.Category,
Sum(GroupCenterUnionQry.Amount) AS [Total Of Amount]
FROM GroupCenterUnionQry
GROUP BY GroupCenterUnionQry.Centers, GroupCenterUnionQry.Category
PIVOT GroupCenterUnionQry.Groups;

Hope that helps!