Complicated Query Problem 1

[krispi]

I have a table containing job descriptions & salary details. There are >20k records in the table. In order to filter out statistical anomalies, I want to delete certain records where the salary details are unusually high or low. Is it possible to create a query or continuous forms view which does the following?

Look at each record in Table1
Look for duplicate instances instance of field 'Jobtitle' Where there are >10 duplicate values in this field, display the records with the Min and Max values for field 'Salary'.
Do not display any other records.

Any help, as always, gratefully appreciated.

 

[PHV]

A starting point:
SELECT Jobtitle, Count(*) AS CountOfDups, Min(Salary) AS MinOfSalary, Max(Salary) AS MaxOfSalary
FROM Table1
GROUP BY Jobtitle
HAVING Count(*) > 10;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[krispi]

Thanks! If I could give more than one star I would.