Lab Test 3 MS Access CS 101 PRACTICE #2

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 Properties. It needs 6 fields, called House ID (Short Text), Street Address (Short Text), City (Short Text), State (Short Text), Zip (Short Text), Property Value (Currency).
  4. Add 5 records to the table. The data that should go in the fields is
    House ID Street Address CityStateZipProperty Value
    P1234 123 Main Street Lexington KY 40506 $250,000.00
    P2552 199 South Broadway Lexington KY 40507 $300,000.00
    P4251 82 Second Street Berea KY 40712$98,000.00
    P814178 Main Street Berea KY 40741$80,000.00
    P82935923 Nicholasville Road LexingtonKY 40151$150,000.00
  5. Make House ID the primary key of the table.
  6. Create a new table called Agents. It needs five Fields, called Agent ID (Short Text), First Name (Short Text), Last Name (Short Text), Phone Number (Short Text), and Licensed? (yes/no).
  7. Add four records to the table. The data that should go in the fields is
    Agent IDFirst NameLast NamePhone NumberLicensed?
    A119RalphBrown238-1414no
    A234John Smithson923-1413yes
    A425MaryJones123-4567yes
    A455GeorgeMurphy238-1241yes
  8. Make the Agent ID field the primary key. (It may already be 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. Change the size of the Phone Number field to 15.
  12. Close the table.
  13. 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.)
  14. Create a new table called Sales. It needs five Fields, called Sales ID (Short Text), House ID (Short Text), Agent ID (Short Text), Date of Sale (date/time), and Commission (Single). Remember you have to make the Commission field type NUMBER, then go down to the Properties grid at the bottom and change "Field size" to the right type of number.
  15. Add three records to the table. The data that should go in the fields is
    Sales IDHouse IDAgent IDDate of saleCommission
    S2311P4251A4251/15/20110.04
    S4151P1234A11912/14/20100.05
    S9238P8141A45511/22/20100.035
  16. Make the Sales ID field the primary key (if it isn't already).
  17. Close the table.
  18. Open the Relationships view. Make sure your tables are closed.
  19. Create a relationship between the Agents table and the Sales table, specifically between the Agent ID field in the Agent table and the Agent ID field in the Sales table. It should be one-to-many from the ID field in the Agents table to the ID field in the Sales table. Enforce referential integrity. Do not cascade update or cascade delete.
  20. Create a relationship between the Properties table and the Sales table between the House ID field in the Properties table and the House ID field in the Sales table. It should be one-to-many from the ID field in the Properties table to the ID field in the Sales tables. Enforce referential integrity. Do not cascade update or cascade delete.
  21. Close the Relationships view.
  22. 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.
  23. Create a query from the Properties table, giving all fields of the records where the Property Value field was greater than $150,000. Sort the query result by the Property Value field, ascending. Do not display the Property Value field twice in the result. Save the query with the name "Value Query".
  24. Save the database if needed.
  25. Create a query using all three tables. You want the fields Last Name (from Agents), Phone Number (from Agents), Date of Sale (from Sales), Street Address (from Properties) and City (from Properties). Sort the query result by the City field, ascending. Save the query with the name "Agents who have Sales".
  26. Save the database if needed.
  27. Create a query using the Properties table and the Sales table. You want the fields Street Address, City, Property Value from the Properties table and Agent ID and Commission from the Sales table. The criteria: the Property Value is greater than $200,000 or the Commission is greater than 3.5 percent (0.035). Save the query with the name "High End Properties".
  28. Save the database if needed.
  29. Make a Report using the Report Wizard including the fields First Name, Last Name, Commission, Street Address. Put a title on the report of "Monthly Report". Group by Street Address. Change to landscape orientation.
  30. Save the database if needed.
  31. Select the Sales table and export it as a PDF file. Save it in the folder with your database file.
  32. Make the Last Name field from the Agents table to be indexed, with Duplicates allowed.
  33. Create a form to show the details (one record at a time) of the Sales 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.