Homework answers / question archive / BPC110 Access Unit Exam Project 1:  Tables Download the exam, set primary keys, change field properties in both tables, move a field, and delete a field

BPC110 Access Unit Exam Project 1:  Tables Download the exam, set primary keys, change field properties in both tables, move a field, and delete a field

MS Access

Share With

---

BPC110 Access Unit Exam

Project 1:  Tables
Download the exam, set primary keys, change field properties in both tables, move a field, and delete a field.
 

1. Open the HealthClub.accdb database with Access.  Use the File tab and Save As command to save the file with a new name HealthClubXXX.accdb, where the XXX are your initials or last name.
2. Background: Notice that there are two tables: FitnessPrograms and ClubMembers.  This is a database used to track the membership of a fitness center / health club, the “All Access Fitness Center”.
3. Open the ClubMembers table in Design View and specify the MemberID as the Primary Key.  Save and Close the ClubMembers table. (For steps 3 and 4, if you do not successfully complete these steps you will have many problems later. Refer to your book section AS2, Activity 2.3 “Setting the Primary Key”)
4. Open the FitnessPrograms table in design view and specify the ProgramID as the Primary Key.  Save and Close the FitnessPrograms table.
5. Open the ClubMembers table in design view and make the following changes.

1. Select the State field, and specify a Default Value of VA. 
2. Select the MembershipStatus field, and specify a Default Value of Active. 
3. Select the FirstName field, and specify a Caption of First Name.
4. Select the LastName field, and specify a Caption of Last Name. 
5. Select the Zip field, and change the Field Size from 20 characters to 10 characters. 
6. Save and close the table.

6. Open the FitnessPrograms table in design view and make the following changes.
   1. Select the MonthlyFee field.
      1. Change the Format to Currency.
      2. Change the Decimal Places to 2.
      3. Change the Caption to Monthly Fee.
      4. Create a Validation Rule to specify that the fee must be greater than or equal to $20 and less than or equal to $60.
      5. Create the Validation Text (an error message) of Fee must be between $20 and $60. 
   2. Move the ProgramDirector field to be third in the list, below ProgramType.
   3. Delete the AssistantDirector field.
   4. Save and close the table.

 

1.  

Project 2:  Relationships
Find and fix a ClubMember who is assigned to an invalid program, and then establish Referential Integrity between the two tables.
 

Background:  Each club member must join one of the fitness programs.  This database needs to ensure that each member joins a valid (existing) program.  The FitnessPrograms table is the “master” table, and the ClubMembers table is the “related” or secondary table. Unfortunately, because the previous employee in charge of this database did not establish Referential Integrity between the two tables, one club member was mistakenly assigned to a program that does not exist. Your job is to find and fix this error.  To prevent this from happening again, you need to establish “Referential Integrity” so that Access will not allow any more invalid ProgramID’s in the ClubMembers table. 

1. Open the ClubMembers table. In the ProgramID field, find the club member assigned to program 205 and change it to 202.
2. Establish Referential Integrity. (Refer to Section AS2 Activities 2.8 through 2.10 in your book)
   1. On the Database Tools tab, open the Relationships window, click on Show Table, and add the two tables to the window.  Close the Show table dialog box. 
   2. Note: If you accidentally add a table to the Relationships Window more than once, just select the extra table by clicking on the title bar at the top of the extra table and then press the delete key. 
   3. Join the two tables together using the common field ProgramID.  
   4. Check the box to Enforce Referential Integrity in the Edit Relationships dialogue box.  
   5. Close the Edit Relationships dialogue box.
   6. Note: You must first successfully correct the ProgramID field (Step 1 above), and successfully established the primary keys (Steps 3 and 4 above), or Access will not allow you to “Create” the relationship.
   7. Save and close the Relationships Window.

 

Optional Verification:  If you want to prove that your relationship changes are now correct and working, try opening the ClubMembers table and change any member’s ProgramID to a non-existent ProgramID, such as 999.  Try to save your change by clicking on any other record in the table.  You should see that Access no longer allows a ClubMember to be assigned to a ProgramID that does not exist in the FitnessPrograms table.  Hit the Esc key to throw away your attempted change, and close ClubMembers table.

 

7.  

Project 3:  Creating Queries
Create queries to analyze the data in the HealthClub database.

1. Create a Query with a Single Criteria and one table. 
   1. On the Create tab, in the Queries group, select Query Design. 
   2. Add the FitnessPrograms table to the query.
   3. Add these fields to the query grid in order:
      1. ProgramType
      2. MonthlyFee
      3. PhysicalRequired?   
   4. In the MonthlyFee field, specify a Criteria to select only programs whose fee is equal to 40.
   5. Click on the Save icon in the Quick Access tool bar, and name this query SelectByMonthlyFee.
   6. Run your query, and check the results. Close the query.

 

