Problem with not returning data from a field required in an expression 1

[ps32208]

Hi,

I am trying to create a query which has an expression as one of the fields. The expression uses 3 other fields but I don't want to see all the other fields in the data returned as I want a summary. When I change the total to 'where' to the field 'Sales Stage' in the data returned I get the following message:
You tried to exececute a query that does not include the specified expression ('then details the expression...') as part of an aggregate function. When the total is set to group by for field Sales stage there is no problem. Any help would be greatfully received.

The sql from the query which returns the error is below:

SELECT tbl_funnel_by_month_summary.[Employee ID], [Sales Rep].[Sales Rep Name], IIf(InStr([Sales Stage],"Won")>0,"WON",IIf([Committed]="Y","COMMITTED",IIf([Probability]>=50,"UPSIDE","WIP"))) AS [Forecast Flag], Sum(tbl_funnel_by_month_summary.[Forecast Value USD]) AS [SumOfForecast Value USD], tbl_funnel_by_month_summary.Committed, tbl_funnel_by_month_summary.Probability
FROM [Sales Rep] INNER JOIN tbl_funnel_by_month_summary ON [Sales Rep].[Employee ID] = tbl_funnel_by_month_summary.[Employee ID]
GROUP BY tbl_funnel_by_month_summary.[Employee ID], [Sales Rep].[Sales Rep Name], tbl_funnel_by_month_summary.Committed, tbl_funnel_by_month_summary.Probability;

Regards
Pete

 

[lespaul]

You need to have every field that is not an aggregate in the GROUP BY clause including your IIF statement:

SELECT tbl_funnel_by_month_summary.[Employee ID], [Sales Rep].[Sales Rep Name], 
IIf(InStr([Sales Stage], "Won")>0, "WON", IIf([Committed]="Y","COMMITTED",
IIf([Probability]>=50,"UPSIDE","WIP"))) AS [Forecast Flag], Sum(tbl_funnel_by_month_summary.[Forecast Value USD]) AS [SumOfForecast Value USD], tbl_funnel_by_month_summary.Committed, tbl_funnel_by_month_summary.Probability
FROM [Sales Rep] INNER JOIN tbl_funnel_by_month_summary ON [Sales Rep].[Employee ID] = tbl_funnel_by_month_summary.[Employee ID]
GROUP BY tbl_funnel_by_month_summary.[Employee ID], [Sales Rep].[Sales Rep Name], 
IIf(InStr([Sales Stage], "Won")>0, "WON", IIf([Committed]="Y","COMMITTED",
IIf([Probability]>=50,"UPSIDE","WIP"))), tbl_funnel_by_month_summary.Committed, tbl_funnel_by_month_summary.Probability

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins