Bizarre subquery syntax problem

C

colin_e

Guys, just back to Access after some time away (and I wasn't much good the
first time...)

I am seeing bizarre problems with a subquery.

1) I am joining two tables of user account data on a "User id" field. The
core query runs fine-

SELECT p.[User id] AS UID, p.Firstname AS Firstname, p.Lastname AS Lastname,
p.Email AS Email, p.[Profiled member] AS Prof , Left(p.[Date updated],10) AS
Updated, Left(a.[Date last access],10) AS Accessed, (SELECT COUNT(*) FROM
[Users Profile (Detail)] AS q WHERE p.[Lastname] = q.[Lastname] AND
p.[Firstname] = q.[Firstname]) AS Num
FROM [Users Profile (Detail)] AS p LEFT JOIN [Users Address (detail)] AS a
ON p.[User id]=a.[User id];

2) Now, of a query "Q" works, then wrapping it in an outer query-

SELECT * FROM( Q );

must also work, by definition, no?

However this-

SELECT *
FROM (
SELECT p.[User id] AS UID, p.Firstname AS Firstname, p.Lastname AS Lastname,
p.Email AS Email, p.[Profiled member] AS Prof , Left(p.[Date updated],10) AS
Updated, Left(a.[Date last access],10) AS Accessed, (SELECT COUNT(*) FROM
[Users Profile (Detail)] AS q WHERE p.[Lastname] = q.[Lastname] AND
p.[Firstname] = q.[Firstname]) AS Num
FROM [Users Profile (Detail)] AS p LEFT JOIN [Users Address (detail)] AS a
ON p.[User id]=a.[User id]
);

fails with an error "syntax error in FROM clause".

3) Now it gets really interesting. By trying every combination of
adding/deleting spaces and carriage returns to the query, I actually got a
slightly more complex version (with an outer ORDER BY) clause to run.

When I closed and reopened this query in Access, two things happened-

3.1) Access had changed the syntax to-

SELECT *
FROM [SELECT p.[User id] AS UID, p.Firstname AS Firstname, p.Lastname AS
Lastname, p.Email AS Email, p.[Profiled member] AS Prof
, Left(p.[Date updated],10) AS Updated, Left(a.[Date last
access],10) AS Accessed
, (SELECT COUNT(*) FROM [Users Profile (Detail)] AS q WHERE
p.[Lastname] = q.[Lastname] AND p.[Firstname] = q.[Firstname]) AS Num
FROM [Users Profile (Detail)] AS p LEFT JOIN [Users Address (detail)] AS
a ON p.[User id]=a.[User id]
]. AS [%$##@_Alias]
ORDER BY Num DESC , Lastname, Firstname;

Notice the square brackets around the subquery, with a "].", and what the
heck is that "%$##@_Alias" construct?


3.2) It's almost impossible to edit! If I edit this resulting query in any
way, even by deleting a space character, then reinserting the same space,
Access decides the query has a syntax error and refuses to run it. I'm
slightly nonplussed, I though SQL was as insensitive to whitespace as HTML, C
etc?


Any ideas on what's going on, and how to deal with it, much appreciated.
 
S

Sylvain Lafontaine

With Access, the official syntax for a subquery always requires an alias, so
you must write:

SELECT Q.*
FROM (
SELECT p.[User id] AS UID, p.Firstname AS Firstname, p.Lastname AS Lastname,
p.Email AS Email, p.[Profiled member] AS Prof , Left(p.[Date updated],10) AS
Updated, Left(a.[Date last access],10) AS Accessed, (SELECT COUNT(*) FROM
[Users Profile (Detail)] AS q WHERE p.[Lastname] = q.[Lastname] AND
p.[Firstname] = q.[Firstname]) AS Num
FROM [Users Profile (Detail)] AS p LEFT JOIN [Users Address (detail)] AS a
ON p.[User id]=a.[User id]
) as Q;

Also, Access uses [ ... ]. and not the parenthesis () as the delimiter for a
subquery (notice the point . after the right bracket ] ). However, if you
uses parenthesis, Access will understand it. Sometime, it will rewrite
(replace) them with []. and sometime, it will not.

Finally, as you have noticed, Access is very weak at understanding queries
with multiple Left and Right Outer Join and with Subqueries; especially but
not necessarily if you mix both. There is no point of coming here to say
that Access doesn't understanding one of your queries, rewrites it
(correctly or - worse - incorrectly) or even simply choose to core-dump when
you try to execute it.

In some cases, explicitely dividing a big query into multiples queries in
the Queries collection might help but the only thing to do might be often to
use temporary tables or to use simpler queries in association with DLookup()
and DCount() on the form or the report to complete the result.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


colin_e said:
Guys, just back to Access after some time away (and I wasn't much good the
first time...)

