crosstab query

[mtghr]

hi

i have a table that looks like that

SKU parent
--- ------
ab1 AA1
ab2 AA1
ab3 AA1

now i want a output that would look like that

parent sku1 sku2 sku3
----- ---- ---- ----
AA1 ab1 ab2 ab3

what i did was
i create it a query called qrnkrelated

SELECT related.parent, related.sku, Count(related_1.sku) AS skunum
FROM related INNER JOIN related AS related_1 ON related.parent = related_1.sku
GROUP BY related.parent, related.sku;


now i am trying to create the crosstab query but cant get it to work here is what i have meanwhile

TRANSFORM first(qrnkrelated.sku) AS SumOfsku
SELECT qrnkrelated.parent
FROM qrnkrelated, tblPriceUOM
GROUP BY qrnkrelated.parent
PIVOT [colname] & [skunum];


but i am not getting the right output

 

[dhookom]

First create a ranking query [qrnkSKU] with SQL of

SELECT Related.SKU, Related.Parent, Count(Related_1.SKU) AS Rank
FROM Related INNER JOIN Related AS Related_1 ON Related.Parent = Related_1.Parent
WHERE (((Related_1.SKU)<=[Related].[SKU]))
GROUP BY Related.SKU, Related.Parent;

Then create your crosstab from this query

TRANSFORM First(qrnkSKU.SKU) AS FirstOfSKU
SELECT qrnkSKU.Parent
FROM qrnkSKU
GROUP BY qrnkSKU.Parent
PIVOT "SKU" & [Rank];

Duane
Hook'D on Access
MS Access MVP

 

[mtghr]

now the output is ok my issues is that the fields look like that
SKU1 SKU10 SKU102 SKU11 SKU12

i wan to add a sort by that will give me
SKU1 SKU2 SKU3 etc

can you give me the syntax for that

 

[PHV]

Replace this:
PIVOT "SKU" & [Rank];
with this:
PIVOT "SKU" & Format([Rank], "000");

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mtghr]

still something wrong in some rows sku001 in empty and sku004 has value

shuoldt it allways start on 001???

 

[mtghr]

still something wrong in some rows sku001 in empty and sku004 has valueshuoldt it allways start on 001???

 

[dhookom]

How about sharing your actual SQL views of your queries. Maybe some sample data also. Is the combination of SKU and Parent unique as per your initial posting?

Duane
Hook'D on Access
MS Access MVP

 

[mtghr]

how can i send you my data???
my email address
moshe.rosenberg
@ gmail.com

email me where i can send you the data file?

 

[dhookom]

I just want you to try copy and paste the SQL views. It would also help if you type some records into the reply.

Duane
Hook'D on Access
MS Access MVP

 

[mtghr]

i fixed the issuue there ware some dublicatesd SKU i ran query to delte all the dublicates

 

[mtghr]

thank you vey much i got the handle on pivot and it really help me a lot

 

[mtghr]

i am trying to figure out now how to unpivot a table
my pivot table has 63 fields
SKU001 sku002 sku003 ...... SKU063


in order to unpivot them will i need to type all the fields names ir is there a easier way to do that

 

[dhookom]

Didn't the values start "unpivoted"? How about:

SELECT DISTINCT sku
FROM related;

Duane
Hook'D on Access
MS Access MVP

 

[mtghr]

the above code will not work since i need to exlcude SKU001 from the new table that i am trying to create so i am looking into unpivot all but sku001

 

[dhookom]

How about telling us what you have, what you want, and why? What value do you get from unpivoting? Again what was wrong prior to pivoting with the crosstab?

It doesn't make sense to me to use a query to pivot and then another query to unpivot. Why?

Duane
Hook'D on Access
MS Access MVP

 

[mtghr]

i wiill try to run a select query against this query

SELECT Related.SKU, Related.Parent, Count(Related_1.SKU) AS RankFROM Related INNER JOIN Related AS Related_1 ON Related.Parent = Related_1.ParentWHERE (((Related_1.SKU)<=[Related].[SKU]))GROUP BY Related.SKU, Related.Parent;

and will add where rank >1 this shuold do it