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!

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!
>

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!
>>