Subquery to Calculate Year To Date Amount?

[xraive]

Hi I am trying to calculate the YTD amount and I keep getting the following error "You tried to execute a query that does note include the specified expression 'Fiscal Year' as part of an aggregate function". Can you offer any assistance?

SELECT GetFiscalYear([InvoiceDate]) AS [Fiscal Year], fFiscalPeriod([InvoiceDate]) AS Period, BudgetCodeID, Sum(InvoiceDetails.Amount) AS SumOfAmount, 
(SELECT Sum(InvoiceDetails.amount) AS YTD                                 
   FROM Invoices AS Inv INNER JOIN InvoiceDetails AS InvD ON Inv.InvoiceID = InvD.InvoiceID    
   WHERE Inv.InvoiceDate >= DateSerial(Year([Invoices].[InvoiceDate]),1,1)                
     AND Inv.InvoiceDate < DateSerial(Year([Invoices].[InvoiceDate]),                     
       Month([Invoices].[InvoiceDate]) + 1, 1)) AS YTDAmount 
FROM Invoices INNER JOIN InvoiceDetails ON Invoices.InvoiceID = InvoiceDetails.InvoiceID 
GROUP BY GetFiscalYear([InvoiceDate]), fFiscalPeriod([InvoiceDate]), InvoiceDetails.BudgetCodeID; 
 

[Jerry Winston]

Your GROUP BY statement literally does not include the alias [Fiscal Year].

Remove the AS [Fiscal Year] part and see if there are any other errors.

[NeoPa]

Hi Jerry. Nice to see you visiting Access :)

I'd be surprised if the ALIAS had any effect in Jet SQL. It normally ignores the ALIAS and doesn't even allow GROUPing BY it. What may be an issue here is that it's a function call. I can't see how exactly, as normally a function which includes a record-level parameter - one of the fields - generally gets run for each record. A function which has no relative parameter will typically be optimised such that it's only called at the start and the result simply reused (EG when desiring a random value, it needs to be passed a field reference to force it to run for each record - otherwise the same, single, random value is returned each record).

It may still be worth losing the ALIAS just to test, but I expect it doesn't like running the function in both the SELECT and GROUP BY clauses.