Problem of calculating result

  • 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.
 
The result should be 19

Try this query
SELECT tlbDelivery.lot_no, tlbDelivery.item_no, tlbDelivery.rcv_date,
tlbInventory.quantity, Sum(tlbDelivery.quantity) AS Sumquantity,
[tlbInventory].[quantity]-Sum([tlbDelivery].[quantity]) AS QuantityLeft
FROM tlbInventory INNER JOIN tlbDelivery ON (tlbInventory.rcv_date =
tlbDelivery.rcv_date) AND (tlbInventory.item_no = tlbDelivery.item_no) AND
(tlbInventory.lot_no = tlbDelivery.lot_no)
GROUP BY tlbDelivery.lot_no, tlbDelivery.item_no, tlbDelivery.rcv_date,
tlbInventory.quantity

A group by query, that join the two tables with the fields you specify
When the expression id for
[tlbInventory].[quantity]-Sum([tlbDelivery].[quantity])
 
Thanks Ofer, your query method is very helpful. Actually, i have a more table
want to calculate. "tlbDelivery", "tlbInventory", "tlbReturn"

tlbInventory: 012345, 44552, 13-Jun-05, 40

tlbDelivery: 012345, 44552, 13-Jun-05, 3
012345, 44552, 13-Jun-05, 10
012345, 44552, 13-Jun-05, 6
012345, 44552, 13-Jun-05, 2

tlbReturn: tlbInventory: 012345, 44552, 13-Jun-05, 2
012345, 44552, 13-Jun-05, 5

Result = 40 - (3+10+6+2) - (2+5)

I have tried to modify your coding to

SELECT tlbDelivery.lot_no, tlbDelivery.item_no, tlbDelivery.rcv_date,
tlbReturn.lot_no, tlbReturn.item_no, tlbReturn.rcv_date,
tlbReceiving.quantity, Sum(tlbDelivery.quantity)+Sum(tlbReturn.quantity) AS
Sumquantity,
[tlbReceiving].[quantity]-Sum([tlbDelivery].[quantity])-Sum([tlbReturn].[quantity]) AS QuantityLeft
FROM tlbReceiving INNER JOIN tlbDelivery, tlbReturn ON
(tlbReceiving.rcv_date =
tlbDelivery.rcv_date = tlbReturn.rcv_date) AND (tlbReceiving.item_no =
tlbDelivery.item_no = tlbReturn.item_no) AND
(tlbReceiving.lot_no = tlbDelivery.lot_no = tlbReturn.lot_no)
GROUP BY tlbDelivery.lot_no, tlbDelivery.item_no, tlbDelivery.rcv_date,
tlbReturn.lot_no, tlbReturn.item_no, tlbReturn.rcv_date, tlbReceiving.quantity

But it prompt "SQL Error", also in my case.
Sometime the tlbDelivery has record but tlbResult has no record of specify
item no
so i hope the sql string can run either tlbrecord does or not. thank you so
much.
 
Are you sure that it should be
Result = 40 - (3+10+6+2) - (2+5)
And not
Result = 40 - (3+10+6+2) + (2+5)

Now follow this stages:
================================================
Create Query 1 - Name:SumReturn

SELECT tlbReturn.lot_no, tlbReturn.item_no, tlbReturn.rcv_date,
Sum(tlbReturn.quantity) AS SumReturnquantity
FROM tlbReturn
GROUP BY tlbReturn.lot_no, tlbReturn.item_no, tlbReturn.rcv_date

================================================
Create Query 2 - Name:SumDelivery

SELECT tlbDelivery.lot_no, tlbDelivery.item_no, tlbDelivery.rcv_date,
Sum(tlbDelivery.quantity) AS SumDeliveryquantity
FROM tlbDelivery
GROUP BY tlbDelivery.lot_no, tlbDelivery.item_no, tlbDelivery.rcv_date
================================================
Create Query 3 - That will join the two queries with tlbInventory, with NZ
incase there are no records return from the top queries

