update from using ms access query

Dear Sirs,

I have the following select query and i need to update the field
EmailLastReceivedDate (showing null in the select) to the values in the
field MaxOfSentOn using access 2000 query

SELECT Max(EmailsDatabase.SentOn) AS MaxOfSentOn, CoEmails.CoCode,
CoBank.EmailLastReceivedDate
FROM (EmailsDatabase INNER JOIN CoEmails ON EmailsDatabase.From =
CoEmails.Email) INNER JOIN CoBank ON CoEmails.CoCode = CoBank.Code
GROUP BY EmailsDatabase.From, CoEmails.CoCode,
CoBank.EmailLastReceivedDate;


MaxOfSentOn CoCode EmailLastReceivedDate
8/30/2005 12:49:51 PM 37
4/5/2005 11:35:34 AM 10
9/16/2005 6:36:53 AM 81

Any help would be much apreciated,
Thank you

*** Sent via Developersdex http://www.developersdex.com ***

Re: update from using ms access query

elie chucrallah wrote:
> Dear Sirs,
>
> I have the following select query and i need to update the field
> EmailLastReceivedDate (showing null in the select) to the values in
> the field MaxOfSentOn using access 2000 query
>
> SELECT Max(EmailsDatabase.SentOn) AS MaxOfSentOn, CoEmails.CoCode,
> CoBank.EmailLastReceivedDate
> FROM (EmailsDatabase INNER JOIN CoEmails ON EmailsDatabase.From =
> CoEmails.Email) INNER JOIN CoBank ON CoEmails.CoCode = CoBank.Code
> GROUP BY EmailsDatabase.From, CoEmails.CoCode,
> CoBank.EmailLastReceivedDate;
>
>
> MaxOfSentOn CoCode EmailLastReceivedDate
> 8/30/2005 12:49:51 PM 37
> 4/5/2005 11:35:34 AM 10
> 9/16/2005 6:36:53 AM 81
>

Open your database in Access and create a saved query using the above sql
statement. Save it with the name "LatestEmailsPerCoBank"

Then create a new query with this sql:

UPDATE CoBank AS b INNER JOIN LatestEmailsPerCoBank AS e
ON b.Code = e.CoCode
SET b.EmailLastReceivedDate = e.MaxOfSentOn

If you only want to update the ones where EmailLastReceivedDate is null then
add this WHERE clause:

WHERE EmailLastReceivedDate is null

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: update from using ms access query

Dear Mr Barrows

I tired your suggestion and i got "Operation must use an updatable
query" and CoCode wouold never be duplicated (
max and group by) so it is not a one to many relationship

am thinking about inserting the result in a predefined table where
cocode is primary key and use it any other solution ?

Thanks

*** Sent via Developersdex http://www.developersdex.com ***

Re: update from using ms access query

elie chucrallah wrote:
> Dear Mr Barrows
>
> I tired your suggestion

Did you try it in Access or from ASP? Hopefully you tested this in Access
first ...

> and i got "Operation must use an updatable
> query"

hmmm

> and CoCode wouold never be duplicated (
> max and group by) so it is not a one to many relationship

Why do you think this is relevant? Something in the error message ... ?

>
> am thinking about inserting the result in a predefined table where
> cocode is primary key and use it any other solution ?
>
I would need some table structures and sample data to allow me to see what
is happening.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.