pulling the top 40 records for each user

[homeguard]

I have a database that has the testing scores of users but some users did the test more then once when they were only suppose to do it only once, for every test there is 40 records generated with a time stamp. I want to pull off the first 40 records for each user so i can get their first test results.

here is the reg select statement from the table:

SELECT [entrance exam].ID, [entrance exam].[last name], [entrance exam].[first name], [entrance exam].date1, [entrance exam].[question name], [entrance exam].answers, [entrance exam].correct_r, [entrance exam].Field8, [entrance exam].Field12
FROM [entrance exam];

date1 is the date the test was submitted

How would i go about doing this?

 

[namliam]

1) You shouldnt use spaces in your column names
2) Do all 40 records you are looking for the same Date1??

 

[homeguard]

nope they have different dates per user per test, so all the questions to one test have the same time stamp.

I think i have a way to do it, do a group by user name and min on the timestamp then to a join on the main table.

 

[homeguard]

yup that worked, thanks for your time

 

[namliam]

I think i have a way to do it, do a group by user name and min on the timestamp then to a join on the main table.

Something like that, I was thinking too...