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.

 

[Gary Walter]

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

PMFBI

In addition to Doug's sage advice,
it might be worth considering

brackets/periods in FROM clause subqueries
and
aliases in subqueries in general

which persist even in newer versions of Access.

==========
brackets
==========

A Jet subquery used in a FROM clause will always
end up being changed by Access (when you save it) to

FROM
[select ....]. As somealias

i.e., it is wrapped in brackets with an ending period

so..you can start out typing the subquery wrapped in
parentheses and no ending period, but the above
will be what you end up with.

One burden this puts on the "insides of the brackets"
is that it cannot contain any further brackets within it
or the Jet query parser will choke on them.

So..if your table name needed to be surrounded by
brackets ("[FileName]") that you were out of
luck no matter what version of Access.

=========
aliases
=========

You need to give a table an alias in a subquery
if it is used elsewhere in the query so Access
can figure out where the field(s) come from.
This extends to a "virtual table" that you create
from a subquery in the FROM clause.

So...if your table name did not need brackets
around it, and if Access 97 played nice like
newer versions, your query might have looked
like:

SELECT
Field1,
Field2,

(Select
count(D.Field3)
From
[Select Distinct
q.Field3
From Filename As q
where
q.Field1 ="X"
and
q.Field2="Y"]. As D

) AS DistinctCnt,
SUM(amount) AS TotalAmount

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

But, even in newer versions, I probably
would have created the separate distinct
query as Doug suggested. There is nothing
wrong with this "divide and conquer"
method, especially if you give these
preliminary queries meaningful names.
At least that's my opinion....

Apologies again for butting in...

 

[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