Calculating the Sum of two different fields in two different table

[Moe]

I am trying to find the "sum" of "OrderTotal" from "Orders Table" and "sum"
of "PaymentAmount" from "Payments Table" then find the "Balance" by
subtracting them. The two tables are joined at "OrderID" field however, they
both have "customerID" field also. When I try this in one query, Group by
"customerID" the "SumOfOrderTotal" is not correct. But when I use two
separate queries the calculation is right. Any suggestion?

 

[Jeff C]

use a totals query to get the sum of each value you want. place both totals
queries in a third query and subtract