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	Semester	Grade
   E1111	C1294	S1111	Fall 2007	B
   E2222	C1294	S2222	Fall 2007	C
   E3333	C3333	S1111	Fall 2008	A
   E4444	C4444	S3333	Spring 2008	A
   E5555	C4444	S2222	Fall 2008	B
   E6666	C1294	S3333	Fall 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 ID	First Name	Last Name	Birthday
   S1111	Jack	Green	1/1/1991
   S2222	Maureen	Samuels	2/2/1992
   S3333	Ralph	Brown	3/3/1993
   S4444	Sally	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 ID	Course Title	Credit Hours	Department
    C1294	Introduction to Programming	3	Computer Science
    C2222	Calculus I	4	Math
    C3333	English Composition	3	English
    C4444	Management 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.

• Now you should have ONE database file, YourName.accdb and one PDF file, Students.pdf.
• Create a zip file that contains your files named YourName.zip. Put this zip file on the Desktop in your folder.
• Files should be zipped as usual, and submitted through Canvas.
• Delete your folder and all your files from the Desktop.