Homework answers / question archive / Shelly Cashman Access 2019 | Modules 8-10: SAM Critical Thinking Capstone Project 1c Economic Development Conference Macros, Navigation Forms, Database Administration, Using SQL GETTING STARTED Open the file SC_AC19_CT_CS8-10c_FirstLastName_1

Shelly Cashman Access 2019 | Modules 8-10: SAM Critical Thinking Capstone Project 1c Economic Development Conference Macros, Navigation Forms, Database Administration, Using SQL GETTING STARTED Open the file SC_AC19_CT_CS8-10c_FirstLastName_1

MS Access

Share With

---

Shelly Cashman Access 2019 | Modules 8-10: SAM Critical Thinking Capstone Project 1c

Economic Development Conference

Macros, Navigation Forms, Database Administration, Using SQL

GETTING STARTED

• Open the file SC_AC19_CT_CS8-10c_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_CT_CS8-10c_FirstLastName_2.accdb by changing the “1” to a “2”.
  • 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.
• Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. The Economic Development Professional Organization hosts an annual conference for its members, college students, and other interested persons to share new ideas, discuss problems, and brainstorm solutions. You are a conference coordinator responsible for tracking registrations and for recording income based on advertisers, exhibitors, and sponsors.
   
   Open the Preview Advertisers Contact Report macro, and then update it so that the report opens in Print Preview. Save the change to the macro, and then close the Preview Advertisers Contact Report macro.
2. Create a new macro with two submacros as described below:
    
   1. Add a submacro, using Open Attendees Table as the name.
   2. Edit the submacro to open the Attendees table in Datasheet View and in Edit data mode.
   3. Add a second submacro to the macro, using Open Registration Table as the name for the macro.
   4. Edit the second submacro to open the Registration table in Datasheet View and in Edit data mode.
   5. Save the macro using Open Tables as the macro name.
      
      Confirm that your macro matches Figure 1, and then close the macro.

3. Open the EarlyRegistration table and create the following Before Change data macro for the table:
   
   If [Paid]>[TotalFees] Then
   SetField
   Name [Paid]
   Value = [TotalFees]
   Else If [Paid]<0 Then
   SetField Name [Paid]
   Value = 0
   End If
   
   Confirm that the macro matches Figure 2. Save and close the macro, and then save and close the EarlyRegistration table.

4. Create a Navigation form for the database with the following options:
    
   1. Use the Horizontal Tabs layout.
   2. Add the Advertisers, Exhibitors, and Sponsors Datasheet forms to the Navigation form in that order.
   3. Change the title (in the Form Header section), using Main Navigation Form as the new title.
   4. Save the navigation form using Main Navigation Form as the form name.
      
      Switch to Form View and confirm that your form matches Figure 3. Save and close the Main Navigation Form.

5. Open the Advertisers form and create a UI macro that opens the Advertiser Master Form when a user selects a value in the AdvertiserID column as follows:
   
   SetTempVar
   Name AN
   Expression =AdvertiserID
   OpenForm
   Form Name Advertiser Master Form
   View Form
   Filter Name
   Where Condition =[AdvertiserID]=[TempVars]![AN]
   Data Mode
   Window Mode Dialog
   Remove TempVar
   Name AN
   
   Confirm that the macro actions match Figure 4. Save and close the macro, and then save and close the form.

6. Open the Main Menu Form and update it as follows:
    
   1. Add the Forms List form to the Main Menu Form as the last horizontal tab.
   2. Rename the Forms List tab, using Master Forms as the new name.
   3. Move the Sponsors tab so that it appears first in the list.
      
      Confirm that the form matches Figure 5, and then save and close the form.

7. Open the Open Master Forms form and add a command button to the form in the approximate position shown in Figure 6. The left edge of the button should be at the 2" mark on the horizontal ruler, and the top edge of the button should be at the 2.5" mark on the vertical ruler. Use the following options when creating the command button:
    
   1. Select Miscellaneous as the category and Run Macro as the action.
   2. Select Forms.Open Workshop Master Form as the macro.
   3. Select the Text option, and then enter Open Workshop Master Form as the text.
   4. Name the command button using Open_Workshop_Master_Form as the name.
      
      Save the changes to the form, but do not close it.
8. With the Open Master Forms form still open, complete the following actions:
    
   1. Select the three buttons on the form, and then use the Size/Space menu to adjust the size to To Widest.
   2. Use the Size/Space menu to adjust the spacing of the button controls to Equal Vertical.
   3. Align the buttons to the left. The left edge of the buttons should be at the 2" mark on the horizontal ruler. The top edge of the first button should be at the 1" mark on the vertical ruler. The right edge of the buttons should be at the 4" mark on the horizontal ruler.
      
      Confirm that your form matches Figure 6, and then save and close the Open Master Forms form.

9. 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 below:
    
   1. Create a custom property using Final as the name of the property.
   2. Select Date as the type.
   3. Enter 01/13/2020 as the value.
      
      Confirm that your custom property matches the one shown in Figure 7. (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.)

 

10. 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. (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.)

