Aggregate Function in a query

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!
 
K

KARL DEWEY

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");
 
N

Nona

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...
 
K

KARL DEWEY

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...
 
N

Nona

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional criteria in a query 4
Error - CANNOT HAVE AGGREGATE FUNCTION IN EXPRESSION 4
can't use an aggregate function in an expression? 5
Aggregate function error 2
How do I add a subquery and field in an aggregate query 1
Problem with SUM Query 5
run a make table query using parameters in multi-select list box 7
The "You tried to execute... aggregate function... blah" error... 2

Top