sub query

Does MS Access support sub query?
I wanted to create a query which has sub query like following

Select MyField, (select MyField1 from mytable1 where mytable1.myfiled2 = 
mytable.MyField3) from mytable


Do I need create 2 queries to do this or just one query with one sub query?

Your information is great appreciated,

0
Utf
1/17/2008 12:40:01 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
1519 Views

Similar Articles

[PageSpeed] 28

Yes. Access supports subqueries like that.

More info and some examples:
    http://allenbrowne.com/subquery-01.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Souris" <Souris@discussions.microsoft.com> wrote in message
news:7BE99C52-73C9-4371-B52C-1CBB3D70E2F8@microsoft.com...
> Does MS Access support sub query?
> I wanted to create a query which has sub query like following
>
> Select MyField, (select MyField1 from mytable1 where mytable1.myfiled2 =
> mytable.MyField3) from mytable
>
>
> Do I need create 2 queries to do this or just one query with one sub 
> query? 

0
Allen
1/17/2008 12:46:09 PM
Yes MS Access does support subqueries.

You will run into a problem if you plan to use a subquery in the FROM clause 
and your table and field names don't conform to the naming convention of 
only letters, numbers, and the underscore characters (with at least one 
non-number character).  Also you must avoid reserved words.

In addition, a subquery in the SELECT Clause can only return one value from 
one row, so you normally need to use one of the aggregate functions in the 
query to ensure that.

Select MyField
, (Select First(MyField1)
   from mytable1
   where mytable1.myfiled2 = mytable.MyField3) as xxx
from mytable

A subquery in the WHERE clause can only return one field, but many rows if 
you use the In  (or Exists) operator as the comparison operator.

-- 
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Souris" <Souris@discussions.microsoft.com> wrote in message 
news:7BE99C52-73C9-4371-B52C-1CBB3D70E2F8@microsoft.com...
> Does MS Access support sub query?
> I wanted to create a query which has sub query like following
>
> Select MyField, (select MyField1 from mytable1 where mytable1.myfiled2 =
> mytable.MyField3) from mytable
>
>
> Do I need create 2 queries to do this or just one query with one sub 
> query?
>
> Your information is great appreciated,
> 


0
John
1/17/2008 1:06:57 PM
Thanks for great information,

"John Spencer" wrote:

> Yes MS Access does support subqueries.
> 
> You will run into a problem if you plan to use a subquery in the FROM clause 
> and your table and field names don't conform to the naming convention of 
> only letters, numbers, and the underscore characters (with at least one 
> non-number character).  Also you must avoid reserved words.
> 
> In addition, a subquery in the SELECT Clause can only return one value from 
> one row, so you normally need to use one of the aggregate functions in the 
> query to ensure that.
> 
> Select MyField
> , (Select First(MyField1)
>    from mytable1
>    where mytable1.myfiled2 = mytable.MyField3) as xxx
> from mytable
> 
> A subquery in the WHERE clause can only return one field, but many rows if 
> you use the In  (or Exists) operator as the comparison operator.
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007-2008
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Souris" <Souris@discussions.microsoft.com> wrote in message 
> news:7BE99C52-73C9-4371-B52C-1CBB3D70E2F8@microsoft.com...
> > Does MS Access support sub query?
> > I wanted to create a query which has sub query like following
> >
> > Select MyField, (select MyField1 from mytable1 where mytable1.myfiled2 =
> > mytable.MyField3) from mytable
> >
> >
> > Do I need create 2 queries to do this or just one query with one sub 
> > query?
> >
> > Your information is great appreciated,
> > 
> 
> 
> 
0
Utf
1/17/2008 2:08:00 PM
Reply:

Similar Artilces:

