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
Import data
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.
Note that the pdf file refers to a tblBookAuthorConnector, but in the source workbook, you will find instead a tblConnection
Tables:
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.
Fields:
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
if the import tool adds data fields to your tables that are not included in the list below, you may use them or delete them
but be sure to include at least these data fields, though you may desire or need additional fields
consider how each of the fields should be described in the design view
Primary Key:
In the import process, you will be asked if you want to allow the Wizard to create a Primary Key for you
say YES and allow the wizard to add an autonumber field for the primary key.
it should result in a field size Long Integer
for the event that you may have to join a Number field to an AutoNumber field in a many-to-one relationship
in such cases, the Number field size must be Long Integer in both the Primary and Foreign Key fields
Tables to create
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.
Book Table should include the following fields, whether in the import phase or by you creating them after the import phase
tblBook_ID field: this will be the Primary Key
tblBook_Title field
tblBook_Date field
the publication or copyright date
be careful about the field property - should it be number, text, or date/time?
tblBook_ShelfLocation field
its Library of Congress number
be careful about the field property - is it a number?
tblBook_Language field: with appropriate data type
tblBook_Topic field: with appropriate data type
tblBook_RetailPrice field: with appropriate data type
tblBook_Copies field: with appropriate data type
tblBook_tblPublisher_ID field: this will be a Foreign Key
this will link this table to others
be sure to make its field size Long Integer
Publisher Table should include the following fields, whether in the import phase or by you creating them after the import phase
tblPublisher_ID field: this will be the Primary Key
tblPublisher_Name field
tblPublisher_City field
tblPublisher_State field: two letter abbreviation
tblPublisher_ZipCode field
with appropriate data type and input mask
if there is no data for this field, you may leave it blank or make it up for new publishers you add to the table
tblPublisher_Phone field
with appropriate data type and input mask
if there is no data for this field, you may leave it blank or make it up for new publishers you add to the table
tblPublisher_Country field: two letter abbreviation
Author Table should include the following fields, whether in the import phase or by you creating them after the import phase
tblAuthor_ID field: this will be the Primary Key
tblAuthor_Name field: last name and first name
Connection Table
this table will become important when we get to relationships
tblConnection_ID field: this will be the Primary Key
tblConnection_tblBook_ID field: this will be a Foreign Key
this will link this table to the book table
be sure to make its field size Long Integer
tblConnection_tblAuthor_ID field: this will be a Foreign Key
this will link this table to the author table
be sure to make its field size Long Integer
One more table
You have one table that you will have to create from scratch using your choice of table creation tools
Concentrate on setting up the table design correctly. You will not be adding live data to this table until a subsequent session and task.
Order Table
tblOrder_ID field
this will be the Primary Key
you will have to create a primary key for this table and you might as well allow the wizard to use an autonumber for this purpose
tblOrder_BookTitle field
this will be a Foreign Key
you must identify the title of the book you will order
the data in this field in the Order Table may well come from a field in another table, but we'll discuss that when we relate tables
you will probably want to rename this field as you decide how to relate tblOrder to another table
tblOrder_NumberOfBooksOrdered field: with appropriate data type
tblOrder_DateOfOrder field: make the current date the default value for the date the book was ordered
tblOrder_DateOrderReceived field: appropriate data format
tblOrder_DiscountPrice field
with appropriate data type
add in your description of this field that it will always be no more than 80% of the retail price of a book
this field is included to allow us to do some calculations in queries
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
Don't forget - every table needs a primary key
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.