Performing SQL Join and Calculation Simultaneously
Posted on 2011-02-24
I have two tables (temp1 and temp2) in an MS Access Project that contain 3 fields each: Code, Symbol, Quantity. My goal is to JOIN these tables on Symbol and select information into a new table, called TableResult but AT THE SAME TIME calculate the SUM of the Quantity field from temp1 and temp2.
I can achieve a simple SELCT w/JOIN as such:
DoCmd.RunSQL "SELECT temp1.Code, temp1.Symbol, temp1.Quantity INTO TableResult FROM temp1 LEFT JOIN temp2 ON temp1.Symbol=temp2.Symbol"
but do NOT know how to modify the above statement to SUM temp1.Quantity + temp2.Quantity and then have ONLY the resultant NET QUANTITY in my table, TableResult.