Taking corresponding record using max function

[Stevestan1]

I have a table with 3 columns. The columns are "Case Number", "Agent ID", and "Time the Agent Touched Case". There are multiple records of the same "Case Number" since a case can be touched by multiple agents at different times. I am trying to find the last agent who touched the case and the time they touched the case. I can find the time by using the Max function in a query, but how do I get Access to return the corresponding agent? Thanks in advance.

[NeoPa]

I'd use a subquery to identify the max time then link this data in to the original table to find only the relevant records. Be careful though. If cases can have multiple agents touching them at the same time, then you may need to handle this scenario specially or get multiple results.

Anyway, something like this should work for you :

SELECT   tT.[Case Number]
       , tT.[Agent ID]
       , tT.[Time the Agent Touched Case]
FROM     [Table] AS tT
         INNER JOIN
    (
    SELECT   [Case Number]
           , Max([Time the Agent Touched Case]) AS MaxTime
    FROM     [Table] AS tTi
    GROUP BY [Case Number]
    ) AS sT
  ON     tT.[Case Number] = sT.[Case Number])
 AND     tT.[Time the Agent Touched Case] = sT.[MaxTime]

[Stevestan1]

Still haven't found a process for this yet. Right now, I am grouping first by "Case Number" and then doing a Max for "Time the Agent Touched Case." If I include "Agent ID" as a group by after "Time the Agent Touched Case", I'll get multiple records with the same case number. I am trying to only find the "Agent ID" associated with the last "Time the Agent Touched Case".

[NeoPa]

You don't say why the suggested idea wouldn't work.

Have you tried it like that?

If so what happened?

[liimra]

Approach

Another simple approach is to use the Last function

DLast("[Agent ID]","[TableName]","[Case Number] = " & [NumberFieldOnTHeForm])

One thing you might want to do is to sort the table or query (recommended) according to time so you make sure you always getting the right correspondent.

Regards,
Ali

[NeoPa]

I'm afraid that wouldn't work Ali (except maybe coincidentally sometimes).

[liimra]

Thanks/

Thanks NeoPa, you are right. I though it would work because I once implemented it in payroll database (salaries in advance --> last payment) and I can't recall I got any problems there. Maybe, because users were inputing data in order and so DLast was getting the actual last record. Anyways, for this particular problem we can still overcome it using the DLast by specifying two criteria instead of one (Case Number and DMax of Date); so it becomes

=DLast("[AgentID]","QueryName","CaseNumber =   [CaseNumber]  
And [DateFieldName] = #" & DMax("DateFieldName","QueryName","CaseNumber =" & [CaseNumber]) & "#")

I tried it and it works without any problems and even if many agents touch(edit) the case on the same date, still this expression will get the last one as time value is stored as fraction of 24 hours.

Thanks again & Regards,
Ali

[NeoPa]

It can be made to work Ali (You would need to take more care with Date literals of course - See Literal DateTimes and Their Delimiters (#)), but is there a good reason to go to all the trouble, when the Max is what is really being searched for? Don't let the name confuse you into thinking you're looking for the last item. You're looking for the item with the Max date.

It's almost an accident that it would be referred to as last in normal language. Read normal language as saying this is last {when sorted in date order}, where the phrase within {} is the default and needn't be stated.

[liimra]

True

True. I totally agree, the main thing here is the MAX; Lookup should give the same outcome.

Regards,
Ali