Posted by Mintyman on 03/21/07 12:20

Hi Roy,

Many thanks. I've managed to use your example to get exactly what I need.
Cheers!


"Roy Harvey" <roy_harvey@snet.net> wrote in message
news:7240031r1ken2gmb5a3qe8gfost4nvma25@4ax.com...
> 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]