Assessment Review! 

You have used Access for several assignments and should be comfortable with the basic features of Access and the ability to create a database, modify the database, run queries, and generate reports.  From the projects you completed, hopefully you can see potential uses of Access for your personal or business use.  Below is one final review of the concepts and tasks you will be required to complete on your Access test.  Working with the class or individually, go through this review to ensure that you are ready for your final Access assessment!  Good luck!

During this unit you have worked with creating objects in a database. The review below will give you a good idea if you know your database content.

 

1.  Create a new database named Eye Colors and save it to your H: drive Access folder.

 

2.  Create a table named Eyes.  Using the information in the table below, add the following fields and change the properties as indicated.  Remember, every table needs a field that holds a unique value (Primary Key).  Include an ID field with AutoNumber for the Primary Key.  In addition, use the Input Mask to identify the Phone number properties so that the field should (   ) ___-____.

              Field Name ID StudentID   Eyes      Age Hall  Phone    
  Data Type: AutoNumber Number Text Number     Text Text
  Input Mask:           Yes
   Default Value:         Esker    
   Field Size:     12     15
   Required:       Yes    

 

3.  Enter the following information into the Eyes table.  Save.

              StudentID      Eyes    Age        Hall      Phone 
  100 Blue 18 Esker 262-774-8347     
  110 Green        20 Esker 262-774-8577
   120

Green

22 Esker 414-786-8229

 

4.  Create a second table using the information below and name the table Lunch Plan.  Again, include an ID field using AutoNumber as a Primary Key.

              Field Name ID StudentID   NutAllergy?      Time MealPlanCost Weeks
  Data Type: AutoNumber Number Yes/No Date/Time Currency Number      
  Input Mask:       Yes (use medium time format)    
   Default Value:            
   Field Size:            
   Required:            

 

5.  Input this data into the Lunch Plan table.

              StudentID        NutAllery?        Time         MealPlanCost Weeks     
  100 Yes 11:00 15    18   
  110 Yes       11:30 10 36
   120

No

12:00 7 36

 

6.  Create a relationship between the StudentID field in the two tables.   Database Tools | Relationships. This is a One to One relationship.

 

7.  Create a Relationship Report by going to Tools|Relationship Report.

 

8.  Close the relationship report and accept the default name to save.

 

9.  Create a query using Query Wizard with the Student ID, Eyes, MealPlanCost, and Weeks fields. Rename the query Costs.

 

10.  Add a calculated field named Total: in the last column of the query grid.  The expression is: [MealPlanCost]*[Weeks]  Note: field name must be followed by a colon. Total: [MealPlanCost]*[Weeks]  Also, use the bracket symbol to enclose the fields if building the formula from scratch or you can use the Expression builder to assist you with the formatting of this formula.  You may need to close tables before Running the Query if you get a message.  Click here for additional help with formulas in a query.

 

11.  Run the Query.

 

12.  Create a query to find students who are 20 or older.  Include the following fields: StudentID, Age, MealPlanCost, Phone, NutAllergy?, Time, and Weeks.  Name this query 20 and over.

 

13.  Using the Lunch Plan table and all fields, create a report with the Report Wizard named  Group by NutAllergy?  As you create the report, group the report by NutAllergy and then sort numerically by ID number.

 

14.  Adjust field size so all data is visible (Layout View).  Add your name to the Header using a Label control. (Aa)

 

15.  Save as Nut Allergy? and change to Report View to see what the report looks like.

 

16.  Are you ready for your Assessment?  Feel free to practice these concepts as much as needed so that you can successfully complete these types of tasks on your assessment.  Good luck!