SQL Statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AccessHelp - 14 Nov 2005 14:52 GMT

Good morning,

I am trying to create a formula in my SQL query for my report, and it's not
working.

My formula would be: Diff %: [A]/[B]. Both A & B are the fields in the SQL
query, and I have my Total field as Expression. The fields are pulling from
a Select query that I created.

I got the error message below when I tried to run the SQL query.

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

Thanks.

Reply to this Message

John Spencer - 14 Nov 2005 15:52 GMT

Try surrounding the name with brackets. Special characters often cause
problems in queries.

[Diff%]: [A]/[B]

> Good morning,
>
[quoted text clipped - 15 lines]
>
> Thanks.

Reply to this Message

AccessHelp - 14 Nov 2005 18:05 GMT

Hi John,

Thanks for your help. The problem is now fixed. I took a different
approach. I type in the formula in Control Source of that text box.

I do need one more help. If you would, please look up the tread title
"Different Outcomes between sum and non-sum in SQL".

Thanks.

> Try surrounding the name with brackets. Special characters often cause
> problems in queries.
[quoted text clipped - 20 lines]
> >
> > Thanks.

Reply to this Message

Chris2 - 14 Nov 2005 16:25 GMT

> Good morning,
>
> I am trying to create a formula in my SQL query for my report, and it's not
> working.

<snip>

> I got the error message below when I tried to run the SQL query.
>
> "You tried to execute a query that does not include the specified expression
> as part of an aggregate function."
>
> Thanks.

AccessHelp,

Make sure each column in the query that is not inside an aggregate
function (AVG, SUM, COUNT, etc.) is also located on the GROUP BY
clause.

Example 1:

SELECT YT1.Col1
,COUNT(YT1.Col1)
FROM YourTable AS YT1

This will not work because YT1.Col1 is not on a GROUP BY clause.

Example 2:

SELECT YT1.Col1
,COUNT(YT1.Col1)
FROM YourTable AS YT1
GROUP BY YT1.Col1

This will work because YT1.Col1 is now on a GROUP BY clause.

Sincerely,

Chris O.

Reply to this Message