Get Multiple Max Records

[ahd2008]

Good day,

Hope you can help me to build this query. I have two tables one is for the tasks and the other one is for trials (see description of tables below). Each employee is allowed to perform more than a trial per each task. In other words, each task could have more than one trial for any employee. What I want to get is the records in the Trials Table based on the latest or maximum Trial Number performed in each task for all the employees (which are lines# 2, 4&5 in the example below).

Example:

Trial ID Task ID Trial Number Employee Badge
1 50 1 277321
2 50 2 277321
3 50 1 280000
4 50 2 270000
5 60 1 277321


Description:

Tasks Table (Parent Table)
1- Task ID (Primary Key)
2- Task Title
Trials Table (Child Table)
1- Trial ID (Primary Key)
2- Task ID (Foreign Key)
3- Trial Number (this reflects how many times the task has been performed)
4- Employee Badge

Thanks in advance

[twinnyfo]

I would use an aggregate query, grouping on the Employee Badge Number, the Task ID and the Max Trial Number.

In your set of records above, you should get:

EBN Task ID Trial Number
270000 50 2
277321 50 2
277321 60 1
280000 50 2

Is this the result you want?

[zmbd]

try:
SQL:

SELECT 
   tbl_trials.badge,
   Max(tbl_trials.trial_number) AS MaxOftrial_number
FROM tbl_trials
GROUP BY tbl_trials.badge;

Returns:

badge	MaxOftrial_number
270000	2
277321	2
280000	1

If you want to show trial_id too then that's a different bag of tricks.
-z

[ahd2008]

Thanks for your swift response gents. I regret to tell you that I have done a mistake while posting the question. Although I have double checked the post but couldn't spot that minor error. The error is in the fourth record; the badge number should be 280000 instead of 270000.

twinnyfo, thanks for your support. The problem with your suggestion is that when I want to show the Trial ID in the query, I don't get the required result.

zmbd, thanks for your help too. Your approach is almost what I need and I have tried that before. The only problem is as you mentioned I need to show the Trial ID but without affecting the results. Yes, please I need your bag of tricks :)

Thank you all again

[Rabbit]

Take the aggregate query and join it back to the table to retrieve the other fields that you didn't group by.

[zmbd]

Ahh Rabbit,
You spoiled my fun
:)

Of course, Rabbit is absolutely correct.
-z

[twinnyfo]

Yes.... I keep forgetting that one can only query one additional field when using the Max Function....

[ahd2008]

Good day all,

Thanks for everyone participated. Dear Rabbit, I cannot seem to have your suggestion worked out.

I built an aggregate query as directed by twinnyfo & zmbd, but when I joined it to the Trials table it didn't bring the required result.

This is the aggregate query to get the maximum tirals "Query1"

SELECT Trials.BadgeNo, Trials.TaskID, Max(Trials.[Trial Number]) AS [MaxOfTrial Number]
FROM Trials
GROUP BY Trials.BadgeNo, Trials.TaskID;

This is the second query that joins the query above with the Trials table "Query2"

SELECT Query1.BadgeNo, Query1.TaskID, Query1.[MaxOfTrial Number], Trials.[Trial ID]
FROM Trials INNER JOIN Query1 ON Trials.TaskID = Query1.TaskID;

This is result that I am looking for:

EBN   Task ID MaxOftrial_number Trial ID
270000 50              2            4
277321 50              2            2
277321 60              1            5
280000 50              1            3

Thanks again

[zmbd]

Close,

Try this...

Design, show the trials table, show the query
Pull the desired fields from trials table to the builder grid
in the table link between the table and query on the two related fields in the query
Show nothing of the query in the grid.
-z

[twinnyfo]

ahd2008,

Here is what I came up with:

qryMaxTrial:

SELECT Trials.[Employee Badge], Trials.[Task ID], Max(Trials.[Trial Number]) AS [MaxOfTrial Number]
FROM Trials
GROUP BY Trials.[Employee Badge], Trials.[Task ID];

qryMaxTrialID

SELECT Trials.[Employee Badge], Trials.[Task ID], Trials.[Trial Number], Trials.[Trial ID]
FROM qryMaxTrial INNER JOIN Trials ON (qryMaxTrial.[MaxOfTrial Number] = Trials.[Trial Number]) AND (qryMaxTrial.[Task ID] = Trials.[Task ID]) AND (qryMaxTrial.[Employee Badge] = Trials.[Employee Badge]);

qryMaxTrialID produces this result:

Employee Badge Task ID Trial Number Trial ID
277321         50      2            2
280000         50      1            3
270000         50      2            4
277321         60      1            5

Hope this helps.......... ..

[zmbd]

and a different approach

so many ways to skin this cat...

Here's mine:

SELECT 
   tbl_trials.[badge],
   tbl_trials.[task_id], 
   tbl_trials.[trial_number], 
   tbl_trials.[trial_id]
FROM tbl_trials 
   INNER JOIN 
      (SELECT 
          tbl_trials.badge,
          Max(tbl_trials.trial_number) 
             AS MaxOftrial_number
        FROM tbl_trials
        GROUP BY tbl_trials.badge)  
            AS qry_trials_maxoftrial 
        ON (tbl_trials.badge = qry_trials_maxoftrial.badge) 
            AND (qry_trials_maxoftrial.MaxOftrial_number = tbl_trials.trial_number);

1) Simple select query on the original table showing all of the fields,
2) Add the first aggregate query I posted
3) and join on the two returned fields from aggregate

And now you get:

badge	task_id	trial_number	trial_id
277321	   50	     2	            2
280000	   50	     1	            3
270000	   50	     2	            4

(note: I didn't change the badgeid in the data table as this is just an example...)
-z

[ahd2008]

Many thanks for twinnyfo & zmbd. You have done great inspiring efforts and enlightend me a lot. Now it is working

Thanks again and best wishes to you all