Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
One of the most efficient ways to bring data into your Access file is to import it from other programs. Access has the ability to import in several different formats. Two of the most common are Excel Spreadsheets and text, such as Comma Separated Values, or CSV. Let's create a new table based on an Excel file. Click on the External Data tab and then click Excel. A new window will appear, asking you to browse for your file. Click on Browse, go to our Chapter 11 files and double-click on Olive Cost.
Now you have the option of creating a brand-new table from scratch, adding the file's data to an already existing table, or linking the table. If you add the records to a current table, you will need to edit the Excel spreadsheet so that the file names are identical, the number of columns, the spelling, the spacing, everything. If you link to the table, you actually create a live connection between the two. Any changes made to your data, in either program, will be reflected in the other. Click OK. If your Excel file has more than one spreadsheet, Access will ask which one to use.
Click Next. Now assuming that your row one contains your field headings, select this check mark. If it doesn't, it's actually worth going back into your spreadsheet and deleting the first few rows. Click Next. You now have the opportunity to scan through your columns and set Data Types right now. Olive is a Text file; Cost/barrel is Currency; Quantity, we will leave on the default; and Total we will also make Currency. You also have the option of skipping the field if you don't want it in your Access file.
Click Next. If you have a Primary Key field already, you can choose your own primary key and pick it off of this dropdown. If you don't, Access will add an auto number field. We will go ahead, and we will leave that there. Click Next. Let's name our new table Olive Cost, and then click Finish. If you'll be repeating this exact import frequently, you can save these steps; otherwise, just click Close. Now, in your Navigation Pane you can see your new table.
Double-click on it to check it out. Now, let's import a Text file. The procedure is almost the same. This time click on the Text File button, go to the Browse button and choose Olive Inventory. We have the same options of creating a new table, appending the records to a table, or linking. We are going to create a new table and click OK. Now this file is a CSV, meaning that commas separate the values. So we'll stay with the first option, Delimited. Fixed Width would be used if each column is the exact number of characters. Click on Next.
Now it recognized that commas separate our values, but if your data file is different, it could also be tabs, or semicolons, or spaces, or you can put in your own punctuation mark, but as you could see that ours definitely was commas. Again, our First Row Contains Field Names. Click Next. Again, we can choose what Data Type each column is. Olive is Text; BarrelsOnHand, we'll leave as a Long Integer; BarrelsOnOrder, we'll also leave as the default; Cost, we will change to Currency, and click Next.
Again, we will let Access add a primary key and click Next. And we will leave the table name as Olive Inventory, so click Finish. We don't need to save our steps, so click Close. And now we have our second table. You can see that it's easy to import data from other sources and convert it to a table on the fly.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 90877 Viewers
80 Video lessons · 138053 Viewers
59 Video lessons · 56833 Viewers
52 Video lessons · 70470 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.