Crosstab Query To Populate Table 2

[SarasotaIT]

Hello,

I have a single table (tbl1) with 4 columns: StoreName, StoreOwnerID, ProdSold, Sales. It looks like this:

StoreName StoreOwnerID ProdSold Sales
-------------------------------------------------------------
Store1 001 100 1000
Store2 001 200 2000
Store1 002 300 3000
Store2 002 200 2000
Store3 002 300 3000
Store1 003 200 2000
Store2 003 300 3000

I am trying to build a query in Access that will take the data from this table and populate a second table (tbl2) that has the following record layout:
StoreOwnerID
StoreName1
ProdSold1
Sales1
StoreName2
ProdSold2
Sales2
StoreName3
ProdSold3
Sales3

so, tbl2 should look like this:

StoreOwnerID StoreName1 ProdSold1 Sales1 StoreName2 ProdSold2 Sales2 StoreName3 ProdSold3 Sales3
-----------------------------------------------------------------------------------------------------------------------------------
001 Store1 100 1000 Store2 200 2000
002 Store1 300 3000 Store2 200 2000 Store3 300 3000
003 Store1 200 2000 Store2 300 3000


This seems like it should be quite simple, but I admit, I am a newbie at SQL! :-/ Any help would be GREATLY appreciated!!

Thank you in advance!
Terry

 

[PHV]

Something like this (untested) ?
SELECT A.StoreOwnerID
, A.StoreName As StoreName1, A.ProdSold As ProdSold1, A.Sales As As Sales1
, B.StoreName As StoreName2, B.ProdSold As ProdSold2, B.Sales As As Sales2
, C.StoreName As StoreName3, C.ProdSold As ProdSold3, C.Sales As As Sales3
FROM (Sales A
LEFT Join (SELECT * FROM Sales WHERE StoreName="Store2") B ON A.StoreOwnerID=B.StoreOwnerID)
LEFT Join (SELECT * FROM Sales WHERE StoreName="Store3") C ON A.StoreOwnerID=C.StoreOwnerID
WHERE A.StoreName="Store1"
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

There is a FAQ on generating multiple-columns in a crosstab query that would also work here.

If using PHV's solution and there are possibly store owners with store2 and no store1, I would use (Select Distinct StoreOwnerID) as the first FROM "table" and then Left Join the other 3 (aliased above as A, B, and C) to it.

John

http://www.interautomateconsulting.com

 

[SarasotaIT]

Thank you very much PH! I will give it a shot. Also, John, you are correct in that there are store owners with store2 and no store1. so, if i get you correctly, the FROM should read:

FROM ((Select Distinct StoreOwnerID from Sales) A LEFT Join (SELECT * FROM Sales WHERE StoreName...etc?

Are the parenths correct? (sorry for my SQL syntax butchery!)

Thanks to you both!
Terry

 

[PHV]

You may try this:
SELECT A.StoreOwnerID
, A.StoreName As StoreName1, A.ProdSold As ProdSold1, A.Sales As As Sales1
, B.StoreName As StoreName2, B.ProdSold As ProdSold2, B.Sales As As Sales2
, C.StoreName As StoreName3, C.ProdSold As ProdSold3, C.Sales As As Sales3
FROM (((SELECT DISTINCT StoreOwnerID FROM Sales) S
LEFT Join (SELECT * FROM Sales WHERE StoreName="Store1") A ON S.StoreOwnerID=A.StoreOwnerID)
LEFT Join (SELECT * FROM Sales WHERE StoreName="Store2") B ON S.StoreOwnerID=B.StoreOwnerID)
LEFT Join (SELECT * FROM Sales WHERE StoreName="Store3") C ON S.StoreOwnerID=C.StoreOwnerID
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SarasotaIT]

Awesome, PH! Your sol'n works almost flawlessly...only has one small problem: If StoreOwnerID does not own a Store1, but owns a Store2 or Store3, StoreOwnerID is blank for that record in the query results. For instance, in my tbl1 above, for record 6, if you change Store1 to Store3, then StoreOwnerID doesn't show in the query results.

THANKS SO MUCH for all you have done thus far!!!
Terry

 

[JonFer]

Change the alias to S from A -> "SELECT S.StoreOwnerID, ..."

John

http://www.interautomateconsulting.com

 

[SarasotaIT]

WORKS PERFECTLY!! Thanks PHV and JonFer! AWESOME! You each get a star!
Terry