If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Now it works... now it doesn't... What is up with this query
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; |
#2
|
|||
|
|||
Now it works... now it doesn't... What is up with this query
"John T Ingato" wrote: 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. |
#3
|
|||
|
|||
Now it works... now it doesn't... What is up with this query
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. "Gary Walter" wrote in message ... "John T Ingato" wrote: 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. |
#4
|
|||
|
|||
Now it works... now it doesn't... What is up with this query
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. "Gary Walter" wrote in message ... "John T Ingato" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|