Need help with select query statement for fields in different tables

[nightangel73]

This is the query Stockmovements

SELECT "Acquisition" AS MovementType, LotNumber, Quantity
FROM StockAcquisitions



UNION
SELECT "Withdrawal" , LotNumber, Quantity*-1
FROM StockWithdrawals

UNION SELECT "Stocktake", LotNumber, Quantity
FROM StockTakes;


What I want is this same query but with the user name added. But the users are in the table Users

So if I just simply build a new query using this union query Stock Movements (that works well) and the Users table I get duplicate records.

SELECT Users.UserID, Users.Name, qryStockMovements.MovementType, qryStockMovements.LotNumber, qryStockMovements.Quantity

FROM qryStockMovements, ((Users INNER JOIN StockAcquisitions ON Users.UserID = StockAcquisitions.UserID) INNER JOIN StockTakes ON Users.UserID = StockTakes.UserID) INNER JOIN StockWithdrawals ON Users.UserID = StockWithdrawals.UserID;

[lfpm062010]

Using "SELECT DISTINCT" will remove the duplicate from individual select.

You have to test and find out if that will give you the result that you are looking for.

[nightangel73]

This is my closest query so far, now if only you guys can help me with this join statement that I need. Everything I try it's a syntax error message of some sort.


SELECT "Acquisition" AS MovementType, LotNumber, Quantity, userID, userID.name
FROM StockAcquisitions

UNION
SELECT "Withdrawal" , LotNumber, Quantity*-1, UserID, userID.name
FROM StockWithdrawals

UNION SELECT "Stocktake", LotNumber, Quantity, UserID, userID.name
FROM StockTakes;

[nightangel73]

I got it!!! Here is the query I needed.

SELECT qryStockMovementsUnion.MovementType, qryStockMovementsUnion.LotNumber, qryStockMovementsUnion.Quantity, qryStockMovementsUnion.userID, Users.Name
FROM Users RIGHT JOIN qryStockMovementsUnion ON Users.UserID = qryStockMovementsUnion.userID;


I built another query in design view and created an outer join (#3) between the userID from the qryStockMovement and the UserID from the Users table. Then I dragged the fields I wanted and voila! Can't believe it was so simple.