Calculating Dollar Value Shift from two similar queries

[mnty89]

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

 

[Joe4]

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:

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).

 

[mnty89]

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..

 

[Joe4]

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.