Turning a row into a column?

[graedus]

Hi,

I have a particular table --

Store	Product			Quantity
4241	Perdue Fryers		1
4242	Perdue Fryers		2
4243	Perdue Fryers		6
4244	Perdue Fryers		3
4246	Perdue Fryers		2
4241	Perdue Breast		3
4242	Perdue Breast		1
4243	Perdue Breast		0
4244	Perdue Breast		6
4246	Perdue Breast		4
4241	Perdue Boneless Breast	5
4242	Perdue Boneless Breast	4
4243	Perdue Boneless Breast	1
4244	Perdue Boneless Breast	2
4246	Perdue Boneless Breast	2

What I'd like to do via queries is display the table to a report as a recourdsource like this:

Product			4241	4242	4243	4244	4246
Perdue Fryers		1	2	6	3	2
Perdue Breast		3	1	0	6	4
Perdue Boneless Breast	5	4	1	2	2

Is this possible at all, or should I just hardcore my store numbers as columns in a separate table and use that one instead?

It seems like there should be a method to do this, but I certainly haven't been able to find it. Any help is greatly appreciated.

 

[Jack Cowley]

I am NOT an expert in this area but take a look at a Crosstab Query as I think this is what you are after....

hth,
Jack

 

[graedus]

Thanks, that looks like the right direction! I downloaded a few crosstab examples and am fiddling with them. If anyone has further advice I would greatly appreciate it.

Thanks!

 

[graedus]

Got it!

TRANSFORM Avg(tblOrders.Quantity) AS AvgOfQuantity
SELECT tblOrders.PID, tblOrders.Product, tblOrders.Vendor
FROM tblOrders
GROUP BY tblOrders.PID, tblOrders.Product, tblOrders.Vendor
PIVOT tblOrders.StoreNumber;

is what I was looking for. Thanks a lot, Jack.

 

[Jack Cowley]

I am glad you were able to get it to work because Crosstab queries have always been a mystery to me!

Jack