Nested or Sub query?

[Joseph Atie]

Im a bit lost as to how to go about this, I have 3 queries that i built in
access query builder which join 4 tables together in order to generate a
report

What I would like to do is turn those 3 queries into a single sql statement
that can be run from vba.

or run all three in vba and get the correct output.

so here are my queries

Query A
SELECT Transdata.Barcode, Transdata.Out, Transdata.[In],
Transactions.Usercode, Transactions.location
FROM Transactions LEFT JOIN Transdata ON Transactions.Transcode =
Transdata.Transcode
WHERE (((Transdata.[In]) Is Null));

Query B
SELECT Equipment.Name, Equipment.Description, Equipment.Type,
onhire_a.location, onhire_a.Out, onhire_a.Usercode
FROM Equipment RIGHT JOIN onhire_a ON Equipment.Barcode=onhire_a.Barcode;

Query C
SELECT Users.[First Name], Users.Surname, onhire_b.Name,
onhire_b.Description, onhire_b.Type, onhire_b.location, onhire_b.Out
FROM onhire_b LEFT JOIN Users ON onhire_b.Usercode = Users.Usercode;


as you can see:
query A joins 2 tables (transactions & trans_data).
query B joins query A & equipment.
query C joins query B & users to give me the final output.

is it possible to turn these three queries into 1 and if so how do i go
about it?

 

[Marshall Barton]

Im a bit lost as to how to go about this, I have 3 queries that i built in
access query builder which join 4 tables together in order to generate a
report

What I would like to do is turn those 3 queries into a single sql statement
that can be run from vba.

Query A
SELECT Transdata.Barcode, Transdata.Out, Transdata.[In],
Transactions.Usercode, Transactions.location
FROM Transactions LEFT JOIN Transdata ON Transactions.Transcode =
Transdata.Transcode
WHERE (((Transdata.[In]) Is Null));

Query B
SELECT Equipment.Name, Equipment.Description, Equipment.Type,
onhire_a.location, onhire_a.Out, onhire_a.Usercode
FROM Equipment RIGHT JOIN onhire_a ON Equipment.Barcode=onhire_a.Barcode;

Query C
SELECT Users.[First Name], Users.Surname, onhire_b.Name,
onhire_b.Description, onhire_b.Type, onhire_b.location, onhire_b.Out
FROM onhire_b LEFT JOIN Users ON onhire_b.Usercode = Users.Usercode;


as you can see:
query A joins 2 tables (transactions & trans_data).
query B joins query A & equipment.
query C joins query B & users to give me the final output.

is it possible to turn these three queries into 1 and if so how do i go
about it?

Have you tried just joining all the tables in one query? I
think it may be something like this, but it usually takes ne
a cople of tried to get the ( )s in the right places:

SELECT Transdata.Barcode, Transdata.Out, Transdata.[In],
Transactions.Usercode, Transactions.location,
Equipment.Name, Equipment.Description,
Equipment.Type, onhire_a.location, onhire_a.Out,
onhire_a.Usercode, Users.[First Name],
Users.Surname, onhire_b.Name, onhire_b.Description,
onhire_b.Type, onhire_b.location, onhire_b.Out
FROM ((Transactions
LEFT JOIN Transdata
ON Transactions.Transcode = Transdata.Transcode)
RIGHT JOIN onhire_a
ON Equipment.Barcode=onhire_a.Barcode)
LEFT JOIN Users
ON onhire_b.Usercode = Users.Usercode
WHERE Transdata.[In] Is Null

 

[Joseph Atie]

Thanks very much that help me out

Im a bit lost as to how to go about this, I have 3 queries that i built in
access query builder which join 4 tables together in order to generate a
report

What I would like to do is turn those 3 queries into a single sql statement
that can be run from vba.

Query A
SELECT Transdata.Barcode, Transdata.Out, Transdata.[In],
Transactions.Usercode, Transactions.location
FROM Transactions LEFT JOIN Transdata ON Transactions.Transcode =
Transdata.Transcode
WHERE (((Transdata.[In]) Is Null));

Query B
SELECT Equipment.Name, Equipment.Description, Equipment.Type,
onhire_a.location, onhire_a.Out, onhire_a.Usercode
FROM Equipment RIGHT JOIN onhire_a ON Equipment.Barcode=onhire_a.Barcode;

Query C
SELECT Users.[First Name], Users.Surname, onhire_b.Name,
onhire_b.Description, onhire_b.Type, onhire_b.location, onhire_b.Out
FROM onhire_b LEFT JOIN Users ON onhire_b.Usercode = Users.Usercode;


as you can see:
query A joins 2 tables (transactions & trans_data).
query B joins query A & equipment.
query C joins query B & users to give me the final output.

is it possible to turn these three queries into 1 and if so how do i go
about it?

Have you tried just joining all the tables in one query? I
think it may be something like this, but it usually takes ne
a cople of tried to get the ( )s in the right places:

SELECT Transdata.Barcode, Transdata.Out, Transdata.[In],
Transactions.Usercode, Transactions.location,
Equipment.Name, Equipment.Description,
Equipment.Type, onhire_a.location, onhire_a.Out,
onhire_a.Usercode, Users.[First Name],
Users.Surname, onhire_b.Name, onhire_b.Description,
onhire_b.Type, onhire_b.location, onhire_b.Out
FROM ((Transactions
LEFT JOIN Transdata
ON Transactions.Transcode = Transdata.Transcode)
RIGHT JOIN onhire_a
ON Equipment.Barcode=onhire_a.Barcode)
LEFT JOIN Users
ON onhire_b.Usercode = Users.Usercode
WHERE Transdata.[In] Is Null