Access 2007: Entering and Editing Data in Tables

Uploaded by gcflearnfree on 14.10.2009

Now, we are going to populate our database. All that really means is that we are entering
data records into it. This can be done a few ways, one of which
is by entering records directly into a table. We'll do that with the Books table, so we
can start entering the books we have to sell. As you can see our Books table is empty; it
doesn't have any records in it so we're going to add some.
In Access you enter records into a table in Datasheet view which is how the table opens.
To add a record to an empty table, type the record into the row with the asterisk.
We don't need to enter anything into the Book ID field because the data type is set to Autonumber,
this field will assign a unique number to each record you enter.
We already set the Price data type to Currency, so when someone enters numbers here, it appears
as a dollar amount. If I try to enter text here, I get this message
that lets me know that the data I entered does not match the currency data type that
I set up. I'm going to add one more record.
I want to point something out to you that has to do with formatting.
If you look closely you'll notice that you can't see the entire title so I'm going to
double click on the line between the two fields to expand my title field.
That's a lot better! We should make sure the info that will be entered into the
fields is formatted properly, so that it is accurate and can be useful later.
This is called Data Validation, and it is done by setting the field properties in Design
View. You've seen this view before when we set up
the data types up here and now we'll be setting some of these properties in the bottom half
of the window. We'll start with the Validation Rule.
We'll set it up so that the price must be greater than 0 - because we're not
giving books away. The Validation Text is what you want to appear
for your user when they enter something incorrectly. So if they were to enter 0 for the price,
we want a message to pop up that says Must be a dollar value higher than $0.00.
The Required field property is set to NO by default but it must be set to Yes, so that
a user HAS TO enter a price for each book in our store.
They will never be allowed to NOT enter a price.
Let's take a look at the default field properties for a field that has a text data type.
Field Size refers to how many characters the user can type in.
For the category field we don't know how long a category name may be so we'll leave it set
to the default 255 characters, which is probably way more characters than we'll ever use but
you never know! I also want to set up a Validation rule for category.
I want the database user to have to enter one of the categories that's used at the store.
I'll use the expression builder to do this. The validation rule starts with an equals
sign. We'll place quotation marks around each category
name which will tell Access that it needs to check the entered data to make sure it
matches exactly one of the choices we have inside the quotation marks.
So if someone enters something in lower case or a category that's not in the list, it won't
accept the data. This helps cut down on data entry mistakes.
I'm inserting Or between each word which simply means I'm giving Access lots of options to
choose from. I'm telling Access this field must include
Fiction OR Non-Fiction, OR Kids and so forth. Now, so our users know what we expect of them
we have to set the Validation text to read Must be Fiction, Non-Fiction, and so on.
This text is what our user will see if they enter a category that doesn't match what we
have told Access to accept...just like when set the price to be greater than 0 earlier.So
all of the settings here look good, I'll save my changes.
Ok, since we entered Non-Fiction in our category field earlier before setting our field properties,
I received this message saying Data Integrity rules have been changed; existing data may
not be valid for the new rules. By clicking yes I can choose to have my data
tested with the new rules so I'm going to do that.
Now, let's get back to entering records. I want to show you how the Validation rule
works so for category I'll enter cooking when it really should be food.
See...I get a message that tells me I must enter one of the following options.
I'll go ahead and correct this. Let's take a look at the customers table.
Sometimes you may have to enter data into tables that already have a lot of data records
in them. You can see that I've already added some records
to this table. I can see that it's populated with 10 records
by taking a quick glance but I can also see the number of records here at the bottom left
side of the table. This is the record navigation.
I can choose to go to the first, previous, next, last, or create a new record from here.
I can also create a new record from the home tab by clicking on New in the records group.
Now all I have to do is enter my data. Sometimes, you'll need to edit a record that
already exists in a table. For example, let's say one of our customer's
has gotten married and I need to change her name.
I could take time to look through every record to find this one customer but what if I had
1000s of records? It would be much easier to use the Find feature to find and replace
an entry. From the Home tab, click on Find in the Find
group. The Find and Replace dialog box appears.
Type in what you want to find here. I want to find Williams so I can change Kiara
Williams to Kiara Rogers so I'll click on the Replace tab and type in Rogers beside
Replace with. This down here shows that the last field I
was in happened to be the customer ID field and that I'm searching the whole field for
a match and that I want to search all records in this table.
I don't want to search in the Customer ID field for this info.
so I'm to click the drop down arrow and choose to search the Customers table to find this
information. Click Find Next to find the information and
Replace to make the change. Be careful not to click Replace All because
this will replace everyone with the last name Williams.
Sometimes, you may want to Copy and Paste a record - Let's go back to the Books table.
It's common to have more than one volume of a particular book--like State Parks: Volume
1 and Sate Parks: Volume 2. To copy a record, select the record, right
click and select copy. Now select the new record, right click, and
select paste. Now all I have to do is make this Volume 2.
Now you know how to work with records in a
table itself. And you can see that you have to be careful
in how data is entered into a database, because you only get out of a database what you put
into it.