Update Query that updates one field based on the value of another field

In Access 2003/XP/2000/97, I have a table called DQListUpdate and I would like to update the [Area Number] field with the value "Mesa001" when the [St Zip] field is equal to 85201 or 85203. How can I do this?

Related Tutorials

Update a date field with VBA code
I have a database which contains a date field called TransactionDate. I want to be able to query the user for a new date and then update the TransactionDate field based on what the user entered.

Using multivalued fields in queries
In Microsoft Office Access 2007, you can create a field that holds multiple values, such as a list of people to whom you have assigned an issue. Multivalued fields are appropriate for certain situations, such as when you use Office Access 2007 to work with information stored in a Microsoft Windows SharePoint Services 3.0 list, and that list contains a field that uses one of the multivalued field types available in Windows SharePoint Services 3.0. This article discusses multivalued fields and how to use them in queries. In this article * What is a multivalued field? * Introducing queries * Displaying a multivalued field in a query * Displaying the individual values in a multivalued field * Understanding the bound value and the display value in a lookup field * How to display the bound value in a query * Entering simple query criteria for a multivalued field * Searching for more than one value in a multivalued field * Understanding the impact of including a .Value field in the field list. * Counting, grouping and using other aggregate functions * Using an Append query with a multivalued field * Using an Update query with a multivalued field * Using a Delete query with a multivalued field

Delete a field
You can delete a field from a query or from a table. When you delete a field from a query, the field remains in the database, but is no longer used in the query. When you delete a field from a table, the field is deleted from the entire database. This topic provides an overview of the considerations and process of deleting fields, and gives steps for deleting a field from a query or a table.

Specify criteria for a text field
Want to limit the results of your query based on the value of a text field? You can specify exact values that you want a query field to match, partial values to match, or a set of values, any one of which to match. You can also exclude results based on specified values. Watch this demo to see how to do it.

Change the formatting of a field in a report based on its value
In Access 2003/XP/2000/97, I am trying to create a report that will have one field change colors based on the results of a query. How do I do this?

Examples of query criteria
When you want to limit the results of a query based on the values in a field, you use query criteria. A query criterion is an expression that Access compares to query field values to determine whether to include the record that contains each value. In this topic: * Introduction to query criteria * Criteria for Text, Memo, and Hyperlink fields * Criteria for Number, Currency, and AutoNumber fields * Criteria for Date/Time fields * Criteria for other fields

Calculated fields
A calculated field is a "virtual field" in a query. The field is virtual in the sense that it is not stored anywhere in the database. Instead, it is calculated dynamically when the query is used. Learning objectives: create a calculated field; understand why ACCESS add square; brackets around field names; understand the use of the ampersand operator (&). (pdf file, install Acrobat Reader to read this tutorial)

Update the data in a database
This article explains how to update existing data. Microsoft Office Access 2007 provides a number of tools for updating existing records, including datasheets, forms, queries, find-and-replace, and the new Data Collection feature. In this article * How database design affects updating * How data types affect updating * How table field properties affect updating * Use a form to update data * Use a datasheet to update data * Use an update query to change existing data * Use an append query to add records to tables * Use Data Collection to update records * Use the Find and Replace dialog box to change data * Use cascading updates to change primary and foreign key values

Promote or demote a field, table, or query
Promoting a field creates a new group level immediately above the group level to which the field belongs. Demoting a field removes the group level to which the field belongs, and moves the field to the next lower group level. You can also promote all fields that belong to a table or query.

Require a value in a field
What do you want to do? *Set the Required property for a field to Yes *Create a non-null index for a field by using a data-definition query

Guide to multivalued fields
This topic covers multivalued fields in Office Access 2007 and in Windows SharePoint Services, how to create a multivalued field and when to use one, and how to employ a multivalued field in a query. In this article: * What is a multivalued field? * Multivalued fields in Windows SharePoint Services * When to use a multivalued field * How to create a multivalued field * Using the Lookup Wizard * Modify the design of a multivalued field * How to employ a multivalued field in a query

Create a custom field template for the Field Templates pane
Sometimes it is easier to choose from a predefined list of fields than to manually create a field. You can use the Field Templates pane to choose from a list of predefined fields. You can also create your own custom defined field templates and then add them to the Field Templates pane. Microsoft Office Access 2007 comes with a set of built-in field templates that can save you considerable time when you create fields. To create a new field by using a field template, you display the Field Templates pane and then drag one or more templates to a table that is open in Datasheet view. This article introduces field templates and explains how to insert a field that is based on a prebuilt template into your table. The article then provides detailed instructions about how to create and insert your own custom field templates. In this article: What is a field template? Add a field by using a field template, Create a custom field template.

