CODEWITHSUNDEEP
sqlms-access-2013
Johnny C
Johnny C

Reputation: 100

MS- Access 101 - Sum expression as part of an aggregate function

I've just switching back to MS Access (been on Excel VBA for a year) and my SQL is a bit rusty ... I'm getting an error

You tried to execute a query that does not include the specified expression (Sum line below) as pat of an aggregate function

from this code:

SELECT 
    FYF.ACNT_CODE, 
    FYF.[Team code], 
    Sum([FYF]![Value])*(1+[IncreaseAssumptions]![Amount]) AS [Year 1 Cost] 
INTO [Year 1 costs]
FROM FYF 
INNER JOIN 
    (SunAccounts INNER JOIN IncreaseAssumptions ON SunAccounts.IncreaseType = IncreaseAssumptions.IncreaseType) 
    ON FYF.ACNT_CODE = SunAccounts.Account_Code
WHERE (((IncreaseAssumptions.YearName)="Year 1"))
GROUP BY FYF.ACNT_CODE, FYF.[Team code];

Where am I going wrong? The Sum part in Access is defined as an expression in the Total: line

Cheers

Upvotes: 0

Views: 373

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

I believe you have a bracketing issue - this:

Sum([FYF]![Value])*(1+[IncreaseAssumptions]![Amount]) AS [Year 1 Cost]

Should be:

Sum([FYF]![Value]*(1+[IncreaseAssumptions]![Amount])) AS [Year 1 Cost]

Upvotes: 1

Related Questions