Adding new field to a query is multiplying returned results instead of just adding a field to existi

[halabis]

I have the following query:

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:

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!

[ranman256]

yes, adding a field to a MAX calc will give more record.
You are asking for Max of the given fields.
If you want the max of 1 field and show extra data ,use 2 queries

Q1 to get the max, select Max(level), levelID from table

then Q2 to pull the rest of the data based on Q1.
Q2: select * from Q1,table where Q1.id = table.ID

[halabis]

When I add ID to the Q1 query it causes the same issue as adding [Corrective Action Level].CorrectiveActionLevel It expands it from the 3 records I want to 12 that I don't

[halabis]

Ok, I solved it with

SELECT TMSummaryQ3.AgentName, TMSummaryQ3.Maxdate, [Corrective Action Level].CorrectiveActionLevel


FROM TMSummaryQ3 INNER JOIN [Corrective Action Level] ON (TMSummaryQ3.Maxdate = [Corrective Action Level].UpdateDate) AND (TMSummaryQ3.AgentName = [Corrective Action Level].AgentName)


GROUP BY TMSummaryQ3.AgentName, TMSummaryQ3.Maxdate, [Corrective Action Level].CorrectiveActionLevel;