This lecture will provide two solutions for the Orders table question. There are two ways of adding a table to an existing database, one by importing an existing spreadsheet, or two, using the access GUI and entering the field name and data type for each record. Let us now attempt the first way. As Seen on the screen, I've already created an Excel spreadsheet, I called it orders, you can do the same, that is create your own spreadsheet. Assuming you did this, we will now import the spreadsheet into our database. First, you'll need to know where the spreadsheet was stored to access it later.

Mine was saved in OneDrive documents. I'll elaborate on this further. So let us now go to the Access database We're going to import this table this Orders table. So this is usually, this should be your entry point. We have now got the customer table already created. And we want to add a second table called the order table.

And how are we going to access this? Well, external data, we're going to look for a new source. And the new source is going to be an Excel spreadsheet. And what pops up is a dialog box, asking us first of all, the file name of where the spreadsheet that we're going to import is located. As I mentioned, mine is located on OneDrive documents, but you will have to browse and locate this. So I will find mine which is in the location of OneDrive documents.

And let me try to find the Orders table. And here it is. So I'll double click that. And the Browse button has found the location. And I'm going to import the source data into a new table. Click ok.

This comes up dialog saying that the first row contains some data. Let's see what happens if we click OK. Yes, the first row does include some column headings, and not particularly data, the data follows after the column heading. So I've ticked off the first row contains column headings. Click Next. And it's identified the first field as an ID field and whether or not we want to index our order table on that index of ID.

However, I want to identify that this is The field does but no duplicates around. don't want any duplication on the ID field. And I'll choose my own primary key, which is going to be the ID. And I'm going to call this table, the order table, the front the finished product. Finish. Now, this information box says an index, our primary key cannot contain a null value.

I purposely put in some extraneous data to see what would happen if we want to filter out some of the rows or some of the columns that we don't need. So wait and I'll show you what happened. Okay. We'll save the import steps. And what has happened in the objects pane the left hand side is an order table has been created. Let's have a look at this order table though by double clicking on it.

And here it is. So it seems as if everything is fine, except it has created some data that we don't need to repeat the process by holding down the Shift key clicking on the first row, right clicking and cutting that out. So we want to delete 39 Records. And again, it's just cleaning up our final table which is the order table, so it's always worthwhile once the table has been downloaded to review it and any extraneous data just remove it or modified where it's necessary. So let's complete the process of bringing in the import and from an existing spreadsheet. And downloaded into auditable.

Now let us attempt to create a new table using the access GUI. The first step we will need to do is to go into the table design and enter all of the field names and the data types. Then once we've completed that, we will have to go into the datasheet view and enter some records in other words, populating the database. So let's go through step one. Let us first of all go to the table design form and creating the field names of our audit table. So the field name for the first column eventually is going to be ID and the data type is going to be numeric.

And this is going to be a primary key. So let me just identify this by right clicking and choosing this as a primary key. The second field I need is a foreign key, which is the customer ID, again will be numeric. And let me just add a description by identifying this to be a foreign key. And if we check, the third field is going to be paid. And if you recall, this is a choice is either going to be false or true.

So let us do a lookup wizard on this one. And I will type the values that I want And there's going to be two columns which the first column is going to be true. And the second one will be false as my selection. Next. Next, and we will limit it to list and finish. The third field name is notes.

And the data type is going to be short text certainly sufficient for the notes. We'll be making 255 characters down here and we will enter the date so let's call this The order date short checks and letters entered in as say, day, month, year, something like that. Okay, so those are the five fields that we want entered. So we'll do a Ctrl S and save this as the order table finished. What we want to do now is populate a database populate this particular table. So we'll go into the datasheet view and add sub records.

So the fields have been identified in column markers, and we will enter the ID, customer ID, etc. If we look back on our spreadsheet, we'll see that the first field that we entered was five for the ID And the customer ID was 44. And the selection for this particular order ID was true. And the notes, let's just make up some for a party. And the order date, we said we would enter it as month day year. So all four, and we'll choose this 2017.

Let's tab to the next one, and I'm checking my spreadsheet. The next entry was six for order 135. And this was true also. No notes. However, the date on this one was old 912 2017. And let's just add one more.

Thank you. Getting the idea. We certainly don't have the convenience of all the records in the spreadsheet and downloading them. And we're putting them in manually a record at a time. And this third one is a customer id 131. We'll tab over.

And again, now let's change it, make it false. For holiday and make up a date let's call this the 2012 and again 2017 Okay. So certainly that is sufficient to populate in this particular case three records is so what we have done is provide a second way of creating a table. First of all, of course, we went into the table design and added the fields and all of the columns customer ID paid On order date, and we've entered the actual values by going into the datasheet view, and we can add all of the records. We've only entered three of them. So that completes the second alternative in populating the database.

First one, of course is by using an existing spreadsheet. And in this particular case, we took advantage of Microsoft Access GUI