Problem of calculating result

A

Alan48

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

Ofer

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])
 
A

Alan48

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

Ofer

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

Alan48

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem of calculation 2
Looping through data with calculations 1
Combine 2 Charts 2007 Excel 1
Help!! find two number recurring numbers in row?? 1
Need a query to return a value if out of range 4
Modifying a complex date formual to count months 4
Countifs or a pivot 1
Average problem 1

Top