Re: update query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: John Vinson (jvinson_at_STOP_SPAM.WysardOfInfo.com)
Date: 08/02/04 

Date: Mon, 02 Aug 2004 13:22:09 -0600

On Mon, 2 Aug 2004 08:26:16 +0200, "Ivan Debono"
<ivanmdeb@hotmail.com> wrote:

>I have the following query:
>
>UPDATE plans SET plan_date = (
>SELECT Min(calendar.date)
>FROM calendar
>WHERE (((calendar.completed_yn)=False) AND ((calendar.deleted_yn)=False) AND
>((calendar.parent_table)='plans') AND ((calendar.parent_id)=64))
>)
>WHERE plans.id = 64
>
>What is does is that it updates the plan_date in table plans with the
>smallest date in table calendar which is not deleted and not completed.
>
>But I get an error: Operation must use an updatable query. (Error 3073)
>
>Anyone knows why??

Because any query that contains a Min, Max, or any other Totals
operation is non-updateable... even if (as in this case) it is not a
logical constraint! It's just the way Access was implemented.

You can use the DMin() VBA function in place of the subquery:

UPDATE plans
SET plan_date = DMin("[Date]", "[calendar]", "[Completed_yn] = False
AND deleted_yn = False AND parent_table = 'Plans' and parent_id = " &
[Plans.ID])
WHERE plans.ID = <criteria>

Note that I'm suggesting using a "correlated" pseudo-subquery,
referencing the outer query's ID rather than explicitly putting 64
into both the main query criterion and the DMin().

                  John W. Vinson[MVP]
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Relevant Pages

  • Re: SBS2008 Emails
    ... and copy the pst file and then import it back as you have suggested. ... Ctrl-C in old calendar and then do a paste in new mailbox ... And many updates each are listed as requiring a reboot. ... download all those updates and then a single reboot in the end will ...
    (microsoft.public.windows.server.sbs)
  • Re: changing timeline to account for different types of accounting months?
    ... Our company uses a 4-4-5 financial calendar. ... months) to develop and track the program plans. ... Since I was the Project Manager for one of our programs, ... The macro was so successful that ...
    (microsoft.public.project)
  • Re: Outlook Calendar Sharing Publishing/Syncing Work Around
    ... Outlook 2007 with all the latest updates. ... If I try to update the calendar again or reboot the computer, I cannot publish again without killing off the calendar at the Microsoft site, same as always. ... No subscriber who doesn't know the username and password I use to upload the calendar successfully with Outlook 2007 the once ONLY can view it at all. ... I can go the the Microsoft calendar online using IE, delete it completely and delete it from my computer as a published calendar and everyone else can delete it from their computer as an Internet calendar and then I upload using Outlook 2007 again and for the first time only - and no other - it works perfectly and then it never works again IF I try to upload changes made or IF I reboot the computer. ...
    (microsoft.public.outlook.calendaring)
  • RE: Create a task in MS Project to reflect in Outlook
    ... I have this vba code that you will need to adapt. ... ' Opens Maurices Outlook Calendar and updates or creates ... ' any appointments with Location = ProjectName ...
    (microsoft.public.project)
  • OL 2003 Calendar events missing categories/categorization
    ... I have a user that is using Outlook 2003 with MS Exchange 2003 in a corporate ... The updates that were installed were as follows: ... Pursuant to that, the user can still see all of the events on his calendar, ... program installed on his work machine called SyncMyCal. ...
    (microsoft.public.outlook.general)