By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,701 Members | 1,782 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,701 IT Pros & Developers. It's quick & easy.

Subquery to Calculate Year To Date Amount?

P: 30
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?

Expand|Select|Wrap|Line Numbers
  1. SELECT GetFiscalYear([InvoiceDate]) AS [Fiscal Year], fFiscalPeriod([InvoiceDate]) AS Period, BudgetCodeID, Sum(InvoiceDetails.Amount) AS SumOfAmount, 
  2. (SELECT Sum(InvoiceDetails.amount) AS YTD                                 
  3.    FROM Invoices AS Inv INNER JOIN InvoiceDetails AS InvD ON Inv.InvoiceID = InvD.InvoiceID    
  4.    WHERE Inv.InvoiceDate >= DateSerial(Year([Invoices].[InvoiceDate]),1,1)                
  5.      AND Inv.InvoiceDate < DateSerial(Year([Invoices].[InvoiceDate]),                     
  6.        Month([Invoices].[InvoiceDate]) + 1, 1)) AS YTDAmount 
  7. FROM Invoices INNER JOIN InvoiceDetails ON Invoices.InvoiceID = InvoiceDetails.InvoiceID 
  8. GROUP BY GetFiscalYear([InvoiceDate]), fFiscalPeriod([InvoiceDate]), InvoiceDetails.BudgetCodeID; 
Aug 2 '10 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 145
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.
Aug 27 '10 #2

Expert Mod 15k+
P: 31,769
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.
Aug 27 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.