I have the following query:
Code:
SELECT Agent.AgentName, Max([Corrective Action Level].UpdateDate) as Maxdate, Agent.TeamManager
FROM Agent INNER JOIN [Corrective Action Level] ON Agent.[AgentName] = [Corrective Action Level].[AgentName]
GROUP BY Agent.AgentName, Agent.TeamManager, Agent.TerminationDate
HAVING (((Agent.TeamManager)=[Forms]![Attendance Forms].[NavigationSubform].[form]![TMSelect]) AND ((Agent.TerminationDate) Is Null));
It does what it is supposed to do giving me a list of all Agents belonging to a certain manager, as well as the date of their most recent infraction. The returned value for my test manager is 3 agents and their most recent infraction dates each.
What I want to do is to include the infraction level of those agents that occurred on those dates. I figured that adding "[Corrective Action Level].CorrectiveActionLevel" to the Select and Group by lines would do this, but it does not.
It looks like this:
Code:
SELECT Agent.AgentName, Max([Corrective Action Level].UpdateDate) as Maxdate, Agent.TeamManager, [Corrective Action Level].CorrectiveActionLevel
FROM Agent INNER JOIN [Corrective Action Level] ON Agent.[AgentName] = [Corrective Action Level].[AgentName]
GROUP BY Agent.AgentName, Agent.TeamManager, Agent.TerminationDate, [Corrective Action Level].CorrectiveActionLevel
HAVING (((Agent.TeamManager)=[Forms]![Attendance Forms].[NavigationSubform].[form]![TMSelect]) AND ((Agent.TerminationDate) Is Null));
Unfortunately it does not do what I expect. Now the query returns every entry in the Corrective Action Level table that matches the Team manager.
So instead of 3 lines, one for each agent (with the corresponding most recent date, and the associated corrective action level), I am getting 4 lines for each agent, each with a different date and corrective action level. It seems to no longer be filtering by Max of the date field.
What am i doing wrong, and how can I correct it?
Thanks!