Find 3rd Record

  • Thread starter Thread starter Johnny Bright
  • Start date Start date
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!
 
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.
 
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
 
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;
 
Back
Top