>>>I am trying to run a query in Access. Here is the equivalent query in SQL Server:
>>>
>>>
>>>SELECT
>>> CustNum
>>> , Count(SupplierTransID) As TransCount
>>> , Count(SupplierTransID) * 2 AS DoubleTransCount
>>>FROM
>>> SupplierTrans
>>>GROUP BY
>>> CustNum
>>>
>>>
>>>However, Access doesn't seem to want to calculate the DoubleTransCount field using the same concept. I get a "Syntax error (missing operator) in query expression... '. Why can't Access handle this query? Do I need to be doing it differently?
>>
>>I've tried it in Access 2007 (the menu so changed I hardly found my way around) and it worked just fine.
>
>I was also able to create a test case where this worked when I was querying from another table.
>
>My specific scenario involves querying from another query. I wonder if that is somehow causing the issue? I am looking at both queries side by side and they are similar.
>
>This query works just fine:
>SELECT
> IncidentMonthlyGrouped.System,
> SUM(IncidentMonthlyGrouped.[Total Downtime]) AS [Test1]
>FROM
> IncidentMonthlyGrouped
>GROUP BY
> IncidentMonthlyGrouped.System
>
>But when I add the second calculated column in this query, I get an error:
>SELECT
> IncidentMonthlyGrouped.System,
> SUM(IncidentMonthlyGrouped.[Total Downtime]) AS [Test1],
> SUM(IncidentMonthlyGrouped.[Total Downtime])*2 AS [Test2]
>FROM
> IncidentMonthlyGrouped
>GROUP BY
> IncidentMonthlyGrouped.System
>
>The error I get reads:
>"You tried to execute a query that does not include the specified expression 'Test2' as part of an aggregate function."
Furthermore, I put the contents of the source query into a table, changed the broken query to point to the table instead of the source query, and it worked. It seems this issue only exists when I query another query.