Balance stock

[dr]

Hi All,
Have a good day.

I have 2 table,Incoming_tbl ( PK : incomingID --> auto number )
and Outgoing_tbl ( PK : outgoingID --> auto number ).
They are related each others through IncomingID with
referential integrity on.

Now I want to create a query that count balance stock ( input - output ).
But I'm facing a problem,some incoming products that have no related
outgoing data,not listed in the balance stock query.I think it caused by
the relationship.How to solve it?

Thank's in advance.
Regards,

 

[Ken Sheridan]

I'm puzzled by these tables' being related on the incomingID columns. In
fact I don't see why they are related directly at all rather than each
referencing a separate Products table with a productID foreign key column in
each referencing the primary key of Products. That way you can compute the
stock in hand per product by means of subqueries:

SELECT product,
(SELECT SUM(quantity)
FROM Incoming_tbl
WHERE Incoming_tbl.productID = Products.productID)
-NZ(SELECT SUM(quantity)
FROM Outgoing_tbl
WHERE Outgoing_tbl.productID = Products.productID),0)
AS balance
FROM Products;

where quantity is the name of the columns in the two tables with the number
of each product added to/removed from stock at each stock movement in or out.
Calling the Nz function caters for those products which have been moved into
stock, but not out.

Ken Sheridan
Stafford, England

 

[dr]

Thank's for response Ken,
but I can't figure it out.
I created Products table as your mentioned,
but in the query,it sends me error message:
extra ) in query expression '0)'

I deleted 0) but everything going wild then.

 

[Ken Sheridan]

Mea culpa! I missed an opening parenthesis. It should be:

SELECT product,
(SELECT SUM(quantity)
FROM Incoming_tbl
WHERE Incoming_tbl.productID = Products.productID)
-NZ((SELECT SUM(quantity)
FROM Outgoing_tbl
WHERE Outgoing_tbl.productID = Products.productID),0)
AS balance
FROM Products;

The zero is returned by the Nz function if the second subquery returns Null,
i.e. if there are no outward movements from stock for the product in
question. Without it Null would be subtracted from the result of the first
subquery, and the result would be Null (in the jargon its said that Nulls
'propagate' in arithmetical expressions) rather than stock in hand.

Apologies for the confusion.

Ken Sheridan
Stafford, England