Percentage Calculation-Forms
I have a field in query "Car_Value" then I want create a field (not in table/query) "Percent_15%" formatted 'number', and another field (not in table/query), "15%_OF_Car_Value". I want to calculate Percent_15% with Car_value, and show this value in "15%_OF_Car_Value". For some reason If I put in any number in percent_15% field it is disappering...

Create a query that returns a new table based on 3 nested subqueries
I have an Access 2003/XP/2000/97 database with a base table called tblApplicant whose primary key is AppId. There are also 3 additional tables all connected using the AppId field. Each of these additional tables has a PermitID field and an Active (yes/no) field. How do I create a query that returns a new table with the Applicant (AppId) and each of the latest PermitID's from the 3 additional tables? The latest PermitID is determined by finding the largest PermitID for the Applicant where the Active field is set to Yes.

Copy One Field Definition to Another Field
Copying a field definition ensures that your new field will be identical to the original, which can save some time. Keep in mind that this procedure only copies a field's definition; it doesn't copy any values in the field. Here’s how to copy one field definition to another field in Microsoft Access 2003.

Create a Query Using More Than One Field
A single-field query in Microsoft Access 2003 extracts specific information from a database, such as “everybody with a birthday in November.” You can add other fields to this--say, making the query “everybody with a birthday on an even day in November who is under 60.” Here’s how to use more than one field in Access 2003 queries.

Creating and using Descriptive and Meaningful Field Names in your Database Design
A field in your database represents a characteristic of the subject of the table to which it belongs. If the field is appropriately named it should be easy to identify the characteristic that the field is supposed to represent. If a field name is ambiguous, unclear or is vague it may suggest that the purpose of the field is not fully identified.

Query That Uses More Than One Field
Queries allow you to display information in different ways. For example, suppose you have a table that contains complete addresses for your friends, but you just want a list of names and telephone numbers. You could create a two-field query to accomplish this. These instructuctions apply to MS Access 97.

Database Fields on Forms and Reports
As we saw in previous lessons, fields are created on a table, then they can be added to a query. Based on the way Microsoft Access deals with fields, a field on a table cannot be created as a combination of other fields, as if it were an expression (unless the field is created as lookup�). Instead, the fields on a table are primarily created to store data. On the other hand, as we will see in the next few lessons, a new field can be added to a query as a combination, through an expression, of other fields. Forms and reports present this same solution. Besides using the fields of a table or a query, a new field can be added to a form or report. This is because forms and reports make use of objects called Windows controls. In other words, the fields of a form or a report are created from Windows controls. Learn: Labels, Text Boxes, Combo Boxes, Navigation Buttons, Forms Fields, Subform, Record Selector, Reports and Controls, Subreport, Common Field Design on Forms and Reports, Introduction to Form Design, Introduction to Report Design

Set the field size
You can adjust the amount of space that each record in a table uses by changing the field size property of number fields in the table. You can also change the field size of a field that stores text data, although this action has a smaller effect on the amount of space that is used. In this article * What happens when I change the field size? * Change the field size of a number field * Change the field size of a text field

Advanced Triggers
Learning objectives: How do I run VBA code using a macro? How do I use the value in one field to automatically suggest a value for a different field? How do I change the table or query a form is bound to once the form is already created? What is the After Update event? How is it used? How do I provide a search capability for my forms? How do I create an unbound combo box? Can I implement the search capability using Visual Basic? (pdf file, install Acrobat Reader to read this tutorial.)

Access Form / Date Calculation
Inside of a form, I am trying to have a 90 days calculate based off field called 'ReceivedDate'. I have another field that people are manually entering the 90th day. Is there a way that I can have the form automatically fill in the 90th day field?

Copy One Field Definition to Another Field
Copying a field definition ensures that your new field will be identical to the original, which can save some time. These instructions apply to MS Access 97. Procedures for other versions may vary.

Customize how data is displayed by creating a lookup field
When you want a field in a datasheet, form, or report to display a certain value while the field stores a different value, you create a lookup field. A lookup field is useful when you want the field to display something more meaningful than a number, such as an ID (or other foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) value). For example, instead of displaying a contact ID number, Microsoft Office Access can display a contact name. In this example, the contact ID number is called the bound value, or the value that is stored in the source table, query, or list. The contact ID number is automatically looked up in the source and replaced with the contact name. The contact name is referred to as the display value.

