System not Responding when query is executed

[WB]

I am using Access 2000 and trying to execute the following query.


SELECT x0.*
FROM qryRetainedBase AS x0
WHERE (((x0.TransactionId) In (SELECT TOP 2 x1.TransactionId
FROM qryRetainedBase x1
WHERE x1.CustomerId = x0.CustomerId)));



qryRetainedBase:
SELECT tblTransaction.CustomerId, tblTransaction.TransactionId,
tblTransaction.StylistId
FROM (tblTransaction INNER JOIN tblTransactionDetail ON
tblTransaction.TransactionId = tblTransactionDetail.TransactionId) INNER
JOIN tblInventory ON tblTransactionDetail.InventoryId =
tblInventory.InventoryId
WHERE (((tblTransaction.CustomerId)<>66 And (tblTransaction.CustomerId)<>70
And (tblTransaction.CustomerId)<>71 And (tblTransaction.CustomerId)<>6240)
AND ((tblTransaction.TransactionDate)>#7/1/2004#) AND
((tblInventory.Retail)=0))
ORDER BY tblTransaction.CustomerId, tblTransaction.TransactionId DESC;


When I execute the qryRetainedBase I get results; however, when I execute
the first query it just hangs and I have to kill the process. When I run
the same SQL on SQL Server 2000 there is no problem. Is there a reason
Access can't handle this type of nested query?

WB

 

[[MVP] S.Clark]

I wouldda thought you'd get an error, as the subquery should only be able to
return 1 value. You have TOP 2. Not sure how that's getting handled.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

 

[John Spencer (MVP)]

First, I would try the order by clause in qryRetainedBase. It just slows things
down. You probably do need an order by clause in the subquery though if you
want to get a specific top 2.

Do you have an index on CustomerID, InventoryID, and TransactionID? I would
assume you do since those look as if they are the primary keys.

Access does have its limits and this may be one of them.