This task is introduced with the Tables Session.
The Access class lectures move fast. Watch these videos before class to prep for class, or watch them after class to catch anything in class that you might miss.
If you need an Access overview before watching the Importing Tables video, watch this first:
Access Introduction (6:29). I decided not to go too far in this overview, so I don't go into relationships. There is a brief passage where you will see how close a relationship tab, and it might seem a bit out of place. We will go over relationships later. This section is just covering tables.
Virtual Lab for Mac Users (6:22 minutes) This will walk Mac Users through the process of setting up Virtual Labs for the Access Project. Saving files is a bit tricky; don't use the default folders, they are virtual and will not retain your data. You will notice that the virtual lab connections will not go as fast for you as they do in the video. (The lag wait times have been edited out of the video.)
Virtual lab video that demonstrates how to get your tables set up:
Importing Tables Video (15:24). This demonstration was created using the Virtual Lab so that Mac Users can get some more practice with creating, saving and opening files.
Windows Access: Start-up version.This version is a bit more polished than the Virtual Lab import tables video above
Getting Started and Importing Tables: Windows (2:15).
Windows Access: Detailed field and data type setup for imported tables. How to change field size and how to set up input masks.
Setting up Fields and Data Types (6:29).
Windows Access: Order Table Setup and Date function demonstration.
Setting up Order Table and date function demo (6:09).
Start to create these tables now and we will use them during the next class when we add relationships to them.
When you are ready, start to design the tables you want to use in your database
you will have specific guidance about what these tables should look like so start to design them, concentrating on design and formats do not spend too much time populating the tables with data at this time though you may wish to enter some sample data in table fields to see if the formatting you have selected is appropriate for the data you wish to use
Relax; don't rush these steps. Mindless clicking will create a mess.
Start by importing the data in these worksheets into your database. Use the import tool to allow it to create the first four tables for you. You do not need to open the Excel file in Excel; Access will be able to connect to the file for importing. All of the tables are in this one file. You cannot import all files at one time. You will have to do each one in three more repetitive steps.
Try not to import the same table twice. If you do, delete the extra one.
The screen shots are from a previous version of Access, so the very first import wizard button may look different than your version. Just make sure you don't choose an Excel Icon that is for Export.
Make sure you import the column headings that are in the Spreadsheet file. They are using a standard naming structure. You should follow this model and use the standard naming structure for your tables and fields within the tables. If you miss this step, you will be better off deleting your table and starting over.
Using the wizard, a pdf file with screen shots for help
Note that the pdf file refers to a tblBookAuthorConnector, but in the source workbook, you will find instead a tblConnection
you will have at least these four tables in your database. You can use the examples demonstrated today as models for how to modify the four imported tables.
if the import tool doesn't do it for you, ensure that the fields in the worksheets are in your tables and that the field properties are correct
In the import process, you will be asked if you want to allow the Wizard to create a Primary Key for you
The import tool will create tables with
⇒⇒⇒⇒⇒⇒ | ⇓⇓⇓⇓⇓⇓ |
---|---|
fields | based on Excel column headings |
records | based on Excel rows |
but your finished tables will require you to create additional fields in the tables after the import process.
should include the following fields, whether in the import phase or by you creating them after the import phase
should include the following fields, whether in the import phase or by you creating them after the import phase
The highlighted items are commonly undone when the project is graded. Don't overlook them!
Concentrate on setting up the table design correctly. You will not be adding live data to this table until a subsequent session and task.
we have not yet at this time decided what field will link the Order Table to another table; that will come during the discussion of relationships
In all these tables, you may or may not add data fields at a later date as you construct relationships between the tables. For today, however, concentrate on creating the tables.