Return top N rows ordered by date after table join
<p>I posted a few weeks back with an issue, now I've gotten past it and found a new need and new issue. I'm using access 2010, unsure what version database, have on;y access grunt tools to use (no objects, for instance). I have a query that works, it looks like this. The goal of this query is to use one row as a baseline and find all related rows that come after it chronologically. For all I know, this may be a very crude solution.</p>

<pre><code>SELECT QueueA.cnlyMemberId, QueueA.updateUser AS 305UpdateUser, QueueA.updateDt AS 305UpdateDt, QueueB.statusCd
FROM (SELECT cnlyMemberID, updateUser, updateDt 
      FROM V_Queue_History
      WHERE statusCd = "305" AND
            V_Queue_History.updateDt Between [Enter Start Date:] And [Enter End Date: (must be at least one day apart)]
     ) AS QueueA INNER JOIN
     V_Queue_History AS QueueB
     ON (QueueA.cnlyMemberID = QueueB.cnlyMemberID AND
         QueueA.updateDt &lt; QueueB.updateDt
        )
</code></pre>

<p>Now what I want to do is I want to find the first 305 statusCd and the next statusCd chronologically, rather than every statusCd that follows. I've tried a few things now, mostly trying to take the TOP 2 with an ORDER BY updateDt slipped in the ON condition. Then I tried replacing the V_Queue_History with a table and doing the same, didn't work. I just tried using a where condition where I check to see if the </p>

<p>QueueB.cnlyMemberId is IN (SELECT TOP 2 cnlylMemberID FROM QueueB WHERE conditions);</p>

<p>but still nothing. The system would tell me it didn't know what QueueB was anymore, so none of it would run in the WHERE statement. I would be more specific on but I just made Access crash and my mind is starting to get hazy from how frustrating this can be. I don't have much experience with Access/SQL at all and I'm learning it pretty much as I go.</p>

<p>So to recap, the first query runs fine but I need to return only the row with the status updated first after the initial 305.</p>

<p>Thanks!</p>