Query for only the highest level

[NISMOJim]

Hello All,



It's been a few months since I have worked on a database, and I must have forgotten everything. Here is my dilema, and what I am looking to do...

I'm using a table to track all employee's training. This training ranges from A1 Training (the highest), A2 Training, B1 and B2 (lowest). I have built 4 queries (qryA1, qryA2, etc), each one gives me a list of all employees that have received that training.

In the end, I want to have a report that shows the rank level at the top of each column, with a list of names underneath of who has completed the training. The problem is, I only want a person to show up in the column of their highest level completed, not in the columns of the lower training that they have also completed. In other words, if someone is A2 trained, their name would only show up in the A2 column, not on the B1 or B2 columns, even though they have completed that training also.

How can I get a query to essentially remove the names that appear on a lower level training query, or is there a better way to go about this?

As always, thank you for your help.

[recyan]

Is it possible to make a query & use iff in it to assign numbers to the levels. Eg : A1 = 4, A2 = 3, B2 = 2, B1 = 1 ?
Then use another GROUP BY query on the results of above, using GROUP BY on Name & MAX on the numbers.

Thanks

[NISMOJim]

...and that's why I come here for help. Works great, thank you! I now have 6 queries based on the one you suggested, each showing employee names for their particular training levels. Perfect!
Now, onto the report...I thought I would be able to create a blank report and build the textbox Control Sources to the EmployeeName fields in the queries. Textbox1 Control Source = [qryA1]![EmployeeName], Textbox2 = [qryA2]![EmployeeName], etc. Instead, all I get are textboxes that say "#Name?". Do I need to gather these 6 queries into one, then have the Record Source for the report as this one query?

[recyan]

I had something like below in my mind. Do not know if it works practically.
Query1

SELECT 
    myTable.fullname, 
    myTable.traininglevel, 
    IIf([traininglevel]="A1",4,IIf([traininglevel]="A2",3,IIf([traininglevel]="B1",2,1))) AS TheMaxLevel
FROM 
    myTable;

Query2

SELECT 
    Query1.fullname, 
    Max(Query1.TheMaxLevel) AS MaxOfTheMaxLevel
FROM 
    Query1
GROUP BY 
    Query1.fullname;

An addition, if possible, I would perhaps have had a tblMasterLevel table with the fields LevelID ( PK - Autonumber ), Level ( VAlues A1; A2; B1; B2 ) & LevelValue (4 FOR A1; 3 FOR A2; 2 FOR B1 & 1 FOR B2 ) .


Thanks

[NISMOJim]

I think I usually end up doing things the hard way. In any case, the queries work just fine. As for the report, shouldn't I be able to build the Control Source for each textbox on the report from different queries? I thought it would work, but no luck yet. Could I be missing a syntax or format somewhere, or does the report itself have to have a single record source as opposed to the textboxes having their own control source?

[recyan]

Do not know much about Reports. All the same, try using Query2 in my post as the source for the report & see what happens.

Thanks