Problem of calculation

[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.

 

[Wolfgang Kais]

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.

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

 

[Alex Dybenko]

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