Strange Access SQL conversion

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mscertified - 08 Nov 2007 21:31 GMT

I coded this query in SQL:

SELECT DOS, COUNT(CPT)
FROM (SELECT DISTINCT DOS, CPT FROM tblDOS)
GROUP BY DOS
HAVING COUNT(CPT)>=2;

When I saved and re-opened it, it was converted to:

SELECT DOS, COUNT(CPT)
FROM [SELECT DISTINCT DOS, CPT FROM tblDOS]. AS [%$##@_Alias]
GROUP BY DOS
HAVING COUNT(CPT)>=2;

Any idea what the strange syntax means, especially the period ???

Reply to this Message

John W. Vinson - 08 Nov 2007 22:05 GMT

>I coded this query in SQL:
>
[quoted text clipped - 11 lines]
>
>Any idea what the strange syntax means, especially the period ???

It means that the developers of your version of Access invented a strange
nonstandard way of depicting subqueries and implemented it.

That's just the way A97 (and I don't recall which other versions) handles
subqueries.

John W. Vinson [MVP]

Reply to this Message

David W. Fenton - 09 Nov 2007 01:30 GMT

>>I coded this query in SQL:
>>
[quoted text clipped - 18 lines]
> That's just the way A97 (and I don't recall which other versions)
> handles subqueries.

Er, this *class* of subqueries, which I think of as "virtual tables"
and most people call "derived tables," I think. There are plenty of
other types of subqueries that Jet SQL does *not* treat
idiosyncratically.

And, of course, it's not Access that requires this, but Jet --
Access is just formatting your query so that it will work properly
with Jet. Access doesn't have its own dialect of SQL, but Jet does.

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message