11. Open the Attendees table, and then create a custom input mask for the AttendeeID field as described below:
     
    1. The AttendeeID field must consist of two letters and three numbers.
    2. All letters should display in uppercase. (Hint: Enter >LL999 as the input mask.)
    3. Save the changes to the table.
12. With the Attendees table still open, 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.
13. Open the Advertisers table and create a multiple-field index as described below:
     
    1. Use ContactName as the index name.
    2. Use the ContactLName field, sorted in descending order, as the first field in the index.
    3. Use the ContactFName field, sorted in ascending order, as the second field in the index.
    4. Save the changes to the table design, and then close the table.
14. Open the EarlyRegistration table and make the following updates:
     
    1. Create a validation rule for the table requiring that the Paid field value is always less than or equal to the TotalFees field value.
    2. Enter Paid cannot exceed TotalFees as the validation text.
       
       Close the Property Sheet, save the changes to the table, and then close the table. (Hint: Because there was a change to the data integrity rules, the "Existing data may not be valid" warning message will appear. Click No and continue saving the table.)
15. 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.)
16. With the Navigation Pane open, switch to viewing database items by the custom category named Supporter Information in the Navigation Pane. Add the Advertiser Master Form and the Exhibitor Master Form to the Supporter Information Forms group. Confirm the Supporter Information Forms group matches Figure 9.
17. Add a new group to the Supporter Information category as described below:
     
    1. Name the new group, using Supporter Reports as the name.
    2. If necessary, move the Supporter Reports group so that it appears between the Supporter Information Forms group and the Unassigned Objects group.
    3. In the Navigation Pane, add the Advertisers Contact Report to the Supporter Reports group in the Navigation Pane to match Figure 9.

18. Create a new query based on the Workshops table as described below:
     
    1. Add all fields from the Workshops table to the SQL Statement of the new query.
    2. Run the query to produce the results shown in Figure 10. There should be four records in the results.
    3. Save the query using WorkshopQuery as the name, and then close the query.

19. Open the ExhibitorQuery in SQL View, and then add the ContactFname field to the SELECT clause (after the ExhibitorName field). Run the query and ensure that the results match those shown in Figure 11. There should be five records in the results. Save and close the ExhibitorQuery.

20. Open the EarlyPaymentsQuery in SQL View. Add a computed field to the SELECT clause (after the Paid field) as described below:
     
    1. The computed field should calculate the remaining payment for the conference by subtracting the Paid field value from the TotalFees field value.
    2. Use Remaining as the name (alias) for this computed field.
    3. Run the query and ensure that the results match those shown in Figure 12. (Hint: your records may be displayed in a different order.) There should be 13 records in the results. Save and close the query.

21. Open the RegistrationCriteriaQuery in SQL View. Add a WHERE clause to the query as the third line in the query, as shown in Figure 13, that restricts retrieval to those registrations where the RegistrationFee is less 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. There should be six records in the results. Save and close the RegistrationCriteriaQuery.

22. Open the AttendeeStatesQuery 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 NY or MA. Run the query and ensure that the results match those shown in Figure 14. There should be six records in the results. Save and close the AttendeeStatesQuery.

23. Open the RegistrationWorkshopQuery 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 RegistrationFee field is equal to 175 and the WorkshopID field is equal to 1. Run the query and ensure that the results match those shown in Figure 15. There should be two records in the results. Save and close the RegistrationWorkshopQuery.

24. Open the MarylandCountQuery in SQL View. Modify the SELECT clause to count the number of attendees who are located in MD. Use the AS clause to set the alias of the function results to StateCount. Run the query and ensure that the results match those shown in Figure 16. In the results, the value in the StateCount column should be 4. Save and close the MarylandCountQuery.

25. Open the JoinQuery in SQL View. Add a WHERE clause that joins the Registration table and the Attendees table. The common field in both tables is AttendeeID.
    
    You will need to qualify the AttendeeID field in the WHERE clause. Run the query and ensure that the results match those shown in Figure 17. (Hint: All rows are not displayed in the figure. The order of the records may differ. There should be 35 records in the results.) Save and close the JoinQuery.

26. Open the SponsorNameQuery in SQL View. Add a caption to the SponsorName field in the SELECT clause. Use FinancialSupporter as the caption for the SponsorName field. Run the query and confirm that the second field in the query displays as "FinancialSupporter", as shown in Figure 18. If necessary, enlarge the size of the caption so that the entire caption is displayed. Save and close the SponsorNameQuery.

27. Open the SortQuery in SQL View. Modify the query to sort the records in ascending order by the AdvertiserName field. Run the query and ensure that the results match those shown in Figure 19. There should be seven records in the results. The advertiser in the first record should be Business Development Solutions, and the last advertiser should be ProLogic. Save and close the SortQuery.

28. Open the GroupingQuery in SQL View. Modify the query by completing the following tasks:
     
    1. Add the State field to the SELECT statement. The State field should appear before the COUNT(AttendeeID) function.
    2. Group the records by the State field.
    3. Sort the records in the State field in ascending order.
       
       Run the query and ensure that the results match those shown in Figure 20. There should be 20 records in the results. Save and close the GroupingQuery.

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 SAM website to submit your completed project.