I am seeing bizarre problems with a subquery.

1) I am joining two tables of user account data on a "User id" field. The
core query runs fine-

SELECT p.[User id] AS UID, p.Firstname AS Firstname, p.Lastname AS
Lastname,
p.Email AS Email, p.[Profiled member] AS Prof , Left(p.[Date updated],10)
AS
Updated, Left(a.[Date last access],10) AS Accessed, (SELECT COUNT(*) FROM
[Users Profile (Detail)] AS q WHERE p.[Lastname] = q.[Lastname] AND
p.[Firstname] = q.[Firstname]) AS Num
FROM [Users Profile (Detail)] AS p LEFT JOIN [Users Address (detail)] AS a
ON p.[User id]=a.[User id];

2) Now, of a query "Q" works, then wrapping it in an outer query-

SELECT * FROM( Q );

must also work, by definition, no?

However this-

SELECT *
FROM (
SELECT p.[User id] AS UID, p.Firstname AS Firstname, p.Lastname AS
Lastname,
p.Email AS Email, p.[Profiled member] AS Prof , Left(p.[Date updated],10)
AS
Updated, Left(a.[Date last access],10) AS Accessed, (SELECT COUNT(*) FROM
[Users Profile (Detail)] AS q WHERE p.[Lastname] = q.[Lastname] AND
p.[Firstname] = q.[Firstname]) AS Num
FROM [Users Profile (Detail)] AS p LEFT JOIN [Users Address (detail)] AS a
ON p.[User id]=a.[User id]
);

fails with an error "syntax error in FROM clause".

3) Now it gets really interesting. By trying every combination of
adding/deleting spaces and carriage returns to the query, I actually got a
slightly more complex version (with an outer ORDER BY) clause to run.

When I closed and reopened this query in Access, two things happened-

3.1) Access had changed the syntax to-

SELECT *
FROM [SELECT p.[User id] AS UID, p.Firstname AS Firstname, p.Lastname AS
Lastname, p.Email AS Email, p.[Profiled member] AS Prof
, Left(p.[Date updated],10) AS Updated, Left(a.[Date last
access],10) AS Accessed
, (SELECT COUNT(*) FROM [Users Profile (Detail)] AS q WHERE
p.[Lastname] = q.[Lastname] AND p.[Firstname] = q.[Firstname]) AS Num
FROM [Users Profile (Detail)] AS p LEFT JOIN [Users Address (detail)]
AS
a ON p.[User id]=a.[User id]
]. AS [%$##@_Alias]
ORDER BY Num DESC , Lastname, Firstname;

Notice the square brackets around the subquery, with a "].", and what the
heck is that "%$##@_Alias" construct?


3.2) It's almost impossible to edit! If I edit this resulting query in any
way, even by deleting a space character, then reinserting the same space,
Access decides the query has a syntax error and refuses to run it. I'm
slightly nonplussed, I though SQL was as insensitive to whitespace as
HTML, C
etc?


Any ideas on what's going on, and how to deal with it, much appreciated.
Click to expand...
 
J

John Spencer

Also, a subquery in the from clause cannot CONTAIN any square brackets.
Your field and table names require square brackets since they contain
spaces. The only way to get this to work is to nest queries.

This is one reason to name tables and fields with names that contain
only letters, numbers, and underscores.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
C

colin_e

Well that's interesting. I am looking at a fairly large and expensive book on
Access 2003 that completely fails to mention it uses the unusual "[ ... ]."
notation for subqueries.

I have been forced into using subqueries a lot, because Access seems to have
trouble accessing aliases defined in the FROM clause in the WHERE or ORDER by
clauses. Hence I find myself collecting all the data into a virtual table in
the subquery, then sorting and filtering the whole thing.

I had come to the conclusion that Access' SQL parser was a bit flakey in
this area. Bizarrely I have found that if I-

* Replace the square brackets "[ ]." with braces "()" around the subquery,
save the query, THEN open it, it will run again. Pretty weird.

I had not realised subqueries always needed an alias, thanks for that.
 
C

colin_e

Ouch. This is a nasty combination of limitations. The underlying tables were
imported from spreadsheets, and use the column labels as field names, hence
the tendency to have spaces in the names.

Still, this is a learning exercise and i'm definatly learning :-} After
some tribulations I have a basic report from this mess of data. Thanks for
your help.
--

Regards: Colin


John Spencer said:
Also, a subquery in the from clause cannot CONTAIN any square brackets.
Your field and table names require square brackets since they contain
spaces. The only way to get this to work is to nest queries.

This is one reason to name tables and fields with names that contain
only letters, numbers, and underscores.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Sylvain said:
With Access, the official syntax for a subquery always requires an alias, so
you must write:

