Now it works... now it doesn't... What is up with this query

[John T Ingato]

I have a query that was written a while ago that used a temp table which was
deleted. I would like to use this query again. So I replaced the temp
table's name with the new (permanent) name in the queries SQL and I could
not get the query to run.
So I copied the new table and renamed it what the old (temp) name was
(table4). It ran.

So here's the curious part.

I open the query in SQL mode and run it. It runs! See SQL below.
If I change any of the words in the SQL it won't run, even if I change the
words to the same words.

For example, if I delete an "S" and then put the "S" right back, it won't
run. I have to close the query, unsaved, and re-open it. Then it runs.
Even if I add a space between any two letters, then backspace to remove it,
it won't run.

The error is always the same: "Syntax error in FROM clause." The very last
FROM is highlighted.

Any ideas?



SELECT A.ItemNumber, A.Stores, B.[YTD Units]
FROM [SELECT ItemNumber, Count(StoreNumber) AS Stores
FROM

(SELECT Distinct ItemNumber, StoreNumber
FROM table4) AS T

GROUP BY ItemNumber]. AS A INNER JOIN [SELECT ItemNumber, Sum(Sold) AS
[YTD Units]
FROM table4
GROUP BY ItemNumber]. AS B ON A.ItemNumber=B.ItemNumber;

 

[Gary Walter]

I have a query that was written a while ago that used a temp table which
was deleted. I would like to use this query again. So I replaced the temp
table's name with the new (permanent) name in the queries SQL and I could
not get the query to run.
So I copied the new table and renamed it what the old (temp) name was
(table4). It ran.

So here's the curious part.

I open the query in SQL mode and run it. It runs! See SQL below.
If I change any of the words in the SQL it won't run, even if I change the
words to the same words.

For example, if I delete an "S" and then put the "S" right back, it won't
run. I have to close the query, unsaved, and re-open it. Then it runs.
Even if I add a space between any two letters, then backspace to remove
it, it won't run.

The error is always the same: "Syntax error in FROM clause." The very last
FROM is highlighted.

Any ideas?



SELECT A.ItemNumber, A.Stores, B.[YTD Units]
FROM [SELECT ItemNumber, Count(StoreNumber) AS Stores
FROM

(SELECT Distinct ItemNumber, StoreNumber
FROM table4) AS T

GROUP BY ItemNumber]. AS A INNER JOIN [SELECT ItemNumber, Sum(Sold) AS
[YTD Units]
FROM table4
GROUP BY ItemNumber]. AS B ON A.ItemNumber=B.ItemNumber;

BRACKETS BAD!!

1. last subquery

Change field name of [YTD Units] to name that does not need brackets...
say "YTDUnits"

The "save parser" chokes on any brackets within a subquery
used in a FROM clause

2. save

(SELECT Distinct ItemNumber, StoreNumber FROM table4)

as a separate query. Then use that query in your first subquery.

It's the "save parser" again...the above will be *saved* as

[SELECT Distinct ItemNumber, StoreNumber FROM table4].

which means it will cause brackets within its outer subquery
used in the FROM clause.

Do you see what I am saying?

You could write out your original query in code,
and probably always run it without problems
(depending on context), but if Access ever decides
it needs to save it, it will choke on any brackets within a
subquery used in a FROM clause.

That is my experience, at least.

 

[John T Ingato]

Thanks Gary
It appears that your first suggestion fixed the problem immediately; getting
rid of the Brackets, changing [YTD Units] to YTDUnits.
That seems like a bug though, no? My goal has always been to try to write
as complex and self sufficient of a query as possible so to not have a bunch
of little queries in my database. Maybe that's the wrong approach. It just
seems that with many 'little' queries, it can get confusing after a while as
to what belongs to what.

I have a query that was written a while ago that used a temp table which
was deleted. I would like to use this query again. So I replaced the temp
table's name with the new (permanent) name in the queries SQL and I could
not get the query to run.
So I copied the new table and renamed it what the old (temp) name was
(table4). It ran.

So here's the curious part.

I open the query in SQL mode and run it. It runs! See SQL below.
If I change any of the words in the SQL it won't run, even if I change
the words to the same words.

For example, if I delete an "S" and then put the "S" right back, it won't
run. I have to close the query, unsaved, and re-open it. Then it runs.
Even if I add a space between any two letters, then backspace to remove
it, it won't run.

The error is always the same: "Syntax error in FROM clause." The very
last FROM is highlighted.

