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?


0
Utf
11/25/2009 1:50:01 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1023 Views

Similar Articles

[PageSpeed] 8

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]
0
Marshall
11/25/2009 4:44:00 PM
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]
> .
> 
0
Utf
11/25/2009 8:47:01 PM
Reply:

Similar Artilces:

Need some query help.....
I'm very new to Access. I was wondering how I could display a field in a query that is conditional. I will do my best to explain... I want to add a field to my query that grabs the info from Project.Badge or Project.BadgeET depending on if the field TestType is displaying "Eng" or "Pkg" so it would be something like this If TestType = "Eng" then Display Project.BadgeET ElseIf TestType = "Pkg" then Display Project.Badge End if im assuming I would type some formula in the Criteria section?? but under field, i can on...

Parse textbox words for query values
I've created a very simple asp.net web form (via visual studio 2008) that has a text box and a submit button. I want to be able to paste a bunch of 'words' into the textbox and have the words used in a select statement. Something like SELECT name, address, status FROM StatsInfo WHERE status IN (textbox-word1, textbox-word2, textbox-word3,...) I've got something simple working where a single word can be queried, but I can't find a way to parse the contents of the textbox so the query searches for each word. Do you have or know of any examples you co...

Self linking tabel in editabel query
Hi I have a StaffBookings table with Date, Time, StaffID and ClientID columns which I use to enter information via a bound form. Now I also need to have a column that shows the count of jobs that the staff has done. My solution would be to do a count query on StaffBookings table and link it to the StaffBookings table itself. Problem is that such a query makes the form un-editable as far as I understand. How can I get round that; that form bound to StaffBookings table is editable but also has the count? Thanks Regards John You've described a "how"...

Use query with parameters as datasource for subform
Hi I have a subform which gets its data from a query. This query uses parameters I want to be able to modify via textboxes on the main form. The user should enter some values and then press a button which updates the subform. How can I do that? Thx in advance On Sun, 10 Feb 2008 13:48:43 -0800 (PST), julius.fuchs@yahoo.de wrote: >Hi > >I have a subform which gets its data from a query. This query uses >parameters I want to be able to modify via textboxes on the main form. >The user should enter some values and then press a button which >updates the subform. >How can I ...

Newbie with Panel & User Control Query
Hi All, Firstly, im a real newbie to C# so appologies for asking what might be a very basic question. Basically im using DotNetBar and have a Ribbon form. On my Ribbon form, I have a ribbon Bar, With 5 buttons. I also have a a panel on my Main form (pnlMainMenu) I have serveral user controls also. When a user clicks on a button I would like it to display the user control in the Panel. So far i have the following code:- private void btnComplaints_Click(object sender, EventArgs e) { pnlMainMenu.Controls.Clear(); TestApp.User_Controls.uctComplaints = ...

Sub Ledger Account Scrutiny
Friends, I want to do a ledger account scrutiny of a major Vendor. How can I generate a ledger account of a Vendor which clearly shows me the following: 1. The Opening Balance as on the begining of the year 2. All Invoices Billed by the Vendor during the year 3. All Payments made during the year 4. Closing Balance at the end of the year. Also can this be converted into excel. Aging just gives me the closing balances and the invoices outstanding and the aging of the same.Is it possible to generate a ledger account the way I have stated above. Your early reply will be highly appreciated....

Allowing Users to Run Queries Without Showing the Database Window
I have an access front end that connects to a SQL backend, and is designed in such a way that the users never see the database window or default access database menu bar, only the forms and menus that I want them to see. Specifically: 1. I have set a startup form 2. I have a personalized shortcut menu bar 3. I have disabled the bypass key 4. I have disabled all keystrokes not used by my application in the form open event 5. I have unchecked the following option in the startup: * Display Database Window * Display Status Bar * Allow Full Menus * Allow Default Shortcut Men...

MS Query, command View-Query Properties
Hi there, In View-Query Porperties you have options for: - Unique Values - Group Record The first option reduces the result set to one row for field values a-b-c. That's clear. But what about Group Records? To me, it looks similar. And how do these two options cooperate / interfere with the [Totals] button which sets Sum, Avg etc. to the selected column. Thanks for a reply. Frans ...