SELECT Q.*
FROM (
SELECT p.[User id] AS UID, p.Firstname AS Firstname, p.Lastname AS Lastname,
p.Email AS Email, p.[Profiled member] AS Prof , Left(p.[Date updated],10) AS
Updated, Left(a.[Date last access],10) AS Accessed, (SELECT COUNT(*) FROM
[Users Profile (Detail)] AS q WHERE p.[Lastname] = q.[Lastname] AND
p.[Firstname] = q.[Firstname]) AS Num
FROM [Users Profile (Detail)] AS p LEFT JOIN [Users Address (detail)] AS a
ON p.[User id]=a.[User id]
) as Q;

Also, Access uses [ ... ]. and not the parenthesis () as the delimiter for a
subquery (notice the point . after the right bracket ] ). However, if you
uses parenthesis, Access will understand it. Sometime, it will rewrite
(replace) them with []. and sometime, it will not.

Finally, as you have noticed, Access is very weak at understanding queries
with multiple Left and Right Outer Join and with Subqueries; especially but
not necessarily if you mix both. There is no point of coming here to say
that Access doesn't understanding one of your queries, rewrites it
(correctly or - worse - incorrectly) or even simply choose to core-dump when
you try to execute it.

In some cases, explicitely dividing a big query into multiples queries in
the Queries collection might help but the only thing to do might be often to
use temporary tables or to use simpler queries in association with DLookup()
and DCount() on the form or the report to complete the result.
Click to expand...
Click to expand...
 
D

Douglas J. Steele

It's not that difficult to fix the field names after importing. The
following code will replace all spaces with underscores:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDB()
Set tdfCurr = dbCurr.TableDefs("NameOfTable")
For Each fldCurr In tdfCurr.Fields
fldCurr.Name = Replace(fldCurr.Name, " ", "_")
Next fldCurr
Set tdfCurr = Nothing
Set dbCurr = Nothing



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


colin_e said:
Ouch. This is a nasty combination of limitations. The underlying tables
were
imported from spreadsheets, and use the column labels as field names,
hence
the tendency to have spaces in the names.

Still, this is a learning exercise and i'm definatly learning :-} After
some tribulations I have a basic report from this mess of data. Thanks for
your help.
--

Regards: Colin


John Spencer said:
Also, a subquery in the from clause cannot CONTAIN any square brackets.
Your field and table names require square brackets since they contain
spaces. The only way to get this to work is to nest queries.

This is one reason to name tables and fields with names that contain
only letters, numbers, and underscores.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Sylvain said:
With Access, the official syntax for a subquery always requires an
alias, so
you must write:

SELECT Q.*
FROM (
SELECT p.[User id] AS UID, p.Firstname AS Firstname, p.Lastname AS
Lastname,
p.Email AS Email, p.[Profiled member] AS Prof , Left(p.[Date
updated],10) AS
Updated, Left(a.[Date last access],10) AS Accessed, (SELECT COUNT(*)
FROM
[Users Profile (Detail)] AS q WHERE p.[Lastname] = q.[Lastname] AND
p.[Firstname] = q.[Firstname]) AS Num
FROM [Users Profile (Detail)] AS p LEFT JOIN [Users Address (detail)]
AS a
ON p.[User id]=a.[User id]
) as Q;

Also, Access uses [ ... ]. and not the parenthesis () as the delimiter
for a
subquery (notice the point . after the right bracket ] ). However, if
you
uses parenthesis, Access will understand it. Sometime, it will rewrite
(replace) them with []. and sometime, it will not.

Finally, as you have noticed, Access is very weak at understanding
queries
with multiple Left and Right Outer Join and with Subqueries; especially
but
not necessarily if you mix both. There is no point of coming here to
say
that Access doesn't understanding one of your queries, rewrites it
(correctly or - worse - incorrectly) or even simply choose to core-dump
when
you try to execute it.

In some cases, explicitely dividing a big query into multiples queries
in
the Queries collection might help but the only thing to do might be
often to
use temporary tables or to use simpler queries in association with
DLookup()
and DCount() on the form or the report to complete the result.
Click to expand...
Click to expand...
Click to expand...
 
S

Sylvain Lafontaine

Access doesn't execute directly your queries but instead will compile and
run an intermediate form. However, it looks like that sometimes, even if
you've made change to a (complex) query or Access itself has made change to
it by rewritting it, it forgets to recompile it; so you end up with a query
that should work but it doesn't or that shouldn't work but it does.
Sometimes, adding or removing the ; at the end of the query is sufficient to
correct this but other times, you must fully decompile your mdb file first.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Another Query Modification Question 6
Query Question 0
Problem When Sorting Query 6
Getting only the last transaction for each customer 2
Query wont sort ? 10
User Defined Functions 2
Trying to Siplify a Query in MS Access 2003 1
Append from 1 table to 2 tables 4

Top