Lab Test 3 MS Access CS 101 PRACTICE #1

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.

    Small note - the information in the table is not at all historically accurate:)

  3. Create a new table called Books. It needs 5 fields, called Book ID (Short Text), Title (Short Text), Author (Short Text), Publisher (Short Text), Profit Earned (Currency).
  4. Add 6 records to this table. The data that should go in the fields is
    Book ID Title Author PublisherProfit Earned
    1 Gone with the Wind A0123 P111 $5,000.00
    2 Wuthering Heights A4554 P134 $2,500.00
    3 The Catcher in the Rye A2451 P581 $3,000.00
    4 Franny and Zooey A2451 P134 $1,250.00
    5 Shirley A4554 P134$750.00
    6 The Professor A4554 P111$3,000.00
  5. Make Book ID the primary key of the table.
  6. Create a new table called Authors. It needs three Fields, called Author ID (Short Text), Author Name (Short Text) and Royalties (Currency).
  7. Add three records to the table. The data that should go in the fields is
    Author ID Author Name Royalties
    A0123 Margaret Mitchell $5,000.00
    A2451 J. D. Salinger $15,000.00
    A4554 Charlotte Bronte $200.00
  8. Make the Author ID field the primary key, if it isn't already.
  9. Close the table.
  10. 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.)
  11. Create a new table called Publishers. It needs three Fields, called Publisher ID (Short Text), Publisher Name (Short Text) and Publisher Address (Short Text).
  12. Add three records to the table. The data that should go in the fields is
    Publisher ID Publisher Name Publisher Address
    P111 Harcourt Brace New York, NY
    P134 McGraw Hill New York, NY
    P581 Pearson Publishing Nashville, TN
  13. Make the Publisher ID field the primary key if it isn't already.
  14. Close the table.
  15. Open the Relationships view. Make sure all your tables are closed.
  16. Create a relationship between the Authors table and the Books table, specifically between the Author ID field in the Authors table and the Author field in the Books table. It should be one-to-many from the Author ID field to the Author field. Enforce referential integrity. Do not cascade update or cascade delete.
  17. Create a relationship between the Publisher table and the Books table between the Publisher ID field in the Publishers table and the Publisher field in the Books table. It should be one-to-many from the Publisher ID field to the Publisher field. Enforce referential integrity. Do not cascade update or cascade delete.
  18. Close the Relationships view.
  19. 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.
  20. Create a query from the Books table, giving all fields of the records where the Profit Earned field was greater than 1500. (NOTE: do NOT put the $ in the criterion!) Sort the query result by the Profit Earned field, ascending. Do not display the Profit Earned field twice in the result. Save the query with the name "Profit Query".
  21. Save the database if needed.
  22. Create a query using all three tables. You want the fields Title (from Books), Publisher Name (from Publishers), Author Name (from Authors) and Profit Earned (from Books). Select the books of Charlotte Bronte. Sort the query result by the Profit Earned field, ascending. Save the query with the name "Book Titles by Profit".
  23. Save the database if needed.
  24. Create a query using the Books table and the Authors table. You want the fields Title (from Books), Profit Earned (from Books) and Author Name (from Authors). The criteria: the Author Name is "J. D. Salinger" and the Profit Earned is more than $2000.00. Save the query with the name "Salinger".
  25. Save the database if needed.
  26. Make a Report using the Report Wizard including the fields Publisher Name, Publisher Address and Publisher ID. Put a title on the report of "Publishers' Information". Group by Publisher Address, sort by Publisher Name (ascending). Change to landscape orientation.
  27. Save the database if needed.
  28. Select the Publisher table and export it as a PDF file, Publishers.pdf. Save it in the folder with your database file.
  29. Make the Publisher Name field from the Publisher table to be indexed, with Duplicates allowed.
  30. Create a form to show the details (one record at a time) of the Authors table. It should appear in the list of Objects to the left of the screen.
  31. Make sure these things are saved in the database.
  32. Close Access.