2. Create a Query with Multiple criteria and two tables. 
   1. On the Create tab, in the Queries group, select Query Design. 
   2. Add the ClubMembers table to the query.
   3. Add these fields to the query grid in order:
      1. MemberID
      2. FirstName
      3. LastName
      4. City
   4. In the City field:
      1. Specify a criteria of Richmond.
      2. Specify an OR criteria of Chester, using the OR row.   
      3. Specify the Sort as Ascending.
   5. Click on the Save icon in the Quick Access tool bar, and name this query SelectByMultipleCriteria.
   6. Optional Verification:  Run your query, and check the results. Return to Design View.
   7. Add the FitnessProgram table to the query.
   8. In the PhysicalRequired? field, specify criteria of Yes.
   1. Click on the Save icon in the Quick Access toll bar.
   10. Run the query, and check the results.Close the query.

 

 

 

3. Create a Query using OR Criteria and two tables. 
   1. On the Create tab, in the Queries group, select Query Design.
   2. Add the ClubMembers table and the FitnessPrograms table to the query. 
   3. From the FitnessPrograms table, add these fields to the query grid in order:
      1. ProgramType
      2. MonthlyFee
   4. From the ClubMembers table, add these fields to the query grid in order:
      1. FirstName
      2. LastName
      3. City
      4. ExpirationDate
   5. In the MonthlyFee field, specify a criteria of > 35.
   6. In the ExpirationDate field, specify a criteria of >1/1/2014. 
   7. Click on the Save icon in the Quick Access tool bar, and name this query TwoTableORQuery.
   8. Optional Verification:  Run your query to check the results.  Only records with a MonthlyFee greater than 35 and an ExpirationDate greater than 1/1/2014 should appear. Return to Design View.
   1. In the City field, use the OR row to specify an OR criteria of Ashland. 
   10. Click on the Save icon in the Quick Access tool bar.
   11. Run your query to check the results.  In addition to the first group of results, you should also see records with the City of Ashland regardless of whether or not they met the first set of criteria. Close your query.

 

4. Create a query that sorts by two fields and hide a field.
   1. On the Create tab, in the Queries group, select Query Design.
   2. Add the ClubMembers table to the query, and add these fields to the query grid in order:
      1. FirstName
      2. LastName
      3. City
      4. Zip
      5. ExpirationDate
      6. MembershipStatus
   3. In the MembershipStatus field, specify a criteria of Active.
   4. In the City field, specify a criteria of Richmond (using the same criteria row as you did for the MembershipStatus).   
   5. In the Zip field, specify an Ascending Sort.
   6. In the ExpirationDate field, specify an Ascending Sort.
   7. In the MembershipStatus field, uncheck the box in the Show row.
   8. Click on the Save icon in the Quick Access tool bar, and name this query SortAndHideQuery.
   1. Run your query to check the results. Close your query.

 

5. Create a query with a Calculated Field. 
   1. On the Create tab, in the Queries group, select Query Design.
   2. Add the FitnessPrograms and the ClubMembers table to the query.
   3. From the FitnessPrograms table, add these fields to the query grid in order:
      1. ProgramID
      2. ProgramType
      3. MonthlyFee. 
   4. From the ClubMembers table, add these fields to the query grid in order:
      1. FirstName
      2. LastName
      3. MembershipStatus
      4. ExpirationDate
   5. In the MembershipStatus field, specify a criteria of Active.
   6. In the ExpirationDate field, specify a criteria of > 2/1/2014. 
   7. Click on the Save icon in the Quick Access tool bar, and name this query ProposedFee.
   8. Optional Verification:  Run your query to check the results.  Return to Design View. 
   1. Create a new calculated field named ProposedFee that displays the ProposedFee less 10.  (Refer to Section 3.7 in our textbook).  
      1. Type the following into the first blank cell in the field row: ProposedFee:[MonthlyFee]-10
   10. Optional Verification: Run your query to check the results.  Return to Design View. 
   11. Change the format of your new field to Currency. 
       1. Click anywhere in the new field name.

1. 11. 2. Click the Property Sheet button in the Show/Hide group of the Query Tools Design tab.
       3. Click in the empty box next to Format, click the down arrow, and click Currency from the drop-down list.
       4. Close the Property Sheet task pane.
   50. Test your query, return to Design View, and close and save this query as CalculatedFieldQuery.
   1000. Click on the Save icon in the Quick Access tool bar.
   14. Run your query to check the results. Close your query.

 

 

 

Project 4:  Creating a Data Entry Form (10 Points)
 

1. Create a New Form.  (Refer to Activity 3.8 in our textbook)
   1. Click on the ClubMembers table in the Navigation Pane to highlight it.
   2. Click on the Create tab.
   3. Click on the Form button in the Create ribbon.
   4. Click on the Themes button in the Forms Layout Tools Design tab. (You must be in Layout View)
   5. Apply the Facet theme.
   6. Click on the Save icon in the Quick Access tool bar, and save your form with the name ClubMembersForm. Close the form.

 

 

Be sure to close your HealthClub.accdb database and exit (close) the Access program.  Then, return to Canvas and open the Access Unit Exam assignment.  “Attach” your completed HealthClub.accdb database and then “Submit” the assignment for grading. 

That’s it.  Good luck!