You tried to execute a query that does not include the specified expression...

I originally thought this problem was with my report so I posted it under the
reports section but not I'm seeing that it is a query problem so I am re-
posting it here. I ran in to some problems while trying to do a sum in my
report footer but had problems because I was trying to do a Sum of a Sum. So,
I created an expression in the query that my report is based on to do the Sum
for me. However, when I add the totals option to the query and select sum
underneath the On and Off Std Hours, it doesn't sum it up for me in the
report. It just shows the last entry. So, I tried to manually create
it by placing the following in a new field:

On Sum: Sum([On Std Hours])

When I try to run that I receive the following message:

You tried to execute a query that does not include the specified expression
'Supervisor' as part of an aggregate function.

I can select for the Supervisor field to not be shown and the error message
returns with the next field name in line. I've included my SQL code below.
Thanks in advance for any help you can offer. This is driving me insane!

SELECT [Weekly Data].Supervisor, [Weekly Data].Date, [Weekly Data].Day,
[Employee Data].[Current Pay Rate], [Employee Data].[Clock Number] AS
[Employee Data_Clock Number], [Weekly Data].[Job Change], [Weekly Data].
[Contract Number], [Employee Data].Employee AS [Employee Data_Employee],
[Operation Listing Data Field].[Unit Hour], [Operation Listing Data Field].
[Hour Unit], [Operation Listing Data Field].Rate, [Operation Listing Data
Field].Description, [Operation Listing Data Field].[Op Number], [Employee
Data].[Pay Rate], [Employee Data].Bonus AS [Employee Data_Bonus], [Employee
Data].[Current Pay Rate] AS [Employee Data_Current Pay Rate], [Weekly Data].
[Clock Number] AS [Weekly Data_Clock Number], [Weekly Data].Employee AS
[Weekly Data_Employee], [Weekly Data].[Hourly Rate], [Weekly Data].Bonus AS
[Weekly Data_Bonus], [Weekly Data].[Current Pay Rate] AS [Weekly Data_Current
Pay Rate], [Weekly Data].[Op Number], [Weekly Data].Description, [Weekly Data]

.Code, [Weekly Data].Contract, [Weekly Data].Rate AS [Weekly Data_Rate],
[Weekly Data].Quantity, [Weekly Data].[On Std Hours], [Weekly Data].[Off Std
Hours], Sum([On Std Hours]) AS [On Sum], Sum([Off Std Hours]) AS [Off Sum]
FROM [Operation Listing Data Field] INNER JOIN ([Employee Data] INNER JOIN
[Weekly Data] ON [Employee Data].[Clock Number] = [Weekly Data].[Clock Number]

) ON ([Operation Listing Data Field].[Op Number] = [Weekly Data].[Op Number])
AND ([Operation Listing Data Field].[Op Number] = [Weekly Data].[Op Number]);

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201004/1

The problem is you can not have your cake and eat it too. If you sum, count,
avg or any other function that rolls data up then those fields that you do
not do these functions to must be set to Group By so as to roll them up also.

You can sum in a separate query and join or use DSum function.

--
Build a little, test a little.


"Nancy via AccessMonster.com" wrote:

I originally thought this problem was with my report so I posted it under the
reports section but not I'm seeing that it is a query problem so I am re-
posting it here. I ran in to some problems while trying to do a sum in my
report footer but had problems because I was trying to do a Sum of a Sum. So,
I created an expression in the query that my report is based on to do the Sum
for me. However, when I add the totals option to the query and select sum
underneath the On and Off Std Hours, it doesn't sum it up for me in the
report. It just shows the last entry. So, I tried to manually create
it by placing the following in a new field:

On Sum: Sum([On Std Hours])

When I try to run that I receive the following message:

You tried to execute a query that does not include the specified expression
'Supervisor' as part of an aggregate function.

I can select for the Supervisor field to not be shown and the error message
returns with the next field name in line. I've included my SQL code below.
Thanks in advance for any help you can offer. This is driving me insane!

SELECT [Weekly Data].Supervisor, [Weekly Data].Date, [Weekly Data].Day,
[Employee Data].[Current Pay Rate], [Employee Data].[Clock Number] AS
[Employee Data_Clock Number], [Weekly Data].[Job Change], [Weekly Data].
[Contract Number], [Employee Data].Employee AS [Employee Data_Employee],
[Operation Listing Data Field].[Unit Hour], [Operation Listing Data Field].
[Hour Unit], [Operation Listing Data Field].Rate, [Operation Listing Data
Field].Description, [Operation Listing Data Field].[Op Number], [Employee
Data].[Pay Rate], [Employee Data].Bonus AS [Employee Data_Bonus], [Employee
Data].[Current Pay Rate] AS [Employee Data_Current Pay Rate], [Weekly Data].
[Clock Number] AS [Weekly Data_Clock Number], [Weekly Data].Employee AS
[Weekly Data_Employee], [Weekly Data].[Hourly Rate], [Weekly Data].Bonus AS
[Weekly Data_Bonus], [Weekly Data].[Current Pay Rate] AS [Weekly Data_Current
Pay Rate], [Weekly Data].[Op Number], [Weekly Data].Description, [Weekly Data]

.Code, [Weekly Data].Contract, [Weekly Data].Rate AS [Weekly Data_Rate],
[Weekly Data].Quantity, [Weekly Data].[On Std Hours], [Weekly Data].[Off Std
Hours], Sum([On Std Hours]) AS [On Sum], Sum([Off Std Hours]) AS [Off Sum]
FROM [Operation Listing Data Field] INNER JOIN ([Employee Data] INNER JOIN
[Weekly Data] ON [Employee Data].[Clock Number] = [Weekly Data].[Clock Number]

) ON ([Operation Listing Data Field].[Op Number] = [Weekly Data].[Op Number])
AND ([Operation Listing Data Field].[Op Number] = [Weekly Data].[Op Number]);

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201004/1

.