Re: Bizarre subquery syntax problem

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" <coline@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D054B33D-70F7-43BF-8A98-B20589E99C58@xxxxxxxxxxxxxxxx
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.


--

Regards: Colin


.

Relevant Pages

  • RE: EXISTS reserved word in FROM clause
    ... we make a good pair because I know Access SQL ... from what you described the query syntax that I ... >The subquery should return the records: ...
    (microsoft.public.access.queries)
  • Re: syntax error in Total of field - I need help ASAP, PLease
    ... "check the subquery's syntax and enclose the subquery in parentheses". ... "SELECT Rent, FROM PO 1189-616) as Rent Sum ... you posting the entire SQL (query) statement? ... IF you are trying to use a subquery in the select clause of a query then ...
    (microsoft.public.access.queries)
  • Bizarre subquery syntax problem
    ... I am seeing bizarre problems with a subquery. ... SELECT p.AS UID, p.Firstname AS Firstname, p.Lastname AS Lastname, ... Now, of a query "Q" works, then wrapping it in an outer query- ... Access decides the query has a syntax error and refuses to run it. ...
    (microsoft.public.access.queries)
  • RE: Unbound Combo Box Default Value Based on Query
    ... "ScottMsp" wrote: ... "The syntax of the subquery in this expression is incorrect. ... I have a query based on a table. ...
    (microsoft.public.access.formscoding)
  • Re: Jet SQL and Virtual tables/subqueries qiestions
    ... errors out of the query builder and/or Jet SQL parser. ... doesn't the Access/Jet syntax documentation state that s ... selects the outer query instead of just the inner subquery SQL ...
    (microsoft.public.access.queries)