Help with Access Macros

[Chris]

Hi Mel,

It would look something like:

SELECT Table1.EmpName, Table1.Score
FROM Table1
GROUP BY Table1.EmpName, Table1.Score
HAVING (((Table1.Score) In (Select top 8 Score from Table1
as Table2 where table2.empname = table1.empname ORDER BY
SCORE DESC)))
ORDER BY Table1.EmpName, Table1.Score DESC;


Please see the following article that describes subqueries
in depth:

http://www.databasejournal.com/features/msaccess/article.ph
p/3304831

Chris

-----Original Message-----
Hello,

I have a large database with a table that consist of 600

people each with a range of 8 to 12 records (7200 total
records). One of the fields has scores for the employee
per record. I would like to create code that will
automatically get 8 of the highest scores for each
person.

Example (Trying to get highest 2 scores per employee using code in Access)

Actual Trying to do
Sharon 99 Sharon 99
Sharon 95 Sharon 95
Sharon 93 Bobby 92
Bobby 85 Bobby 85
Bobby 92 Mike 100
Mike 100 Mike 99
Mike 95
Mike 99
I do not know how to do this in Access with so many

records nor do I know if it is even possible. I would
think I need to create an array with a loop to check the
scores against each other. If anyone has any answers
please let me know. Thanks

 

[Bas Cost Budde]

Hi Mel,

It would look something like:

SELECT Table1.EmpName, Table1.Score
FROM Table1
GROUP BY Table1.EmpName, Table1.Score
HAVING (((Table1.Score) In (Select top 8 Score from Table1
as Table2 where table2.empname = table1.empname ORDER BY
SCORE DESC)))
ORDER BY Table1.EmpName, Table1.Score DESC;


Please see the following article that describes subqueries
in depth:

http://www.databasejournal.com/features/msaccess/article.ph
p/3304831

Great, thanks Chris.