Update Table

Posted on 2011-10-05
Last Modified: 2012-06-27
Dear Experts

I need to find the most efficient way of updating records on mass.  What I am trying to achieve is to update records in a table that meet a certain criteria. The table is linked to a sharepoint 2010 list (but this shouldn't make any difference (I hope))

The fields involved are as follows:

Table = “Newsletter”

Field to update =  “Uploaded” (a Yes/No field)

Criteria Field = “Story Type” (Text) - where <> “The Ending”

So the statement would be to mark “Uploaded” as Yes except if Story Type = “The Ending”

The table will grow on average by 2,000 records a year, but each time this code is run there is likely to only be 30-40 records that need updating.

Can anybody help?
Question by:correlate
    LVL 77

    Accepted Solution

    The query would just be..

    Update Newsletter set Uploaded = True where [story type] <>"The ending" and Uploaded =false

    'Efficiency'  would be a case of indexing the [Story Type] field (but NOT the Uploaded field)

    I am assuming that Story type cannot change from 'The ending' to something else and that Uploaded must be either True or False.
    LVL 10

    Expert Comment

    You can use below code

    Go to MS Access Design View - > SQL View
    Paste below code

    UPDATE Newsletter SET Uploaded=“Y” where [Story Type] <>"The Ending"

    Open in new window

    LVL 10

    Expert Comment

    code syntax
    UPDATE [Tablename] SET Fieldname=“Y” where [Fieldname] criteria
    then should be
    UPDATE Newsletter SET Uploaded=“Y” where [Story Type] <>"The Ending"

    Open in new window


    Author Closing Comment

    brilliant - thank you for that

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now