Puzzle with a Crosstab Query

not really sure what you are looking for, try this

TRANSFORM Avg(tblPrices.CostPrice) AS AvgOfCostPrice
SELECT tblproducts.productcode & " --> " & tblproducts.description AS Expr1
FROM (tblPrices INNER JOIN tblProducts ON tblPrices.ProductCode = tblProducts.ProductCode) INNER JOIN tblSuppliers ON tblPrices.SupplierID = tblSuppliers.ID
Where tblPrices.Use_Price=True
GROUP BY tblproducts.productcode & " --> " & tblproducts.description
PIVOT tblSuppliers.SupplierName;

Thanks capricorn1,

Not quite what I wanted.
I'll clarify if I can.

I want the crosstab to show all prices from all suppliers.
But since I have flagged one supplier (likely to be the cheapest!) as the one specific supplier for that specific product.

So, I want to see ALL prices (as my form currently shows) but I would like the chosen supplier highlighted somehow (using the crosstab format).

i don't think that will be possible by just using the query.

See SQL below which works in a very crude way.
I linked the two fields "Use_price" and "CostPrice" into one single field.

This way the price chosen is prefixed by a -1  (!).  So for instance a price of 555 is shown as -1555.

Very ugly but it does work.
Do you thinks there is a tidier way based on this logic??



TRANSFORM Avg([use_price] & [CostPrice]) AS Expr3
SELECT [tblproducts].[productcode] & " --> " & [tblproducts].[description] AS Expr1
FROM (tblPrices INNER JOIN tblProducts ON tblPrices.[ProductCode] = tblProducts.ProductCode) INNER JOIN tblSuppliers ON tblPrices.SupplierID = tblSuppliers.ID
GROUP BY [tblproducts].[productcode] & " --> " & [tblproducts].[description]
PIVOT tblSuppliers.[SupplierName];

Thanks again.

Clever

vg