Entering and Editing Data in Tables

In Access entering data into tables offers many options. Learn all about adding and editing Access database entries in this free lesson.

Introduction

Access 2007 Access 2007 databases hold the actual data records inside tables. You can add, edit, and delete records directly from these tables. This lesson will show you how to work in the tables to add new records, as well as how to edit existing records using commands like copy and paste and find and replace. It will also discuss the dangers involved in deleting records from a table, as well as the importance of setting validation rules and other field properties to ensure data is valid.

Adding and editing data in tables

Download the example to work along with the video.

Adding records to tables

When you enter records into your table, you are populating the database. In Access 2007, you can do this a few different ways.

To add records in the new record row:

To add records with the New Record navigation button:

To add records with the New Record command:

Editing records in tables

Sometimes it is necessary to edit records in the database. Like with every other task in Access 2007, this can be done several different ways.

To edit a record directly:

To edit a record using Find and Replace:

CAUTION: DO NOT use Replace All because it will overwrite every instance of the Find term in the table, which can have a serious impact on your data.

To copy and paste a pecord:

To delete a record:

Delete Record Dialog BoxDelete Record Dialog Box

There may be other records that rely on the record you are trying to delete. DO NOT delete a record without knowing how it will impact the rest of your database.

Record NumbersRecord Numbering after Deleting Records

When you delete a record, the record number is permanently deleted from the database table. If you delete the last record from a table and then add a new record, your new record numbers will appear to be out of sequence.

Data validation

Data validation is an important database concept. It is the process by which Access tests the data that is being entered into the database to make sure it is in an acceptable—or valid—format.

Let's say one of your database users has entered an order date of January 4, 2008, in the month/date/year format as 01/04/2008. Another user has entered an order placed on that same date in the day/month/year format as 04/01/2008. If the database is tracking all sales for the month of January 2008, it may not show both orders as placed in January, even though both were placed on the same date.

Access 2007 allows you to set field properties and data validation rules to force the person entering data to follow a specific format.

Data types and validation rules

Data validation begins when data types are set during the process of building tables and fields. For example, if a field data type had been set to Currency and a text value is entered in that table field during data entry, Access will not accept an invalid format and will display a validation error, like the one below.

Mismatched DataData Validation Error Message

Data validation is accomplished by setting data validation rules and other field properties for various fields.

To set data validation rules:

Validation rules work most easily with numerical fields. Rules for text fields require you to enclose each acceptable value inside its own quotation marks, separating them with Or, as seen above.

Validation text

Validation text is a specialized error message you can set to have Access tell the user entering data the specific way you want that person to enter it.

To set the validation text, enter the error message exactly as you want it to appear to users in the row directly beneath the Validation Rule row in the Field Properties section of Design view. For the validation rule we set for Category, you'd set the validation text like this:

Category Validation TextValidation Text for Category Field

The image below shows the resulting error message users would see when the Category validation rule has been broken.

Error Message for Category RuleError Message Showing Validation Text

Using field properties to ensure data integrity

Another way to ensure data integrity is by setting field properties such as Field Size, Format, and Required.

These are just some ways Access helps you ensure data being entered into your database is valid.

Challenge!

If you haven't already done so, save the sample Ready2Read database to your computer.