FIRST function returning incorrect records (1 Viewer)

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.

Spooky two questions on First in as many days... Have a quick read here for issues with the First() and Last() functions
https://www.access-programmers.co.uk/forums/showthread.php?t=292013

If you want the first agent AFTER the sort is applied you can just read the AgentID in the first record, no? I don't see the need to use an aggregate function to return that data.

Another thing I don't understand, your Min(ActivityDate) and Min(ActivityTime) could come from different record, right? But if they do, they don't mean much anymore. You may need to find the Min(ActivityDate + ActivityTime). Typically, and for this reason, I would store a date/time value in the same field.

Hope this helps,

MarkK - I can just read the AgentID in the first record, but I want to omit the second record, as the first one is the only one relevant. To my knowledge, that would require an aggregate function, which, I was hoping, was the FIRST function. I did try getting rid of the ActivityDate field, reformatting ActivityTime in the source table so that it includes date/time, and sorting on Min(ActivityTime) only, but this didn't have an effect.

Minty - Thank you for the link. At least, I learned that the sort order is irrelevant when using FIRST and LAST functions. None of the methods listed, however, were particularly useful, since I need to return all records with unique account numbers.

To my knowledge, that would require an aggregate function

Click to expand...

Not necessarily. The min aggregate merely returns the minimum value for the column within the constraints of the criteria and fields grouped on - it does not return the actual record with the minimum value.

However, you can use it to identify the minimum record (or more if there are several records with the same minimum value)

If you only want a single record to be returned, you do not need to use the min aggregate at all, you can just sort by the relevant column and select TOP 1

e.g.

SELECT TOP 1 *
FROM myTable
WHERE somefield=123
ORDER BY somedate

I agree with Mark, it is a lot easier to combine your activity date and activity time into one field. Consider this

Date.............Time
01/01/2017...10:30
02/01/2017...09:45

the minimum time is 09:45, regardless of the day - so min day is 01/01/2017, minimum time 09:45