Find 3rd Record

J

Johnny Bright

Hi there,

I need to find the third record in a query. This is my basic select query:

SELECT tblGuests.GID, tblGuests.Date
FROM tblGuests
ORDER BY tblGuests.GID, tblGuests.Date;

There are several thousand records in tblGuests, some Guests (GID) have just
one record, others have 20 or more. I've thought of DateDiff function but
that doesn't seem to work. I have also thought that I need to find only
records where there are more than 2 records for a certain GID so I can filter
out the Guests who have only visited 1 or 2 times using the count function
but have no idea how to do this either.

Any help is greatly appreciated!
 
K

KARL DEWEY

There is no such thing as third record as they are all in one big storage
container. They get organized when you do a query and sort.
If you want guest with 3 or more records then use this ---
SELECT tblGuests.GID
FROM tblGuests
GROUP BY tblGuests.GID
HAVING (((Count(tblGuests.GID))>=3));

You can use this query left join to the table to pull records of those with
three or more.
 
J

Johnny Bright

Hi Karl,

Thanks, that does work but what I need to have isolated is the 3rd record
for each guest. I guess this isn't possible. I'll have to try another way.

Thanks!

John
 
K

KARL DEWEY

Try this ---
SELECT Q.GID, Q.Date, (SELECT COUNT(*) FROM tblGuests Q1
WHERE Q1.[GID] = Q.[GID]
AND Q1.Date < Q.Date)+1 AS Rank
FROM tblGuests AS Q
WHERE ((((SELECT COUNT(*) FROM tblGuests Q1
WHERE Q1.[GID] = Q.[GID]
AND Q1.Date < Q.Date)+1)=3))
ORDER BY Q.GID, Q.Date;
 
Top