Fill This Form To Receive Instant Help

Help in Homework
trustpilot ratings
google ratings


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

Shelly Cashman Access 2019 | Modules 8-10: SAM Capstone Project 1a Economic Development Conference Macros, Navigation Forms, Database Administration, Using SQL GETTING STARTED Open the file SC_AC19_CS8-10a_FirstLastName_1

MS Access

Shelly Cashman Access 2019 | Modules 8-10: SAM Capstone Project 1a

Economic Development Conference

Macros, Navigation Forms, Database Administration, Using SQL

*GETTING STARTED

  • Open the file SC_AC19_CS8-10a_FirstLastName_1.accdb, available for download from the SAM website.
  • Save the file as SC_AC19_CS8-10a_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 in Design View. Change the View argument to 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 by following the directions below:
     
    1. When the Macro design window opens, display the Action Catalog.
    2. Add the first submacro to the macro, using Open Attendees Table as the name for the submacro.
    3. In the first submacro, use the OpenTable action to open the Attendees table in Datasheet View and in Edit data mode.
    4. Add a second submacro to the macro, using Open Registration Table as the name for the macro.
    5. In the second submacro, use the OpenTable action to open the Registration table in Datasheet View and in Edit data mode.
    6. Save the macro using Open Tables as the macro name.

      Confirm that your macro matches Figure 1, and then close the macro.

* Figure 1: Open Tables Macro

 

The figure shows the Open Tables macro in Design View. There are two submacros. The first submacro is named Open Attendees Table and uses the OpenTable action to open the Attendees table in Datasheet View and Edit mode. The second submacro is named Open Registration Table and uses the OpenTable action to open the Registration table in Datasheet View and Edit mode.

 

  1. Open the EarlyRegistration table in Datasheet View and create a data macro for the table as follows:
     
    1. Click the Before Change button on the Table tools tab.
      When the Macro window opens, enter the data macro shown below:

      If [Paid]>[TotalFees] Then    
      SetField    
      Name [Paid]     
      Value = [TotalFees]
      Else If [Paid]<0 Then    
      SetField    
      Name [Paid]     
      Value = 0
      End If

       
    2. Confirm that the macro matches Figure 2. Save and close the macro, then save and close the EarlyRegistration table.

* Figure 2: Data Macro for Before Change Event

 

The figure shows the data macro for the EarlyRegistration table that is associated with the Before Change event. The macro uses the IF command to determine if the Paid amount is greater than the TotalFees amount. If that is true, then the SetField action sets the value in the Paid field to the same value in the TotalFees amount field. An Else IF clause checks the value in the Paid field to determine if it is less than zero. If that is true, then the SetField action set the value in the Paid field to zero.

 

  1. 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) 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 the form and close the Main Navigation Form.

* Figure 3: Main Navigation Form—Form View

 

The figure shows the Main Navigation Form in form view and with a horizontal tabs layout. The Advertisers, Exhibitors, and Sponsors datasheet forms have been added to the navigation form in that order from left to right. The title of the form has been changed to Main Navigation Form. Save the navigation form using Main Navigation Form as the form name. The Advertisers datasheet form displays in the figure.

 

  1. Open the Advertisers form in Datasheet View and perform the following tasks to create a UI macro for the form:
     
    1. Select the AdvertiserID column.
    2. Click the Property Sheet button.
    3. Click the Build button for the On Click event and select Macro Builder.
    4. Create a macro that opens the Advertiser Master Form when a user selects a value in the AdvertiserID column. The macro actions are shown below:

      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, then save and close the form.

* Figure 4: UI Macro Associated with On Click Event in the Advertisers Form

 

The figure shows the UI macro associated with the On Click event for the Advertisers datasheet form. The first action, SetTempVar assigns the value AN to Name and AdvertiserID to the Expression. The OpenForm action opens the Advertiser Master Form in Form View where the AdvertiserID equals the TempVars AN. The Window Mode for the Advertiser Master Form is Dialog. The last action, Remove TempVar deletes the AN temporary variable.

 

  1. Open the Main Menu Form in Layout View and perform the following tasks:
     
    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.

      Switch to Form View, confirm that the form matches Figure 5, then save and close the form.

* Figure 5: Main Menu Form—Form View

 

The figure shows the Main Menu Form in Form View. The Forms List form has been added to the form as the last tab on the right. The tab name has been changed from Forms List to Master Forms. The Sponsors datasheet has been moved to be the first tab. The new order of horizontal tabs from left to right is: Sponsors, Advertisers, Exhibitors, Master Forms.

 

  1. 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. The left-edge of the button is at the 2" mark on the horizontal ruler and the top-edge of the button is at the 2.5" mark on the vertical ruler. Ensure that Use Control Wizards is selected and use the following options when creating the command button using the Command Button wizard:
     
    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 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.
  2. With the Open Master Forms form still open in Design View, complete the following actions:
     
    1. Select the three buttons on the form and use the Size/Space menu to adjust the size 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 is at the 4" mark on the horizontal ruler.

      Confirm that your form matches Figure 6. Save and close the Open Master Forms form.

* Figure 6: Open Master Forms Form—Design View

 

The figure shows the Open Master Forms form in Design View. A button with the text, Open Workshop Master Form has been added to the form below the Open Registration Master Form button. The button uses the Forms.Open Workshop Master Form submacro. The name Open_Workshop_Master_Form has been assigned to the button. The three buttons have been adjusted to the widest size and there is equal vertical spacing between the buttons. The buttons are aligned to the left. The left edge of each button is at the 2" mark on the horizontal ruler. The top-edge of the first button is at the 1" mark on the vertical ruler.

 

  1. 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.)

* Figure 7: Custom Properties Tab in the Properties Dialog Box

 

