Re: error message.


Since you are using one of the aggregate functions (SUM) in the query, you need to group by all of the other fields that are not using the sum function.

SELECT [activity table].time
, [activity table].ID
, [activity table].patient
, [activity table].activity
, [activity table].[reason for cancellation]
, [activity table].Dept
, [activity table].Staff
, [activity table].Datein
, [activity table].Ward
, [activity table].cost
, [activity table].xtrstaff
, IIf([activity]="court apperance",30,0) AS extra
, IIf([reason for cancellation]>"",0, Sum(([xtrstaff]*([time]/60))))+
Sum(IIf([activity]="court appearance",30,0))) AS xtrcost
FROM [activity table]
GROUP BY [activity table].time
, [activity table].ID
, [activity table].patient
, [activity table].activity
, [activity table].[reason for cancellation]
, [activity table].Dept
, [activity table].Staff
, [activity table].Datein
, [activity table].Ward
, [activity table].cost
, [activity table].xtrstaff
, IIf([activity]="court apperance",30,0)

I would probably be testing [reason for cancellation] to be not null instead of checking to see if it is a zero length string. (Or perhaps, you need to check for both as follows)
IIF([Reason for Cancellation] & "" = "", ...

All that said, I'm not sure that this query is going to give you the desired result even if you do get it to work. You might need to replace the SUM with the VBA function DSUM or with a subquery in the Select clause; and drop the entire Group by clause.

Perhaps you can explain what you are trying to do with the expression:
, IIf([reason for cancellation]>"", 0, (Sum(([xtrstaff]*([time]/60))))+
Sum(IIf([activity]="court appearance",30,0))) AS xtrcost

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

steve a wrote:
Hi i'm stuggling with this error message and don't understand how to get around it.

the error reads:
"you tried to execute a query that does not includde the expression 'time' as part of an aggregate function."

SELECT[activity table].time, [activity table].ID, [activity table].patient, [activity table].activity, [activity table].[reason for cancellation], [activity table].Dept, [activity table].Staff, [activity table].Datein, [activity table].Ward, [activity table].cost, [activity table].xtrstaff, IIf([activity]="court apperance",30,0) AS extra, IIf([reason for cancellation]>"",0,(Sum(([xtrstaff]*([time]/60))))+Sum(IIf([activity]="court appearance",30,0))) AS xtrcost
FROM [activity table];


the complete SQL is as follows


.

Relevant Pages

  • Re: Counting Unique Data 2
    ... the aggregate functions or must use ... If all you want is the sum by Local group then you would do ... In order to make SQL understand that this is one object you must ... about the "GROUP BY" query. ...
    (microsoft.public.access.queries)
  • Re: Query Conflict
    ... the following query with good results. ... 66 Plus] FROM [Lake Worth CFP Clients] ... The conflict appears to be between the city criteria and the SUM ... any field in the WHERE clause also has to be ...
    (comp.databases.ms-access)
  • Re: Date query in VBA Module
    ... since you are not using any aggregate functions there is no need to GROUP BY. ... You can use the DISTINCT operator in the query to get the same results and don't need the GROUP BY clause at all. ... Apologies for the length of the SQL etc - you can appreciate why I wanted to ...
    (microsoft.public.access.queries)
  • Re: Query Conflict
    ... the following query with good results. ... I now want to add an additional criteria of the city field. ... The conflict appears to be between the city criteria and the SUM ... the SELECT clause. ...
    (comp.databases.ms-access)
  • Re: Query Conflict
    ... running the following query with good results. ... 66 Plus] FROM [Lake Worth CFP Clients] ... SUM of the ages. ... any field in the WHERE clause also has to be ...
    (comp.databases.ms-access)
Loading