Help: Nested Query A97 vs A2000

[A Coyle]

Hi

In Access 97 I would like to create a query in the following format

SELECT <various items and expressions etc here> FROM
(SELECT <various items and expresions here, WHERE clause here)

I have a query in this format in my A2000 database which works fine, in 97
however I get an "error in FROM clause".

Here is a dummy example to recreate the problem I am having:
..Create a table called t_table1.
..Give it 1 field, called myField
..Close
..Open query in design, select SQL view
..Type the following:
..SELECT myField from (SELECT myField from t_table1)

Run under A2000 = OK
Run under A97 = not OK (error in From clause)


So it looks like either
- A97 does not like nested select statements
- A97 does like nested select statements and I have the syntax wrong

If its the first (and I really hope it isnt) then I will have to rework how
I am doing things, possibly creating a dummy query def for the sub-select
statement which I later delete again. If it is the later can someone tell
me what I need to add to the syntax.


All help greatly appreciated
Regards
A

 

[[MVP] S. Clark]

Here are the examples provided in the A97 Help file. Notice the fact that
none of them have the subquery as part of the FROM statement, only a
comparison in the WHERE Clause.

SELECT * FROM Products
WHERE UnitPrice > ANY
(SELECT UnitPrice FROM OrderDetails
WHERE Discount >= .25);

SELECT * FROM Products
WHERE ProductID IN
(SELECT ProductID FROM OrderDetails
WHERE Discount >= .25);

SELECT LastName,
FirstName, Title, Salary
FROM Employees AS T1
WHERE Salary >=
(SELECT Avg(Salary)
FROM Employees
WHERE T1.Title = Employees.Title) Order by Title;

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Dale Fye]

A.

You will need to format it slightly differently in A97.

The subquery has to be encapsulated in brackets, followed by a period.
Try this

SELECT myField from [SELECT myField from t_table1]. as SQ

--
HTH

Dale Fye


Hi

In Access 97 I would like to create a query in the following format

SELECT <various items and expressions etc here> FROM
(SELECT <various items and expresions here, WHERE clause here)

I have a query in this format in my A2000 database which works fine,
in 97
however I get an "error in FROM clause".

Here is a dummy example to recreate the problem I am having:
..Create a table called t_table1.
..Give it 1 field, called myField
..Close
..Open query in design, select SQL view
..Type the following:
..SELECT myField from (SELECT myField from t_table1)

Run under A2000 = OK
Run under A97 = not OK (error in From clause)


So it looks like either
- A97 does not like nested select statements
- A97 does like nested select statements and I have the syntax wrong

If its the first (and I really hope it isnt) then I will have to
rework how
I am doing things, possibly creating a dummy query def for the
sub-select
statement which I later delete again. If it is the later can someone
tell
me what I need to add to the syntax.


All help greatly appreciated
Regards
A