Initial and Remaining Calc.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with 4 fields InitialQty, RemovedQty, RemainingQty & ID .It is
a basic inventory form. For every item I am trying to track IntialQty,
RemovedQty, and remainingQty. The ID is just an increment
Can you plesae help me to achieve this purpose.
I used qtyremaining=qtyintial-qtyremoved.
But the problem for me is how to find the new qtyinitial
Bassel
 
Hi,


Don't update the quantity, append a record for each operation, then use a
Total query that SUM the quantities.


SELECT Item, SUM(quantity) As Remaining
FROM myTable
GROUP BY item



where quantity is positive for a starting amount, and for receiving
material, but negative if the item is removed. The sum of the + and -
quantities return the remaining quantity, for each item, and you use that
query to get that result, instead of the initial table, in cases you need
that information.


Hoping it may help,
Vanderghast, Access MVP
 
Thank you for your reply.
But i need to have a track of the transaction for example:
The form sholud look like this

Id Intial Qty RemovedQty Remaining QTY
1 200 0 200
2 200 20 180
3 180 50 130

Best regards
 
Hi,

You then have a dateStamp field that defines the ordering? Try something
like:



SELECT a.DateStamp, LAST(a.qty) As InOut, SUM(b.qty) As Remaining
FROM myTable as a INNER JOIN myTable As b
ON b.dateStamp <= a.DateStamp
GROUP BY a.DateStamp






So, if your table is like:

DateStamp Qty ' fields
2001.01.01 10
2001.02.02 -5
2001.03.03 -4
2001.05.05 7 ' data sample


the above query returns


DateStamp InOut Remaining
2001.01.01 10 10
2001.02.02 -5 5
2001.03.03 -4 1
2001.04.04 7 8




Hoping it may help,
Vanderghast, Access MVP
 
Thank you

But how can I use it if I am tracking two items.
Thus on same datestamp I will have two item codes, with two entries.

Best regards
 
Hi,


Sure.

----------------------------------
SELECT a.DateStamp,
a.ItemID,
LAST(a.qty),
SUM(b.qty)

FROM myTable As a INNER JOIN myTable As b
ON a.ItemID=b.ItemID
And b.DateStamp <= a.DateStamp

GROUP BY a.DateStamp, a.ItemID
----------------------------------

Note that now, the SUM, on the second reference to the table, b, is now not
only limited to what occurred before (b.DateStamp <= a.DateStamp), but also,
that SUM is limited to only those Items matching those of the first
instance.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top