Pulling records 1

[skd2726]

I have multiple records for a set number of people, 4 records each,I need to extract only the top 3 records for each person...can someone tell me how to do this?

 

[SBendBuckeye]

How are you going to determine which are the top 3 records? Are you doing this in a query or as part of form processing?

Thanks!

 

[skd2726]

Each record has a time field, we are to keep the top 3 times and drop the lowest time

 

[skd2726]

Also, we are doing this in a query

 

[SBendBuckeye]

Use the TOP N select option in a subquery. Here is an example I did in Access2000:

SELECT tblGroupByTest.Group, tblGroupByTest.Number
FROM tblGroupByTest
WHERE tblGroupByTest.GroupID IN
(SELECT TOP 3 tblGBT.GroupID
FROM tblGroupByTest AS tblGBT
WHERE tblGroupByTest.Group = tblGBT.Group
ORDER BY tblGBT.Group, tblGBT.Number DESC)
ORDER BY tblGroupByTest.Group, tblGroupByTest.Number DESC;

The key to making this work is the WHERE clause in the Subselect. By setting it equal to the outside Group you force it to evaluate for every group, not just once overall which is what TOP N normally returns. GroupID is a primary key which ensures that there are no ties. If I sorted by Group only then the Subselect would bring them all back because it brings back all ties.

In your case, within the Subselect you would want to sort by your date/time field DESC so you get the newest ones coming back to you. The main sort is independent of the Subselect sort.

In the example above, I have data like the following in the table named tblGroupByTest:

Group Number
Andy 100
Andy 200
Andy 300
Andy 400
Andy 500
Andy 600
Andy 700
Gary 10
Gary 20
Gary 30
Gary 40
Gary 50
Gary 60
Leslie 1
Leslie 2
Leslie 3
Leslie 4
Leslie 5
Leslie 6
Leslie 7
Leslie 8
Mark 1000
Mark 2000
Mark 3000
Mark 4000
Mark 5000
Mark 6000
Mark 7000
Mark 8000
Mark 9000
Mark 10000
Mark 11000
Mark 12000

The Query returns data like the following:
Group Number
Andy 700
Andy 600
Andy 500
Gary 60
Gary 50
Gary 40
Leslie 8
Leslie 7
Leslie 6
Mark 12000
Mark 11000
Mark 10000

Good Luck! Hopefully, this will get you started!

 

[skd2726]

I tried it but I'm doing something wrong:

SELECT table1.member, table1.time
FROM Table1
WHERE table1.id IN
(SELECT TOP 3 tblgbt.id
FROM table1 AS tblgbt
WHERE table1.time = tblgbt.time
ORDER BY tblgbt.member, tblgbt.time DESC)
ORDER BY table1.member, table1.time DESC;

my table:

ID member time
1 deanna 17.650
2 deanna 17.880
3 deanna 16.980
4 deanna 15.880
5 ashley 16.880
6 ashley 17.560
7 ashley 18.980
8 ashley 15.654

I'm still getting all the records not just 3 of each..what'd I do??

 

[skd2726]

Never mind, I got it:

SELECT Table1.member, Table1.time
FROM Table1
WHERE (((Table1.ID) In
(SELECT TOP 3 tbl.id
FROM table1 AS tbl
WHERE table1.member = tbl.member
ORDER BY tbl.member, tbl.time DESC)))
ORDER BY Table1.member, Table1.time DESC;