COMM 391

Excel to Access Tutorial

 

In this tutorial you will create a database for holding student records, restructure data stored in a flat file spreadsheet, import tables from the restructured spreadsheet into your database, create a form for entering a grade and create a report for your student transcript. For background information you might find it helpful to read Grauer, Chapter 4 (pp. 141-153 & pp. 165-end of chapter).

Instructions:

  1. Locate the Excel 5.0 spreadsheets named "GRADES.XLS". This file store information about grades in a single "flat" file. Your training in database design suggests that some data in the flat file is redundant and that the data is better stored in a relational database. Your first job in this assignment is to look through the data in "GRADES.XLS" and to develop a design for your new database . (HINT: your database should contain a set of three tables, "Students", "Courses", and "CoursesTaken"). Make sure to identify keys and to provide a field for linking the tables.
  2. The second step is to adapt the data in "GRADE.XLS" so it can be imported into an Access database. To accomplish this, you will have to copy and paste data from the Excel sheet named "Data", into the three other tables within the spreadsheet. (HINT: remember to eliminate the redundant data in the Student and Courses table before moving to step 3. By the way, did you notice how much less data you need to store when you eliminate redundancy?)
  3. After populating the "Students", "Courses", and "CoursesTaken" table with data, save the file "GRADES.XLS". Now go to the sheet labeled "Students". Add yourself to the bottom of the list. Your student number should be your UBC student number.
  4. Save the GRADES.XLS file and then close Excel 5.0. Now open Access and start a New Database called "MARKS.MDB".
  5. Use the import function provided in Access to import the sheets you created in Excel 5.0 into the "MARKS.MDB" database. Rename the tables you have imported so that at the end of this process you have 3 tables labeled "Students", "Courses", and "CoursesTaken". There is no need to import the "Data" sheet (all of the information is included in the other three tables). When possible, while in the import wizard, assign your own primary key to the table.
  6. Next, move into the table design for each of the tables and assign a primary key for each table, if you haven’t already. (use the "Set Primary Key" function under the Edit Menu.) Even if you were able to assign primary keys in the import wizard you will need to reassign the key for CoursesTaken (HINT: Setting the primary key for CoursesTaken is a bit tricky, remember the primary key is supposed to be a unique identifier. Does any single field in this table have a unique identifier? If not you may have to assign a compound primary key. This is a primary key made up of two or more fields in a table.)
  7. Open the "Students" table and insert a new field. The name of the field should be your name.
  8. Next, open the ‘Relationships’ page (in the Edit menu, or under Tools) in Access and add the three imported tables to the layout. There are two relationships that must be created between the tables. Create the required relationships now, and make sure both relationships display referential integrity.
  9. Add the course COMM 391 to the Courses Table. Make sure that the mark is for the 97S semester. The CourseID for COMM 391 should be 12345.
  10. Use the Form Wizard to help you create a form for adding grades to the "CoursesTaken" table. The form should be use a single-column format. After creating the form, move to a new record and give yourself a grade for COMM 391 (note ID’s, not names are required here - the ID’s are listed above). Add four more grades for yourself (use the courses already existing in the Courses Table).
  11. Your next step is to create a query that will display your transcript. Your transcript should have your Name, Dept, CrsNum, Title, Credits, Semester, and Grade. These fields should be sorted first by semester, and second alphabetically by Course Name. The query should only display your grades (not everyone in the course table). Run the query and print out the results.
  12. Finally, each department would like to know what the average GPA is for each course, for each semester. Produce a query that will output these results. When you have completed your design of the query run the query and print out the results.