MAX Query help

[HF_JBB]

Hi, i can't seem to display the ONLY worker with the highest salary (with his name).

this is my situation:

in table A i have the names of the workers
in table B i have the pay of the workers

I've set, Max(Salary) FROM TBL_B to find the highest salary in table B...but i'm having trouble figuring out how to display the name of the worker with the highest salary from table A. I keep getting the whole list since im using group by.

btw, i'm new to access , can anyone help me out? thanks

 

[HF_JBB]

i think i found out how to do it.


SELECT ref_emp, salaire_prime
FROM TBL_EMPLOYE_PAIE
WHERE salaire_prime = ( SELECT MAX(salaire_prime) FROM TBL_EMPLOYE_PAIE );

i believe we can only do this with a subquery?

 

[RV]

Assuming you have a relationship between the two tables using WorkersID, try this:

SELECT A.WorkersName, B.Salary
FROM A, B
WHERE A.WorkersID = B.WorkersID
AND B.Salary =
(SELECT Max(Salary)
FROM B);

Greetings,

RV

 

[HF_JBB]

thanks, it works great

 

[mous]

Hello

I've tried this also and it works great .... but

in my table I have say, many workers where I want to find the highest salary for each worker in my table not just the highest paid worker themselves.

Can I alter this query to show this.

Thanks

Dawn

 

[Jon K]

The following query will return the highest salary of each worker:

SELECT *
FROM [TableName] AS a
WHERE [Salary] = (Select max([Salary]) from [TableName] where [WorkerID]=a.[WorkerID]);


Note. The query cannot be optimised by indexes, so it may take time to run if the table contains many records (e.g. over 10,000 records) or the query is run on a relatively slow machine.

If the table is too large, you may find yourself better off adding a HighestSalary field in the Workers table and updating the field whenever salary records are added to the Salaries table.

 

[mous]

Hello Again

As I'm new to SQL I don't fully understand this but after an hour trying I managed to get the Worker and Highest Paid Salary as I requested. Thank you.

Can I go a step further? ....

The above was just therorectical, what I have in one table is:-

Table - A

field - Student ID
field - Course Code
field - Total Hours For Student On Course

I want my query to show for each Student ID the Highest Total Hours For Student On Course and to state that Course Code.

Is it possible to show the sum of Total Hours For Student On Course also in the same query.

Am I asking to much?

Regards

Dawneach

 

[Jon K]

Try this query (type/paste in the SQL View of a new query, using the correct table name in three instances):-


SELECT [Student ID], [Course Code], [Total Hours For Student On Course] AS [Highest Total Hours For Student On Course], (Select Sum([Total Hours For Student On Course]) from [TableName] where [Student ID]=c.[Student ID]) AS [Sum of Total Hours For Student On Course]
FROM [TableName] AS c
WHERE [Total Hours For Student On Course]=(Select Max([Total Hours For Student On Course]) from [TableName] where [Student ID]=c.[Student ID]);

 

[mous]

Many Thanks Jon

WHAT A STAR!!