Parameter Problem

[ooch1]

Hello

Does anyone know how i can calculate 2 calculated fields in the same query, without asking for the parameters.

Basically, i've got vat, price per unit and total cost fieldnames. The total cost is just adding the other 2 and works, but always asks for the parameters??

OOch

 

[PHV]

Can you please post the SQL code and the tables schema ?

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

 

[ooch1]

PHV - Here is the code, but obviously there is a little more it than previosly mentioned, but in essence it is doing the same thing.

SELECT Sum([002-NBS_GAS_ROOTDATA_20040923]![VATDailyUnit]*365) AS VATCharge,
[002-NBS_GAS_ROOTDATA_20040923].Price,
[002-NBS_GAS_ROOTDATA_20040923]![MeterPointAQ])*([002-NBS_GAS_ROOTDATA_20040923]![Price])/100 AS AnnualRevenue, [AnnualRevenue]+[VATCharge] AS TotalRevenue

FROM [002-NBS_GAS_ROOTDATA_20040923]

GROUP BY [002-NBS_GAS_ROOTDATA_20040923].[Group ref], [002-NBS_GAS_ROOTDATA_20040923].[Group Name], [002-NBS_GAS_ROOTDATA_20040923].MeterPointRef, [002-NBS_GAS_ROOTDATA_20040923].MeterPointAQ, [002-NBS_GAS_ROOTDATA_20040923].Price, [AnnualRevenue]+[VATCharge]

 

[PHV]

Something like this ?
SELECT [Group ref], [Group Name], MeterPointRef, MeterPointAQ, Price
, MeterPointAQ * Price AS AnnualRevenue
, 365*Sum(VATDailyUnit) AS VATCharge
, (MeterPointAQ * Price) + 365*Sum(VATDailyUnit) AS TotalRevenue
FROM [002-NBS_GAS_ROOTDATA_20040923]
GROUP BY [Group ref], [Group Name], MeterPointRef, MeterPointAQ, Price
;

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