Select Query filtering duplicate records by criteria

[Dachbo]

Hello,



I have a table with employees. Some employees are listed several times in this table with different qualifications. I want a query that filters all the duplicate records by criteria, that every employee with his highest qualification is left.


I don't know how the criteria should look like.

Thank you

[JoeM]

Create an Aggregate (Totals) Query, where you Group By Employee and take the Max value of Qualification.
You can do this using the Query Builder and using the Totals Query button (looks like a Sigma).

The resulting SQL code of that query will look something like this:

SELECT Table_1.Employee, Max(Table_1.Qualification) AS MaxOfQualification
FROM Table_1
GROUP BY Table_1.Employee;

[Dachbo]

Thank you.

How do I tell the query, which qualification is the lowest, the middle one and the highest?

[JoeM]

Well, I already showed you how to get the highest (Max). Just use Min to get the lowest.
To get the middle is a little trickier, and may involve some VBA. Take a look here: https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

[Dachbo]

I got access to find "the highest" value, but it's not the highest, it's just some random. The problem is the Qualifications are not named in some order. They are called like "A1", "A2", "RBB" or "OT". How do I tell Acces that "A1" < "A2" < "RBB" < "OT". Now Access might say "RBB" is the highest and not "OT"

[JoeM]

If they do not go in alphabetical order, you will need to create a "hierarchy" table (otherwise, how would it know your order?).
You should have two columns in this table, the Qualification and its associated ranking number.
Then you can link to this table, and use the ranking for your query to pick your highest/lowest rankings (much in the same way we should before, with the Aggregate Query).