Union in query not working correctly

[sean]

HI There,

I am trying to execute a union query which returns the total value of the
OptionPrice column (amongst other things) rolled up into on extra column. At
the moment the value is coming up underneath the rest of rows and I can't
access the value by creating a recordset. Is there a way that I can create
an alias column at runtime so I can use the value? Could someone help me out
with a little code help please?

Sean - thank in advance for your answer


PARAMETERS pOrderNumber Long;
SELECT 'Details' AS Source, [tmpproductoptions].[OptionID],
[tmpproductoptions].[OptionValue], [tmpproductoptions].[OptionPrice],
[tmpproductoptions].[OrderNumber], [product_options].[OptionID],
[product_options].[TaxRateID], [TaxRate].[tblTaxRateID], [TaxRate].[TaxRate]
FROM tmpproductoptions, product_options, TaxRate
WHERE [tmpproductoptions].[OrderNumber]=pOrderNumber And
[tmpproductoptions].[OptionID]=[product_options].[OptionID] And
[product_options].[TaxRateID]=[TaxRate].[tblTaxRateID]

UNION ALL SELECT 'Rollup', Null, Null, Sum(TheVal.OptionPrice), Null, Null,
Null,Null, Null
FROM tmpproductoptions TheVal
WHERE TheVal.[OrderNumber]=pOrderNumber
ORDER BY Source;


Source tmpproductoptions.OptionID OptionValue OptionPrice OrderNumber
product_options.OptionID TaxRateID tblTaxRateID TaxRate
Details 97 Chocolate 12 163773040 97 1 1 1.1
Details 96 Balloons 12 163773040 96 1 1 1.1
Rollup

24

 

[Michel Walsh]

Hi,

Use a sub-query in the SELECT clause?


SELECT a, b, c, d, ..., (SELECT SUM(z) FROM zomewhere)
FROM somewhere


Hoping it may help,
Vanderghast, Access MVP