WEBVTT
Kind: captions
Language: en

00:00:06.440 --> 00:00:11.800
Now, we are going to populate our database.
All that really means is that we are entering

00:00:11.879 --> 00:00:15.639
data records into it.
This can be done a few ways, one of which

00:00:15.639 --> 00:00:20.900
is by entering records directly into a table.
We'll do that with the Books table, so we

00:00:20.900 --> 00:00:25.350
can start entering the books we have to sell.
As you can see our Books table is empty; it

00:00:25.350 --> 00:00:28.380
doesn't have any records in it so
we're going to add some.

00:00:28.380 --> 00:00:34.710
In Access you enter records into a table in
Datasheet view which is how the table opens.

00:00:34.710 --> 00:00:39.510
To add a record to an empty table, type the
record into the row with the asterisk.

00:00:39.510 --> 00:00:46.510
We don't need to enter anything into the Book
ID field because the data type is set to Autonumber,

00:00:48.129 --> 00:00:55.129
this field will assign a unique number to
each record you enter.

00:01:03.449 --> 00:01:09.190
We already set the Price data type to Currency,
so when someone enters numbers here, it appears

00:01:09.190 --> 00:01:14.800
as a dollar amount.
If I try to enter text here, I get this message

00:01:14.800 --> 00:01:19.160
that lets me know that the data I entered
does not match the currency data type that

00:01:19.160 --> 00:01:26.160
I set up.
I'm going to add one more record.

00:01:38.850 --> 00:01:42.290
I want to point something out to you that
has to do with formatting.

00:01:42.290 --> 00:01:47.900
If you look closely you'll notice that you
can't see the entire title so I'm going to

00:01:47.900 --> 00:01:52.820
double click on the line between the two fields
to expand my title field.

00:01:52.820 --> 00:01:59.810
That's a lot better! We should make
sure the info that will be entered into the

00:01:59.810 --> 00:02:05.110
fields is formatted properly, so that it is
accurate and can be useful later.

00:02:05.110 --> 00:02:10.619
This is called Data Validation, and it is
done by setting the field properties in Design

00:02:10.619 --> 00:02:14.090
View.
You've seen this view before when we set up

00:02:14.090 --> 00:02:18.819
the data types up here and now we'll be setting
some of these properties in the bottom half

00:02:18.819 --> 00:02:21.860
of the window.
We'll start with the Validation Rule.

00:02:21.860 --> 00:02:26.490
We'll set it up so that the price must be
greater than 0 - because we're not

00:02:26.490 --> 00:02:30.760
giving books away.
The Validation Text is what you want to appear

00:02:30.760 --> 00:02:36.540
for your user when they enter something incorrectly.
So if they were to enter 0 for the price,

00:02:36.540 --> 00:02:43.540
we want a message to pop up that says Must
be a dollar value higher than $0.00.

00:02:50.030 --> 00:02:56.840
The Required field property is set to NO by
default but it must be set to Yes, so that

00:02:56.840 --> 00:03:01.200
a user HAS TO enter a price for each book
in our store.

00:03:01.200 --> 00:03:07.390
They will never be allowed to NOT enter a
price.

00:03:07.390 --> 00:03:14.010
Let's take a look at the default field properties
for a field that has a text data type.

00:03:14.010 --> 00:03:18.370
Field Size refers to how many characters the
user can type in.

00:03:18.370 --> 00:03:24.959
For the category field we don't know how long
a category name may be so we'll leave it set

00:03:24.959 --> 00:03:31.959
to the default 255 characters, which is probably
way more characters than we'll ever use but

00:03:32.090 --> 00:03:36.950
you never know! I also want to set up a Validation
rule for category.

00:03:36.950 --> 00:03:43.950
I want the database user to have to enter
one of the categories that's used at the store.

00:03:44.010 --> 00:03:50.319
I'll use the expression builder to do this.
The validation rule starts with an equals

00:03:50.319 --> 00:03:54.180
sign.
We'll place quotation marks around each category

00:03:54.180 --> 00:03:59.599
name which will tell Access that it needs
to check the entered data to make sure it

00:03:59.599 --> 00:04:05.599
matches exactly one of the choices we have
inside the quotation marks.

00:04:05.599 --> 00:04:11.250
So if someone enters something in lower case
or a category that's not in the list, it won't

00:04:11.250 --> 00:04:17.410
accept the data.
This helps cut down on data entry mistakes.

00:04:17.410 --> 00:04:23.139
I'm inserting Or between each word which simply
means I'm giving Access lots of options to

00:04:23.139 --> 00:04:26.949
choose from.
I'm telling Access this field must include

00:04:26.949 --> 00:04:33.949
Fiction OR Non-Fiction, OR Kids and so forth.
Now, so our users know what we expect of them

