By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,001 Members | 451 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,001 IT Pros & Developers. It's quick & easy.

Taking corresponding record using max function

P: 27
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
Share this Question
Share on Google+
8 Replies

NeoPa
Expert Mod 15k+
P: 31,770
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

P: 27
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

NeoPa
Expert Mod 15k+
P: 31,770
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

100+
P: 119
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.

Regards,
Ali
Sep 13 '10 #5

NeoPa
Expert Mod 15k+
P: 31,770
I'm afraid that wouldn't work Ali (except maybe coincidentally sometimes).
Sep 14 '10 #6

100+
P: 119
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,
Ali
Sep 14 '10 #7

NeoPa
Expert Mod 15k+
P: 31,770
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

100+
P: 119
True. I totally agree, the main thing here is the MAX; Lookup should give the same outcome.

Regards,
Ali
Sep 15 '10 #9

Post your reply

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