Problem of calculation

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

Guest

i have three tables. They call "tlbInventory", "tlbDelivery" and "tlbResult'.
tlbInventory consist of 4 fields - lot_no, item_no, rcv_date, quantity
tlbDelivery consist of 4 fields - lot_no, item_no, rcv_date, quantity

for example:
one of the record i want to search in tlbInventory: 012345, 44552,
13-Jun-05, 40
in tlbDelivery must have the same fields of lot_no, item_no and rcv_date
record :
012345, 44552, 13-Jun-05, 3
012345, 44552, 13-Jun-05, 10
012345, 44552, 13-Jun-05, 6
012345, 44552, 13-Jun-05, 2

I want the total result would check all records each time from tlbDelivery
like above (e.g. found out 4 records) and count 'quantity' field (3+10+6+2 =
21). The count result can save as "tlbResult" to be 012345, 44552, 13-Jun-05,
29.
29 come from 40-21.

Would you help me to solve this problem. if you don't mind, please give me
an example, thank you very much.
 
Alan48 said:
I have three tables. They call "tlbInventory", "tlbDelivery" and
"tlbResult'.
tlbInventory consist of 4 fields - lot_no, item_no, rcv_date, quantity
tlbDelivery consist of 4 fields - lot_no, item_no, rcv_date, quantity

for example:
one of the record i want to search in tlbInventory: 012345, 44552,
13-Jun-05, 40
in tlbDelivery must have the same fields of lot_no, item_no and rcv_date
record :
012345, 44552, 13-Jun-05, 3
012345, 44552, 13-Jun-05, 10
012345, 44552, 13-Jun-05, 6
012345, 44552, 13-Jun-05, 2

I want the total result would check all records each time from tlbDelivery
like above (e.g. found out 4 records) and count 'quantity' field (3+10+6+2
=
21). The count result can save as "tlbResult" to be 012345, 44552,
13-Jun-05,
29.
29 come from 40-21.
Click to expand...

How about using only 1 table tblInAndOut, with records like this:
012345, 44552, 13-Jun-05, -3
012345, 44552, 13-Jun-05, -10
012345, 44552, 13-Jun-05, -6
012345, 44552, 13-Jun-05, -2
012345, 44552, 13-Jun-05, 40

You can use a query qryInventory like this (enter in SQL view):
SELECT lot_no, item_no, rcv_date, SUM(quantity) AS [Sum of quantity]
FROM tblInAndOut
GROUP BY lot_no, item_no, rcv_date
 
as in understand - you can make a select query based on tlbDelivery, group
by lot_no, item_no and rcv_date
and sum by quantity. then join this query with tlbInventory by all three
fields lot_no, item_no and rcv_date and next field will be an epression of
tlbInventory quantity and new query sumof quantity. and finaly make this
query as append query to append records to tlbResult
 
Back
Top