title: Using DATEPART function in access SQL query with joined tables and aggregate function
body: <p>I want to select the year and quarter parts of the date in a table rather than the whole date. I built the following query to achieve this:</p>
  
  <pre><code>SELECT Clients.ClientID,
         Sum(AccountVals.ValuationAmount) AS SumOfValuationAmount,
         Datepart("YYYY", AccountVals.valuationAmount) AS valYear,
         Datepart("Q", AccountVals.valuationAmount) AS valQuarter
  FROM   (Accounts
          INNER JOIN Clients
            ON Accounts.ClientID = Clients.ClientID)
         INNER JOIN AccountVals
           ON Accounts.AccountID = AccountVals.AccountID
  GROUP  BY Clients.ClientID,
            SumOfValuationAmount,
            valYear,
            valQuarter;
  </code></pre>
  
  <p>Although I have successfully used <code>DATEPART</code> in a simple select query the fact that this query is both an aggregate function and have linked tables is throwing me. Any ideas?</p>
  
  <p>EDIT: I realise my query doesn't have any criteria which means it could be trying to aggregate the result from different dates so it can't show the dateparts. I tried adding something like <code>HAVING valYear = 2012 AND valQuarter = 3</code> but get the same error</p>