Nested or Inner Classes
How do you represent a nested, or inner class in Visio? For instance: Public Class SomePublicClass Private ICVar as new InnerClass Private Class InnerClass 'Implementation End Class End Class It looks like the UML for this is a line with a circle at the end, and a plus (+) sign in the circle. However, I can't find this anywhere in Visio, and I'm not sure how to model this. Thanks in advance, and I apologize if I'm not clear enough. I'll gladly add more detail if necessary... ...

ledger form and binding vs queries
I've designed a "ledger" form that generally acts like a datasheet form, but uses text boxes, comboboxes, and a lot of code to act in a much prettier way. I first attempted to link the form to appropriate records (from 1 table) by setting the form's recordsource at runtime to a query string and then using event driven procedures to create new records, update records, and delete records (represented by line items in the ledger) in the form's recordset (the controls in my ledger remaining unbound, only the form is bound). This was leading to problems I wasn't sure how...

two sets of parameters..one query...but how?
I have a query to track when I must renew State Police Clearances AND Child Abuse Clearances. I want to use the "Between [date] and [date]" function on BOTH of the fields in order to bring up BOTH sets of dates for BOTH clearances.The problem I am running into is that when the query results come up, if one date doesn't meet the criteria NONE of the information comes up (even if the other date DOES).Any suggestions!??!?! Thanks in Advance....

How to implement a query that return data with no match
Hi, i have read some posts of inner join and outer join, but no one could help me to solve my problem :( I have 2 tables 1) tblRecords (having, article; User;date in; date out; service) 2) tblUsers (user) what i need is to select from tblRecords those records that does not match the users in the tblUsers, so if for example i have 20 records in the table 1 (all from diferent users) and in table 2 i have 3 users, the result will be like if i delete from tblRecords the records that match tue users in tblUsers. But i don't want to delete those records... so how can i make a select query to ...

