Hello, just a heads-up, I'm a novice with ACCESS.
I would like to use ACCESS to reconcile amounts between two separate tables/sources. I'm trying to create a query that calculates the differences in amounts between two tables. The table names are Tbltreas and Tblagency. I'm linking on the document number field between the two tables and calculating the difference between the amounts. For example, the TblTreas, has an amount of $700 shown for document number 310081 and Tblagency has an amount of $100. The difference of $600 is captured correctly in my query. However, taking the difference between the amounts shown for document number 31082, the query is adding an additional $600 to the TblTreas document number, i.e., it's showing $1,200 instead of $600. Can someone please explain what's wrong with my query/code? I've posted the tables,query results, and code below. I appreciate any help with this.
Code
SELECT tbltreas.[Treasury document number], Sum(tbltreas.Amount) AS SumOfAmount, tblagency.[Treas document number], Sum(tblagency.Amount) AS SumOfAmount1, Sum([tbltreas]![Amount]-[tblagency]![Amount]) AS Expr1
FROM tbltreas LEFT JOIN tblagency ON tbltreas.[Treasury document number] = tblagency.[Treas document number]
GROUP BY tbltreas.[Treasury document number], tblagency.[Treas document number];
TblTreas Treasury document number Amount 31081 700 31082 600 31083 500 31084 400
Tblagency
Treas document number region Amount 31081 east 100 31082 east 200 31082 west 300 31084 east 300
Query Results
Treasury document number SumOfAmount Treas document number SumOfAmount1 Expr1 31081 700 31081 100 600 31082 1200 31082 500 700 31083 500 31084 400 31084 300 100