New Perspectives Access 365 | Modules 1-4: SAM Capstone Project A Merriwell Medical Center
CREATING AND MODIFYING TABLES, QUERIES, FORMS, AND REPORTS
· Excel Projects Help
GETTING STARTED
1. Save the file NP_AC365_CS1-4A_FirstLastName_1.accdb as NP_AC365_CS1-4A_FirstLastName_2.accdb
a. Edit the file name by changing “1” to “2”.
b. If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.
2. To complete this Project, you will also need the following files:
Support_AC365_CS1-4A_CourseTypes.txt
Support_AC365_CS1-4A_Health.png
Support_AC365_CS1-4A_Registrations.xlsx
Support_AC365_CS1-4A_Wellness.accdb
3. With the file NP_AC365_CS1-4A_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table.
a. If the table does not display your name, delete the file and download a new copy.
PROJECT STEPS
1. As a program assistant for Merriwell Medical Center in Atlanta, Georgia, you help to create, maintain, and promote the hospital's wellness programs for patients in Atlanta and the surrounding communities. You are developing an Access database application to manage the programs and their participants. Modify the Members table in Design View as follows to remove an unnecessary field and add others for information you want to track:
a. Delete the Department field, which contains no data.
b. Add a new field after the DateOfBirth field named StartDate with a Date/Time data type.
c. Add another new field after the StartDate field named Email with a Hyperlink data type. Save and close the Members table.
2. Information about wellness programs is stored in a separate Access database. Open the database Support_AC365_CS1-4A_Wellness.accdb, copy the 79 records in the Courses table, and then paste them in the Programs table in the project database. Close the Support_AC365_CS1-4A_Wellness.accdb database.
3. Open the Programs table in Design View, and then set the ProgramID field as the primary key. Save and close the Programs table.
4. Program registration information is stored in an Excel workbook. Use the Import Spreadsheet Wizard to import the data from the Support_AC365_CS1-4A_Registrations.xlsx Excel file as a new table. Choose the RegID field as the primary key. Use Registrations as the name of the new table. Do not save the import steps.
5. Open the Registrations table in Design View and use Short Text as the data type of the ProgTypeID field since the data will not be used in calculations. Save and close the Registrations table.
6. Create a table in Design View as follows to store data about program types:
a. Add a new field named ProgTypeID with the Short Text data type.
b. Set ProgTypeID as the primary key field.
c. Add another new field named ProgDescription and assign it the Short Text data type.
d. Set the Field Size property to 25 for the ProgDescription field.
e. Save the table using ProgramTypes as the name, open it in Datasheet View to confirm it contains no data, and then close the ProgramTypes table.
7. The program type data is stored in a separate text file. Use the Import Wizard to append the records from the Support_AC365_CS1-4A_CourseTypes.txt text file to the ProgramTypes table. Select the Delimited format and a comma as the delimiter. Do not save the import steps. Open the ProgramTypes table in Datasheet View to confirm it contains eight records of imported data, and then close the table.
8. Modify the Programs table in Datasheet View as follows to update the data:
a. Delete the record for ProgramID 11, Diabetes Management in Roswell.
b. Edit Alpharetta to be Virtual for ProgramID 9, Prenatal Prep.
c. Enter a new record into the table using the information in Table 1, and then close the Programs table.
Table 1: New Record for the Programs Table
ProgramID | ProgramTitle | ProgramType | Location | Fee [AutoNumber] | Stretching | 700 | Atlanta | 10
9. Open the Relationships window and then build the following relationships so you can create queries, forms, and reports based on the tables:
a. Create a one-to-many relationship between the primary Members table and the related Registrations table using the MemberID field as the common field. Enforce inferential integrity on the relationship.
b. Create a one-to-many relationship between the primary ProgramTypes table and the related Registrations table using the ProgTypeID field as the common field. Enforce inferential integrity on the relationship.
c. Create a one-to-many relationship between the primary ProgramTypes table and the related Programs table using the ProgTypeID field in the ProgramTypes table and the ProgramType field in the Programs table as the common field. Enforce inferential integrity on the relationship. Save and close the Relationships window.
10. Use the Simple Query Wizard to create a new query as follows to list members and the wellness programs they are using:
a. Use the MemberID, FirstName, and LastName fields from the Members table.
b. Use the ProgTypeID field from the Registrations table.
c. Use the ProgDescription field from the ProgramTypes table.
d. Create a detail query.
e. Use MemberPrograms as the title for the query. Save and close the query.
11. Create a query in Design View using the ProgramTypes and Programs tables as follows to list wellness program information:
a. Select the ProgTypeID and ProgDescription fields from the ProgramTypes table and the ProgramTitle, Location, and Fee fields from the Programs table.
b. Add criteria to select only those records where the Fee is greater than 0.
c. Save the query, using ProgramsWithFees as the name, switch to Datasheet View, resize the ProgramTitle field to its best fit as shown in Figure 1, and then save and close the ProgramsWithFees query.
Figure 1: ProgramsWithFees Query in Datasheet View
12. Modify the MemberPrograms query in Design View as follows to select two types of programs and sort the records by program description and member last name:
a. Move the ProgDescription field to the left of the FirstName field.
b. Add criteria to select all records with First Aid or Lifestyle as the ProgDescription.
c. Sort the records in ascending order by ProgDescription, and then by LastName. Run the query to display it in Datasheet View as shown in Figure 2, and then save and close the MemberPrograms query.
Figure 2: MemberPrograms Query in Datasheet View
13. Copy the MemberPrograms query and paste it in the Navigation pane using MemberAge as the new name.
14. Modify the MemberAge query in Design View as follows to select members who are younger than 25 years old as of January 1, 2029:
a. Remove the criteria from the ProgDescription field, but do not change the sort order.
b. Delete the ProgTypeID field from the query design grid.
c. Insert the following calculated field in the first empty cell of the query design grid to calculate each member's age as of January 1, 2029: Age: Int((#1/1/2029#-[DateOfBirth])/365.25)
d. Add criteria to the calculated Age field to select only those members who are less than 25 years old. Run the query to display it in Datasheet View as shown in Figure 3, and then save and close it.
Figure 3: MemberAge Query in Datasheet View
15. Create a form using the Form Wizard as follows for to enter member information:
a. Select all the fields from the Members table.
b. Choose a Columnar layout.
c. Use MemberEntry as the title. Close the MemberEntry form.
16. Create a form with a subform using the Form Wizard as follows to view and enter program data by program type:
a. Select all the fields from the ProgramTypes table.
b. Select the ProgramTitle, Location, and Fee fields from the Programs table.
c. View the data by ProgramTypes and select a Datasheet layout for the subform.
d. Use ProgramsByType as the title of the main form and use ProgramSubform as the title of the subform. Save the ProgramsByType form.
17. Modify the ProgramsByType form in Layout View as follows to make the labels easier to read and to display all the data:
a. Use Programs by Type as the label in the Form Header section of the form.
b. Use Program Type as the label for the ProgTypeID field.
c. Use Program Description as the label for the ProgDescription field.
d. Delete the Program label.
e. Resize the ProgTypeID text box so it has the same width as the ProgDescription text box.
f. Resize the Location field in the subform to its best fit. Display the ProgramsByType form in Form View, as shown in Figure 4, and then save and close it.
Figure 4: ProgramsByType Form in Form View
18. Create a report using the Report Wizard as follows to list members and the programs they have registered for:
a. Select all the fields from the ProgramTypes table.
b. Select the LastName field from the Members table.
c. Select the RegDate field from the Registrations table.
d. View the records by ProgramTypes.
e. Do not add any grouping levels.
f. Sort the records in ascending order by LastName.
g. Use a Stepped layout and a Portrait orientation.
h. Use MemberRegistrations as the title. Preview, save, and close the report.
19. Open the ProgramList report in Layout View and add conditional formatting so that any Fee value greater than or equal to 25 is formatted using Light Blue 2 background color (5th column, 3rd row in the Standard Colors palette). Save the ProgramList report.
20. With the ProgramList report still open in Layout View, modify it as follows to improve its appearance:
a. Apply the Office theme to this object only.
b. Change the font color of the Program List label in the Report Header section to Blue, Accent 1 (5th column, 1st row in the Theme Colors palette).
c. Insert the picture Support_AC365_CS1-4A_Health.png as a logo in the Report Header. Save and display the report in Print Preview as shown in Figure 5, and then close it.
Figure 5: ProgramList Report in Print Preview
Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the website to submit your completed project.