Delete Queries using Unmatched Query
Reading many helpful responses on this site, but unfortunately none of them seem to remedy my current issue. Just learning Access, took a quick college prep course, got the basics. Now I am expanding freelance. SO the scenarios is my test idea that I have intent to apply on larger scale. I have a database with 15 records... lets say reservations (tbl_reservations) for a hotel. Used a maketable to copy that table because it gets updated often to cancelled or complete status. So I filter out the 9 of those 15 reservations which are still in pending status and make the new table (tbl...

related products table
I would lilke to automate the update of tblRelatedItemsTable for our B2B web (Books-Wholesale) there are only two fields ProdCode & RelatedItem All the needed info can be found by querying tblInventory the most straightforward example is an author with 20 titles ProdCode for title1 is related to ProdCode for title2 --> title20 ProdCode for title2 is related to ProdCode for title1 AND title3 --> title20 ProdCode for title3 is related to ProdCode for title1 AND title2 AND title4 --> title20 etc etc Books in a series would be the same. If someone can point me in the right directi...

An Issue with sub-total
What is the best way to do this : I have several columns in my spreadsheet, I sub-totaled every change in Sta tement. When i compress the file, of course, the only visible rows are the sub totals which says: statement1 total xxx statement2 total xxx statement3 total xxx is there a way for me to see the column contents so that my spreadsheet will look like this : Account Statment Amount 1000 Statement1 total xxxx 2000 Statement2 total xxxx 3000 Statement3total xxxx I tried data fill. But wont work thanks and have a great new year everyone d...

can I query active directory with excel?
Looking for a way to query Active Directory with excel ...

SQL Query in VBScript
I am using the following code to perform a SQL query and return a recordset I am getting the error "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another" This error occurs when I open the record set. What am I doing wrong? Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adUseClient = 3 Set objConnection = CreateObject("ADODB.Connection") Set objRecordset = CreateObject("ADODB.Recordset") objConnection.Open "DSN=ChartMES;" objRecordset.CursorLocation = adUseClient strQuery = "Use...

Deleting data from a table through a query
I have a database that is designed to update a list of credit union members from a master list so that vehicle insurance coverage can be tracked. I have been able to run an unmatched query to achieve a list of old members who have either paid off their vehicles or moved their loans to other locations. What I need to do now is delete these people from the main table. I have the cascade update and delete funtion in place to delete the vehicle information once the member is deleted, but I don't know how to take the information found in the unmatched query and delete those members fr...

help designing query to get results
In my database of employees I have three date/time fields - Agency Start date, Rehire date (a lot of our employees only work part of the year) and Separation Date. If I wanted to show only the employees who have either started, were rehired or separated in a certain time frame, how would I do that? Example: Which employees started or were rehired or separted between july 1 and july 30? -- JoAnn In query design view, enter the date range in the Criteria row under the first date field. Below the Criteria row, you'll see another one marked Or. Enter the same date range...

a query that opens but errors on Set qry=currentDB.Openrecordset()
MSA2k7: An existing select query and an existing sub that all work fine I add one more column to my select query to filter the records further. It uses a "like" function that pulls a value from an unbound combo box on a form: Like "*" & [Forms]![frmGroups].[cboFilterCrit] & "*" The query works. It opens from the db container, and from a docmd.openquery command: But: Now my existing sub crashes when I set an object to the query : Set objSourceQry = CurrentDb.OpenRecordset(strSourceQry) ' - this line crashes with "Too few parameters expecte...

Sub forms procedure execution order
Hi I have a Booking Form which contains 2 Sub Forms, Customer Details and Booking Details. I also have a Customers form in a seperate part of the system, and I am trying to make a link - New Booking, which opens the Booking Form with the selected Customer Details, ready to add new booking details. I used a global variable glob_form_name. When the New Booking button is clicked on the Customers form, it sets glob_form_name to "Customers". In my Booking Form, I put On Load procedures in the Customer Details subform and the Booking Details subform. In the Customer Details form , if glob...

Using namespaces? I've some messy nested contexts that I want to clean up...
Hi, I've got the following code structure Class A { ... private: Class B { public: enum C { ENUM X } C MyVar; C MyFunc() } } So for function definitions in B I have to write A::B::C A::B::MyFunc() and for objects of B in A if(pb->MyVar==B::ENUM_X) It's all just a bit messy. Isn't it. Someone please help. Regards. ...

SQL Query problem
I have a table like below Date, Qty 1-Jan, 10 2-Jan, 10 3-Jan, 20 4-Jan, 10 I want to write a query to display Date_interval, qty 1-Jan to 2-jan, 10 3-Jan to 3-jan, 20 4-jan to 4-jan, 10 <-- seperate row for 4-jan even though the Qty is same I tried by grouping on Qty from which I get Qty, Min(date),Max(date) 10, 1-Jan, 4-jan 20, 2-Jan, 2-jan <-- no seperate row for 4-jan Please help me in getting that extra row of 4-jan Thanks Pradeep On 7 May 2007 08:02:19 -0700, Pradeep <agarwalp@eeism.com> wrote: >I have a table like below > >Date, Qty >1-Jan, 10 >2-Ja...

Help with Update Query
How do I setup my code so that the following query will fire only on the items on form match the order number? UPDATE tblItemSerShipLog INNER JOIN tblLocationDataC2 ON (tblItemSerShipLog. SerialNum = tblLocationDataC2.SerialNum) AND (tblItemSerShipLog.Item = tblLocationDataC2.Item) SET tblLocationDataC2.OrderNum = [tblItemSerShipLog]. [OrderNum], tblItemSerShipLog.CustNum = [tblItemSerShipLog].[CustNum], tblLocationDataC2.Shipped = Yes, tblLocationDataC2.DateShipped = [tblItemSerShipLog].[TransDate]; -- Matt Campbell mattc (at) saunatec [dot] com Message posted via http://www...

Date Query 12-07-07
Hello I'm trying to build a query that shows me all records where a field is older than 90 days from today or are null. I've tried using <Now()-"90" Or Is Null but this doesn't give the desired results. What am I getting wrong? Thanks Assumption: Your field is a date field Field: YourDateField Criteria: Is Null Or <DateAdd("d",-90,Date()) Or try dropping the quotes in your expression so it reads Field: YourDateField Criteria: <Now()-90 Or Is Null -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management Un...