Query Problem - Obtain Client's Second Appointment

[Wendy Tate]

I work with a large amount of data in Microsoft Access, where a client could
attend a hundred appointments all on different dates/times. Using a query,
I can obtain their first appointment and last appointment easily enough.

But I really want to find an easy way to obtain a client's second
appointment. Is this possible without resorting to coding in VBA?

Many thanks

Wendy

 

[Michel Walsh]

Yes, a kind of top 2, by client:


SELECT a.*
FROM myTable AS a
WHERE a.dateTime IN(SELECT TOP 2 b.dateTime
FROM myTable As b
WHERE b.clientID = a.clientID
ORDER BY b.dateTime)
ORDER BY a.clientID, a.dateTime



or by ranking:


SELECT a.clientID, a.dateTime
FROM myTable As a INNER JOIN myTable As b
ON a.clientID=b.clientID AND a.dateTime >= b.dateTime
GROUP BY a.clientID, a.dateTime
HAVING COUNT(*) <= 2


(change the ON clause to ON a.clientID=b.clientID AND a.dateTime <=
b.dateTime for the reverse ordering)
(change the HAVING clause to HAVING COUNT(*)=2 to have ONLY the second
occurrence)




Hoping it may help,
Vanderghast, Access MVP

 

[Wendy Tate]

~ ~ Many thanks for your quick reply Michel - it's much appreciated! ~ ~

~ ~ I'll use this in work tomorrow ~ ~