Re: Bizarre subquery syntax problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.
--

Regards: Colin


.

Relevant Pages

  • Re: Correlated Sub Query with joins in the where clause
    ... If I don't use a join the query works but it doesn't allow me to ... FWIW you can do multiple joins in the FROM clause of the main query ... without using subqueries in the WHERE clause. ...
    (microsoft.public.access.queries)
  • RE: Query too Complex...
    ... It would only work if I put it into a new query. ... Should I be using subqueries? ... simplify things in the FROM clause? ...
    (microsoft.public.access.queries)
  • Re: [Info-Ingres] Few questions concerning Ingres 2006
    ... ingres 2006 RDBMS Release 2 and got same results. ... it DOES NOT support subqueries? ... I suspect a subtle syntax error. ... Subqueries in the from clause ...
    (comp.databases.ingres)
  • Re: Bizarre subquery syntax problem
    ... you've made change to a query or Access itself has made change to ... MVP - Technologies Virtual-PC ... I have been forced into using subqueries a lot, ... trouble accessing aliases defined in the FROM clause in the WHERE or ORDER ...
    (microsoft.public.access.queries)
  • Re: SELECT result as a table in from list
    ... You can use subqueries in the WHERE clause in VFP6 as well. ... >> Dennis Longfellow ...
    (microsoft.public.fox.vfp.queries-sql)