Before opening Access it is important to prepare the data in Excel. The purpose of this step is to ensure that the data is set up for import,
making the steps of importing the data easier and with fewer errors.
Click here to download the Excel workbook which contains the Menagerie Kennel data.
You must save this workbook to a folder on your computer for use in the next section of Unit 6. Once you have downloaded the workbook, verifty each of these items in the workbook:
There are 11 worksheets in the workbook. Each one of the worksheets will become a table in the database.
Review this description of Menagerie Kennel subjects document which simply states the purpose of each worksheet in the workbook.
Each worksheet has a field that could be used as the primary key for each table in Access. It will be easiest during the import if the primary key field is the first in the worksheet.
Appropriate tables have foreign keys to relate data. It will be easiest for relationship creation in the database if these are at the end of the columns in the worksheet.
Remember, foreign keys are the use of primary key fields as relational fields. In the image below, the first two records in the Pet worksheet are shown. (Click on image to enlarge.) The first column, PetID is a good candidate for primary key as it meets all three criteria:
it is unique for each record, is not empty for any record and shouldn’t need changed for any reason in the future. The last three columns: OwnerID, VetID and AnimID are foreign keys. While they contain data that looks like a potential primary key they do not satisfy the three primary key criteria for this worksheet.
They are in the worksheet for relational purposes only.