The figure shows the Properties dialog box with the Custom tab displayed. The Properties box displays Final in the Name column, 01/13/2020 in the Value column, and Date in the Type column.

 

  1. 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.).

* Figure 8: Access Options Dialog Box

 

The figure shows the Access Options dialog box with the options for the current database displayed. Main Menu Form displays in the Display Form box.

 

  1. Open the Attendees table in Design View, 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.
  2. With the Attendees 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.
  3. Open the Advertisers table in Design View and create a multiple-field index as described below:
     
    1. Name the index, using ContactName as the 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.
  4. Open the EarlyRegistration table in Design View, and then open the Property Sheet for the 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. (Hint: Enter [Paid]<=[TotalFees] as the validation rule.)
    2. Enter Paid 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.)
  5. 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.)
  6. 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.
  7. 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.

* Figure 9: Navigation Pane Viewed by Supporter Information Category

 

The figure shows the Navigation Pane viewed by the Supporter Information category. The Supporter Information Forms group lists two forms: Advertiser Master Form and Exhibitor Master Form. The Supporters Report group lists one report, Advertisers Contact Report.

 

  1. Create a new query in SQL View based on the Workshops table:
     
    1. Add all fields from the Workshops table to the new query using the asterisk (*) in the Select clause.
    2. Be sure to end the SQL command with a semicolon (;).
    3. Run the query to produce the results shown in Figure 10. There should be four records in the result.
    4. Save the query using WorkshopQuery as the name, and then close the query.

* Figure 10: WorkshopQuery Results

 

The figure shows the WorkshopQuery results. WorkshopID is the first field in the results and Description is the second field. The query retrieved four records.

 

  1. 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 result. Save and close the ExhibitorQuery.

* Figure 11: ExhibitorQuery Results

 

The figure shows the ExhibitorQuery results. The ContactFname field has been added to the query immediately before the ContactLname field. The column order is ExhibitorID, ExhibitorName, ContactFnane, ContactLname, and BoothCost. The query retrieved five records.

 

  1. 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. (Hint: Enter TotalFees-Paid as the computation.)
    2. Use Remaining as the name (alias) for this computed field. (Hint: Use the AS clause.)
    3. Run the query and ensure that the results match those shown in Figure 12. (Hint: your records may be in a different order.) There should be 13 records in the result. Save and close the query.

* Figure 12: EarlyPaymentsQuery Results

 

The figure shows the EarlyPaymentsQuery results. The computed field, Remaining is the last column in the query. The query retrieved 13 records.

 

  1. 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 result. Save and close the RegistrationCriteriaQuery.

* Figure 13: RegistrationCriteriaQuery in SQL View

 

The figure shows part of the SQL statement to find all records where the registration fee amount is less than 150. The WHERE clause must be completed.

 

  1. 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. (Hint: Currently, the query only retrieves records where the State field is equal to NY.) Run the query, and ensure that the results match those shown in Figure 14. There should be six records in the result. Save and close the AttendeeStatesQuery.

* Figure 14: AttendeeStatesQuery Results

 

The figure shows the AttendeeStatesQuery results. An OR clause was added to find all attendees who reside in NY or MA. The query retrieved six records.

 

  1. 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. (Hint: Currently, the query only retrieves records in which the RegistrationFee field is equal to 175.) Run the query, and ensure that the results match those shown in Figure 15. There should be two records in the result. Save and close the RegistrationWorkshopQuery.

* Figure 15: RegistrationWorkshopQuery Results

 

The figure shows the RegistrationWorkshopQuery results. An AND clause was added to find all records where the registration fee is $175 and the workshopID is 1. The query retrieved two records.

 

  1. Open the MarylandCountQuery in SQL View. Modify the SELECT clause to count the number of attendees that are located in MD. (Hint: Change the SELECT clause to COUNT(AttendeeID).) 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. The value in the StateCount column of the result should be four. Save and close the MarylandCountQuery.

* Figure 16: MarylandCountQuery Result

 

The figure shows MarylandCountQuery result. The COUNT function was used to find the total number of attendees who live in Maryland and the AS clause was used to change the column name to StateCount. The figure shows a total of four customers in Maryland.

 

  1. 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 that 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 result.) Save and close the JoinQuery.

* Figure 17: JoinQuery Results

 

The figure shows the JoinQuery results. The query is the result of joining the Registration and Attendees tables. The query retrieved 35 records but only a portion of the records are displayed.

 

  1. Open the SponsorNameQuery in SQL View. Add a caption to the SponsorName field in the SELECT clause. (Hint: Use the AS 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.

* Figure 18: SponsorNameQuery Results

 

The figure shows the SponsorNameQuery results. The name for the SponsorName field has been changed to FinancialSupporter and the complete caption displays in the query result.

 

  1. Open the SortQuery in SQL View. Modify the query to sort the records in ascending order by the AdvertiserName field. (Hint: Use the ORDER BY clause.) Run the query and ensure that the results match those shown in Figure 19. There should be seven records in the result. The advertiser on the first record should be Business Development Solutions and the last advertiser should be ProLogic. Save and close the SortQuery.

* Figure 19: SortQuery Results

 

The figure shows the SortQuery results. Advertiser records are displayed in ascending order by advertiser name. Business Development Solutions is the name on the first record and ProLogic is the name on the last record.

 

  1. 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 result. Save and close the GroupingQuery.

* Figure 20: GroupingQuery Results

 

The figure shows GroupingQuery results. The first column in the results is the State field and the second column is the TotalAttendees grouped by that state. The records are sorted in ascending order by state. CA is the state on the first record and WI is the state on the last record.

 

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.

Option 1

Low Cost Option
Download this past answer in few clicks

19.99 USD

PURCHASE SOLUTION

Already member?


Option 2

Custom new solution created by our subject matter experts

GET A QUOTE