Strange Access SQL conversion

[mscertified]

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

 

[John W. Vinson]

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

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]

 

[David W. Fenton]

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

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.

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.