Critera in Append query 2

[TimTDP]

In Access 2000, I have a table with the following fields:
ProductId - byte
ShippingId - byte
Price - single

I have 2 ShippingId's. This will not increase!

I need to output this, either in a table but preferably in a query, with the following fields

ProductId - byte
PriceShippingId=1 - single
PriceShippingId=2 - single

I can do this via VBA but would rather do it via a query, and use the field ShippingId as criteria

How can I do this?

 

[PHV]

Something like this ?
SELECT A.ProductId, A.Price AS ShippingPrice1, B.Price AS ShippingPrice2
FROM yourTable AS A INNER JOIN yourTable AS B ON A.ProductId = B.ProductId
WHERE A.ShippingId = 1 AND B.ShippingId = 2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TimTDP]

Thanks PHV

The result is what I am looking for, but not all records are returned!

 

[PHV]

Perhaps this (ac2k or above) ?
SELECT U.ProductId, Sum(U.Price1) AS ShippingPrice1, Sum(U.Price2) AS ShippingPrice2
FROM (
SELECT ProductId, Price AS Price1, 0.0 AS Price2 FROM yourTable WHERE ShippingId = 1
UNION ALL SELECT ProductId, 0.0, Price FROM yourTable WHERE ShippingId = 2
) AS U
GROUP BY U.ProductId

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TimTDP]

a last question!

the resulting output of ShippingPrice1 & ShippingPrice2 shows multiple decimal places, even though the original data is only 2 decimal places. ie, 28.38 is displayed as 28.3799991607666
how can I keep it to the actual data - 28.38. This is important as I want to use this data for comparisons

 

[Golom]

If possible, modify the data types in your tables to the Currency data type. They are probably now Single or Double Data Types and those are only approximate values ... not guaranteed to be precise.

If you can't modify the tables then

SELECT U.ProductId, Sum(U.Price1) AS ShippingPrice1, Sum(U.Price2) AS ShippingPrice2
FROM (
SELECT ProductId, [red]CCur([/red]Price[red])[/red] AS Price1, [red]CCur([/red]0.0[red])[/red] AS Price2 FROM yourTable WHERE ShippingId = 1
UNION ALL SELECT ProductId, [red]CCur([/red]0.0[red])[/red], [red]CCur([/red]Price[red])[/red] FROM yourTable WHERE ShippingId = 2
) AS U
GROUP BY U.ProductId