Subtract Query Help

[Adam McReynolds]

I have a battery inventory DB I am working on. The instances are recorded as "IN" or "OUT" and there is a quantity connected to each record/instance. I need a query to subtract the quantity of "IN's" from "OUT's" based on the CustomerID.

Currently I have made a totals query for Each "IN" and "OUT" but cannot get them to subtract from each other AND display the customer name.

Here is what I have so far:

SELECT QRY_SUM_ACTIONS_IN.[SumOfQty]-QRY_SUM_ACTIONS_OUT.[SumOfQty] AS StockBalance
FROM QRY_SUM_ACTIONS_IN INNER JOIN QRY_SUM_ACTIONS_OUT ON QRY_SUM_ACTIONS_IN.[ActionEntity] = QRY_SUM_ACTIONS_OUT.[ActionEntity];

Any help would be awesome.

 

[plog]

Most likely the best way to achieve this is not by joining queries--you should probably query the source of the underlying queries to achieve this.

What's the SQL of the underlying queries (QRY_SUM_ACTIONS_OUT and QRY_SUM_ACTIONS_IN)?

 

[Adam McReynolds]

Most likely the best way to achieve this is not by joining queries--you should probably query the source of the underlying queries to achieve this.

What's the SQL of the underlying queries (QRY_SUM_ACTIONS_OUT and QRY_SUM_ACTIONS_IN)?

Thanks for the reply.

IN:

SELECT QRY_SUM_ACTIONS.ActionEntity, QRY_SUM_ACTIONS.Action, QRY_SUM_ACTIONS.SumOfQty, QRY_SUM_ACTIONS.StockType
FROM QRY_SUM_ACTIONS
WHERE (((QRY_SUM_ACTIONS.Action)="IN"));

OUT:

SELECT QRY_SUM_ACTIONS.ActionEntity, QRY_SUM_ACTIONS.Action, QRY_SUM_ACTIONS.SumOfQty, QRY_SUM_ACTIONS.StockType
FROM QRY_SUM_ACTIONS
WHERE (((QRY_SUM_ACTIONS.Action)="OUT"));

I was able to get it to work the way I needed but I would like to know a better way/ correct way to do this. Here is how I got it done:

SELECT QRY_SUM_ACTIONS_IN.[SumOfQty]-QRY_SUM_ACTIONS_OUT.[SumOfQty] AS StockBalance, Left([QRY_SUM_ACTIONS_IN].[ActionEntity]+[QRY_SUM_ACTIONS_OUT].[ActionEntity],2) AS Customer
FROM QRY_SUM_ACTIONS_IN INNER JOIN QRY_SUM_ACTIONS_OUT ON QRY_SUM_ACTIONS_IN.[ActionEntity] = QRY_SUM_ACTIONS_OUT.[ActionEntity];

Also, I have not tested this method much and it still could produce errors later on. Thanks again for your help!

 

[plog]

Uh oh, this things goes deeper than I thought. What's the SQL of QRY_SUM_ACTIONS? That's where you should concentrate your work.

What you need to do is change the QTY field of records with an "OUT" in their Action field to negative numbers, then you SUM all those QTY values together.

Let's use this table (Inventory) as an example:

Inventory
ActionEntity, Action, QTY
ABC123, OUT, 15
ABC123, IN, 22
XYZ, IN, 14
ABC123, OUT, 4
XYZ, OUT, 12

You would then run this SQL:

SELECT ActionEntity, SUM(Iif(Action="OUT", -1, 1) * QTY) AS RemainingQty
FROM Inventory
GROUP BY ActionEntity;

And you get these results:

ActionEntity, RemainingQty
ABC123, 3
XYZ, 2

Again, you need to go to the lowest data source possible and probably not QRY_SUM_ACTIONS

 

[Adam McReynolds]

Uh oh, this things goes deeper than I thought. What's the SQL of QRY_SUM_ACTIONS? That's where you should concentrate your work.

What you need to do is change the QTY field of records with an "OUT" in their Action field to negative numbers, then you SUM all those QTY values together.

Let's use this table (Inventory) as an example:

Inventory
ActionEntity, Action, QTY
ABC123, OUT, 15
ABC123, IN, 22
XYZ, IN, 14
ABC123, OUT, 4
XYZ, OUT, 12

You would then run this SQL:

SELECT ActionEntity, SUM(Iif(Action="OUT", -1, 1) * QTY) AS RemainingQty
FROM Inventory
GROUP BY ActionEntity;

And you get these results:

ActionEntity, RemainingQty
ABC123, 3
XYZ, 2

Again, you need to go to the lowest data source possible and probably not QRY_SUM_ACTIONS

I created a new query using your SQL and it is working great! I am going to just use this. Thank you soooo much for your help.