Unique Value's in Query Issue
Hi, I'm trying to run an access query to give me only unique values in a query. This would seemingly be simple to me. I know that I can right click and then hit properties and select "Unique Values". Which would appear to work, but here is my problem... When I run this query with "Unique Values" turned on without any modification on a single column I end up getting about 366 unique values. Now if I take the same query and hit the "group by COUNT" button it returns 455. Am I just misunderstanding the functionality of COUNT? Shouldn't this return the num...

Why is my Access Queries take a long time to generate?
I used to do the query in less than 30 secs, but using the same method to do another query, it took more than a minutes to generate the table. Why is it so? There's only one practical way for us to know: Show us the SQL. Open the query in design view. Next go to View, SQL View and copy and past it here. Information on primary keys and relationships would be a nice touch too. Also any indexes. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Weak Access User" wrote: > I used to do the query i...

Table or Query?
I've been racking by brain and various books to find some answers, but to no avail. I need assistance with a simple task. I have a combo box that looks up items from a table. Once an item is selected, I do not want it to appear again. Should I be using a query for my control source? Please help!! Any assistance would be greatly appreciated. Thank you!!! When you say "Once an item is selected, I do not want it to appear again.", what is the scope? Do you mean never ever again or do you mean while the form is open? It makes a difference in how you will approach this. ...

Re: Prompt Message Query
Hi all, Now, I know that we all hate Excel's paperclip 'helper'; however, have been asked to create a worksheet that once a cell has been fille in the l'il bastard pops up and gives a prompt for what the user shoul do next (e.g. 'Go to Sheet2'). I know that using Validation you can bring up a message when the cel is selected, and that if you're using a validation drop-down you ca summon the evil piece of twisted metal to give a message. However, can't find a way of bringing it up when you've simply completed a cell Any ideas? TIA, SamuelT PS - If you...

Access 2007
Maybe there are a lot of changes in access 2007 that I just dont understand, but I am having NO luck with it! First I tried to link to a Dbase 4 file on our network and it kept telling me it couldnt do it. Something about needing to open it exclusively. So late one night I had to do it when no one was using that db. Seems very odd since I commonly do the same thing (to the same databases) in older versions with no problems. I am not trying to open it excusively myself, and no one else has it opened exculsively either. Second, When I try to run search and replace operations I get many errors ...

Problem with 1 Query with Criteria feeding another query
I have one query summarizing data that then feeds another query that begins to work on it. I made a form to filter the very first query in my process by date, right now I have 2 items begdate and enddate. I added the date field of the underlying table to my first query and in design view on "Total" selected "where" and in the criteria section added "<[Forms]![frmFilterForm]![enddate] And >[Forms]![frmFilterForm]![startdate]" Opening this Query works fine and results in the correct info being shown. When I try to open any query which is based o...

365 Day Avg Query
I have a table with two fields, Date and Sales. The data goes back 25 years. I would like a query that gives me the 365 day avg for each month in the last two years; i.e. The avg for May 06 thru May 07, then April 06 thru April 07, and so forth. The final result would have 24 rows; Date Expression 365 Day Sales Avg 04.06thru04.07Avg $61,500 05.06thru05.07Avg $73,650 06/06thru06.07Avg $65,300 Etc Can I do this in one querie? Terry ? UNTESTED. But this might work if I've got my T1 and T2 relationship set up correctly SELECT Format(T2.Date...

query to find when an event happened
I have 12 years of weather data in one table, and have queries to give me total rainfalls between different dates etc, as well as max and min temperatures. What I would like now is a query to tell me what date in a selected range the max or min temperature was. I have tried the WHERE clause just see what it would return but I got a syntax error. Thanks See: Getting a related field from a GroupBy (total) queryat: http://www.mvps.org/access/queries/qry0020.htm -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/...

