Choose a field and change it (1 Viewer)

A.M

New member
Local time
Today, 08:16
Joined
Jun 30, 2020
Messages
6
Hi there, hope you can help. I'm looking to write a query that when you choose a field from a database, if that field is e.g. 'Monday' you can change all the 'Monday's to a 'Tuesday'.

Also, if a field is either a 'Mon' or 'Tue' or 'Wed', can these all be changed to a 'December'?

(I am not dealing with days/months)

Thanks so much
AM
 

Isaac

Lifelong Learner
Local time
Today, 01:16
Joined
Mar 14, 2017
Messages
8,738
You could try using Parameter values in your query. For example, you could create a query that selects all records where the column value is "Monday", and then in the Update To panel of an Update query, type [Enter Value to Change To], which will pop up for the user to type something in. Just one way of doing it.
 

plog

Banishment Pending
Local time
Today, 03:16
Joined
May 11, 2011
Messages
11,613
I'm looking to write a query that when you choose a field from a database, if that field is e.g. 'Monday' you can change all the 'Monday's to a 'Tuesday'.

Sorry to be Mr. Pedantic, but the way you phrased that seems weird in a few ways. Fields have names (similar to column headings in Excel), a record (similar to a row in Excel) has values for that field. So, you would have a field called [DayName] and then the values of records for that field could be "Sunday", "Monday", "Tuesday", etc. Lastly, it's a sign of an improper structure if you had multiple fields in the same table that allowed the same set of values (e.g. [Day1Name], [Day2Name], [Day3Name], etc.)

Could you be less generic? What are your field names? Do you have a field called [Monday] or do you have values in a field that are "Monday?

With all that said, the way to UPDATE the values in a field for multiple records is with an UPDATE query (https://www.w3schools.com/sql/sql_update.asp). If you could give us more specific information we might be able to assist better.
 

A.M

New member
Local time
Today, 08:16
Joined
Jun 30, 2020
Messages
6
Sorry for not being more specific.
1. I have a column called Event_Category and there are several Event_Category names, i.e. Exam, Pre Entry, Interviews/Assessments and when these have been already been selected and they need to be changed to the name 'Notional'.

2. All the Event_Category 'Virtual Learning' which has already been selected into the database needs to be changed to the name 'Teaching'

thank you for you help :)
 

Isaac

Lifelong Learner
Local time
Today, 01:16
Joined
Mar 14, 2017
Messages
8,738
Sounds like you might just write the Update query mentioned. Your Where clause (criteria) in the query design pane would be the "Virtual Learning" (on the Event_Category column), and the Update To pane would be "Teaching". As an example. Test it out on a database copy and see how it works.
 

A.M

New member
Local time
Today, 08:16
Joined
Jun 30, 2020
Messages
6
Sounds like you might just write the Update query mentioned. Your Where clause (criteria) in the query design pane would be the "Virtual Learning" (on the Event_Category column), and the Update To pane would be "Teaching". As an example. Test it out on a database copy and see how it works.
Thank you very much, I will give that a go!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:16
Joined
Feb 19, 2002
Messages
42,981
This doesn't sound right to me. What you are saying is I want to search a table and select all rows with a value "Pre Entry" in Event_Category and change them to "Notional". Why???

If your problem is that you have not in the past used a combo box to control the contents of a field and so have a hot mess of typos on your hands, that's a different problem and I would create a specific cleanup procedure to handle this rather than making a generic procedure where a user can pick any column from any table and specify a value to be changed and your code creates an update query to change all the values. I'm hyperventilating o_O just thinking about allowing any user this type of access to any shared database.
 

A.M

New member
Local time
Today, 08:16
Joined
Jun 30, 2020
Messages
6
This doesn't sound right to me. What you are saying is I want to search a table and select all rows with a value "Pre Entry" in Event_Category and change them to "Notional". Why???

If your problem is that you have not in the past used a combo box to control the contents of a field and so have a hot mess of typos on your hands, that's a different problem and I would create a specific cleanup procedure to handle this rather than making a generic procedure where a user can pick any column from any table and specify a value to be changed and your code creates an update query to change all the values. I'm hyperventilating o_O just thinking about allowing any user this type of access to any shared database.
This is a database which comes off the back of an educational system. The users create events (timetables) and choose from drop down lists (on the timetable system) various options from Event_Category this automatically updates to a database. Now as the system is going to be updated, I need to take all the Event_Category which are e.g. Exam, Pre-Entry, Private Booking and change them to "Notional". The same goes for Event_Category which are Virtual Learning to change to "Teaching".

Thank you again
AM
 

A.M

New member
Local time
Today, 08:16
Joined
Jun 30, 2020
Messages
6
Let us know how you get on. (y)
Thank you so much for your help. Yes Update Query is perfect (just been informed that i shouldn't use Update Query on this occasion as the database is linked to multiple tables, events and resources) - so I've to leave for now as the whole schema would then have to be updated *nightmare*. But I have this up my sleeve for future. Thanks a million again :0)
 

Isaac

Lifelong Learner
Local time
Today, 01:16
Joined
Mar 14, 2017
Messages
8,738
Thank you so much for your help. Yes Update Query is perfect (just been informed that i shouldn't use Update Query on this occasion as the database is linked to multiple tables, events and resources) - so I've to leave for now as the whole schema would then have to be updated *nightmare*. But I have this up my sleeve for future. Thanks a million again :0)
Sounds like an adventure. Glad you got another tool for the future.
 
  • Like
Reactions: A.M

Users who are viewing this thread

Top Bottom