FYI, it's pretty amazing that Access will convert your query into its own
undocumented syntax for the same thing at all. What you describe is, to my
knowledge, only referred to as a subquery in Oracle parlance, and is referred
to as a declared table in MS SQL Server parlance. In Access, it has no
terminology because it's not documented to exist, though it is used by some of
the Access wizards.
In any case, the Access form of the declared table (or whatever it is) does
not support bracketted expressions within the inner select, so it cannot be
used with table/column names that require brackets such as those with spaces,
etc. This is one of the many reasons to avoice spaces in object names in
Access.
On 1 Dec 2003 14:11:13 -0800,
andrew.mcnab@ptc.pilkington.co.uk (Andrew McNab)
wrote:
[color=blue]
>Hi folks,
>
>I have a problem with an MS Access SQL query which is being used in an
>Access Report, and am wondering if anyone can help.
>
>Basically, my query (shown below) gets some records from a couple of
>tables in my database using INNER JOINS and the WHERE clause to
>specify the required constraints. However, I also want to read two
>fields from a *single* record from a table called 'Locations' and then
>apply one of these field's values (a number reflecting number of hours
>of time zone difference) to ALL timestamp-related records read in the
>'main' part of the query, i.e.:
>
>* Get a series of records from table A, inner joining on B & C to get
>some extra data.
>* Read the time zone difference factor (call it 'tzd') from
>'Locations' ONCE.
>* Apply 'tzd' to all timestamp-related fields retrieved from A, B and
>C - i.e. A.StartTime = A.StartTime + tzd
>
>Now, I believe the logical way to do this is to obtain the tzd factor
>from 'Locations' as a subquery after my FROM clause, therefore
>assuring that this operation only occurs once and does not
>unnecessarily clog up my query.
>
>So, I have ended up with a query like this - which works perfectly
>fine I might add:
>
>SELECT a.v1, a.v2, DateAdd("h", Locations.[Time Difference], a.[t1])
>AS v3, b.v1...
>FROM a
>INNER JOIN (b INNER JOIN c ON ...) ...,
>(SELECT [Location Name], [Time Difference] FROM Locations ml WHERE
>[Location Name] = [Enter the name of your current location]) AS MyLoc
>WHERE ...
>
>Note the subquery occurs during the FROM clause!
>
>I can then run and save this query to my hearts content, and use it in
>whatever reports I choose. All is fine.
>
>Now, THE PROBLEM IS...
>
>If I come to edit (add/remove fields to/from) this query again in
>Access Query Design or raw SQL view, and then go to save the query /
>update the Record Source property, I get this error:
>
>"Invalid bracketing of name 'SELECT [Location Name'."
>
>Investigations reveal that the error is caused because Access decides
>to do the bloody "smart" thing and reformat my query in it's own
>image, so that the part of the query which once read:
>
>FROM a
>INNER JOIN (b INNER JOIN c ON ...) ...,
>(SELECT [Location Name], [Time Difference] FROM Locations ml WHERE
>[Location Name] = [Enter the name of your current location]) AS MyLoc
>
>Now reads...
>
>FROM [SELECT [Location Name], [Time Difference] FROM Locations ml
>WHERE [Location Name] = [Enter the name of your current location]]. AS
>myLoc, a INNER JOIN (b INNER JOIN c ON ...) ON ...
>
>Digging the newsgroups has revealed that Access does not like
>occurences of double brackets ('[[' or ']]') - yet the stupid thing
>still decides to do it!
>
>Now, I can solve this problem by simply replacing the '[SELECT' with
>'(SELECT' and ']]. AS myLoc' with ']) AS myLoc', however,
>
>AND HERE IS MY QUESTION:
>
>Surely there has to be a better way around this? Is this solvable, or
>am I stuck with this workaround?
>
>I really do not want to have to rewrite this query because:
>
>* This type of logic/structure is used in 30+ different queroes.
>* I cannot see how else I can run a subquery to get this information,
>and don't see how a LEFT JOIN or other JOIN type can help me obtain
>the data (tzd) that I need without 'affecting' the data I return!
>
>And just to finish off... can I please go back to SQL Server now?
>I've learnt my lesson!
>
>Apologies for waffling on, if ANYONE can help, I will be eternally
>greatful.
>
>Regards,
>
>Andy McNab[/color]
