Re: Bizarre subquery syntax problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 Lafontaine wrote:
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.

.

Relevant Pages

  • Re: need help with a sql statement
    ... Part of what has me confused is you say 'combine the tables' but the queries ... Access will put subqueries inside square brackets, ... special characters in fieldnames: you must use square brackets to use the ... and you must NOT use square brackets for the subquery. ...
    (microsoft.public.access.forms)
  • Re: Trouble with a specific table name
    ... I would also try enclosing the name in square brackets, ... We have this database that's been around forever, and in recent SQL Server ... Queries that should return almost ...
    (microsoft.public.sqlserver.server)
  • Re: Simple MAX Query?
    ... The subquery is "correlated." ... I don't see what you propose in terms of making it two queries. ... So, this is not a "better mouse trap" for my money, but it is a viable ... Tom Ellison ...
    (microsoft.public.access.queries)
  • Group By
    ... I've been having a problem with grouping in my queries and ... I finally figured out how to do what John Spencer ... would be to build a category using an IIF ... "You have written a subquery that can return more than one ...
    (microsoft.public.access.queries)
  • Re: Update a table with a select
    ... > both of these queries get the same error: ... subquery not a totals query and doesn't use the DISTINCT keyword. ... Dirk Goldgar, MS Access MVP ...
    (microsoft.public.access.formscoding)