Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Calculating Dollar Value Shift from two similar queries

  1. #1
    Board Regular
    Join Date
    Apr 2016
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculating Dollar Value Shift from two similar queries

    I think this is an insanely easy problem to solve, but I am not positive how to go about it correctly.

    Here is the scenario. I pull the same report every day. This report compares the previous day's report, stored in Table1, and compares it to the current day's report Table2. This report has an order ID and a dollar value. I need to have a new query that will find the same order ID from the previous day and then see the shift in dollar value. I only want to include OrderIDs that are on both. So for example

    Table1
    OrderID $
    12345 5
    23456 6

    Table2
    OrderID $
    12345 4
    23456 7


    So the query would populate,

    Query1
    OrderID $
    12345 -1
    23456 1

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,514
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Calculating Dollar Value Shift from two similar queries

    Just create a query between the two tables, joining them on the Order ID field.
    Add the Order ID field from either table, and then add the following calculated field to it:
    Code:
    DollShift:[Table2].[DollValue] - [Table1].[DollValue]
    Where "DollValue" is the name of your field holding the dollar value (I am pretty sure it isn't "$", as I don't think that is a valid field name).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Apr 2016
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating Dollar Value Shift from two similar queries

    Thanks! I indeed tried that and it didn't work initially. I must've made a simple mistake somehow.

    Follow up question.. If for example one of the Order ID fields comes up twice with different S values, is there a way to only have it calculate on the first occurrence? I didnt realize there would be duplicate Order IDs within the same table, and it is not giving the desired outcome because of it..

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,514
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Calculating Dollar Value Shift from two similar queries

    If the details are exactly the same, you can combine them together.
    Just create a new query based on that single table, and click on the Totals button (looks like a Sigma). This will "Group" all records with similar data together.
    Then, create your query between this query and the other table.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •