469,300 Members | 2,220 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,300 developers. It's quick & easy.

Taking corresponding record using max function

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.
Sep 7 '10 #1
8 2444
32,173 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   tT.[Case Number]
  2.        , tT.[Agent ID]
  3.        , tT.[Time the Agent Touched Case]
  4. FROM     [Table] AS tT
  5.          INNER JOIN
  6.     (
  7.     SELECT   [Case Number]
  8.            , Max([Time the Agent Touched Case]) AS MaxTime
  9.     FROM     [Table] AS tTi
  10.     GROUP BY [Case Number]
  11.     ) AS sT
  12.   ON     tT.[Case Number] = sT.[Case Number])
  13.  AND     tT.[Time the Agent Touched Case] = sT.[MaxTime]
Sep 7 '10 #2
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".
Sep 13 '10 #3
32,173 Expert Mod 16PB
You don't say why the suggested idea wouldn't work.

Have you tried it like that?

If so what happened?
Sep 13 '10 #4
119 100+
Another simple approach is to use the Last function
Expand|Select|Wrap|Line Numbers
  1. 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.

Sep 13 '10 #5
32,173 Expert Mod 16PB
I'm afraid that wouldn't work Ali (except maybe coincidentally sometimes).
Sep 14 '10 #6
119 100+
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
Expand|Select|Wrap|Line Numbers
  1. =DLast("[AgentID]","QueryName","CaseNumber =   [CaseNumber]  
  2. 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,
Sep 14 '10 #7
32,173 Expert Mod 16PB
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.
Sep 15 '10 #8
119 100+
True. I totally agree, the main thing here is the MAX; Lookup should give the same outcome.

Sep 15 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

14 posts views Thread by jagadeeshbp | last post: by
18 posts views Thread by Panchal V | last post: by
reply views Thread by Big Daddy | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.