Lab Test 3 MS Access CS 101 PRACTICE #3

This is a practice test.

  1. Create a folder on the Desktop- you choose the name of it.
  2. Create a blank Database file in MS Access. Name it with the filename YourName.accdb. and save it with the name YourName.accdb in your folder on the Desktop. Then open it with MS Access.
  3. Create a new table called Takes. It needs 5 fields, called Enrollment ID (Short Text), Course ID (Short Text), Student ID (Short Text), Semester (Short Text), Grade (Short Text).
  4. Add 6 records to the table. The data that should go in the fields is
    Enrollment ID Course ID Student ID SemesterGrade
    E1111C1294S1111Fall 2007 B
    E2222C1294S2222Fall 2007 C
    E3333C3333S1111Fall 2008 A
    E4444C4444S3333Spring 2008 A
    E5555C4444S2222Fall 2008 B
    E6666C1294S3333Fall 2008 C
  5. Make Enrollment ID the primary key for the table.
  6. Create a new table called Students. It needs four Fields, called Student ID (Short Text), First Name (Short Text), Last Name (Short Text), Birthday (Date).
  7. Add four records to the table. The data that should go in the fields is
    Student IDFirst NameLast NameBirthday
    S1111Jack Green 1/1/1991
    S2222MaureenSamuels 2/2/1992
    S3333Ralph Brown 3/3/1993
    S4444Sally Runyon 4/4/1994
  8. Make the Student ID field the primary key (if it isn't already the key).
  9. Change the size of the First Name field to 20.
  10. Change the size of the Last Name field to 50.
  11. Close the table.
  12. Save the database if needed. (This step is just for your protection in case you are interrupted during your work. You will finish with ONE database file, YourName.accdb.)
  13. Create a new table called Courses. It needs four Fields, called Course ID (Short Text), Course Title (Short Text), Credit Hours (Single), Department (Short Text).
  14. Add four records to the table. The data that should go in the fields is
    Course IDCourse TitleCredit Hours Department
    C1294Introduction to Programming3Computer Science
    C2222Calculus I 4 Math
    C3333English Composition 3 English
    C4444Management 101 3 Business
  15. Make the Course ID field the primary key.
  16. Close the table.
  17. Open the Relationships view. Make sure your tables are closed.
  18. Create a relationship between the Courses table and the Takes table, specifically between the Course ID field in the Courses table and the Course ID field in the Takes table. It should be one-to-many from the ID field in the Courses table to the ID field in the Takes table. Enforce referential integrity. Do not cascade update or cascade delete.
  19. Create a relationship between the Students table and the Takes table between the Student ID field in the Students table and the Student ID field in the Takes table. It should be one-to-many from the ID field in the Students table to the ID field in the Takes tables. Enforce referential integrity. Do not cascade update or cascade delete.
  20. Close the Relationships view.
  21. If you have trouble with the Referential Integrity, an error that says the data in a table is wrong, cancel the relationship, go back to your tables and proofread the data in the fields in the tables. That is usually the problem. Make sure you save the table and try the relationship again.
  22. Create a query from the Courses table, giving all fields of the records where the Credit Hours field was greater than 3. Sort the query result by the Department field, ascending. Do not display the Credit Hours field twice in the result. Save the query with the name "Credits bigger than 3".
  23. Save the database if needed.
  24. Add a Totals row to the Courses table. Show the total of the Credit Hours field.
  25. Create a query using all three tables. You want the fields Course Title (from Courses), Credit Hours (from Courses), Last Name (from Students), First Name (from Students) and Grade (from Takes) and Semester (from Takes). Restrict the records to ones from the Fall 2007 semester. Save the query with the name "Fall 2007 Grades".
  26. Save the database if needed.
  27. Create a query using the Students table and the Takes table. You want all the fields from the Students table, and all fields from the Takes table. The criteria: the Last Name of the student is Green OR the Grade is a B. Save the query with the name "B's or Green's".
  28. Save the database if needed.
  29. Make a Report using the Report Wizard including the fields Student ID, First Name, Last Name from the Students table, the Semester and Grade from Takes, and the Course Title from Courses. Put a title on the report of "Grade Report". Group by Semester. Sort by Semester ascending. Change to landscape orientation.
  30. Save the database if needed.
  31. Select the Students table and export it as a PDF file, named Students.pdf. Save it in the folder with your database file.
  32. Make the Course Title field from the Courses table to be indexed, with Duplicates allowed.
  33. Create a form to show the details (one record at a time) of the Students table. It should appear in the list of Objects to the left of the screen.
  34. Make sure these things are saved in the database.
  35. Close Access.