Microsoft Access: Update Query with "Where","and" and "Or" Clauses

Suppose we have a table in Microsoft Access with various fields including -
Title: Mr., Mrs, Ms, Dr. Er. etc.
Name: Name of the person
Gender: M, F
Marital Status: Married, Unmarried

Microsoft Access makes it easy to collect data from users in Excel
and import it in tables (This has been covered in another post). But the data received may not be as expected. For example, some may have missed entering the required details or there may be specciling errors, or even formatting errors.

For illustration, we were making a small project for internal use in Microsoft Access. In the title field we were expecting users to enter Mr., Mrs.,Ms.,Dr. etc. But instead they send excel sheets with "Shri.", "Smti." etc. - the Indian official titles. So we had to change them. And we can easily do so using SQL query in Microsoft Access.

The basic syntax to update table cells is:
UPDATE Table_Name
SET Table_Name.Column_Name = "What you want to update with"
WHERE (([Column_Name]= "Current Value"));

A simple example query would be:

Update Table1
Set Table1.Title = "Mr."
Where ([Title]="Shri.");
This query will replace/update all "Shri." with "Mr.".

To write an update query in Microsoft Access, do:
1. Open the Microsoft Access Project.
2. Create a new Query
3. Open the SQL View
4. Enter the following:

UPDATE TSG_PO
SET TSG_PO.Title = "Mr."
WHERE ( ([Marital_Status] like "*") AND ([Gender]="M") AND ([Title] Is Null) OR ([Title] Like "Shri*"));

Using the query above, we are looking for males whose title is NULL or something beginning with SHRI and UPDATE those to "Mr.".

5. Click "Run" to execute.

Now, if we want to look for "Unmarried" Female and update the title to "Ms." we would execute something like:

UPDATE TSG_PO
SET TSG_PO.Title = "Ms."
WHERE ( ([Marital_Status] like "Unmar*") AND ([Gender]="F") AND ([Title] Is Null) OR ([Title] Like "Sm*"));

The above query will not update a female who is a doctor, as her title will be Dr. We are using the asterisk (*) to get all records with something that starts with "Unmar", just in case there are spelling errors in the column.