title: Consolidating 2 queries with an INNER JOIN and Get a calculated column from them
body: <p>First off, I am working in Access 2007.</p>
  
  <p>I have two queries that both aggregates by date.</p>
  
  <p>The first one returns an aggregated daily weighted return</p>
  
  <pre><code>SELECT Records.RecDate, Sum([YTD]*[Weight]) AS UnadjReturn
  FROM Records
  WHERE (((Records.[Class1])="eq") AND ((Records.[Class2])="ce"))
  GROUP BY Records.RecDate;
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>RecDate UnadjReturn
  11/27/2012  0.060778036742704
  12/11/2012  0.075592752895318
  12/14/2012  7.47574641136453E-02
  </code></pre>
  
  <p>I also have another query that returns daily total class portfolio weight by summing the weight of all the assets in an asset class with respect to the entire portfolio:</p>
  
  <pre><code>SELECT Records.RecDate, Sum(Records.Weight) AS SumOfWeight
  FROM Records
  WHERE (((Records.[Class1])="eq") AND ((Records.[Class2])="ce"))
  GROUP BY Records.RecDate;
  </code></pre>
  
  <p>which returns:</p>
  
  <pre><code>RecDate SumOfWeight
  11/27/2012  0.479327081236988
  12/11/2012  0.483075813390315
  12/14/2012  0.482791727874428
  </code></pre>
  
  <p>Now I would like to write a consolidated query that multiplies each day's <code>UnadjReturn</code> by <code>SumOfWeight</code>. I have tried <code>INNER JOIN</code> the two and then multiply the two variables as follows,</p>
  
  <pre><code>SELECT joined.RecDate, joined.UnadjReturn * joined.SumOfWeight as AdjReturn
  FROM joined
  (
  SELECT Records.RecDate, Sum([YTD]*[Weight]) AS UnadjReturn
  FROM Records
  WHERE (((Records.[Class1])="eq") AND ((Records.[Class2])="ce"))
  GROUP BY Records.RecDate
  INNER JOIN
  (
  SELECT Records.RecDate, Sum(Records.Weight) AS SumOfWeight
  FROM Records
  WHERE (((Records.[Class1])="eq") AND ((Records.[Class2])="ce"))
  GROUP BY Records.RecDate;
  ) t2
  ON Records.RecDate = t2.RecDate
  AS joined
  )
  </code></pre>
  
  <p>This is not looking good at all. Any suggestions? I could do it by querying the two queries with another query, but I would like to take care of it in just one. </p>