Hi, I am trying to extract monthly TNA values from Access table. Here's the code. However, this returns the data by dividing each months data by 12. I don't want this. I just need the monthly data. If I remove the "avg" function, my code isn't working (I am getting the error message as Syntax error(missing operator) in query expression))

SELECT DISTINCTROW [MONTHLY_TNA].ICDI_NO,
mid([MONTHLY_TNA].CALDT,1,4) AS [Date By Year],
avg(IIf((mid([MONTHLY_TNA].CALDT,5,2)=01),[MONTHLY_TNA].[MTNA],0)) AS Jan,
avg(IIf((mid([MONTHLY_TNA].CALDT,5,2)=02),[MONTHLY_TNA].[MTNA],0)) AS Feb,
avg(IIf((mid([MONTHLY_TNA].CALDT,5,2)=03),[MONTHLY_TNA].[MTNA],0)) AS Mar,
avg(IIf((mid([MONTHLY_TNA].CALDT,5,2)=04),[MONTHLY_TNA].[MTNA],0)) AS Apr,
avg(IIf((mid([MONTHLY_TNA].CALDT,5,2)=05),[MONTHLY_TNA].[MTNA],0)) AS May,
avg(IIf((mid([MONTHLY_TNA].CALDT,5,2)=06),[MONTHLY_TNA].[MTNA],0)) AS Jun,
avg(IIf((mid([MONTHLY_TNA].CALDT,5,2)=07),[MONTHLY_TNA].[MTNA],0)) AS Jul,
avg(IIf((mid([MONTHLY_TNA].CALDT,5,2)=08),[MONTHLY_TNA].[MTNA],0)) AS Aug,
avg(IIf((mid([MONTHLY_TNA].CALDT,5,2)=09),[MONTHLY_TNA].[MTNA],0)) AS Sep,
avg(IIf((mid([MONTHLY_TNA].CALDT,5,2)=10),[MONTHLY_TNA].[MTNA],0)) AS Oct,
avg(IIf((mid([MONTHLY_TNA].CALDT,5,2)=11),[MONTHLY_TNA].[MTNA],0)) AS Nov,
avg(IIf((mid([MONTHLY_TNA].CALDT,5,2)=12),[MONTHLY_TNA].[MTNA],0)) AS Dec

FROM [MONTHLY_TNA] INNER JOIN [FUND_STYLE] ON ([MONTHLY_TNA].ICDI_NO = [FUND_STYLE].ICDI_NO)

WHERE ((((([FUND_STYLE].SP_OBJ_CD="AGG" Or [FUND_STYLE].SP_OBJ_CD="GRI" Or [FUND_STYLE].SP_OBJ_CD="GRO" Or [FUND_STYLE].SP_OBJ_CD="GMC" Or [FUND_STYLE].SP_OBJ_CD="SCG") AND ([MONTHLY_TNA].CALDT > 19931231) AND ([MONTHLY_TNA].CALDT < 20060101)))=True))

GROUP BY [MONTHLY_TNA].ICDI_NO, mid([MONTHLY_TNA].CALDT,1,4);


Any help would be greatly appreciated.

Thanks,

Can you change "avg" to "sum?"


mmcdonal

Thanks for the reply. However, if I use sum instead of avg, it is multiplying the actual data by 2 (in some cases) and 3 (in some instances). Any help why is this happening?