Help with Access Macros

  • Thread starter Thread starter Chris
  • Start date Start date
C

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
Click to expand...
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
Click to expand...
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
 
Chris said:
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
Click to expand...

Great, thanks Chris.
 
Back
Top