Bulk Updates (2 Viewers)

[GaleT]

Hi,

I'm not sure where to ask this question... so I'll try here first. I need to change the value of one field for 50 specific records in one table. The change is the same for each record... change status to "Expired". I was going to just manually do it through the main form but I would like to know what my options are for bulk (record set) updates in Access. I was not successful searching the forums... but I did try before posting this question.

Gale

 

[pbaldy]

The most efficient option is probably an update query with a criteria that identifies the 50 specific records. If you can create a select query that pulls them, you can change it to an update query.

 

[GaleT]

Thank you pbaldy. Update query... that sounds exactly like what I need. I will move to the appropriate forum now to ask more questions.

Gale

 

[pbaldy]

We're not overly picky about the forum a question is in, so it's up to you. Can the 50 be identified by a criteria?

 

[MajP]

Very helpful resource.

SQL UPDATE Statement

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[GaleT]

"We're not overly picky about the forum a question is in, "
Great No, the 50 cannot be identified by common criteria, I have to list each one. Which brings me to my next question... is there an equivalent entry in the Search Query for "IN"... such as this example from one of my SQL scripts OPPORTUNITY_ID in ('SER1265',’SER6542’,'SEE2265').

 

[GaleT]

MajP... sorry I didn't understand your comment at first... it's getting late and my head is spinning. SQL UPDATE ... Thank you I have never used it but I know what it is... not sure how to apply it in Access? My SQL experience has been in extracting data for analysis and reporting... never been allowed to change the data.

Gale

 

[pbaldy]

"We're not overly picky about the forum a question is in, "
Great No, the 50 cannot be identified by common criteria, I have to list each one. Which brings me to my next question... is there an equivalent entry in the Search Query for "IN"... such as this example from one of my SQL scripts OPPORTUNITY_ID in ('SER1265',’SER6542’,'SEE2265').

Well, the same IN clause will work in an update query. If you have an existing query that returns the 50 records, you can join to that query in the update query. Perhaps you can attach a sample db here to play with/demonstrate?

 

[MajP]

MajP... sorry I didn't understand your comment at first... it's getting late and my head is spinning. SQL UPDATE ... Thank you I have never used it but I know what it is... not sure how to apply it in Access? My SQL experience has been in extracting data for analysis and reporting... never been allowed to change the data.

The query builder is a user interface that helps you build a SQL string. The real query is the code that it makes. You can type the query directly into SQL view if you know how.

Design view to help build a delete query

In SQL view, the built query.

 

[Bullschmidt]

You originally wrote in part:

I need to change the value of one field for 50 specific records in one table. The change is the same for each record... change status to "Expired". I was going to just manually do it through the main form but I would like to know what my options are for bulk (record set) updates in Access.

And later after Update Query recommendations you wrote:

No, the 50 cannot be identified by common criteria, I have to list each one.

So instead of using a form in the usual Form View I would suggest using the form in Datasheet View (showing multiple records at one time) or a query (showing multiple records at one time)...

 

[GaleT]

Thank you Pbaldy & MajP... the light is turning on

To Pbaldy:
"Well, the same IN clause will work in an update query. If you have an existing query that returns the 50 records, you can join to that query in the update query. "
I may be missing your point but I think the key statement is "If you have an existing query that returns the 50 records"... which I don't have since I can't use Criteria to gather the record set other than something that allows me to enter a list of records I want to edit. So I am leaning toward the SQL approach MajP is describing since I know how to describe what i want in SQL... such as OPPORTUNITY_ID in ('NAME1',’NAME2’,'NAME3').

To MajP:
"The query builder is a user interface that helps you build a SQL string. "
That's a simple statement but it really turned the light on I was beginning to suspect that is the case.
My SQL experience is with Oracle using TOAD to write SQL queries. So I am familiar with writing the entire query... but I don't know how to use SQL in Access.
"You can type the query directly into SQL view if you know how."
Does this mean I can use SQL in an update query? Or do I still have to use a Search query and then switch to an update query to make the changes?
I will experiment, I may be able to answer that question if I look at the two types of query more closely.

Thank you both very much... Access is looking more and more interesting every day

Gale

 

[GaleT]

You originally wrote in part:


And later after Update Query recommendations you wrote:


