Finding the Nth instance in a query

[Tokash]

I have the following data in a table:

NAME DATE START END
Joe 1/11/04 0900 1730
Joe 1/12/04 0600 1000
Joe 1/12/04 1000 1400
Joe 1/12/04 1400 1430
Joe 1/13/04 0900 1300
Joe 1/13/04 1300 1730
Joe 1/14/04 0900 1730
Joe 1/15/04 0600 1000
Joe 1/15/04 1000 1430
Joe 1/16/04 0900 1730
Joe 1/17/04 0700 1200
Joe 1/17/04 1200 1530

What I need to do is create a query that will pick the Nth
instance of each date. For example, the result for the
1st date instance would be:

NAME DATE START END
Joe 1/11/04 0900 1730
Joe 1/12/04 0600 1000
Joe 1/13/04 0900 1300
Joe 1/14/04 0900 1730
Joe 1/15/04 0600 1000
Joe 1/16/04 0900 1730
Joe 1/17/04 0700 1200

The result for the 2nd date instance would be:

NAME DATE START END
Joe 1/12/04 1000 1400
Joe 1/13/04 1300 1730
Joe 1/15/04 1000 1430
Joe 1/17/04 1200 1530

The result for the 3rd instance would be:

NAME DATE START END
Joe 1/12/04 1400 1430

Anyone have an idea of how to build such a query?
Thanks in advance for any help.
Tok

 

[Tom Ellison]

Dear Tok:

Before you can "rank" the rows in your query, they must be considered
to be in some order. The ranking you desire seems to follow the
"grouping" of NAME and DATE. Within each of these groups, the ranking
would appear to be by START.

Assuming I've got that right, here are two ways to do it.

This one ranks every row:

SELECT NAME, DATE, START, END,
(SELECT COUNT(*) + 1 FROM YourTable T2
WHERE T2.NAME = T1.NAME AND T2.DATE = T1.DATE
AND T2.START < T1.START) AS Rank
FROM YourTable T1
ORDER BY NAME, DATE, START

If you ever have 2 rows with the same START, NAME, and DATE, that will
cause this to skip over a RANK. That is because, when there is a two
way tie for second place then there is no third place. If you want to
prevent this from happening, you could place a UNIQUE constraint
(index) on the table for the columns NAME, DATE, START.

On the above query, you could then run another query to return the 4th
ranked rows only:

SELECT NAME, DATE, START, END
FROM qrySaved
WHERE Rank = 4

You can do all of this in one query if you like:

SELECT NAME, DATE, START, END
FROM YourTable T1
WHERE (SELECT COUNT(*) + 1 FROM YourTable T2
WHERE T2.NAME = T1.NAME AND T2.DATE = T1.DATE
AND T2.START < T1.START) = 4

In all the above, substitute the actual name of your table or saved
query.

Please let me know if this helped.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts