Quantity Adjustment Query

[Uvuriel03]

I have two tables, a "Product In" table and a "Product Out" table. They have lots of different products, each skid of which is labeled with it's own unique ID. The In table will only have one entry for each pallet ID, the out table could have several of the same pallet IDs, and not necessarily in order.

I want to take the quantity that came in on the pallet and subtract from it the total amount that has been taken OFF of that pallet, resulting in the current remaining quantity on the pallet.

For example:

IN:
Pallet 1: 50 qty
Pallet 2: 25 qty
Pallet 3: 10 qty
Etc...

OUT:
Pallet 1: 10 qty
Pallet 2: 25 qty
Pallet 1: 15 qty
Pallet 3: 1 qty
Etc...

So then my result query would show:

REMAINING:
Pallet 1: 35 qty
Pallet 2: 0 qty
Pallet 3: 9 qty

I don't know how to go about getting this result though.

Thanks in advance for your help!

 

[khawar]

Download the attached sample

 

[Uvuriel03]

Thanks! I'll see what I can't do with that. =)