Query problem
Well here I go again. Biting off more than my brain can handle. I have a report that shows all of our machines in our plant. I’m tracking both machine down time (the time the machine is broken) and Plant Down Time (the time the machine is broken that hampers production) I have a form that we use to gather all pertinent information such as the date, time notified of malfunction etc… I have a query that well, queries the data so I can view what is happening each month. That works great. Now to the meat and potatoes… I calculate down times by using: Minutes: DateDiff("n", [Time ...

is this query really too complex?
hello I am getting the error message that this is too complex! Can this be right? A limit on the number of IIFs? Is there another way of doing this? RenewalCost: IIF([1118] and [type]="a",100, IIF([1118] and [type]="c",60, IIF([1118] and [type]="d",130, IIF([1116] and [type]="a",85, IIF([1116] and [type]="c",50, IIF([1116] and [type]="d",100, IIF([1618] and [type]="a",85, IIF([1618] and [type]="c",50, IIF([1618] and [type]="d",100, IIF([1118plus] and [type]="a",100, IIF([1118plus] and [type]...

Query to Change Tables
I input data into a table that looks like this... ID# Class Mark SpecID 1 Bio 80 A 1 Phy 60 B 1 Math 70 B 1 English 70 A 2 Econ 60 B 2 Chem 50 A 2 Math 70 A 2 English 80 B .. I would like to select both the classes with a SpecID of "A" for each student, which I can do with a select Query. But I would like to split them up when I create a table with them... ID# Class1 Mark1 Class2 Mark2 ...

Search Multiple Queries w/ One Search parameter
I would like to use one search parameter; more than likely product name to search multiple queries on a single form. Possible? Thanks LA -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1 Thanks will try now! Jerry Whittle wrote: >Yes. Put the search parameter in a text box on a form. Then in the criteria >of the appropriate fields in the queries, put something like below that calls >on the text box. > > [Forms]![frmParameter]![txtParameter] > >You could also put a button on the form to run code or a macro...

General Query
Gidday all, I was just wondering, we have a large product database that I have designated mostly into kits (of the installations we do). To clean it up and make it easier to view what is required, I have set many of the single items as 'Inactive'. Besides the fact that this probably isn't what 'inactive' status was intended for, will there be any affects to the kits, and quoting using the kits while these Items are 'inactive' or is it purley cosmetic. ...

Web content query problem .. pls help..
i am using query to retreive data every minutes, however when the tim it refresh, EXCEL itself hang its UI to proceed until all data has bee proceeded... is there any way to make it to do it in the background ? another thing is that .. am i able to import the picture from the we query also? Thank for help.............. -- kitste ----------------------------------------------------------------------- kitster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1495 View this thread: http://www.excelforum.com/showthread.php?threadid=26587 ...

run two queries from one click?
This has to be easier than I'm making it. I have a form with these fields: TaskID - number Task Description - text Month Completed - text list pulled from a query Year Completed - text list pulled from a query Category - text list pulled from a query, can be more than one category per task Task Description - memo Now what I want to do is... insert TaskID, Description, Month Completed, Year Completed and Description in one table AND insert TaskID and each Category into another table with a single button click to make it easy for users. As stated above, there can be more than one cate...

How 2 avoid sub-reports?
I have a query always calculating the totals of five different products and the result being: Product A Total Amount (or 0 in case of null) Product B Total Amount (or 0 in case of null) Product C Total Amount (or 0 in case of null) Product D Total Amount (or 0 in case of null) Product E Total Amount (or 0 in case of null) Now I want to make/design a report based upon such a query, however, I want each of the entry, i.e. the sum of the products be placed manually at different locations of the page instead of being presented in a tabular form. ...

microsoft query add-in
I have just installed the Microsoft Query add-in to Excel...can anyone tell me if this is the exact query that Access uses, or are there differences in them. Thanks diane :?: Query kind of looks like Access, but it not nearly as powerful. It has several limitations. For example, what I think is the biggest hinderance, is the inability to do Outer Joins in Query. I do use Query, but mainly to bring something in from Access. >-----Original Message----- >I have just installed the Microsoft Query add-in to >Excel...can anyone tell me if this is the exact query that >Ac...

Select Query Problem
hi all The table have lots of duplicate rows except for the last column skills The skills has diffrent values some rows 'Middle East' and the other rows 'Resident Engineer', 'planner' etc When the user enters the name and the skills set, he wants to see thouse who has all the skills set Table sample Name Address salary skills samy d Dubai 1000 manager samy d Dubai 1000 Resident Engineer samy r Abu Dhabi 2000 manager samy r Abu Dhabi 2000 Software here i want to retry records of which has the name "samy" and skills Resident Enginee...

Send Email Based On a Query
Hey guys,I have been looking for an issue that I have.I have a database with a number of customer email addresses in themand I want to send an email to a number of them based on a query(essentially to limit who gets the email), what I need to know is howto do this.Any help would be greatly appreciated. TimWal wrote:> Hey guys,>> I have been looking for an issue that I have.>> I have a database with a number of customer email addresses in them> and I want to send an email to a number of them based on a query> (essentially to limit who gets the email), what I need to know is...

Queries and Reports in Office 2007
For the life of me, I can't find where to open any of my saved queries or reports in Office 2007. Can anyone help? What are you trying? What happens when you double click on them in the Navigation Pane? Do you see the Navigation Pane at all? Press F11 if not and see if it appears. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Mesunkel" wrote: > For the life of me, I can't find where to open any of my saved queries or > reports in Office 2007. Can anyone help? ...

query database
Every day I have to copy a database (a client listing that is added to daily by many other people) into excel and then find the new clients. This information is copied from a business programme which I am able to paste into excel. The software used to record the original data does produce a unique code for each client which, I think, would be the way to find the new listings. I have to produce a weekly report showing the new clients added during the week (on a daily basis). Is there a formula that will compare the list (todays list against yesterdays list) to show me the new clients rather...

Query Text Size
Is there a way to increase the text size when writing queries, either in design view or SQL view? Just to make it easier on my eyes.... On the File Menu 1.. On the File menu go to the Tools menu, click Options. 2.. Click the Tables/Queries tab. 3.. In the Query design font group, select the font and font size. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II <ed0211e@gmail.com> wrote in message news:b0a088ff-97f1-42c1-986c-097bb1c44a2e@41g2000hsc.googlegroups.com... > Is there a way to increase the text size when writi...

Query Problem--> returning wildcard
I created a query that is linked to a form where I created a drop down list to choose which values to include in the query. Currently, the form works when I am trying to return only specific values but I cannot figure out how to return all values. The criteria and parameter is currently: [Forms]![SVP]![SVP] Switch the query to SQL View (View menu.) In the WHERE clause, replace: [Field1] = [Forms]![SVP]![SVP] with: (([Forms]![SVP]![SVP] Is Null) OR ([Field1] = [Forms]![SVP]![SVP])) (Use your own field name instead of Field1.) If you have several of these fields, this approach will...

Web query cannot find URL. Why?
I am unable to get to square one with a web query. When I enter nces.ed.gov (a legitimate URL) in Field 1 of the "new Web Query" dialog window, I get an error message that says, "The address of this site is not correct. CHeck the address and try again." Yes, it is the correct address. Why do I get this error. I can open IE to the URL with no problems. How do "Internet Connections" in IE 6.0/Tools/Internet Options/Connections have to be set up for web queries to work? Is this what the problem is? THank you. John Wirt I just tested this using nces.ed.gov...

Web Query problem
I have created a web query in Excel to download "key statistics"for a stock into my spreadsheet. The url and query is http://finance.yahoo.com/q/ks?s=PFE Selection=yfncsumtab,21,24,26,29,32,35,38,41,44 Formatting=None PreFormattedTextToColumns=True ConsecutiveDelimitersAsOne=True SingleBlockTextImport=False DisableDateRecognition=False DisableRedirections=False with the ticker being PFE. I want to be able to reference a cell in the spreadsheet with a ticker so I can easily change the stock I am requesting data for. Can you tell me how to do that? -- Al Eaton aleaton@bak.rr....