select sql query from multiple table

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

Guest

Hello everyone,

I have a problem in a sql query used in MS ACCESS. I intend to get a
updated stocklevel for products. procedure is
Sum(inwardsQuantity)-Sum(outwardsQuantity)=stocklevel. However, my following
table and query design give ms wrong figure on stock levle. Please have a
look:

Tbl_Products

ProductID ProductCode ProductName
4 56987 Australia Wine
5 50 Chinese high spirit
6 232323 Queensland Beer



Tbl_TransInwards

TransactionID ProductID Quantity
1 4 444
2 5 555
3 6 666
4 4 44
5 5 55
6 6 10

Tbl_TransOutwards

TransactionID ProductID Quantity
1 4 44
2 5 55
3 6 66

*Above three tables be related through ProductID

Query_TOTALINWARDS

SELECT P.ProductID, P.ProductCode, P.ProductName, Sum(TI.Quantity) AS
TOTALINWARDS
FROM Products AS P LEFT JOIN TransInwards AS TI ON P.ProductID=TI.ProductID
GROUP BY P.ProductID, P.ProductCode, P.ProductName;

Run the above query, result:

ProductID ProductCode ProductName TOTALINWARDS
4 56987 Australia Wine 488
5 50 Chinese high spirit 610
6 232323 Queensland Beer 676

Query_TOTALOUTWARDS

SELECT P.ProductID, P.ProductCode, P.ProductName, Sum(TO.Quantity) AS
TOTALOUTWARDS
FROM Products AS P LEFT JOIN TransOutwards AS TO ON P.ProductID=TO.ProductID
GROUP BY P.ProductID, P.ProductCode, P.ProductName;

Run the above query, result:

ProductID ProductCode ProductName TOTALOUTWARDS
4 56987 Australia Wine 44
5 50 Chinese high spirit 55
6 232323 Queensland Beer 66

*Up to now, everything goes all right!

Query_currentStockLevel

SELECT P.ProductID, P.ProductCode, P.ProductName, Sum(TI.Quantity) AS
TOTALINWARDS,
Sum(TO.Quantity) AS TOTALOUTWARDS,
(TOTALINWARDS-TOTALOUTWARDS) AS StockLevel
FROM Products AS P, TransInwards AS TI, TransOutwards AS TO
WHERE P.ProductID=TI.ProductID and P.ProductID=TO.ProductID
GROUP BY P.ProductID, P.ProductCode, P.ProductName;

Run above query, result:

ProductID ProductCode ProduceName OTALINWARDS TOTALOUTWARDS StockLevel
4 56987 Australia Wine 488 88 400

5 50 Chinese high spirit 610 110 500

6 232323 Queensland Beer 676 132 544

Problem: You can see from here, the above query generates wrong
TOTALOUTWARDS figures for all Product ID 4,5,6.
Those figures double their correct one. I.E. 44 becomes88; 55
becomes110, 66 becomes132.
No doubt, there is a mistake in the above Query_currentStockLevel

Any precious advice?

Thanks very much
 
Your final query needs to have inner joins between the Products table and
the two transaction queries.

The SQL statement is:
SELECT P.ProductID, P.ProductCode, P.ProductName, TI.TOTALINWARDS,
TO.TOTALOUTWARDS, (TOTALINWARDS-TOTALOUTWARDS) AS StockLevel
FROM (Products AS P INNER JOIN TotalInwards AS TI ON P.ProductID =
TI.ProductID)
INNER JOIN TotalOutwards AS TO ON P.ProductID = TO.ProductID;

Check how this appears in the QBE window.

Rob
 
Many thanks to Rod

Yes, as you suggested, the Inner Join fix the problem. Stock Level shows
the right figure now.

Qian
 
Hi Rod

May I ask another question regarding value enquiry?

Say, now I can get the stock Level for the whole list of items in the store.
But can I just type a certain item's ProductCode or ProductName, then it
will show the stocklevel for this item?

I believe ACCESS can do it. But I am not sure how to approch it and what the
query I should use.

Thanks a lot
 
Hello again

I got another quetion about query in MS ACCESS.

I tried to build a query which will produce a report on total inwards and
total outwards quantity of all stock items during a certain period of time.
The required variables :
BeginningDate; EndingDate

My query looks like this

SELECT P.ProductID, P.ProductCode, P.ProductName, Nz(Sum(TI.Quantity),0) AS
TOTALINWARDS, Nz(Sum(TO.Quantity),0) AS TOTALOUTWARDS
FROM (Products AS P INNER JOIN TransInwards AS TI ON
P.ProductID=TI.ProductID) INNER JOIN TransOutwards AS [TO] ON
P.ProductID=TO.ProductID
WHERE BeginningDate<TI.TransactionDate<EndingDate And
BeginningDate<TO.TransactionDate<EndingDate
GROUP BY P.ProductID, P.ProductCode, P.ProductName;

When I tried to run the query, a popup window asked for input BeginningDate
and EndingDate, then I type in something like 01/01/2004, 01/01/2005, then it
shows no data in a report, except a row of tiles for ProductID, ProductCode,
ProductName TOTALINWARDS,TOTALOUTWARDS.

Note here:
tblProduct has fileds: ProductID, ProductCode, ProductName
tblTransInwards has fileds: TransID, ProductID,TransactionDate,Quantity
tblTransOutwards has fields: TransID, ProductID,TransactionDate,Quantity

May I have anybody's precious advice on this query?

Thanks so much

Jen
 
Back
Top