title: Aggregate calculations from 2 separate tables
body: <p>I have a <code>SALES_RECEIPT</code> table and a (sales) <code>RETURNS</code> table that both have <code>REP_ID</code> as a foreign key from the <code>SALES_REP</code> table. I want to sum total sales and total returns for each sales rep and calculate the commission from the sales and the lost commission from the returns.  </p>
  
  <p>The query runs without an error but the values in the columns are not correct. The values are much larger that what the actual values should be. I tried both INNER JOIN and LEFT JOIN to link the RETURNS table but that did not solve the problem.  </p>
  
  <pre><code> SELECT Format(SALES_RECEIPT.SALE_DATE,'yyyy-mm') AS [Year-Month], 
   SALES_REP.rep_Name, 
  
   Sum(SALES_RECEIPT.SELLING_PRICE*SALES_RECEIPT.quantity) AS [Total Sales], 
   Sum((Nz(SALES_RECEIPT.SELLING_PRICE,0)*Nz(SALES_RECEIPT.quantity,0))*(Nz(SALES_RECEIPT.commission_percent,100)*0.001)) AS [Gross Commission],
   Sum(Nz(returns.selling_price * returns.quantity)) AS [Returns Sales], 
   Sum(Nz(returns.selling_price*returns.quantity)) AS [Returns Sales], Sum((Nz(RETURNS.SELLING_PRICE,0)*Nz(RETURNS.quantity,0))*(Nz(RETURNS.commission_percent,100)*0.001)) AS [Lost Commission], 
   Sum((Nz(SALES_RECEIPT.SELLING_PRICE,0)*Nz(SALES_RECEIPT.quantity,0))*(Nz(SALES_RECEIPT.commission_percent,100)*0.001))-Sum((Nz(RETURNS.SELLING_PRICE,0)*Nz(RETURNS.quantity,0))*(Nz(RETURNS.commission_percent,100)*0.001)) AS [Net Commission]
  
  FROM
   (SALES_RECEIPT INNER JOIN SALES_REP ON SALES_RECEIPT.REP_ID = SALES_REP.REP_ID)
   LEFT JOIN RETURNS ON SALES_RECEIPT.REP_ID = RETURNS.REP_ID
   WHERE (((SALES_RECEIPT.SALE_DATE) Between #1/1/2000# And #12/31/2050#))
   GROUP BY Format(SALES_RECEIPT.SALE_DATE,'yyyy-mm'), SALES_REP.rep_Name;
  </code></pre>