Learn how to create a new table to capture data in your database. Author Jen McBee will demonstrate the steps to take to create new tables and will give tips on things to keep in mind when designing your database.Author Jen McBee will demonstrate how to i
- [Voiceover] People have their favorite programs to work with, and most people love working in Excel. They want to do most, if not all, of their data entry in Workbook because they find it easy and they're more comfortable with it. There are other people who only work with databases, either Access databases of SQL databases. We may also receive data from a text file. All of this information may need to be imported or linked to tables within your database. On the Access 2013 MOS exam you may be asked to import data from either an Excel file or a text file to create a new table in a database.
In this course we will explore both file types, Excel and text, so you can see how in just a couple of steps you can import the data and create a new table. When you create a new table by importing information, you can import the information into a table, you can append the data to an existing table, or you can link the data back to the original data source. Notice the MOS icon next to Import Data and Link Data. Remember that this is an indication that these two items will probably be on the Access 2013 MOS exam, so those are the two that we'll focus on in this video.
Let's go ahead and get started. To begin with, I'm gonna create a new blank database so we have a place to store our new tables. I'll launch the Access application, and from the template window I'll select Blank Desktop Database. Click on it once to select it. Here I have the opportunity to name my database. I'll call this Imported Customer Data. I'll click the Browse button to the right of the field so that I can navigate back to my exercise files on the desktop, and save it in the Chapter Three folder.
I'll go ahead and click OK, and then click Create. There, now we have our new database and it's just waiting for us to import our new data and create our two new tables. Let's take a look at the two files that we're going to be importing in. First we'll look at the Excel file. I'll navigate back to the exercise files on my desktop, going to chapter three, the first information that we'll bring in is the customer's text file. I'll double click to open it up so we can look at it.
Looks like it contains information that is separated with commas, that should be pretty easy to bring into a new table. Now that we know how it's separated, we can go ahead and close the document and we'll start the process of importing that information in. We'll click on the External Data tab. Here's our Import and Link group. It's a text file, so I'll go ahead and select Text File. First step is to browse back to the exercise file. Chapter three, here's our customer file.
Good, now here's the three options. We can import the source data into a new table, which is what we want to do. We also can append a copy of the records to a specific table, or we can link to the data source by creating a linked table. We'll link the first radio button selected and click OK. We're in the Import Text wizard. First step looks good, we'll go ahead and click Next. The second step, we just want to verify that it has the correct separator, which is a comma.
That looks good, we'll go ahead and click Next. In this step we could go through field by field and make sure that the data type is correct. I usually wait until I have the data imported into a table and then I'll go into Design view and take care of any challenges. We'l click Next. In this step we can let Access add a primary key, or, if we know what the primary key should be, we can select Choose My Own Primary Key. This looks like our customer ID number in that first field, so we'll make sure that field one is showing in the primary key field, and go ahead and click Next.
In this step we'll name our table. Customers is great, we'll leave that in there, and just click Finish. Let's go ahead and open our new Customers table. That looks much better than what we saw when it was in its native text format. That looks great, let's go ahead and close the Customers table. This initial table that Access created when we created the database, we can go ahead and close that. Excellent, we're ready to bring in our second set of data, and it's in the format of an Excel worksheet.
Let's navigate back to the Chapter Three folder so we can open the Excel document and look at the information before we import it. It's called Orders. I'll go ahead and double click and open it up. So we have an order ID, customer ID, order date and ship date, and I'll just double check, it looks like we should have 39 records that come in. I'm gonna go ahead and close the Excel document and go back to Access. Let's start the import process once again. This time we're going to select the Excel icon.
We'll go ahead and link to the data source. What that means is, we'll have the information in a table in Access that we can use to include in reports, but the true data source will reside in the Excel worksheet, so if anyone wants to update the information, they'll need to open the Excel worksheet and enter the data there. And the next time that we open this Access database, Access will check the connection and refresh the data so any changes that have been made to the Excel worksheet will flow into our table in our database.
Let's go ahead and click Browse, make sure we're in the Chapter Three folder. We'll select the Orders, Excel Workbook. Everything looks good, we can go ahead and click OK. Now we're in a link spreadsheet wizard. The very first thing that I want you to always look at is, make sure that if you do have column headings you check off First row contains column headings. If you don't, your column headings will be included in the data. Everything looks good in this step, so I'll go ahead and click Next.
That's really all we need to do. We'll leave the default name Orders, and go ahead and click Finish. Now this new table that we just created, the icon looks quite a bit different than the one that we created with the text file. We can tell right away that this data is linked back to an Excel workbook. See the Excel icon, and it also has an arrow, and that arrow tells us that there's a live link between the two. Let's go ahead and open up our new Orders table.
That looks really nice. Everything came through very clean. We can go into Design view and take a look at the field names and the data type that's attached to each. While we're in Design view, let's go ahead and assign a primary key. This is our Orders table, so let's make the order ID the primary key field. And as you'll recall, that primary key is set so that we cannot put a duplicate order ID number in the table. Let's go ahead and save and close our new Orders table.
Being comfortable importing data from Excel or from a text file are great skills for you to have. You may also face a challenge on the Access 2013 MOS exam where you're asked to import information. Pay close attention to whether you're being asked to import the data, append the data, or link the data.
Disclaimer: Microsoft does not produce, provide, or endorse this video training course.
The course first explores the MOS certification program and highlights its cost, format, and objectives. Jennifer then walks viewers through all of the Access exam areas in detail. She explains how to create and manage Access databases, build tables, join tables with relationships, and create queries, forms, and dynamic reports. She includes free practice files to follow along while learning about each topic. There are challenges to test your skills at the end of almost every chapter, and the full-length, 50-minute practice exam at the end of the course will ensure you're ready for the real exam.
- Identify the location of the search field in a table.
- Recall how to update identical information across multiple records.
- Explain how to create a report that pulls information from two tables.
- Explain the difference between a split form and a subform.
- Identify two methods used to create a calculated field in a query.
- Summarize how to display information in a particular order when creating a report.