title: pivot ACCESS with subquery
body: <p>need some help here…
  I manage to make running with MS-ACCES a query which integrate subquery. </p>
  
  <pre><code>SELECT
      TS3.YearTimeSheet, 
      Ress1.[Complete Name], 
      Rates2.[Applicable Currency], 
      Rates2.[Applicable Rate], 
      TS3.WeekTimeSheet, 
      Sum(TS3.TotalWeek) AS [Total Hrs], 
      Sum([TotalWeek]*[Applicable Rate]*(1+[WAO])) AS [Cost Incl WAO]
  FROM 
      (
          [Resources Rates] AS Rates2
          INNER JOIN Resources AS Ress1 ON Rates2.StaffID = Ress1.STAFFID
      ) 
      INNER JOIN TimeSheets AS TS3 ON Ress1.STAFFID = TS3.STAFFID
  WHERE Rates2.[Applicable Rate] = (
      SELECT [Resources Rates].[Applicable Rate] 
      FROM [Resources Rates] 
      WHERE  
          [Resources Rates].[StaffID]= [Rates2].[StaffID]
          AND DatePart('yyyy', [Resources Rates].[Starting Date]) = [TS3].[YearTimeSheet] 
          AND NOT DatePart('ww', [Resources Rates].[Starting Date])&gt;=[TS3].WeekTimeSheet 
          AND NOT DatePart('ww', [Resources Rates].[Ending Date])&lt; [TS3].WeekTimeSheet
      )
  GROUP BY
      TS3.YearTimeSheet, 
      Ress1.[Complete Name], 
      Rates2.[Applicable Currency], 
      Rates2.[Applicable Rate], 
      TS3.WeekTimeSheet;
  </code></pre>
  
  <p>Unfortunately while trying to transform it into a Pivot… error!
  "Le Moteur de la base de données Microsoft Access be reconnait pas "TS3.WeekTimeSheet" en tant que nom de champ ou expression correcte." In English Access does not recognise TS3.WeekTimeSheet being a field. </p>
  
  <p>My understanding goes that declaration of this variable is at the end of the instruction and therefore cannot be used before? It works in a normal Subquery… in a TRANSFORM PIVOT instruction. Certainly wrong method… Please Help
  the below SQL instruction however works When I force the criteria with static data. I doing certainly something wrong in the structure with the below.
  I was thinking the below should do the job. Any idea what I'm doing wrong?</p>
  
  <pre><code>TRANSFORM Sum(TS3.TotalWeek) AS WeekSum
  
  SELECT TS3.YearTimeSheet,
    Ress1.[Complete Name], 
    RatesBis.[Applicable Currency], 
    RatesBis.[Applicable Rate], 
    Sum(TS3.TotalWeek) AS [Total Hrs], 
    Sum([TotalWeek]*RatesBis.[Applicable Rate]*(1+[WAO])) AS [Cost Incl WAO]
  
  FROM Resources AS Ress1 INNER JOIN TimeSheets AS TS3 ON Ress1.STAFFID = TS3.STAFFID, 
    (
    SELECT [Resources Rates].[Applicable Currency], [Resources Rates].[Applicable Rate] 
    FROM [Resources Rates] 
    WHERE [Resources Rates].[StaffID]=Ress1.STAFFID
     AND NOT DatePart('ww', [Resources Rates].[Starting Date])&gt;= TS3.WeekTimeSheet
     AND NOT DatePart('ww', [Resources Rates].[Ending Date])&lt; TS3.WeekTimeSheet
     ) AS RatesBis
  
  GROUP BY 
    TS3.YearTimeSheet, 
    Ress1.[Complete Name], 
    RatesBis.[Applicable Currency], 
    RatesBis.[Applicable Rate]
  
  PIVOT TS3.WeekTimeSheet;
  </code></pre>