select sql query from multiple table

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
 
R

Rob Parker

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
 
G

Guest

Many thanks to Rod

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

Qian
 
G

Guest

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
 
G

Guest

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
 
Top