00:04:40.740 --> 00:04:47.740
we have to set the Validation text to read
Must be Fiction, Non-Fiction, and so on.

00:04:56.060 --> 00:05:00.490
This text is what our user will see if they
enter a category that doesn't match what we

00:05:00.490 --> 00:05:06.210
have told Access to accept...just like when
set the price to be greater than 0 earlier.So

00:05:06.210 --> 00:05:10.990
all of the settings here look good, I'll save
my changes.

00:05:10.990 --> 00:05:17.990
Ok, since we entered Non-Fiction in our category
field earlier before setting our field properties,

00:05:19.400 --> 00:05:25.550
I received this message saying Data Integrity
rules have been changed; existing data may

00:05:25.550 --> 00:05:30.460
not be valid for the new rules.
By clicking yes I can choose to have my data

00:05:30.460 --> 00:05:34.770
tested with the new rules so I'm going to
do that.

00:05:34.770 --> 00:05:41.270
Now, let's get back to entering records.
I want to show you how the Validation rule

00:05:41.270 --> 00:05:47.310
works so for category I'll enter cooking when
it really should be food.

00:05:47.310 --> 00:05:53.520
See...I get a message that tells me I must
enter one of the following options.

00:05:53.520 --> 00:06:00.520
I'll go ahead and correct this.
Let's take a look at the customers table.

00:06:01.070 --> 00:06:05.710
Sometimes you may have to enter data into
tables that already have a lot of data records

00:06:05.710 --> 00:06:08.740
in them.
You can see that I've already added some records

00:06:08.740 --> 00:06:12.590
to this table.
I can see that it's populated with 10 records

00:06:12.590 --> 00:06:18.009
by taking a quick glance but I can also see
the number of records here at the bottom left

00:06:18.009 --> 00:06:22.270
side of the table.
This is the record navigation.

00:06:22.270 --> 00:06:29.270
I can choose to go to the first, previous,
next, last, or create a new record from here.

00:06:33.960 --> 00:06:40.960
I can also create a new record from the home
tab by clicking on New in the records group.

00:06:41.570 --> 00:06:48.570
Now all I have to do is enter my data.
Sometimes, you'll need to edit a record that

00:06:55.320 --> 00:06:59.850
already exists in a table.
For example, let's say one of our customer's

00:06:59.850 --> 00:07:03.050
has gotten married and I need to change her
name.

00:07:03.050 --> 00:07:09.270
I could take time to look through every record
to find this one customer but what if I had

00:07:09.270 --> 00:07:14.720
1000s of records? It would be much easier
to use the Find feature to find and replace

00:07:14.720 --> 00:07:20.020
an entry.
From the Home tab, click on Find in the Find

00:07:20.020 --> 00:07:23.850
group.
The Find and Replace dialog box appears.

00:07:23.850 --> 00:07:30.850
Type in what you want to find here.
I want to find Williams so I can change Kiara

00:07:33.430 --> 00:07:40.430
Williams to Kiara Rogers so I'll click on
the Replace tab and type in Rogers beside

00:07:41.509 --> 00:07:48.509
Replace with.
This down here shows that the last field I

00:07:51.330 --> 00:07:56.699
was in happened to be the customer ID field
and that I'm searching the whole field for

00:07:56.699 --> 00:08:00.590
a match and that I want to search all records
in this table.

00:08:00.590 --> 00:08:04.069
I don't want to search in the Customer ID
field for this info.

00:08:04.069 --> 00:08:11.069
so I'm to click the drop down arrow and choose
to search the Customers table to find this

00:08:11.090 --> 00:08:16.479
information.
Click Find Next to find the information and

00:08:16.479 --> 00:08:20.850
Replace to make the change.
Be careful not to click Replace All because

00:08:20.850 --> 00:08:22.900
this will replace everyone with the last name
Williams.

00:08:22.900 --> 00:08:29.900
Sometimes, you may want to Copy and Paste
a record - Let's go back to the Books table.

00:08:29.940 --> 00:08:35.539
It's common to have more than one volume of
a particular book--like State Parks: Volume

00:08:35.539 --> 00:08:42.539
1 and Sate Parks: Volume 2.
To copy a record, select the record, right

00:08:43.610 --> 00:08:50.610
click and select copy.
Now select the new record, right click, and

00:08:51.160 --> 00:08:55.779
select paste.
Now all I have to do is make this Volume 2.

00:08:55.779 --> 00:08:57.510
Now you know how to work with records in a

00:09:00.050 --> 00:09:04.110
table itself.
And you can see that you have to be careful

00:09:04.110 --> 00:09:09.300
in how data is entered into a database, because
you only get out of a database what you put

00:09:09.300 --> 00:09:16.300
into it.