Using Max function need to get highest Tier for two groupings or fields

[Silver_A]

Hello,

I have a problem I have been working on for several days without sucess. A current query shows the "highest Tier" an agent belongs to based on criteria. An agent can belong to more than one Tier but only the highest Tier should be on the report. For example John is in Tier I for Tax call type and in Tier III for Water Call Type. He should be in Tier III only in the report. This is working fine, however I am trying to add the Call type to the querry to show the call type for highest Tier, but all Tiers show in the query breaking the original results:

Here is an example for the original report:

MaxTier Agent
Tier I Angela
Tier II Monique
Tier III John
Tier III Tiffany



With the New field Call Type I should see:

MaxTier Agent Call Type
Tier II Angela TAX
Tier III Monique TAX
Tier III John TAX
Tier III Tiffany Recycling

But the results show something like this:

MaxTier Agent Call Type
Tier I Angela Water
Tier II Angela TAX
Tier III Monique TAX
Tier II Monique Water
Tier I Monique Recycling
Tier III John TAX
Tier III Tiffany Recycling

We need to see only the Call type for the highest Tier. Can some please help me with this? I am using Access 2010

Here is an example of the original query:
SELECT Max(Category.tier) AS InnerMaxTier, [CLastName] & ", " & [CFirstName] AS CSR
FROM g_CSR CSR INNER JOIN (Category INNER JOIN (CallMonitor INNER JOIN CallMonitor_Category ON CallMonitor.CallID = CallMonitor_Category.CallID) ON Category.CategoryID = CallMonitor_Category.CategoryID) ON CSR.CSRID = CallMonitor.CSRID
WHERE (((CallMonitor.MonitorDate) Between [TempVars]![g_dtRepBeg].[Value] And [TempVars]![g_dtRepEnd].[Value]))
AND ((CallMonitor.FYI_Only)=No) AND ((CallMonitor.CoachingOnly)=No) AND ((CallMonitor_Category.FYI)=No) AND
((CallMonitor.MonitorNotPossible)=No)
GROUP BY [CLastName] & ", " & [CFirstName]

[Dal Jeanis]

One item is missing - which table is Calltype in?

[Dal Jeanis]

This is really unelegant, brute force version, but barring typos on my part, it should get you what you want. If I were coding it myself, I would make TierCat a separate query, make Maxcat an aliased aggregate call to Tiercat, and then the code would look more elegant and be more maintainable. Notice that I assumed the CSR could happen to have more than one "best" tier, and just used the first one that Access returned.

      
SELECT TierCat.Tier, [CLastName] & ", " & [CFirstName], First(TierCat.CategoryID)
FROM g_CSR INNER JOIN 
      (
      SELECT CM1.CSRID AS CSRID, CAT1.tier AS Tier, CAT1.CategoryID AS CategoryID
      FROM  Category AS CAT1 INNER JOIN 
            (CallMonitor AS CM1 INNER JOIN CallMonitor_Category AS CMC1
             ON CM1.CallID = CMC1.CallID) 
         ON CAT1.CategoryID = CMC1.CategoryID) 
      WHERE ((CM1.MonitorDate) Between [TempVars]![g_dtRepBeg].[Value] And [TempVars]![g_dtRepEnd].[Value])
      AND ((CM1.FYI_Only)=No) 
      AND ((CM1.CoachingOnly)=No) 
      AND ((CM1.FYI)=No) 
      AND ((CM1.MonitorNotPossible)=No)
      ) AS TierCat
      INNER JOIN
         (
         SELECT CM2.CSRID AS CSRID, MAX(CAT.tier) As InnerTierMax
         FROM  Category AS CAT2 INNER JOIN 
               (CallMonitor AS CM2 INNER JOIN CallMonitor_Category AS CMC2
                ON CM2.CallID = CMC2.CallID) 
            ON CAT2.CategoryID = CMC2.CategoryID) 
         WHERE ((CM2.MonitorDate) Between [TempVars]![g_dtRepBeg].[Value] And [TempVars]![g_dtRepEnd].[Value])
         AND ((CM2.FYI_Only)=No) 
         AND ((CM2.CoachingOnly)=No) 
         AND ((CM2.FYI)=No) 
         AND ((CM2.MonitorNotPossible)=No)
         ) AS MaxCat
      ON TierCat.CSRID = MaxCat.CSRID
      AND TierCat.Tier = MaxCat.InnerTierMax
   ON g_CSR.CSRID = TierCat.CSRID
GROUP BY  TierCat.Tier, [CLastName] & ", " & [CFirstName]

[Silver_A]

