Query for Updating table - comparing 2 recs per user then choosing

[Guest]

Hi,

Please assist,

Training Table
LName, FName Training class, Training status
In some cases there will 2 recs per user
I want to update my table (all users that must be trained) 1 rec per user

IF the user has taken class1 and class 2 then s/he is considered trained
because class1 is the priorirty status.

My table will be updated with training = class1 and status trained
Or
if the user has only taken class2 then
my table will be updated with training = class2 but training status will
remain blank

My train of thought is:

if user had training class1 and class2

then update training to class1 and training status = trained


else

update training to class2

 

[Guest]

If a user has taken only class 1 then is status = trained ?
If the answer is no then try this approach

I would suggest that you create a separate table to record the training
status of the users (one record only per user). You can use this query to
get at the status. Then build a query on it to create the separate table
containing only user and status.

SELECT training!LName+training!FName AS userKey,
Count(training.TrainingClass) AS CountOfTrainingClass
FROM training
WHERE (((training.TrainingClass)="class1" Or
(training.TrainingClass)="class2"))
GROUP BY training!LName+training!FName;