Find latest record for each customer

I am using A2K. I have a table of customer names tblCustomer with a one to many relationship to a table of tasks tblTask using the primary key tblCustomer.CustKey. tblTask has fields: CustKey, TaskDate and TaskComment. I would like to see a list of only the most recent task associated with each customer in tblCustomer.

Can anyone suggest a query that would do this and also be the source for a form?

One potential difficulty is where a customer has no task yet or where 2 or more tasks are carried out on the same day (quite a rarity). Where there is no task then the customer should still appear in the list. Where there are 2 or more tasks that day then the latest one is required. Any help much appreciated.

[RV]

>Where there are 2 or more tasks that day then the latest one is required<

How do you know what's "the latest one" ?

RV

At present there is no easy way to tell - it would be possible to add a further field that incremented with each new record or as an alternative use TaskDate field to store date and time with each new record. The most recent time could then be used to filter to the correct record.

To clarify tblTask has an autonumber field TaskId.
I have tried to group the records on CustKey and then find the most recent date from each group. The total query I have is - qryRecent:

SELECT tblTask.CustKey, Max(tblTask.TaskDate) AS MaxOfTaskDate
FROM tblTask
GROUP BY tblTask.CustKey
ORDER BY tbl.CustKey;

The list of dates this obtains looks right - returns 3589 records. However I also need to see TaskComment for each record with the most recent date. I have tried - qryFindComment

SELECT tblTask.CustKey, Max(tblTask.Date) AS MaxOfTaskDate, tblTask.Comment
FROM tblTask
GROUP BY tblTask.CustKey
ORDER BY tblTask.CustKey;

which gives an error because tblTask.Comment must be grouped:

GROUP BY tblTask.CustKey, tblTask.Comment,

and the result has 4118 records. A similar result occurs if I substitute tblTask.TaskId for tblTask.Comment. I had hoped to use tblTask.TaskId to locate individual record. I assume that the aggregate effects the result - I guess I have missed some fundamental issue when using a total query. I tried to refer to the result of qryRecent joining tblTask.CustKey and tblTask.Date - the hope is that qryRecent limits the records and the next query uses the result to pull the relevant comment from tblTask - qryRecentComment

SELECT tblTask.CustKey, tblTask.TaskDate, tblTask.Comment
FROM qryRecent INNER JOIN tblTask ON (qryRecent.CustKey = tblTask.CustKey) AND (qryRecent.MaxOfTaskDate = tblTask.TaskDate);

The result is 3593 records so I expect there is an error occuring. Is there a solution to the original problem? The only answer I can find (despite the overhead) is to flag the most recent task for each customer and update the flag whenever there is a new task. Then filter using the flag.

[Pat Hartman]

This is a very common problem and you have to understand how aggregation works to see the solution. Every field in the select clause except the one being referenced by the aggregate function needs to appear in the group by clause. Therefore if you include, CustKey and Comment, the recordset is sorted ascending by CustKey and Comment and descing by TaskDate because you want the maximum value of taskDate. Then the recordset is processed. The two columns in the group by clause are compared to the next record in the recordset, and all duplicate values are discarded. So consider the following recordset:

key1, "comment1", 5/5/2
Key1, "comment2", 5/8/2
key1, "comment3", 4/1/2

Since none of the comments are identical, none of the rows will be aggregated and you'll end up with three records in the recordset. However, if you remove the comment field, you will end up with just one record:

Key1, 5/8/2

So, the solution is either two queries, one to find the max date and the second to retrieve the related information. Or, if you're a little more sophisticated in your use of SQL, you can use a sub query.

Query1:
Select CustKey, Max(TaskDate) as MaxTaskDate
From tblTask
Group By CustKey;

Query2:
Select q.CustKey, q.MaxTaskDate, t.Comment
From query1 as q Inner Join tblTask as t on q.CustKey = t.CustKey AND q.MaxTaskDate = t.TaskDate;

If you need an updateable recordset, you can use the TOP values predicate. However, you do need to be aware, that this may actually return more than a single record for each customer. The two query solution will also if there are multiple tasks for the same date.

Select Top 1 CustKey, TaskDate, Comment
From tblTask
Order by CustKey, TaskDate Desc;