Posted by Roy Harvey on 03/20/07 16:53

This sounds like something that can be handled by a view, rather than
creating a table that has to be maintained. Either way the general
approach is something like that below. Note that it is all based on
assumptions, but hopefully it will be enough to give you the idea.

SELECT *
FROM Contact as C
JOIN Activity as A
ON C.ID = A.main_contact
WHERE A.ActivityDate =
(SELECT MAX(X.ActivityDate) FROM Activity as X
WHERE A.main_contact = X.mainContact)

Roy Harvey
Beacon Falls, CT

On Tue, 20 Mar 2007 15:02:06 -0000, "Mintyman" <mintyman@ntlworld.com>
wrote:

>Hi,
>
>I have a need to create a table detailing the ID of all contacts and the
>last time they were contacted. This information is stored in 2 tables,
>'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'
>in the 'activity' table).
>
>I guess I need some sort if iteration to go through each contact and find
>find the last activity that took place against each of them (there many be
>more than 1 activity against each contact) and then place the output values
>into the new table.
>
>Can anyone show me how to go about this?
>
>Thanks!
>

[Back to original message]