I have a table that my agents use to track activity. In a query, I am trying to determine how many accounts have had some work on them, when they were first worked, and by whom. However, there might be multiple activity records for each account entered by different agents.
To do this, I have a query that pulls Min([ActivityDate]), Min([ActivityTime]), [AccountNumber], and First([AgentID]). It is sorted ascending by [ActivityDate] and [ActivityTime]. In theory, this should return the earliest date and time, and, if multiple agents entered activity, it would return the first one in the list, since it is sorted by date and time.
It is not doing this consistently. It does only return one record per account with the earliest date and time, but which [AgentID] it chooses is inconsistent.
Here is my SQL code:
SELECT Min(qryActivityTableNoFilters.ActivityDate) AS ActivityDate, Min(qryActivityTableNoFilters.ActivityTime) AS ActivityTime, Min(qryActivityTableNoFilters.TellerID) AS MinOfTellerID, qryActivityTableNoFilters.AccountNumber, qryActivityTableNoFilters.tblActivity.InitiativeID, qryActivityTableNoFilters.Department
FROM tblDisposition INNER JOIN qryActivityTableNoFilters ON tblDisposition.Disposition = qryActivityTableNoFilters.Disposition
WHERE (((tblDisposition.DispoType)="Contact"))
GROUP BY qryActivityTableNoFilters.AccountNumber, qryActivityTableNoFilters.tblActivity.InitiativeID, qryActivityTableNoFilters.Department
ORDER BY Min(qryActivityTableNoFilters.ActivityDate), Min(qryActivityTableNoFilters.ActivityTime);
Any help on this would be greatly appreciated.
To do this, I have a query that pulls Min([ActivityDate]), Min([ActivityTime]), [AccountNumber], and First([AgentID]). It is sorted ascending by [ActivityDate] and [ActivityTime]. In theory, this should return the earliest date and time, and, if multiple agents entered activity, it would return the first one in the list, since it is sorted by date and time.
It is not doing this consistently. It does only return one record per account with the earliest date and time, but which [AgentID] it chooses is inconsistent.
Here is my SQL code:
SELECT Min(qryActivityTableNoFilters.ActivityDate) AS ActivityDate, Min(qryActivityTableNoFilters.ActivityTime) AS ActivityTime, Min(qryActivityTableNoFilters.TellerID) AS MinOfTellerID, qryActivityTableNoFilters.AccountNumber, qryActivityTableNoFilters.tblActivity.InitiativeID, qryActivityTableNoFilters.Department
FROM tblDisposition INNER JOIN qryActivityTableNoFilters ON tblDisposition.Disposition = qryActivityTableNoFilters.Disposition
WHERE (((tblDisposition.DispoType)="Contact"))
GROUP BY qryActivityTableNoFilters.AccountNumber, qryActivityTableNoFilters.tblActivity.InitiativeID, qryActivityTableNoFilters.Department
ORDER BY Min(qryActivityTableNoFilters.ActivityDate), Min(qryActivityTableNoFilters.ActivityTime);
Any help on this would be greatly appreciated.