Re: Buggy SQL Syntax for External File Path in Access 2007
- From: "David W. Fenton" <XXXusenet@xxxxxxxxxxxxxxxxxxx>
- Date: 6 Mar 2009 23:55:33 GMT
RD <progr@xxxxxxxxx> wrote in
news:lj93r4dumrlp9bl8iibovrae96rebn95hu@xxxxxxx:
Access' query grid does do strange things for no apparent (at
least to me) reason. I'm using Acc2K3.
On the Access Web, under Queries is the article:
Getting a related field from a GroupBy (total) query
The last query looks like this:
SELECT Q.BookID, Q.BorrowerID
FROM TableName As Q INNER JOIN
(SELECT BookID, Max(DateOut) As S
FROM TableName
GROUP BY BookID) As T
ON Q.BookId=T.BookId AND Q.DateOut = T.S
Entering that into the grid, saving it and re-opening it produces
this:
SELECT Q.BookID, Q.BorrowerID
FROM TableName AS Q INNER JOIN [SELECT BookID, Max(DateOut) As S
FROM TableName
GROUP BY BookID]. AS T ON (Q.DateOut = T.S) AND
(Q.BookId=T.BookId);
Parentheses around the sub-select are turned into square brackets
and a dot is appended to the sub-select. As you pointed out, the
query seems to run fine unless you make a change and then you get
a syntax error.
I've never seen anything about this glitch anywhere.
Hoping someone at least has an explanation,
[]. AS Alias is Jet SQL's syntax for derived tables. Standard ANSI
syntax is () As Alias, and when you type that in, you find that
Access converts it to Jet's syntax.
This is a good thing, seems to me.
And I've used it for years (I do lots of SQL with derived tables,
though mostly not in saved queries).
One option you might consider is setting your database to use ANSI
92 SQL, which will mean that you can use the standard () AS Alias
syntax. But it also means you have to use ANSI wildcards, % in place
of * and _ in place of ?.
Now, as to your other assertion, that the SQL breaks if you edit it,
I've never had that issue myself. Perhaps your edit is in error?
It's certainly the case that the bracket syntax means you can't have
any brackets inside the SELECT statement of your derived table, so
if you're introducing the need for brackets (e.g., by adding a field
to the SELECT clause that has a space or non-standard character in
its name), then that would definitely break it. If you've got
fields/tables with badly-designed names (i.e., with spaces and
non-alphanumeric characters) then you're going to have to use
brackets and in that case, ANSI 92 mode is probably going to make
life easier for working with derived tables (though it means
converting all the wildcards...).
But the better alternative is to NOT USE STUPID NAMES for fields and
tables. That means no spaces and no funny characters.
Of course, often you have no control over the schema so you're
forced to use badly-named fields/tables. In that case, I'd likely
create saved queries that alias the fields to good names, or if
using SQL Server, create a server-side view with the aliases and use
that in place of the basic table. That way, you don't have to worry
about mucking around with ANSI 92 mode and finessing issues with
brackets in SQL derived tables.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
.
- Follow-Ups:
- References:
- Prev by Date: Re: Buggy SQL Syntax for External File Path in Access 2007
- Next by Date: Re: ADO Minus/Intersect?
- Previous by thread: Re: Buggy SQL Syntax for External File Path in Access 2007
- Next by thread: Re: Buggy SQL Syntax for External File Path in Access 2007
- Index(es):
Relevant Pages
|
Loading