Create a Query That Uses Multiple Criteria in One Field
Creating a query that uses multiple criteria for a field is like running a filter on your query results. For example, if you have a table that contains complete addresses of all your friends, you can run a query that will display brief information for those who live in New York, or for those who live in New York and whose surname starts with the letter "B".

Updating Records in Microsoft Access with an Update Query
A situation may arise where we need to update many records in a database table when certain information changes or needs to be modified. A Microsoft Access Update Query updates specified values in a table for all records or for those records that match a specified criteria. It is possible to update records manually in a database table, either via a form or through the tables datasheet, however this may take a very long time. Changing records manually is not only time consuming, it is also inefficient, and lends itself to errors as you update the records. The best way to handle this type of event, the updating of many records in a database table, is to use an Update Query to make the changes to data in one operation. You will save yourself time, and eliminate the possibility of manual errors.

Insert, change, or delete a Memo field
You use a Memo field when you need to store large amounts of text in a database. This topic explains how to use Microsoft Office Access 2007 to add a memo field to new and existing database tables. What do you want to do? * Understand Memo fields * Add a Memo field in Datasheet view * Add a Memo field in Design view * Delete a Memo field * Memo field property reference

Work with data: insert, delete and update. Sorting and Grouping
A datasheet is the visual representation of the information contained in a database table, or of the results returned by a query. When you add or remove a column from a datasheet, you add or remove a field from the table that underlies the datasheet. If that field contains data, you also eliminate that information.

MS Access Date/Time ODBC issue
When you run a query based on a linked (attached) Microsoft SQL Server table that contains a Date/Time field, and the criteria for the Date/Time field contains a literal time value, Microsoft Access returns an empty result set.

Date field lookup
How can I create a field on a form (and report) that will query 12 date fields and return the latest date? I will need that new field to be updated every time the form (and report) is opened.

how can I start query by command
I have a form with 2 combo boxes, the first is company name, the second is contact name. When the company name is entered a query limits the contact names to that company This works fine until the operator goes back and changes the company name or goes on to a new record the contact field never updates and the original list never changes. I'm not sure if I need VBA code or if there is another solution.

Add a field to a form or report
You can quickly add fields to a form or report by using the Field List pane. When you double-click a field in the Field List pane (or if you drag a field from the list to your form or report), Microsoft Office Access 2007 automatically creates the appropriate control to display the field - for example, a text box or check box - and then binds the control to that field.

Conditionally format a field in a report
In Access 2003/XP/2000/97, I have a database and I want to change the Background color of a field on a Report based on the value. Is this possible?

Update Query that updates values in one table with values from another table
How to create an update query that updates values in one table with values from another table in Access 2003.

Create a Query That Uses Multiple Criteria in One Field
When you create a query that uses multiple criteria in one field for Access 2003, you restrict the number of search results that you will receive from that query. The multiple criteria can be either inclusive or exclusive depending on how the criteria are joined.

Database Strings
A string is text made of a readable letter, a symbol, a word or a group of words. When creating a table in Datasheet View, to apply a Text field: (1)If you create a new field by replacing the Add New Field column header with a name, the new field automatically receives the Text data type (2)To change the data type of a field, click its column header or a cell under its column. In the Data Type & Formatting section of the Datasheet tab of the ribbon, click the arrow of the Data Type combo box and select Text Learning Objective: String-Based Data Types, Operations on Strings, String Concatenation, Document and Object Linking, String-Based Data Types: The Hyperlink, Objects and Data Fields, An Attachment, Using OLE Objects.

Update data by using a query
This article describes how to create and run an update query. You use an update query when you have to update or change existing data in a set of records. You cannot use an update query to add new records to a database, or to delete entire records from a database. To add new records to a database you use an append query, and to delete whole records from a database you use a delete query. In this article * Overview * Create and run an update query * Update data from one table to another * Stop Disabled Mode from blocking a query * SQL version: UPDATE statement

Insert, create, or delete a field that stores text data
You add a Text field to a table when you need to store smaller amounts of textual data, such as names, addresses, and telephone numbers. This article explains how to use Microsoft Office Access 2007 to add and delete a Text field in new and existing database tables. In this article: * Understand Text fields * Add a Text field in Datasheet view * Add a Text field in Design view * Delete a Text field * Text field property reference

Prevent duplicate values in a field
You can prevent duplicate values in a field by creating a unique index. A unique index is an index that requires that each value of the indexed field is unique. What do you want to do? * Set a field's Indexed property to Yes (No duplicates) * Create a unique index for a field by using a data-definition query

Update Query that updates one field based on the value of another field

View Tutorial