I
Indra
Hi There,
I migrated mdb database to sql and frot end adp file. I have some
problem running query, form and report. in my querry i have created a
expression say:
CASE WHEN ([Items].[BonusCategory] = 'Service') THEN DiscountedPrice
ELSE 0 END
CASE WHEN ([Items].[BonusCategory] = ' Mechanical') THEN
DiscountedPrice ELSE 0 END
CASE WHEN ([Items].[BonusCategory] = 'Expendable') THEN
DiscountedPrice ELSE 0 END
And give them name in Alias as Service, Mechanical and Expendable.
Till this its working fine. In my report i was to use these three
colum to calculate the sum as below:
=Sum(([Service]*([ElectriclineBonus]/100))+([Mechanical]*[SlicklineBonus]/100)+([Expendable]*[ConsumableBonus]/100)),
but when i run the report its display the error message say,
"Aggregate function are only allowed on output fields or the record
source".
What i come to know (not sure) to use the aggregate function the
colume or field which we want to use should be there in talbe or query
as a output fields. Now i wanted to put one more expression in my
query with the abouve sum function "
Sum(([Service]*([ElectriclineBonus]/100))+([Mechanical]*[SlicklineBonus]/100)+([Expendable]*[ConsumableBonus]/100)),
but its again giving me error message saying "Service", Mechanical,
Expendable , using in the expression are not the part of the query. I
am really looking how we can solve this problem. Is there any way to
use name in alias as a coulum or expression out or expression.
Thanks in advance.
Indra.
I migrated mdb database to sql and frot end adp file. I have some
problem running query, form and report. in my querry i have created a
expression say:
CASE WHEN ([Items].[BonusCategory] = 'Service') THEN DiscountedPrice
ELSE 0 END
CASE WHEN ([Items].[BonusCategory] = ' Mechanical') THEN
DiscountedPrice ELSE 0 END
CASE WHEN ([Items].[BonusCategory] = 'Expendable') THEN
DiscountedPrice ELSE 0 END
And give them name in Alias as Service, Mechanical and Expendable.
Till this its working fine. In my report i was to use these three
colum to calculate the sum as below:
=Sum(([Service]*([ElectriclineBonus]/100))+([Mechanical]*[SlicklineBonus]/100)+([Expendable]*[ConsumableBonus]/100)),
but when i run the report its display the error message say,
"Aggregate function are only allowed on output fields or the record
source".
What i come to know (not sure) to use the aggregate function the
colume or field which we want to use should be there in talbe or query
as a output fields. Now i wanted to put one more expression in my
query with the abouve sum function "
Sum(([Service]*([ElectriclineBonus]/100))+([Mechanical]*[SlicklineBonus]/100)+([Expendable]*[ConsumableBonus]/100)),
but its again giving me error message saying "Service", Mechanical,
Expendable , using in the expression are not the part of the query. I
am really looking how we can solve this problem. Is there any way to
use name in alias as a coulum or expression out or expression.
Thanks in advance.
Indra.