WEBVTT Kind: captions Language: en 00:00:06.770 --> 00:00:10.760 In our previous lesson we focused on designing our database. 00:00:10.760 --> 00:00:15.170 We said that we wanted a customers table, an orders table, and books table. 00:00:15.170 --> 00:00:17.510 So let's start by creating our books table. 00:00:17.510 --> 00:00:22.490 I could have started with customers, orders or books, but in this case I think it makes 00:00:22.490 --> 00:00:27.280 sense to start with books since we need to have our inventory of books before our customers 00:00:27.280 --> 00:00:29.120 can place an order. 00:00:29.120 --> 00:00:33.980 I've already opened Access from my start menu so now we need to decide whether we want to 00:00:33.980 --> 00:00:39.000 use a built in template or start from scratch using a blank database. 00:00:39.000 --> 00:00:41.050 Let's start from scratch! 00:00:41.050 --> 00:00:46.350 Click on the blank database icon to open a new database. 00:00:46.350 --> 00:00:48.739 Now we need to name the database. 00:00:48.739 --> 00:00:54.989 By default Access assigns a name and also adds the file extension even if you delete 00:00:54.989 --> 00:00:58.070 it while you're entering your name. 00:00:58.070 --> 00:01:01.890 Just delete this to name your database. 00:01:01.890 --> 00:01:07.020 In this case I'll name it Ready2Read and click Create. 00:01:07.020 --> 00:01:12.380 The database opens in Datasheet view and you'll see Table 1 listed in the Navigation Pane 00:01:12.380 --> 00:01:15.630 and on the table tab to the right. 00:01:15.630 --> 00:01:19.409 Table 1 is the default name Access assigned to your first table. 00:01:19.409 --> 00:01:26.340 To give the table a unique name click the Miscrosoft Office button and select Save from 00:01:26.340 --> 00:01:28.039 the menu. 00:01:28.039 --> 00:01:32.959 A dialog box will open asking you to name your table. 00:01:32.959 --> 00:01:36.899 I'll name it Books and click Ok. 00:01:36.899 --> 00:01:41.409 The new name changes in the Navigation Pane and on the table tab. 00:01:41.409 --> 00:01:43.869 It looks like I misspelled books. 00:01:43.869 --> 00:01:51.200 To rename a table go to the Navigation Pane, right-click on the table and Select rename. 00:01:51.200 --> 00:01:55.189 If your table to the right is open you'll receive a message telling you to close it 00:01:55.189 --> 00:01:57.219 before you can rename. 00:01:57.219 --> 00:01:59.170 Just Ok. 00:01:59.170 --> 00:02:06.469 To close the data table to the right, right-click on the table tab and select Close. 00:02:06.469 --> 00:02:12.250 Now, right-click on the table in the Navigation Pane and select Rename. 00:02:12.250 --> 00:02:16.740 A cursor appears which means we can start typing. 00:02:16.740 --> 00:02:22.000 Left-click anywhere outside of the field to make the change. 00:02:22.000 --> 00:02:25.280 Double click on the table to reopen. 00:02:25.280 --> 00:02:29.140 By default Access started us out with one table. 00:02:29.140 --> 00:02:38.180 To create additional tables click on the Create tab, and then Table, in the Tables group. 00:02:38.180 --> 00:02:40.670 A new table tab appears. 00:02:40.670 --> 00:02:45.730 From the MS Office button select Save to name your table. 00:02:45.730 --> 00:02:49.470 We'll name this one Customers. 00:02:49.470 --> 00:02:56.590 We have once more table to add, the Orders table. 00:02:56.590 --> 00:03:01.790 You can always tell which table you're in by seeing which tab is highlighted. 00:03:01.790 --> 00:03:06.690 So currently we're in the Orders table. 00:03:06.690 --> 00:03:12.580 I'm going to go back to the Books table by clicking on the Books tab. 00:03:12.580 --> 00:03:14.960 Now let's start adding some fields. 00:03:14.960 --> 00:03:18.400 By default Access starts us off with an ID field. 00:03:18.400 --> 00:03:24.650 The ID field is already set to AutoNumber which will give each record a unique number. 00:03:24.650 --> 00:03:30.900 Remember, each record is one row of information. 00:03:30.900 --> 00:03:35.580 Each table you create in Access will have this ID field so it's a good idea to give 00:03:35.580 --> 00:03:43.590 this field a unique name in each table because it will be easier to understand later. 00:03:43.590 --> 00:03:50.070 We'll name this Book ID by clicking on the ID header and then click on Rename in the 00:03:50.070 --> 00:03:54.319 Fields and Columns group on the Ribbon. 00:03:54.319 --> 00:03:57.020 Let's add our other fields. 00:03:57.020 --> 00:04:01.710 To do this double click on Add New Field. 00:04:01.710 --> 00:04:05.020 The text will disappear so you can enter your field name. 00:04:05.020 --> 00:04:08.620 We'll start with Title. 00:04:08.620 --> 00:04:15.380 Once you've added this new field you can press Tab on your keyboard to add other fields. 00:04:15.380 --> 00:04:19.259 Repeat these steps until all of your field headers have been entered. 00:04:19.259 --> 00:04:28.960 I'm going to quickly add all of my field headers here. 00:04:28.960 --> 00:04:35.099 In Access we can work in Datasheet view which is our current view, or Design view. 00:04:35.099 --> 00:04:40.389 Datasheet view is kinda like working will cells in Excel where Design view gives you 00:04:40.389 --> 00:04:46.870 more control over the information you enter into cells which in this case are actually 00:04:46.870 --> 00:04:49.320 fields. 00:04:49.320 --> 00:04:51.639 Let's take a look at the Design view. 00:04:51.639 --> 00:04:56.720 The Views option appears under the Home Tab and the Datasheet Tab which is the tab that 00:04:56.720 --> 00:04:59.069 is currently open. 00:04:59.069 --> 00:05:04.539 Click the drop down arrow under the Views option and select Design View. 00:05:04.539 --> 00:05:09.159 The benefit of setting up our fields here is that we can tell Access to allow only certain 00:05:09.159 --> 00:05:11.939 types of data to be entered into a field. 00:05:11.939 --> 00:05:15.520 For example, I need to add a price field. 00:05:15.520 --> 00:05:21.270 I can do that here under Field Name. 00:05:21.270 --> 00:05:25.800 If I press Tab on my keyboard, I'm now in the Data Type field. 00:05:25.800 --> 00:05:31.430 This field is used to define the type of data that can be entered. 00:05:31.430 --> 00:05:34.550 We'll talk about data integrity and validation later. 00:05:34.550 --> 00:05:37.340 By default the data type is text. 00:05:37.340 --> 00:05:41.719 If I click the drop down arrow I can see additional options. 00:05:41.719 --> 00:05:48.060 Since price deals with money, I'll change my data type to currency. 00:05:48.060 --> 00:05:53.309 You won't see any immediate changes by changing the media type but you'll notice it later 00:05:53.309 --> 00:05:56.029 when we start entering data. 00:05:56.029 --> 00:05:59.669 The information here at the bottom will be discussed later so there's no need to worry 00:05:59.669 --> 00:06:00.819 about it right now. 00:06:00.819 --> 00:06:06.509 I'm going to switch back to Datasheet view to finish setting up the fields in my tables. 00:06:06.509 --> 00:06:12.909 Oops looks like I have to save my table before I move on. 00:06:12.909 --> 00:06:16.599 Now we can switch back to the Datasheet view. 00:06:16.599 --> 00:06:18.909 Now let's add fields to the Customers table. 00:06:18.909 --> 00:06:23.790 As I said before, the first field is the ID field by default. 00:06:23.790 --> 00:06:28.970 We'll rename this field by clicking on the Datasheet tab under Table Tools and then Rename 00:06:28.970 --> 00:06:33.620 in the Fields & Columns group. 00:06:33.620 --> 00:06:39.460 Now we're gonna call this Customer ID. 00:06:39.460 --> 00:06:48.190 I'll go ahead and add my other fields. 00:06:48.190 --> 00:06:53.880 Once I've added all of my fields I can reorder these fields by dragging and dropping. 00:06:53.880 --> 00:06:56.659 Here I'll show you. 00:06:56.659 --> 00:06:58.870 Click on a field header. 00:06:58.870 --> 00:07:04.610 Move your mouse in the area of the header and when you see a cross with arrows appear, 00:07:04.610 --> 00:07:14.039 hold down your left click button and drag the column to another location. 00:07:14.039 --> 00:07:19.741 I can also delete a field by clicking on the field I want to get rid of and from the Fields 00:07:19.741 --> 00:07:24.720 and Columns group on the ribbon click Delete. 00:07:24.720 --> 00:07:29.909 If you inherited this database meaning someone else set it up for you, you want to be careful 00:07:29.909 --> 00:07:35.119 if you decide to delete anything in a database because it could impact other parts of the 00:07:35.119 --> 00:07:37.930 database. 00:07:37.930 --> 00:07:39.330 We're almost finished! 00:07:39.330 --> 00:07:42.020 Now we need to add fields to the Orders table. 00:07:42.020 --> 00:07:47.330 I'll start by changing the ID field. 00:07:47.330 --> 00:07:53.689 Earlier we talked about the ID fields and how they assign a unique number to each record. 00:07:53.689 --> 00:07:59.119 For our Orders table we want to figure out who is ordering our books. 00:07:59.119 --> 00:08:06.050 So the first field I'm going to create is the Customer ID field. 00:08:06.050 --> 00:08:10.059 This is where a customer's unique ID number will reside. 00:08:10.059 --> 00:08:12.979 The next field will be the Book ID. 00:08:12.979 --> 00:08:18.460 This will tell me which book my customer ordered. 00:08:18.460 --> 00:08:26.020 I'll explain more about the Customer ID field and the Book ID field in our next lesson. 00:08:26.020 --> 00:08:31.460 Remember the point of our Orders table is to see who ordered what and when so we need 00:08:31.460 --> 00:08:34.360 to add a date field. 00:08:34.360 --> 00:08:41.030 We just learned how to use Design view to change the data type but we can also do it 00:08:41.030 --> 00:08:45.000 from the Ribbon in the Data Type & Formatting group. 00:08:45.000 --> 00:08:52.810 To do this click on a field header and then click on the drop down menu beside Data Type. 00:08:52.810 --> 00:08:56.190 It automatically defaults to Text. 00:08:56.190 --> 00:09:02.210 But since we want to know the order dates we're going to change it to Date/Time. 00:09:02.210 --> 00:09:06.830 Immediately you don't see any changes at all but once we start entering our data later 00:09:06.830 --> 00:09:09.360 you'll see this calendar icon come up. 00:09:09.360 --> 00:09:12.330 Again, we'll talk about this a little later. 00:09:12.330 --> 00:09:15.550 Setting up tables and fields in Access is just the beginning. 00:09:15.550 --> 00:09:19.820 Now we need to link them all together - to build the relationships in our relational 00:09:19.820 --> 00:09:23.800 database - because that is where the real power is in Access.