Any ideas?



SELECT A.ItemNumber, A.Stores, B.[YTD Units]
FROM [SELECT ItemNumber, Count(StoreNumber) AS Stores
FROM

(SELECT Distinct ItemNumber, StoreNumber
FROM table4) AS T

GROUP BY ItemNumber]. AS A INNER JOIN [SELECT ItemNumber, Sum(Sold)
AS [YTD Units]
FROM table4
GROUP BY ItemNumber]. AS B ON A.ItemNumber=B.ItemNumber;

BRACKETS BAD!!

1. last subquery

Change field name of [YTD Units] to name that does not need brackets...
say "YTDUnits"

The "save parser" chokes on any brackets within a subquery
used in a FROM clause

2. save

(SELECT Distinct ItemNumber, StoreNumber FROM table4)

as a separate query. Then use that query in your first subquery.

It's the "save parser" again...the above will be *saved* as

[SELECT Distinct ItemNumber, StoreNumber FROM table4].

which means it will cause brackets within its outer subquery
used in the FROM clause.

Do you see what I am saying?

You could write out your original query in code,
and probably always run it without problems
(depending on context), but if Access ever decides
it needs to save it, it will choke on any brackets within a
subquery used in a FROM clause.

That is my experience, at least.

 

[Gary Walter]

I don't know...

As far as I know early Access could not
use subquery in FROM clause until Acc97
allowed you to use the brackets/ending period.
I was just glad they came up with a way to do
that and have never looked at the limitation
as a bug.

My goal is always to be able to know what
I was doing when I come back to a project
after 6 months of working on several other
projects (like just recently when all my projects
needed to handle ISBN's with new 13 digits or
old 10 digits at the same time...aargh).

A well-named query, say

qryDistinctItemStore_Table4

would document itself well enough for me
(except for "Table4") based on my
"6-month-haze" criteria.

And the final query using this small query
would probably document itself better (*for me*)
then the complex, self-sufficient variety. Plus
I know it will save properly.

Construct your queries how ever you (and Access)
are most comfortable.

"John T Ingato"wrote:

It appears that your first suggestion fixed the problem immediately;
getting rid of the Brackets, changing [YTD Units] to YTDUnits.
That seems like a bug though, no? My goal has always been to try to write
as complex and self sufficient of a query as possible so to not have a
bunch of little queries in my database. Maybe that's the wrong approach.
It just seems that with many 'little' queries, it can get confusing after
a while as to what belongs to what.

I have a query that was written a while ago that used a temp table which
was deleted. I would like to use this query again. So I replaced the
temp table's name with the new (permanent) name in the queries SQL and I
could not get the query to run.
So I copied the new table and renamed it what the old (temp) name was
(table4). It ran.

So here's the curious part.

I open the query in SQL mode and run it. It runs! See SQL below.
If I change any of the words in the SQL it won't run, even if I change
the words to the same words.

For example, if I delete an "S" and then put the "S" right back, it
won't run. I have to close the query, unsaved, and re-open it. Then it
runs.
Even if I add a space between any two letters, then backspace to remove
it, it won't run.

The error is always the same: "Syntax error in FROM clause." The very
last FROM is highlighted.

Any ideas?



SELECT A.ItemNumber, A.Stores, B.[YTD Units]
FROM [SELECT ItemNumber, Count(StoreNumber) AS Stores
FROM

(SELECT Distinct ItemNumber, StoreNumber
FROM table4) AS T

GROUP BY ItemNumber]. AS A INNER JOIN [SELECT ItemNumber, Sum(Sold)
AS [YTD Units]
FROM table4
GROUP BY ItemNumber]. AS B ON A.ItemNumber=B.ItemNumber;

BRACKETS BAD!!

1. last subquery

Change field name of [YTD Units] to name that does not need brackets...
say "YTDUnits"

The "save parser" chokes on any brackets within a subquery
used in a FROM clause

2. save

(SELECT Distinct ItemNumber, StoreNumber FROM table4)

as a separate query. Then use that query in your first subquery.

It's the "save parser" again...the above will be *saved* as

[SELECT Distinct ItemNumber, StoreNumber FROM table4].

which means it will cause brackets within its outer subquery
used in the FROM clause.

Do you see what I am saying?

You could write out your original query in code,
and probably always run it without problems
(depending on context), but if Access ever decides
it needs to save it, it will choke on any brackets within a
subquery used in a FROM clause.

That is my experience, at least.