Inventory

[Irene]

hi, i need you all help. I've two table which is table DO and table time
sheet. Table DO have 4 field, that is DO number, DO qty, DO date and PartID.
Table time sheet have autonumber, PartID, Packing Date, Location, Lot no,
Packing qty and delivered status. now i want to do running sum which is group
by PartID.
example:
PartID Packing qty Running Sum
A 500 500
A 100 600
A 500 1100
B 1000 1000
B 500 1500

Can i make it like this? Cos i want to use it when the DO issue, then the DO
qty will minus out the Part from the table time sheet and it is by sequence
like FIFO method. so i will use date to let the Part which packing first the
deliver first.

Example:
i have a DO no 1234 and DO qty is 1000 for PartID A and DO no 1235, DO qty
500 for PartID B. so i want the result come out for the table time sheet like
this:
PartID Packing qty Running Sum
A 100 100
B 500 500
B 500 1000

can make it? hope somebody can help me. thanks!

 

[Michel Walsh]

Sure, there are many solutions. I prefer the joins:


SELECT a.partID, a.packingQty, SUM(b.packingQty) As RunningSum
FROM tableName As a INNER JOIN tableName As b
ON a.partID=b.partID AND a.date >= b.date
GROUP BY a.partID, a.packingQty



Hoping it may help,
Vanderghast, Access MVP

 

[Irene]

Thanks for your reply and i really appreciate.

besides this, i would like to further more. now i have another question. now
my DO table is like this:

DO no DOqty DODate PartID
1234 1000 8/8/07 A
1235 1000 8/8/07 B

Then table time sheet is like that
PartID Packing Qty DatePacking Delivered
A 300 1/8/07 no
A 1000 5/8/07 no
B 2000 5/8/07 no

i want to use FIFO method. then i want my result come out as below:
PartID Packing Qty DatePacking Delivered Balance
A 300 1/8/07 yes 0
A 1000 5/8/07 no 300
B 2000 5/8/07 no 1000

can make it? the delivered status is when all finish delivery (means
balance=0) then will become yes. i try to make it but i jus can do for one
part. if have two part, then i dont know how to do it. can you help me? i
want to use the DOqty to minus PackingQty by date cos use FIFO system and
have different location.

thanks!

 

[Michel Walsh]

It is easier if there is just one set of data (maybe with an UNION ALL) ,
packingQty as negative:


SELECT DoPartID As PartID, DoDate, Doqty As quantity FROM do
UNION ALL
SELECT PartID, DatePacking, -PackingQty FROM packing



Say it is qu1 (or make a table out of it, with indexes on DoDate and on
PartID ) You can now make the running sum, on that query (table) as before.
Let us call it qrun.

Open another query based on qrun, keep only rows where quantity is <0,
change back the negative quantity for a positive one, and check if the
running sum is not-negative (in which case it was possible to complete the
shipment) or not (in which case there was some back-order quantity).


Hoping it makes sense,
Vanderghast, Access MVP