help need it with self join query

[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;

 

[MarkK]

use these search terms ...

This will need to be a "Crosstab" query. The SQL for a crosstab query contains the keyword "TRANSFORM".