Results 1 to 5 of 5

Basic Access Query to Calculate Differences between 2 Tables

  1. #1
    noxperience is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2019
    Posts
    3

    Basic Access Query to Calculate Differences between 2 Tables

    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

  2. #2
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,833
    it's because you have two records for 31082. You need to sum each table separately, then do the comparison, it cannot be done in one query, you need three - sum TblTreas by doc number, sum TblTreas by doc number then a final query to join these two on doc number.

    with regards your left join, what if you have entries in tblAgency, but not tblTreas?

  3. #3
    noxperience is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2019
    Posts
    3
    Quote Originally Posted by Ajax View Post
    it's because you have two records for 31082. You need to sum each table separately, then do the comparison, it cannot be done in one query, you need three - sum TblTreas by doc number, sum TblTreas by doc number then a final query to join these two on doc number.

    with regards your left join, what if you have entries in tblAgency, but not tblTreas?
    Thanks for your response Ajax. I'll give that a try. With respect to your left join question, my goal is to eventually have this information shown in the query results as well. I'm just taking it one step at a time, since I'm fairly new to this. On the other hand, I'd appreciate your help on this as well, if you can. Thanks again.

  4. #4
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,833
    you would do a 4th query, like the 3rd query, but with the left join going the other way

    then you could do a 5th query which would be a union query of the 3rd and 4th queries

  5. #5
    noxperience is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2019
    Posts
    3
    It worked! Thanks for your help Ajax

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-05-2018, 01:10 PM
  2. How to calculate differences
    By ghodgkins in forum Access
    Replies: 5
    Last Post: 06-24-2015, 11:01 AM
  3. Replies: 9
    Last Post: 12-03-2013, 01:33 PM
  4. Replies: 1
    Last Post: 09-07-2011, 01:42 AM
  5. Visual Basic / Acces Form differences
    By MWMike in forum Forms
    Replies: 3
    Last Post: 10-06-2010, 08:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums