query won't run in access 97

[Hornsby]

I have created the query below which runs in access 2000 but not in
version 97. It returns a syntax error FROM Clause. I have since change
my approach by creating temp tables to obtain the output that I want.
However, I am still interested to know why Access 97 gives me the
syntax error.
SELECT Field1, Field2

(Select count(Field3) From

(Select Distinct Field3 From [Filename] where Field1 ="X" and
Field2="Y")) AS Total, SUM(amount) AS TotalAmount

FROM [Filename]
WHERE Field1 ="X" and Field2 ="Y"
GROUP BY Field1,Field2

Basically what I am trying to do is select distinct records and then
count the number of recors based on certain conditions.
Any guidance would be appreciated. Thanks

 

[Douglas J. Steele]

It's difficult to nest queries like that in Access 97.

Your best bet is to save the subqueries as separate queries and then write a
last query that uses those subqueries.

 

[Hornsby]

Thank you to both Douglas and Gary for their response.
Rgds
Hornsby

It's difficult to nest queries like that in Access 97.

Your best bet is to save the subqueries as separate queries and then write a
last query that uses those subqueries.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have created the query below which runs in access 2000 but not in
version 97. It returns a syntax error FROM Clause. I have since change
my approach by creating temp tables to obtain the output that I want.
However, I am still interested to know why Access 97 gives me the
syntax error.
SELECT Field1, Field2

(Select count(Field3) From

(Select Distinct Field3 From [Filename] where Field1 ="X" and
Field2="Y")) AS Total, SUM(amount) AS TotalAmount

FROM [Filename]
WHERE Field1 ="X" and Field2 ="Y"
GROUP BY Field1,Field2

Basically what I am trying to do is select distinct records and then
count the number of recors based on certain conditions.
Any guidance would be appreciated. Thanks