Use First(), of course! Thanks for your quick response Dal Jeanis. I believe I am going now in the right direction, however I am still confused . Below is the whole SQL. At first I just sent part of the SQL that got the tiers, but the query below also gets the Max Tier (Exceptional) and lower tier (Tier III). I added the Call Type (from Call Type Table, field called Description) with the First() instead of Group by in the query and is still getting too many records, a few less though. Could you please tell me what I am doing wrong?
SELECT Max([%$##@_Alias].innermaxtier) AS MaxTier, First([%$##@_Alias].Description) AS Description, [%$##@_Alias].CSR
FROM (SELECT Max(Category.tier) AS InnerMaxTier, First(CallType.Description) AS Description, [CLastName] & ", " & [CFirstName] AS CSR
FROM Category INNER JOIN (CallType RIGHT JOIN ((g_CSR AS CSR INNER JOIN CallMonitor ON CSR.CSRID = CallMonitor.CSRID) INNER JOIN CallMonitor_Category ON CallMonitor.CallID = CallMonitor_Category.CallID) ON CallType.CallTypeID = CallMonitor.CallTypeID) ON Category.CategoryID = CallMonitor_Category.CategoryID
WHERE (((CallMonitor.MonitorDate) Between [TempVars]![g_dtRepBeg].[Value] And [TempVars]![g_dtRepEnd].[Value]) AND ((CallMonitor.FYI_Only)=No) AND ((CallMonitor.CoachingOnly)=No) AND ((CallMonitor_Category.FYI)=No) AND ((CallMonitor.MonitorNotPossible)=No))
GROUP BY [CLastName] & ", " & [CFirstName]
ORDER BY Max(Category.tier)

UNION
SELECT DISTINCT "Tier Exceptional" AS InnerMaxTier, First(CallType.Description) AS Description, [CLastName] & ", " & [CFirstName] AS CSR
FROM CallType RIGHT JOIN (g_CSR AS CSR INNER JOIN CallMonitor ON CSR.CSRID = CallMonitor.CSRID) ON CallType.CallTypeID = CallMonitor.CallTypeID
WHERE (((CallMonitor.MonitorDate) Between [TempVars]![g_dtRepBeg].[Value] And [TempVars]![g_dtRepEnd].[Value] And (CallMonitor.MonitorDate) Is Not Null) AND ((CallMonitor.FYI_Only)=No) AND ((CallMonitor.CoachingOnly)=No) AND ((CallMonitor.ReturnDate) Is Null) AND ((CallMonitor.MonitorNotPossible)=No))
GROUP BY "Tier Exceptional", [CLastName] & ", " & [CFirstName]

UNION SELECT DISTINCT "Tier III" AS InnerMaxTier, First(CallType.Description) AS Description, [CLastName] & ", " & [CFirstName] AS CSR
FROM CallType RIGHT JOIN (g_CSR AS CSR INNER JOIN CallMonitor ON CSR.CSRID = CallMonitor.CSRID) ON CallType.CallTypeID = CallMonitor.CallTypeID
WHERE (((CallMonitor.MonitorDate) Between [TempVars]![g_dtRepBeg].[Value] And [TempVars]![g_dtRepEnd].[Value]) AND ((CallMonitor.ZeroScore)=Yes))
GROUP BY "Tier III", [CLastName] & ", " & [CFirstName]) AS [%$##@_Alias]
GROUP BY [%$##@_Alias].CSR, [%$##@_Alias].Description
ORDER BY Max([%$##@_Alias].innermaxtier), [%$##@_Alias].Description, [%$##@_Alias].CSR;

[Dal Jeanis]

Well, the first thing I'd do is pull apart the unions and make sure each part is working right before merging them.

The second thing I'd do is to indent the SQL code so I can see what it's doing.

The third thing I'd do is to put the joins in an order where I knew exactly what they are doing. Here's the joins from the first of the three queries:

FROM Category INNER JOIN 
   (
   CallType RIGHT JOIN 
      ( 
         (
         g_CSR AS CSR INNER JOIN CallMonitor 
         ON CSR.CSRID = CallMonitor.CSRID
         ) 
      INNER JOIN CallMonitor_Category 
      ON CallMonitor.CallID = CallMonitor_Category.CallID
      )    ON CallType.CallTypeID = CallMonitor.CallTypeID
   ) 
ON Category.CategoryID = CallMonitor_Category.CategoryID

This is a query about people, right? So why would you do a RIGHT JOIN there on CallType? If there are no callmonitor/gcsr records for a given Calltype, why do you want a null record returned?

[Dal Jeanis]

2)The other thing I don't understand about the whole concept, is that you are using alpha "Tier" names, and selecting Max? is "Tier Exceptional" supposed to be less than "Tier I"?

3) There seem to be a lot of extra open parenthesis.

4) In a UNION query, the field names in the first table are the ones accessible to the query as a whole. The swear-word alias will only be available within the last of the three queries, not up at the top. And, even if it would have worked, there would have to have been enough close parenthesis that the group by for the swear-aliases was outside ALL the parens.