Participation Project #15 - Access Database Creation

Instructor Notes

Please follow the instructions to download and extract the files you will import into the database you create.  This is the Database portion of the assignment you completed last week. This will show you why it's important to have certain information in Access vs Excel. 

Step 2 of the process asks you to open access and create a database named education_ppdc_wvkep.  I suggest saving this on your desktop either on your computer or on the Windows Virtual Desktop or someplace easy to find.

Importing Data into Access

Step 3 has you begin to process of importing items into the database.  You do not double click and open the files into the database.  Follow the steps in the video, and I will also detail them below.

1. Click external data tab.
2. Click new data source under import & link.
   1. to import XML Files - Click From File, XML file 
   2. to import CSV files - Click From file, Text file

Be mindful that each step (a, b, c, d) has suggestions on how to import the files properly as you will follow a wizard once past the previous step to import the files correct. Follow the directions. 

Step 3E

Please pay attention to the instructions particularly.   Click that the first row of the file contains field names, and do not set a primary key during the import.

Step 4

For step 4, you’ll want to create a table.  

1. Click Create in the ribbon
2. Click Table Design
3. Enter the field names (SchoolTypeAbbrv and SchoolTypeName – don’t use spaces in between your words!) in the Field Name column.  Be sure to choose Short Text as the type. (It is the default, so should be automatic.)
4. Make SchoolTypeAbbrv the primary key by selecting the field and picking Primary Key from the ribbon (see below)

 It will look like this when complete (a small key icon to the left of SchoolTypeAbbrv)

 Enter the records. (As you switch to table view, you’ll be prompted to save. Please do. Name the table SchoolTypes)

 An important part of creating a database is to enter the records correctly.  SchoolTypeAbbrv is the primary key. It will be related to other tables.  If you don’t key all the records correctly and with no typos, the tables won’t relate correctly.

Step 5

Step 5 begins the process, using lookup fields, to put our toe in the water of relationships.  In step 5a, you will go to the design view of SchoolStatistics. I did this by selecting the table, then right clicking.  (see below)

Follow the instructions and you should be able to set up so that volues from SchoolTypes are used in the SchoolStatistics tables.  This creates a relationship between the table (automatically) and also makes the data uniform between the two tables.

 A second type of lookup field is created in step b.  In this, you’ll simply enter the values listed.  Why?  Again, it has to do with making the data uniform – it forces users to choose an option rather than allowing them to type enteries.  The data is standardized and makes the data more useful for queries.

Step 6

 Finally, step 6 has you create relationships. Relationships are the glue that hold our databases together.  They allow you to write queries, use data from multiple tables in reports and forms.  Without relationships, the database will come apart.

 To create a relationship, click on Database Tools in the ribbon, then click relationships:

Only add the tables you need. Adding more tables than you need can create problems. In this step, I'm going to first display district and district statistics:

Then, the instructions note the common field is district.  

I will click on the field District (which is the primary key) in the districts table and drag it over the the DistrictStatistics table and drop it on District. I get the Edit Relationships dialog box.  

Be sure to click "Enforce referential integrity".  The relationship type is One-to-Many.  That makes sense because District is the primary key in the Districts table – it should be unique.  It is not in the DistrictStatistics table (there will be a record for each district for each school year), thus the Many.

Hopefully this detailed note helps you begin the process of creating an Access database and gives you a good reference to work from going forward.  Let me know if you have any questions!