If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Nested or Sub query?
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? |
#2
|
|||
|
|||
Nested or Sub query?
Joseph Atie wrote:
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 -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Nested or Sub query?
Thanks very much that help me out
"Marshall Barton" wrote: Joseph Atie wrote: 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 -- Marsh MVP [MS Access] . |
Thread Tools | |
Display Modes | |
|
|