SELECT tlbInventory.lot_no, tlbInventory.item_no, tlbInventory.rcv_date,
[quantity]-nz([SumReturnquantity],0)-nz([SumDeliveryquantity],0) AS Expr1
FROM (tlbInventory LEFT JOIN SumReturn ON (tlbInventory.rcv_date =
SumReturn.rcv_date) AND (tlbInventory.item_no = SumReturn.item_no) AND
(tlbInventory.lot_no = SumReturn.lot_no)) LEFT JOIN SumDelivery ON
(tlbInventory.rcv_date = SumDelivery.rcv_date) AND (tlbInventory.item_no =
SumDelivery.item_no) AND (tlbInventory.lot_no = SumDelivery.lot_no)
GROUP BY tlbInventory.lot_no, tlbInventory.item_no, tlbInventory.rcv_date,
[quantity]-nz([SumReturnquantity],0)-nz([SumDeliveryquantity],0)
=============================================
HTH

Alan48 said:
Thanks Ofer, your query method is very helpful. Actually, i have a more table
want to calculate. "tlbDelivery", "tlbInventory", "tlbReturn"

tlbInventory: 012345, 44552, 13-Jun-05, 40

tlbDelivery: 012345, 44552, 13-Jun-05, 3
012345, 44552, 13-Jun-05, 10
012345, 44552, 13-Jun-05, 6
012345, 44552, 13-Jun-05, 2

tlbReturn: tlbInventory: 012345, 44552, 13-Jun-05, 2
012345, 44552, 13-Jun-05, 5

Result = 40 - (3+10+6+2) - (2+5)

I have tried to modify your coding to

SELECT tlbDelivery.lot_no, tlbDelivery.item_no, tlbDelivery.rcv_date,
tlbReturn.lot_no, tlbReturn.item_no, tlbReturn.rcv_date,
tlbReceiving.quantity, Sum(tlbDelivery.quantity)+Sum(tlbReturn.quantity) AS
Sumquantity,
[tlbReceiving].[quantity]-Sum([tlbDelivery].[quantity])-Sum([tlbReturn].[quantity]) AS QuantityLeft
FROM tlbReceiving INNER JOIN tlbDelivery, tlbReturn ON
(tlbReceiving.rcv_date =
tlbDelivery.rcv_date = tlbReturn.rcv_date) AND (tlbReceiving.item_no =
tlbDelivery.item_no = tlbReturn.item_no) AND
(tlbReceiving.lot_no = tlbDelivery.lot_no = tlbReturn.lot_no)
GROUP BY tlbDelivery.lot_no, tlbDelivery.item_no, tlbDelivery.rcv_date,
tlbReturn.lot_no, tlbReturn.item_no, tlbReturn.rcv_date, tlbReceiving.quantity

But it prompt "SQL Error", also in my case.
Sometime the tlbDelivery has record but tlbResult has no record of specify
item no
so i hope the sql string can run either tlbrecord does or not. thank you so
much.

Ofer said:
The result should be 19

Try this query
SELECT tlbDelivery.lot_no, tlbDelivery.item_no, tlbDelivery.rcv_date,
tlbInventory.quantity, Sum(tlbDelivery.quantity) AS Sumquantity,
[tlbInventory].[quantity]-Sum([tlbDelivery].[quantity]) AS QuantityLeft
FROM tlbInventory INNER JOIN tlbDelivery ON (tlbInventory.rcv_date =
tlbDelivery.rcv_date) AND (tlbInventory.item_no = tlbDelivery.item_no) AND
(tlbInventory.lot_no = tlbDelivery.lot_no)
GROUP BY tlbDelivery.lot_no, tlbDelivery.item_no, tlbDelivery.rcv_date,
tlbInventory.quantity

A group by query, that join the two tables with the fields you specify
When the expression id for
[tlbInventory].[quantity]-Sum([tlbDelivery].[quantity])
Click to expand...
Click to expand...
 
Ofer, Thank for your help. The code is very helpful. And my problem is solved
now. Thanks

Ofer said:
Are you sure that it should be
Result = 40 - (3+10+6+2) - (2+5)
And not
Result = 40 - (3+10+6+2) + (2+5)

Now follow this stages:
================================================
Create Query 1 - Name:SumReturn

SELECT tlbReturn.lot_no, tlbReturn.item_no, tlbReturn.rcv_date,
Sum(tlbReturn.quantity) AS SumReturnquantity
FROM tlbReturn
GROUP BY tlbReturn.lot_no, tlbReturn.item_no, tlbReturn.rcv_date

================================================
Create Query 2 - Name:SumDelivery

