Aggregate Function in a query

  • Thread starter Thread starter Nona
  • Start date Start date
N

Nona

The crosstab query below works. However, I would like to add the following
condition to the query:
TotInv: IIf([InvType]="Cost
Invoice",[TotNonUCRClaim],([ServCodeRate]*[UnitsUsed]))

However, when I add the condition, I get an error that says: "You tried to
execute a query that does not include the expression…as part of a aggregate
function."

TotInv: IIf([InvType]="Cost
Invoice",[TotNonUCRClaim],([ServCodeRate]*[UnitsUsed]))

This SQL for the query works fine until I try to add the condition:
TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) AS TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]) AS TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth In
("July","August","September","October","November","December","January","February","March","April","May","June");

Is there anything I can do to make this work?

Thanks in advance!
 
Were you trying to add it like this ---

TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) AS TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum(IIf([InvType]="Cost
Invoice",[TotNonUCRClaim],([ServCodeRate]*[UnitsUsed])) AS TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth In
("July","August","September","October","November","December","January","February","March","April","May","June");
 
In design mode because I am not proficient in SQL statements. I did some
research and one source seemed to say that the aggregate function needed to
be defined in the opening TRANSFORM statement.
I tried to do that but was not successful.

--
Nona


KARL DEWEY said:
Were you trying to add it like this ---

TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) AS TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum(IIf([InvType]="Cost
Invoice",[TotNonUCRClaim],([ServCodeRate]*[UnitsUsed])) AS TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth In
("July","August","September","October","November","December","January","February","March","April","May","June");

--
Build a little, test a little.


Nona said:
The crosstab query below works. However, I would like to add the following
condition to the query:
TotInv: IIf([InvType]="Cost
Invoice",[TotNonUCRClaim],([ServCodeRate]*[UnitsUsed]))

However, when I add the condition, I get an error that says: "You tried to
execute a query that does not include the expression…as part of a aggregate
function."

TotInv: IIf([InvType]="Cost
Invoice",[TotNonUCRClaim],([ServCodeRate]*[UnitsUsed]))

This SQL for the query works fine until I try to add the condition:
TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) AS TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]) AS TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth In
("July","August","September","October","November","December","January","February","March","April","May","June");

Is there anything I can do to make this work?

Thanks in advance!
Click to expand...
Click to expand...
 
Did you try the SQL I posted?
--
Build a little, test a little.


Nona said:
In design mode because I am not proficient in SQL statements. I did some
research and one source seemed to say that the aggregate function needed to
be defined in the opening TRANSFORM statement.
I tried to do that but was not successful.

--
Nona


KARL DEWEY said:
Were you trying to add it like this ---

TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) AS TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum(IIf([InvType]="Cost
Invoice",[TotNonUCRClaim],([ServCodeRate]*[UnitsUsed])) AS TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth In
("July","August","September","October","November","December","January","February","March","April","May","June");

--
Build a little, test a little.


Nona said:
The crosstab query below works. However, I would like to add the following
condition to the query:
TotInv: IIf([InvType]="Cost
Invoice",[TotNonUCRClaim],([ServCodeRate]*[UnitsUsed]))

However, when I add the condition, I get an error that says: "You tried to
execute a query that does not include the expression…as part of a aggregate
function."

TotInv: IIf([InvType]="Cost
Invoice",[TotNonUCRClaim],([ServCodeRate]*[UnitsUsed]))

This SQL for the query works fine until I try to add the condition:
TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) AS TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]) AS TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth In
("July","August","September","October","November","December","January","February","March","April","May","June");

Is there anything I can do to make this work?

Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
Yes, I did, but got the same error. Meantime I have just now (after many many
hours!) figured out another way to accomplish this. Thanks for your time,
though. I sincerely appreciate this site, your expertise, and the donation of
your time and energy.


Nona


KARL DEWEY said:
Did you try the SQL I posted?
--
Build a little, test a little.


Nona said:
In design mode because I am not proficient in SQL statements. I did some
research and one source seemed to say that the aggregate function needed to
be defined in the opening TRANSFORM statement.
I tried to do that but was not successful.

--
Nona


KARL DEWEY said:
Were you trying to add it like this ---

TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) AS TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum(IIf([InvType]="Cost
Invoice",[TotNonUCRClaim],([ServCodeRate]*[UnitsUsed])) AS TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth In
("July","August","September","October","November","December","January","February","March","April","May","June");

--
Build a little, test a little.


:

The crosstab query below works. However, I would like to add the following
condition to the query:
TotInv: IIf([InvType]="Cost
Invoice",[TotNonUCRClaim],([ServCodeRate]*[UnitsUsed]))

However, when I add the condition, I get an error that says: "You tried to
execute a query that does not include the expression…as part of a aggregate
function."

TotInv: IIf([InvType]="Cost
Invoice",[TotNonUCRClaim],([ServCodeRate]*[UnitsUsed]))

This SQL for the query works fine until I try to add the condition:
TRANSFORM Sum([ServCodeRate]*[UnitsUsed]) AS TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]) AS TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth In
("July","August","September","October","November","December","January","February","March","April","May","June");

Is there anything I can do to make this work?

Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top