SQL Bracketing Bug

[Guest]

The following query returns unique combinations and record count of the
[Reporting Unit] and [Group] columns in table [Location] having more than one
[Group] column value.

This works fine, but after saving it and re-opening in SQL view I see that
the parentheses around the subquery select statements have been replaced with
square brackets (plus a period after closing brackets). Oddly, the query will
still execute in this state, but any editing of the SQL code causes the
improper bracketing to result in an error. This can resolved by changing the
brackets back to parentheses and deleting the inserted periods, but this
should not be needed.

Does anyone have an idea what is causing this, or better yet, a way to
prevent it from happening???

SELECT C.[Reporting Unit], C.Group, C.Recs
FROM (SELECT [Reporting Unit] FROM

(SELECT [Reporting Unit], Group
FROM [Location]
GROUP BY [Reporting Unit], Group) A

GROUP BY A.[Reporting Unit] HAVING Count(*) >1) AS B INNER JOIN (SELECT
[Reporting Unit], Group, Count(*) AS Recs FROM [Location] GROUP BY [Reporting
Unit], Group) AS C ON B.[Reporting Unit] = C.[Reporting Unit];

FYI, I am using Access 2002 on Windows XP.

Thanks for any comments,

TK

 

[Marshall Barton]

The following query returns unique combinations and record count of the
[Reporting Unit] and [Group] columns in table [Location] having more than one
[Group] column value.

This works fine, but after saving it and re-opening in SQL view I see that
the parentheses around the subquery select statements have been replaced with
square brackets (plus a period after closing brackets). Oddly, the query will
still execute in this state, but any editing of the SQL code causes the
improper bracketing to result in an error. This can resolved by changing the
brackets back to parentheses and deleting the inserted periods, but this
should not be needed.

Does anyone have an idea what is causing this, or better yet, a way to
prevent it from happening???

SELECT C.[Reporting Unit], C.Group, C.Recs
FROM (SELECT [Reporting Unit] FROM

(SELECT [Reporting Unit], Group
FROM [Location]
GROUP BY [Reporting Unit], Group) A

GROUP BY A.[Reporting Unit] HAVING Count(*) >1) AS B INNER JOIN (SELECT
[Reporting Unit], Group, Count(*) AS Recs FROM [Location] GROUP BY [Reporting
Unit], Group) AS C ON B.[Reporting Unit] = C.[Reporting Unit];

FYI, I am using Access 2002 on Windows XP.

Well, it's small compensation, but I think you should not
have this issue either. However, the parenthesis syntax was
added as a convenience, while the Square bracket syntax is
what Access really uses. The truely unfortunate part of all
this is that you can not use any square brackets within
square brackets. This means that you can not nest
subqueries and that you need to change the field names to
get rid of any space or other non-alphanumeric characters
(i.e. use something like ReportingUnit instead of
[Reporting Unit].

There may be an additional confusion caused by your use of a
filed named Group, which is an SQL keyword.

 

[david epsom dot com dot au]

The []. syntax was the original (undocumented) Jet syntax.
Unfortunately, it appears that when the syntax was changed
to agree with the standard, the Query-By-Example system
was not fully fixed: Opening in SQL-view it writes your
SQL in the old style, which it no longer understands.
Changing the SQL invalidates the compiled version of the query,
and next time you try to run the query, it fails while trying to
interpret the SQL.

If you have a lot of queries like this, you may wish to
create your own form to edit the SQL (using Querydefs().SQL)

(david)