SELECT tlbDelivery.lot_no, tlbDelivery.item_no, tlbDelivery.rcv_date,
Sum(tlbDelivery.quantity) AS SumDeliveryquantity
FROM tlbDelivery
GROUP BY tlbDelivery.lot_no, tlbDelivery.item_no, tlbDelivery.rcv_date
================================================
Create Query 3 - That will join the two queries with tlbInventory, with NZ
incase there are no records return from the top queries

SELECT tlbInventory.lot_no, tlbInventory.item_no, tlbInventory.rcv_date,
[quantity]-nz([SumReturnquantity],0)-nz([SumDeliveryquantity],0) AS Expr1
FROM (tlbInventory LEFT JOIN SumReturn ON (tlbInventory.rcv_date =
SumReturn.rcv_date) AND (tlbInventory.item_no = SumReturn.item_no) AND
(tlbInventory.lot_no = SumReturn.lot_no)) LEFT JOIN SumDelivery ON
(tlbInventory.rcv_date = SumDelivery.rcv_date) AND (tlbInventory.item_no =
SumDelivery.item_no) AND (tlbInventory.lot_no = SumDelivery.lot_no)
GROUP BY tlbInventory.lot_no, tlbInventory.item_no, tlbInventory.rcv_date,
[quantity]-nz([SumReturnquantity],0)-nz([SumDeliveryquantity],0)
=============================================
HTH

Alan48 said:
Thanks Ofer, your query method is very helpful. Actually, i have a more table
want to calculate. "tlbDelivery", "tlbInventory", "tlbReturn"

tlbInventory: 012345, 44552, 13-Jun-05, 40

tlbDelivery: 012345, 44552, 13-Jun-05, 3
012345, 44552, 13-Jun-05, 10
012345, 44552, 13-Jun-05, 6
012345, 44552, 13-Jun-05, 2

tlbReturn: tlbInventory: 012345, 44552, 13-Jun-05, 2
012345, 44552, 13-Jun-05, 5

Result = 40 - (3+10+6+2) - (2+5)

I have tried to modify your coding to

SELECT tlbDelivery.lot_no, tlbDelivery.item_no, tlbDelivery.rcv_date,
tlbReturn.lot_no, tlbReturn.item_no, tlbReturn.rcv_date,
tlbReceiving.quantity, Sum(tlbDelivery.quantity)+Sum(tlbReturn.quantity) AS
Sumquantity,
[tlbReceiving].[quantity]-Sum([tlbDelivery].[quantity])-Sum([tlbReturn].[quantity]) AS QuantityLeft
FROM tlbReceiving INNER JOIN tlbDelivery, tlbReturn ON
(tlbReceiving.rcv_date =
tlbDelivery.rcv_date = tlbReturn.rcv_date) AND (tlbReceiving.item_no =
tlbDelivery.item_no = tlbReturn.item_no) AND
(tlbReceiving.lot_no = tlbDelivery.lot_no = tlbReturn.lot_no)
GROUP BY tlbDelivery.lot_no, tlbDelivery.item_no, tlbDelivery.rcv_date,
tlbReturn.lot_no, tlbReturn.item_no, tlbReturn.rcv_date, tlbReceiving.quantity

But it prompt "SQL Error", also in my case.
Sometime the tlbDelivery has record but tlbResult has no record of specify
item no
so i hope the sql string can run either tlbrecord does or not. thank you so
much.

Ofer said:
The result should be 19

Try this query
SELECT tlbDelivery.lot_no, tlbDelivery.item_no, tlbDelivery.rcv_date,
tlbInventory.quantity, Sum(tlbDelivery.quantity) AS Sumquantity,
[tlbInventory].[quantity]-Sum([tlbDelivery].[quantity]) AS QuantityLeft
FROM tlbInventory INNER JOIN tlbDelivery ON (tlbInventory.rcv_date =
tlbDelivery.rcv_date) AND (tlbInventory.item_no = tlbDelivery.item_no) AND
(tlbInventory.lot_no = tlbDelivery.lot_no)
GROUP BY tlbDelivery.lot_no, tlbDelivery.item_no, tlbDelivery.rcv_date,
tlbInventory.quantity

A group by query, that join the two tables with the fields you specify
When the expression id for
[tlbInventory].[quantity]-Sum([tlbDelivery].[quantity])
Click to expand...
Click to expand...
Click to expand...
 
Back
Top