problem with slef join query please help me

[mtghr]

hi

this is how my table looks like

ITEM UOM PRICE
---- --- -----
ac10 EA 1.99
ac10 BX 6.99
ac10 CS 9.99

now i would like to get a output that would like this

ITEM UOM PRICE UOM1 PRICE1 UOM2 PRICE2
---- --- ----- ---- ------ ---- ------
AC10 EA 1.99 BX 6.99 CS 9.99

i build a quey in ms access and it doesnt work for me

this is how my query looks like
SELECT DISTINCT tblprice.ITEM, tblprice.UOM, tblprice_1.UOM, tblprice_1.PRICE, tblprice_2.UOM, tblprice_2.PRICE
FROM (tblprice INNER JOIN tblprice AS tblprice_1 ON tblprice.ITEM = tblprice_1.ITEM) INNER JOIN tblprice AS tblprice_2 ON tblprice_1.ITEM = tblprice_2.ITEM;

 

[dhookom]

You first need to create a ranking query [qrnkItemPrice] to get the 1,2,3,...

SELECT tblPrice.ITEM, tblPrice.UOM, tblPrice.PRICE, Count(tblPrice_1.ITEM) AS PriceNum
FROM tblPrice AS tblPrice_1 INNER JOIN tblPrice ON tblPrice_1.ITEM = tblPrice.ITEM
WHERE (((tblPrice_1.PRICE)>=[tblPrice].[PRICE]))
GROUP BY tblPrice.ITEM, tblPrice.UOM, tblPrice.PRICE;

Then create a table [tblPriceUOM] with single text field named [ColName] and two records with values
[tt][blue]
[ColName]
PRICE
UOM[/blue][/tt]
Then create a crosstab query with this SQL:

TRANSFORM First(IIf([ColName]="UOM",[UOM],[PRICE])) AS Expr2
SELECT qrnkItemPrice.ITEM
FROM qrnkItemPrice, tblPriceUOM
GROUP BY qrnkItemPrice.ITEM
PIVOT [ColName] & [PriceNum];

If you don't like the order of the columns, you can update the Column Headings property of the crosstab query.

TRANSFORM First(IIf([ColName]="UOM",[UOM],[PRICE])) AS Expr2
SELECT qrnkItemPrice.ITEM
FROM qrnkItemPrice, tblPriceUOM
GROUP BY qrnkItemPrice.ITEM
PIVOT [ColName] & [PriceNum] In ("UOM1","PRICE1","UOM2","PRICE2","UOM3","PRICE3");

Using the Column Headings limits the number of UOM to 3. You might need more.

Duane
Hook'D on Access
MS Access MVP

 

[mtghr]

ok i just treid to run the first query
and ms access doesnt like this part
Count(tblPrice_1.ITEM) AS PriceNumFROM tblPrice
gives me error on tblPrice

the Select satement includes a reserved word or an argument name that is misspelleed or missing or the punctuantion is incorrect

 

[dhookom]

When you pasted or entered the SQL, the carriage returns were not included. There should be a space between "PriceNum" and "FROM tblPrice"

Count(tblPrice_1.ITEM) AS PriceNum[highlight #FF99FF] [/highlight]FROM tblPrice

Make sure you understand how the SQL should work/look so you can correct any paste errors.

Duane
Hook'D on Access
MS Access MVP

 

[mtghr]

tanks a million i was i n a rush and didnt focus what i am doing now its great!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!