sub query

[Souris]

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,

 

[Allen Browne]

Yes. Access supports subqueries like that.

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

 

[John Spencer]

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]

Thanks for great information,

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