Access allergic to certain subqueries?

[Element]

Execution of the following statement sends Access into permanent la-la
land, but works fine in SQL Server on the same table:

SELECT *

FROM tblDailyProofTickets

WHERE TicketID NOT IN
(SELECT Keeper FROM
(SELECT MIN(A.TicketID) AS Keeper, SeqNbr
FROM tblDailyProofTickets AS A
GROUP BY A.SeqNbr)
AS KeepSet) ;

The subquery executes fine independently, but not as listed above.
Also, Access' query design view interpets the whole shebang with no
problems. Your thoughts?

Mark H.

[bcap]

Ah, the dreaded correlated subquery. Access will eventually finish running
the query, but you might need to take your summer vacation whilst it does
so.

"Element" <mh*****@lotuscreek.comwrote in message
news:75**********************************@34g2000h sh.googlegroups.com...

Execution of the following statement sends Access into permanent la-la
land, but works fine in SQL Server on the same table:

SELECT *

FROM tblDailyProofTickets

WHERE TicketID NOT IN
(SELECT Keeper FROM
(SELECT MIN(A.TicketID) AS Keeper, SeqNbr
FROM tblDailyProofTickets AS A
GROUP BY A.SeqNbr)
AS KeepSet) ;

The subquery executes fine independently, but not as listed above.
Also, Access' query design view interpets the whole shebang with no
problems. Your thoughts?

Mark H.

[Element]

On Aug 7, 9:05*am, "bcap" <[e-mail]:

Ah, the dreaded correlated subquery. *Access will eventually finish running
the query, but you might need to take your summer vacation whilst it doesso.

I see -- and so I shall use a join instead (at least there's an
alternative).

Come to think of it, I prefer joins better anyway.

Thanks for the reply.

[Allen Browne]

JOINs will be faster.

Access is not great at subqueries. You seem to be comfortable with SQL, but
you might get a laugh from some of these generic suggestions for Access
anyway:
Surviving Subqueries
at:
http://allenbrowne.com/subquery-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Element" <mh*****@lotuscreek.comwrote in message
news:2a**********************************@z72g2000 hsb.googlegroups.com...
On Aug 7, 9:05 am, "bcap" <[e-mail]:

Ah, the dreaded correlated subquery. Access will eventually finish running
the query, but you might need to take your summer vacation whilst it does
so.

I see -- and so I shall use a join instead (at least there's an
alternative).

Come to think of it, I prefer joins better anyway.

Thanks for the reply.

[Element]

On Aug 7, 10:19*am, "Allen Browne" <[e-mail]:

Surviving Subqueries at: http://allenbrowne.com/subquery-02.html

A very complete treatment of the subject in the Access world, thanks
for the link.

I should also note -- as most of you probably already know -- that
many times the need for a subquery points to deficiencies in the SQL
language, not as much to a problem with a specific database engine.
Maybe someday the SQL committee will wake up and come up with a real
revamp, not what we got with SQL-99 -- esp. multi-valued
fields...yikes.
Mark H.

[Element]

On Aug 8, 8:42*am, lyle fairfield <[e-mail]:

I don't see this as a deficiency in any SQL language but a deficiency
in the Access query dialog/wizard.

Just to clarify -- I originally wrote the query in a SQL text editor,
not Access' query builder. I ended up manually converting it to a join
and it worked expediently...which, again, is preferable to the IN
(subquery) deal anyway.

I definitely agree that Access has its own specific deficiencies with
query processing, but some of them wouldn't be a problem to solve in
the first place if SQL were a better language. SQL is still the best
thing we have, but there seems to have been no motivation over the
past 15 years for the major DB vendors to truly improve it by making
it more relational and have a more consistent syntax (e.g. QUEL). They
obviously have other fad-driven priorities like XML support (yuck) and
creating things like LINQ (puke), instead of increasing the leveraging
of logic and set mathematics.

Well, I suppose this thread is leaning heavily towards
comp.databases.theory now :)