andr3w

You can use CASE, together with MAX to get what you need in one shot.For each Price Level the case statement gives NULL when the price level does not match, these get ignored by the MAX. SELECT InvoiceNumber, c.ContractNumber, c.ItemID, YearlyPrice, MAX(CASE WHEN fkPriceLevel=18 THEN FlatPrice END) AS PriceAt18, MAX(CASE WHEN fkPriceLevel=19 THEN FlatPrice END) AS PriceAt19FROM tvwr_ContractInvoices i, tblContractDetail c, tblPriceLevelOverride po, tblPriceLevels pWHERE i.ContractNumber = c.ContractNumber AND c.ItemID = po.ItemID AND po.FkPriceLevels = p.PriceLevelsKeyID AND i.InvoiceNumber = 31 GROUP BY InvoiceNumber, c.ContractNumber, c.ItemID, YearlyPrice,

You need the CASE statement. It work like this:SELECT NoName, CASE WHEN BLM_CONSOLIDATED=0 THEN 'No' ELSE 'Yes' END , CASE WHEN BLM_HOLDING_LIMITED=0 THEN 'No' ELSE 'Yes' END , etc...You need a case statement for each column.It's long-winded but it is in the standard and has been implement on most SQL platforms.For Access use the IIF functionIIF([bLM CONSOLIDATED]=0,'No','Yes')