Need help with select query statement for fields in different tables

[nightangel73]

Okay I have this query statement



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

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

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


Now what I want is that this query also displays me the UserName. But the UserName is in the table Users and I don't know how to join this field already having this union all statement. Help as always appreciated.

[June7]

You can have the JOIN take place in each of the SELECT lines or you can build another query that uses the UNION query and the Users table joining on the UserID.

Use the query builder to help you get the correct syntax for the SELECT JOIN and then edit the UNION.

[nightangel73]

You can have the JOIN take place in each of the SELECT lines or you can build another query that uses the UNION query and the Users table joining on the UserID.

Use the query builder to help you get the correct syntax for the SELECT JOIN and then edit the UNION.

I tried with join statement but it didn't work but probably is that I don't know how to code it right. I tried doing a new query but this union query doesn't let do anything else but to modify that code.

[June7]

What does 'didn't work' mean - error message, wrong results, nothing? Did you use the query builder to get the sql syntax for the JOIN?

Post your attempted SQL statement for analysis.

A UNION query can be used in another query so I don't understand your last statement.

[nightangel73]

ELECT "Acquisition" AS MovementType, LotNumber, Quantity, UserId, 'UserID.Name
Join Users
on StockAcquisitions.UserID = Users.UserID
FROM StockAcquisitions


UNION ALL
SELECT "Withdrawal" , LotNumber, Quantity*-1, UserId, UserID.Name
Join Users
on 'StockAcquisitions.UserID = Users.UserID

FROM StockWithdrawals


UNION ALL SELECT "Stocktake", LotNumber, Quantity , UserId, UserID.Name
Join Users
on' StockTakes.UserID = Users.UserID
FROM StockTakes;



It says it's syntax error..

[June7]

Missing join type (INNER, RIGHT, LEFT) and it is in wrong position.

Did you really use query builder to get those SELECT statements?

SELECT "Acquisition" AS MovementType, LotNumber, Quantity, Users.UserId, [Name] FROM StockAcquisitions INNER JOIN Users ON StockAcquisitions.UserID = Users.UserID

UNION ...


BTW, Name is a reserved word and should avoid reserved words as field names.

[nightangel73]

Missing join type (INNER, RIGHT, LEFT) and it is in wrong position.

Did you really use query builder to get those SELECT statements?

SELECT "Acquisition" AS MovementType, LotNumber, Quantity, Users.UserId, [Name] FROM StockAcquisitions INNER JOIN Users ON StockAcquisitions.UserID = Users.UserID

UNION ...


BTW, Name is a reserved word and should avoid reserved words as field names.

Oh no I couldn't use the query builder with the union all statement. I tried to create new query in wizard and call this query but it said that query is out reach for the wizard...

[June7]

My suggestion was to use the query builder to construct the SELECT statement. Then replicate that statement over in the UNION query for each SELECT line.

As for using the UNION or UNION ALL (once it is properly constructed) in another query - that is definitely possible. I have done it.

[nightangel73]

Okay using the code builder I have now this query. It works well but the problem is it repeats the transaction 4 times. I have no idea why it does 4 times.

SELECT 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]

You use "UNION ALL". It means all the records on all tables.

"UNION" should remove the duplicate. If that is what you want.

For more information,

http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

[nightangel73]

It still gives me the duplicates (not dupplicate but twiceduplicate lol).. I really have to figure this one out I appreciate so much your help.

[lfpm062010]

Can you post your query?

UNION should only select distinct value. unless the tables already have duplicate value.

[nightangel73]

Experimenting here is this other query I built. This one will give me what I want too but it asks me parameter value userid and name

SELECT LotNumber, Quantity, Users.UserID, Users.Name
FROM StockAcquisitions

UNION
SELECT LotNumber, Quantity*-1, Users.UserID, Users.Name
FROM StockWithdrawals

UNION SELECT LotNumber, Quantity, Users.UserID, Users.Name

FROM StockTakes;

[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

[lfpm062010]

SELECT LotNumber, Quantity, Users.UserID, Users.Name
FROM StockAcquisitions

UNION
SELECT LotNumber, Quantity*-1, Users.UserID, Users.Name
FROM StockWithdrawals

UNION SELECT LotNumber, Quantity, Users.UserID, Users.Name

FROM StockTakes;

There is no join to "USERS" Table. So, the "Users.UserID" and "Users.Name" is unknown.