Subtracting Data / Running Sum but SUBTRACTING /

[cpinney1120]

This seems so simple but I just cant figure out how to take inventory values and get the difference.
I have an item table with basic info and an inventory table with Just item no, inventory date and qty.
On report, I want to calculate the difference of the inventory. Not SUM and I just cant figure out how to do it.
Maybe I have to run a cross tab query or something but I cant seem to figure it out. Some inventory items may have 1 qty and some may have 15. I am basically calculating sales of closeout items without actually having the sales data I want to be able to say this many sold total by week but when inventory is gone, its gone. Is there a "difference" function like the sum?
Please help. It is really appreciated!!!!!!

 

[Guus2005]

Fields: item no, inventory date and qty.

You can solve this by creating a group by query (A) showing the sum for week 50 (inventory date between X and Y) per itemno
Create a group by query (B) showing the sum for week 51 (inventory date between Y and Z) per itemno

Create a third query (C) to subtract B from A

A: or B:

select itemno, sum(qty) as sumqty from inventory group by itemno where invdate between X and Y

C:

select a.itemno, b.sumqty - a.sumqty as Difference from A inner join B on a.itemno = b.itemno

Above code is an example.

With no doubt there are faster ways to do it. I believe this step by step approach is easier to understand.

HTH