Making query using Max() Function in MS Access

Asked By olisamuels 10 points N/A Posted on - 10/26/2011

I am using MS-Access.  I want to make a query that will show the highest paid employee on each department in my company. I used the totals query and the Max() function to get the highest paid employee and yet I can’t seem to get my expected output. How can I do this in a right way? How can I get an output that will show the lucky employee on each department?

• Status: Open
• Question Views: 2732
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: MS Access

Answered By Scot Barrett 0 points N/A #94649

Making query using Max() Function in MS Access

I will give you a simple instruction on how to do that.

1. I created a table name Table1 Which have fields NAME, DEPARTMENT AND SALARY.

 

2. As you said you want a query that will get the highest paid employee in every department. So we need to get the max sally and filter it also with the department. So I provided you the query.

Here is the design view of the query.

             and here is the code.

SELECT MAX(SALARY) AS MAXAMOUNT FROM Table1 WHERE DEPARTMENT='EDP';

In this query I select the Max salary which department name is EDP. And the result of the query is shown below

As you can see there are two names that the same EDP department, and the query select EDP department with a max salary which is 1000.  If you want to view also the name and the department which is the highest paid employee just add it to your SQL code.  Hope it helps you.

 

 

About Scot Barrett

Questions
0

Answers
1

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 10/28/2011
• Question Category: MS Access