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,

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? 

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,
> 

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,
> > 
> 
> 
>