error message that “you tried to execute a query..." (1 Viewer)

KirRoyale

Registered User.
Local time
Today, 21:19
Joined
Apr 22, 2013
Messages
61
I have a query that shows all calls from corporate mobile phones and indicates whether the number called is also a company mobile or whether it is a number in one of the company offices.
When I try to run a totals query based on this:
SELECT Call0InternalSplit.Month, Call0InternalSplit.NrCalled, Call0InternalSplit.CallType, Call0InternalSplit.PartNrCalled, Count(Call0InternalSplit.Charge) AS ChargeOfCount
FROM Call0InternalSplit
GROUP BY Call0InternalSplit.Month, Call0InternalSplit. NrCalled, Call0InternalSplit.CallType, Call0InternalSplit.PartNrCalled
HAVING (((Call0InternalSplit.CallType) Like "Internal")) OR (((Call0InternalSplit.PartNrCalled) Like "1111" Or (Call0InternalSplit.PartNrCalled) Like "2222"));
I get the error message that “you tried to execute a query that does not include the specified expression…..as part of an aggregate function.”
I have researched other questions regarding this issue but have not been able to work out how it relates to my problem.
Could somebody please help?
 

Brianwarnock

Retired
Local time
Today, 12:19
Joined
Jun 2, 2003
Messages
12,701
It would have helped if you had completed the error message which I believe would have named the field however it looks as though you have a space in front of NrCalled in the Group By

Brian
 

KirRoyale

Registered User.
Local time
Today, 21:19
Joined
Apr 22, 2013
Messages
61
Apologies. I didn’t realise that the full message would have been more helpful. The complete error message is:
“You tried to execute a query that does not include the specified expression ‘Call0InternalSplit.CallType Like “Internal” or (Call0InternalSplit.PartNrCalled Like “1111” Or Call0InternalSplit.PartNrCalled Like “4800”)’ as part of an aggregate function.”
I don’t think I have any spaces. The actual query has some Japanese field names and I just replaced them with English names for the sake of asking the question.
The actual query is:
SELECT Call0InternalSplit.請求年月, Call0InternalSplit.相手先電話番号, Call0InternalSplit.CallType, Call0InternalSplit.PartNrCalled, Count(Call0InternalSplit.通話料金) AS 通話料金OfCount
FROM Call0InternalSplit
GROUP BY Call0InternalSplit.請求年月, Call0InternalSplit.相手先電話番号, Call0InternalSplit.CallType, Call0InternalSplit.PartNrCalled
HAVING (((Call0InternalSplit.CallType) Like "Internal")) OR (((Call0InternalSplit.PartNrCalled) Like "5715" Or (Call0InternalSplit.PartNrCalled) Like "4800"));
Thanks for your help.
 

Brianwarnock

Retired
Local time
Today, 12:19
Joined
Jun 2, 2003
Messages
12,701
The full message tells us that the Having clause is the problem, my problem is I don't know why. :eek:

However I would like to make a couple of observations

I would use a Where clause before the aggregation as the criteria is not dependent on the aggregation and the filtering would be more efficient

You have used Like without wildcards, it would be better to use =
Are the numbers in text format? If not you should not use "". .

If you created the query in the design grid you create a Where clause by dragging the fields in again, select Where from the drop down, which causes the show to be deselected, and apply the criteria to that column.

Brian
 

KirRoyale

Registered User.
Local time
Today, 21:19
Joined
Apr 22, 2013
Messages
61
Brian,
Thank you. Splitting the query into:
1. Filtering with WHERE
2. A totals query
did the trick!
 

Brianwarnock

Retired
Local time
Today, 12:19
Joined
Jun 2, 2003
Messages
12,701
Glad you have it working , but I did not mean two queries, just a where clause in the Totals query instead of the Having.

Select
From
Where
Group By

But what matters is that you have a solution

Brian
 

KirRoyale

Registered User.
Local time
Today, 21:19
Joined
Apr 22, 2013
Messages
61
Ah, sorry Brian, I misunderstood.
I have also now realised that the ‘where’ from the dropdown you mentioned is in the ‘total’ row of the query grid.
After following your advice, this now works perfectly and I have fewer queries to clutter up the database.
SELECT Call0InternalSplit.請求年月, Call0InternalSplit.電話番号, Call0InternalSplit.相手先電話番号, Call0InternalSplit.CallType, Sum(Call0InternalSplit.通話料金) AS 通話料金OfSum, Call0InternalSplit.Office, Call0InternalSplit.User, Call0InternalSplit.CODE, Call0InternalSplit.EnglishDeptName
FROM Call0InternalSplit
WHERE (((Call0InternalSplit.CallType)="Internal")) OR (((Call0InternalSplit.Office)="tokyo" Or (Call0InternalSplit.Office)="Osaka"))
GROUP BY Call0InternalSplit.請求年月, Call0InternalSplit.電話番号, Call0InternalSplit.相手先電話番号, Call0InternalSplit.CallType, Call0InternalSplit.Office, Call0InternalSplit.User, Call0InternalSplit.CODE, Call0InternalSplit.EnglishDeptName;
Thanks again.
 

Users who are viewing this thread

Top Bottom