Execute a Query that Does Not Include...

[Huskergirl]

I am creating a Make Table Query and calculating new fields based on current and prior year-end numbers. If the prior year-end number does not exist (Is Null), I want the use the current rate or calculate the change in rate. I have typed the below in the Field Box:

CHG_IN_PGM_RATE: IIf(([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE]) Is Null,([MASTER LIST CURRENT]![PTD_PM_PGM_RATE]),(([MASTER LIST CURRENT]![PTD_PM_PGM_RATE])-([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE])))

However, when running the query, I get the attached error message. I have tried to figure this out but a stumped. Any help would be appreciated. The screenshot will also show how the two tables are joined.

Any help would be greatly appreciated!!

Sara :banghead:

 

[Monardo]

Hi,
First of all attached error message contains different IIF expression than in the post; secondly according to message your query is using Group By and the result of your expression is not aggregated (apparently).

Could you post complete SQL statement.

 

[Huskergirl]

I don't know why the error message and IIF expression are not matching but the same message keeps showing up regardless of how I change the expression. I have the "Total" line selected to "Expression", not "Group By". I've attached the entire SQL statement.

SELECT [MASTER LIST CURRENT].BUSINESS_UNIT_NAME, [MASTER LIST CURRENT].RP_NO, [MASTER LIST CURRENT].RP_NAME, [MASTER LIST CURRENT].PIC, [MASTER LIST CURRENT].PIC_NAME, [MASTER LIST CURRENT].PM, [MASTER LIST CURRENT].PM_NAME, [MASTER LIST CURRENT].[PTD_PM_%COMP], [MASTER LIST CURRENT].PTD_PM_REPT_FR, [MASTER LIST CURRENT].PTD_PM_REPT_PGM, [MASTER LIST CURRENT].PTD_PM_PGM_RATE, [P0_10 Tbl - AM PYE PTD Detail w Rates].PTD_PYE_REPT_PGM, [P0_10 Tbl - AM PYE PTD Detail w Rates].PTD_PYE_PGM_RATE, [MASTER LIST CURRENT].AS_SOLD_RATE, IIf(([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_%COMP]) Is Null,([MASTER LIST CURRENT]![PTD_PM_%COMP]),Sum(([MASTER LIST CURRENT]![PTD_PM_%COMP])-([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_%COMP]))) AS [CHANGE_IN_%COMP], IIf(([MASTER LIST CURRENT]![AS_SOLD_RATE]) Is Null,([MASTER LIST CURRENT]![PTD_PM_PGM_RATE]),(([MASTER LIST CURRENT]![PTD_PM_PGM_RATE])-([MASTER LIST CURRENT]![AS_SOLD_RATE]))) AS CHG_IN_PAC_PGM_RATE, IIf(IsError(([MASTER LIST CURRENT]![AS_SOLD_RATE])*([MASTER LIST CURRENT]![PTD_PM_REPT_FR])),0,(([MASTER LIST CURRENT]![AS_SOLD_RATE])*([MASTER LIST CURRENT]![PTD_PM_REPT_FR]))) AS EXPECTED_PAC_AS_SOLD_RATE_PGM, IIf(IsError(([MASTER LIST CURRENT]![PTD_PM_REPT_FR])*([MASTER LIST CURRENT]![PTD_PM_%COMP])),0,(([MASTER LIST CURRENT]![PTD_PM_REPT_FR])*([MASTER LIST CURRENT]![PTD_PM_%COMP]))) AS EXPECTED_PAC_PTD_RATE_PGM, IIf(([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE]) Is Null,([MASTER LIST CURRENT]![PTD_PM_PGM_RATE]),Sum(([MASTER LIST CURRENT]![PTD_PM_PGM_RATE])-([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE]))) AS CHG_IN_PGM_RATE, [MASTER LIST CURRENT].YTD_PM_REPT_PGM, IIf(([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE]) Is Null,Sum((([MASTER LIST CURRENT]![PTD_PM_REPT_FR])*([MASTER LIST CURRENT]![PTD_PM_PGM_RATE]))*([MASTER LIST CURRENT]![PTD_PM_%COMP])),Sum((([MASTER LIST CURRENT]![PTD_PM_REPT_FR])*([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE]))*([MASTER LIST CURRENT]![PTD_PM_%COMP]))) AS EXPECTED_PGM_PYE_RATE INTO [P2_02 Tbl - PAC and YTD Calculations]
FROM [MASTER LIST CURRENT] LEFT JOIN [P0_10 Tbl - AM PYE PTD Detail w Rates] ON [MASTER LIST CURRENT].RP_NO = [P0_10 Tbl - AM PYE PTD Detail w Rates].RP_NO
WHERE ((([MASTER LIST CURRENT].KEY_CODE) Is Null))
GROUP BY [MASTER LIST CURRENT].BUSINESS_UNIT_NAME, [MASTER LIST CURRENT].RP_NO, [MASTER LIST CURRENT].RP_NAME, [MASTER LIST CURRENT].PIC, [MASTER LIST CURRENT].PIC_NAME, [MASTER LIST CURRENT].PM, [MASTER LIST CURRENT].PM_NAME, [MASTER LIST CURRENT].[PTD_PM_%COMP], [MASTER LIST CURRENT].PTD_PM_REPT_FR, [MASTER LIST CURRENT].PTD_PM_REPT_PGM, [MASTER LIST CURRENT].PTD_PM_PGM_RATE, [P0_10 Tbl - AM PYE PTD Detail w Rates].PTD_PYE_REPT_PGM, [P0_10 Tbl - AM PYE PTD Detail w Rates].PTD_PYE_PGM_RATE, [MASTER LIST CURRENT].AS_SOLD_RATE, [MASTER LIST CURRENT].YTD_PM_REPT_PGM;