Shelly Cashman Access 2016 | Modules 8-10: SAM Capstone Project 1a
Shelly Cashman Access 2016 | Modules 8-10: SAM Capstone Project 1a
Carpenter Family Camp
Macros, navigation forms, database administration, using sql
GETTING STARTED
Open the file SC_AC16_CS8-10a_FirstLastName_1.accdb, available for download from the SAM website.
Save the file as SC_AC16_CS8-10a_FirstLastName_2.accdb by changing the “1” to a “2”.
0. If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
PROJECT STEPS
Open the Preview Parent Contact Report macro in Design View. Change the View argument to Print Preview. Save the change to the macro, and then close the Preview Parent Contact Report macro.
Create a new macro with two submacros by following the directions below:
a. When the Macro Design window opens, display the Action Catalog.
b. Add the first submacro to the macro, using Open Camper Table as the name for the submacro.
c. In the first submacro, use the OpenTable action to open the Camper table in Datasheet View and in Edit data mode.
d. Add a second submacro to the macro, using Open Reservation Table as the name for the macro.
e. In the second submacro, use the OpenTable action to open the Reservation table in Datasheet View and in Edit data mode.
f. Save the macro with the name OpenTables.
Confirm that your macro matches Figure 1 on the next page, and then close the macro.
Figure 1: OpenTables Macro
Open the Payments table in Datasheet View and create a data macro for the table by performing the following tasks:
g. Click the Before Change button on the Table Tools tab.
h. Create the macro shown in Figure 2 on the next page.
Save and close the macro, then save and close the Payments table.
Figure 2: Data Macro for Before Change Event
Create a Navigation form for the database with the following options:
i. Use the Horizontal Tabs layout.
j. Add the Camper, Parent, and Reservation Datasheet forms to the Navigation form in that order.
k. Change the title (in the Form Header) to Main Navigation Form. (Hint: Do not type the period.)
l. Save the navigation form with the name Main Navigation Form. (Hint: Do not type the period.)
Switch to Form View, and confirm that your form matches Figure 3 on the next page. Save the form and close the Main Navigation Form form.
Figure 3: Main Navigation Form – Form View
Open the Counselor form in Datasheet View and perform the following tasks to create a UI macro for the form:
m. Select the CounselorID column.
n. Click the Property Sheet button.
o. Click the Build button for the On Click event and select Macro Builder.
p. Create a macro that opens the Counselor Master Form when a user selects a value in the CounselorID column. The macro actions are shown in Figure 4 on the next page.
Save and close the macro, then save and close the form.
Figure 4: UI Macro Associated with On Click Event in the Counselor Form
Open the Main Menu Form in Layout View and perform the following tasks:
q. Add the Forms List form to the Main Menu Form as the last horizontal tab.
r. Rename the Forms List tab as Master Forms. (Hint: Do not type Period.)
s. Move the Counselor tab so that it appears first in the list.
Confirm that the form matches Figure 5 on the following page and then save and close the form.
Figure 5: Main Menu Form – Form View
Open the Open Master Forms form in Design View and add a command button to the form in the approximate position shown in Figure 6 on the following page. Ensure that Use Control Wizardsis selected, and use the following options when creating the command button using the Command Button wizard:
t. Select Miscellaneous as the category and Run Macro as the action.
u. Select Forms.Open Parent Master Form as the macro.
v. Select the Text option and enter the text Open Parent Master Form. (Hint: Do not type Period.)
w. Name the command button Open_Parent_Master_Form. (Hint: Do not type the period.)
Save the changes to the form but do not close it.
With the Open Master Forms form still open in Design View, complete the following actions:
x. Select the three buttons on the form and use the Size/Space menu to adjust the size to Widest.
y. Use the Size/Space menu to adjust the spacing of the button controls to Equal Vertical.
z. Align the buttons to the Left.
Confirm that your form matches Figure 6 on the following page. Save and and close the Open Master Forms form.
Figure 6: Open Master Forms – Design View
Open the database in Backstage View, and then click the View and edit database properties link. When the Properties dialog box displays, create a custom property for the database as described on the following page:
aa. Create a custom property named Confirm. (Hint: Do not type the period.)
ab. Select Date as the type.
ac. Enter 05/10/2017 as the value.
Confirm that your custom property matches the one shown in Figure 7 on the following page. (Hint: Your database may contain other properties, depending on the version of Access used to open the file or the method by which the file was downloaded from SAM. Ignore these additional properties.) Save and close the Properties box.
Figure 7: Custom Properties Tab in the Properties Dialog Box
With the database still open in Backstage View, click the Options tab. Select the Main Menu Form as the display form for the database, as shown in Figure 8 on the following page. (Hint: The display form is sometimes referred to as the startup form.)
Save the changes to the options. (Hint: When the Access dialog box appears and displays the message, “You must close and reopen the current database for the specified option to take effect.”, click the OK button.)
Figure 8: Access Options Dialog Box
Open the Counselor table in Design View, and then create a custom input mask for the CounselorID field as described below:
ad. The CounselorID field must consist of two letters and two numbers.
ae. All letters should display in uppercase. (Hint: Enter >LL99 as the input mask.)
af. Save the changes to the table.
With the Counselor table still open in Design View, create a single-field index on the LastName field. The index should allow duplicate values. Save the changes to the table design and then close the table.
Open the Parent table in Design view and create a multiple-field index as described below:
ag. Name the index ParentLocation. (Hint: Do not type Period.)
ah. Use the State field, sorted in Descending order, as the first field in the index.
ai. Use the City field, sorted in Ascending order, as the second field in the index.
aj. Save the changes to the table design, and then close the table.
Open the Payments table in Design View, and then open the Property Sheet for the table and make the following updates:
ak. Create a validation rule for the table requiring that the Deposit field value is always less that or equal to the TotalFees field value. (Hint: Enter [Deposit]<=[TotalFees] as the validation rule.)
al. Enter Deposit cannot exceed TotalFees as the validation text.
Close the Property Sheet, save the changes to the table, then close the table. (Hint: Because there was a change to data integrity rules, the “existing data may not be valid” warning message will appear. Click No and continue saving the table.)
Create a blank form based on the 1 Right application part. (Hint: The blank form will appear in the Navigation Pane as a forms object named “SingleOneColumnRightLabels” and is saved automatically.)
With the Navigation Pane open, switch to viewing database items by the custom category named Family Information in the Navigation Pane. Add the following Parent Master Form and the Camper Master Form to Family Information Forms group. Confirm the Family Information Forms group matches Figure 9 on the following page.
Add a new group to the Family Information category as described below:
am. Name the new group Family Reports. (Hint: Do not type Period.)
an. If necessary, move the Family Reports group so that it appears between the Family Information Forms group and the Unassigned Objects group.
ao. In the Navigation Pane, add the Parent Contact Report to the Family Reports group in the Navigation Pane to match Figure 9 on the following page.
Figure 9: Navigation Pane Viewed by Family Information Category
Create a new query in SQL View based on the Adventure table:
ap. Add all fields from the Adventure table to the new query using the asterisk (*) in the Select clause.
aq. Be sure to end the SQL command with a semicolon (;).
ar. Run the query to produce the results shown in Figure 10 below.
as. Save the query as AdventureQuery, and then close the query.
Figure 10: AdventureQuery Results
Open the ActivityQuery in SQL View, and then add the Audience field to the SELECT clause (after the Description field.) Run the query, and ensure that the results match those shown in Figure 11 on the following page. Save and close the the ActivityQuery.
Figure 11: ActivityQuery Results
Open the PaymentsQuery in SQL View. Add a computed field to the SELECT clause (after the Deposit field) as described below:
at. The computed field should calculate the remaining payment for the camp session by subtracting the Deposit field value from the TotalFees field value. (Hint: Enter TotalFees-Deposit as the computation.)
au. Use Remaining as the name (alias) for this computed field. (Hint: Use the AS clause.)
av. Run the query and ensure that the results match those shown in Figure 12 on the following page. (Hint: Your records may be in a different order.) Save and close the query.
Figure 12: PaymentsQuery Results
Open the ReservationsCriteriaQuery in SQL View. Add a WHERE clause to the query in the position shown in Figure 13 below that restricts retrieval to only those reservations where the lodgingfee is greater than 150. (Hint: Figure 13 demonstrates the proper location for the WHERE clause, but it does not show the criteria that should be included in the WHERE clause.) Run the query, and check your results. Save and close the ReservationsCriteriaQuery.
Figure 13: ReservationsCriteriaQuery in SQL View
Open the ParentStatesQuery in SQL View. Add an OR clause to the WHERE clause in the query so that the query retrieves only those records in which the State field is equal to PA or NJ. (Hint: Currently, the query only retrieves records where the State field is equal to PA.) Run the query, and ensure that the results match those shown in Figure 14 on the next page. Save and close the ParentStatesQuery.
Figure 14: ParentStatesQuery Results
Open the ReservationSessionQuery in SQL View. Add an AND clause to the WHERE clause in the query so that the query retrieves only those records in which the SessionID field is equal to 2 and the LodgingFee field is equal to 0. (Hint: Currently the query only retrieves records in which the SessionID field is equal to 2.) Run the query, and ensure that the results match those shown in Figure 15 below. Save and close the ReservationSessionQuery.
Figure 15: ReservationSessionQuery Results
Open the MassachusettsCountQuery in SQL View. Modify the SELECT clause to count the number of parents that are located in MA. (Hint: Change the SELECT clause to COUNT(ParentID). Use the AS clause to set the alias of the function result to StateCount. Run the query and ensure that the results match those shown in Figure 16 below. Save and close the MassachusettsCountQuery.
Figure 16: MassachusettsCountQuery Results
Open the JoinQuery in SQL View. Add a WHERE clause that joins the Reservation table and the Parent table. The common field in both tables is ParentID. You will need to qualify the ParentID field in the WHERE clause. Run the query and ensure that the results match those shown in Figure 17 below. (Hint: All rows are not displayed in the figure. The order of the records may differ. There should be 26 records in the result.) Save and close the JoinQuery.
Figure 17: JoinQuery Results
Open the CounselorSkillsQuery in SQL View. Add a caption to the Specialty field in the SELECT clause. (Hint: Use the AS clause.) Use SpecialSkills as the caption for the Specialty field. Run the query, and confirm that the last field in the query displays as SpecialSkills, as shown in Figure 18 on the next page. If necessary, enlarge the size of the caption so that the entire caption is displayed. Save and close the CounselorSkillsQuery.
Figure 18: CounselorSkillsQuery Results
Open the SortQuery in SQL View. Modify the query to sort the records in ascending order by the State field. (Hint: Use the ORDER BY clause.) Run the query and ensure that the results match those shown in Figure 19 below. Save and close the SortQuery.
Figure 19: SortQuery Results
Open the GroupingQuery in SQL View. Modify the query by completing the following tasks:
aw. Add the State field to the SELECT statement. The State field should appear before the COUNT(ParentID) function.
ax. Group the records by the State field.
ay. Sort the records by the State field in Ascending order.
Run the query, and ensure that the results match those shown in Figure 20 below. Save and close the GroupingQuery.
Figure 20: GroupingQuery Results
Save and close any open database objects. Compact and repair your database, and then exit Access. Follow the directions on the SAM website to submit your completed project.
Related Services