So instead of using a form in the usual Form View I would suggest using the form in Datasheet View (showing multiple records at one time) or a query (showing multiple records at one time)...

Thank you Bullschmidt,

I want to use a "query (showing multiple records at one time)". If I can learn how to use SQL in an Access query I will have a very good method for maintaining bulk record updates... one I am already somewhat familiar with.

Gale

 

[MajP]

Is this a reoccurring process where you have to manually determine and update? If that is the case and there is no search rule that you can apply, you may consider building a user interface with a multiselect listbox. You can then select all records to update. Hit the "update" button and then run a loop that calls a single update query for each selected item.

 

[The_Doc_Man]

If you have the luxury of doing this, add a column to the table, one that you can set to 0 or 1 (or if you like Yes/No fields, TRUE or FALSE). Then do these things in order:

1. UPDATE mytable SET flagfield = FALSE ;

2. Manually set the indicators for the 50 records.

3. UPDATE mytable SET particular-field = newvalue WHERE flagfield = TRUE ;

4. Figure out how to be able to identify the fields more easily next time you have the potential of a bulk operation.

EDIT: I see that MajP and I have the same base idea - looking to the future - even if we might have a different way of making the selection.

 

[GaleT]

Is this a reoccurring process where you have to manually determine and update? If that is the case and there is no search rule that you can apply, you may consider building a user interface with a multiselect listbox. You can then select all records to update. Hit the "update" button and then run a loop that calls a single update query for each selected item.

This is more of an Admin function. I think I need to be more specific... this Access database is replacing a Lotus Notes database I developed about ten years ago. I am still using the Lotus Notes database and keeping the Access database in synch manually. While doing some data "cleaning" in the Notes database I decided to add a new record status = "Expired". I analyzed which records have expired in the Lotus Note copy of the database and updated up them. Now I want to update the Access version of the database. So I have a list of unique Identifiers that are quite random in nature and I want to collect the specific records in the Access database to add the new status . I only have 50 records to update and I could have done this manually but I really need to know how to do this type of admin function in Access.

Gale

 

[GaleT]

If you have the luxury of doing this, add a column to the table, one that you can set to 0 or 1 (or if you like Yes/No fields, TRUE or FALSE). Then do these things in order:

1. UPDATE mytable SET flagfield = FALSE ;

2. Manually set the indicators for the 50 records.

3. UPDATE mytable SET particular-field = newvalue WHERE flagfield = TRUE ;

4. Figure out how to be able to identify the fields more easily next time you have the potential of a bulk operation.

EDIT: I see that MajP and I have the same base idea - looking to the future - even if we might have a different way of making the selection.

 

[GaleT]

Thank you, this totally makes sense but I am more interested in Admin type updates... something I can modify for other types of updates. Like running an SQL script or modifying the criteria in an update Query to suit the current needs.

Gale

 

[pbaldy]

Regarding "is there an equivalent entry in the Search Query for "IN"... such as this example from one of my SQL scripts OPPORTUNITY_ID in ('SER1265',’SER6542’,'SEE2265')", my point was that this same type of criteria will work with an update query.

You could also use a multiselect listbox to select records and apply a change to each.

 

[GaleT]

The query builder is a user interface that helps you build a SQL string. The real query is the code that it makes. You can type the query directly into SQL view if you know how.

Design view to help build a delete query
View attachment 80345

In SQL view, the built query.

View attachment 80346

Here is the SQL I entered in a Query shown with a reduced number of ProjNum's. But I received an error when I ran it saying the table could not be found. I hope I haven't made a silly mistake naming the table "Documents Table" with the two words separated by a space? Or maybe I have to do this in the Database back end instead of the front end?

Update Documents Table
SET Status = 'Expired'
Where ProjNum in ('AZ1432141','AZ19S0127','AZ19S0168','AZ19S0701','CO1232058','CO1232305')

If the explanation is complex maybe I should ask the question again in the appropriate forum?

Gale

 

[pbaldy]

You would need to bracket the name due to the inadvisable space:

Update [Documents Table]
SET Status = 'Expired'
Where ProjNum in ('AZ1432141','AZ19S0127','AZ19S0168','AZ19S0701','CO1232058','CO1232305')