Shelly Cashman Access 2019 | Modules 1-3: SAM Capstone Project 1a Economic Development Conference Creating Tables, Queries, Forms, and Reports in a Database     GETTING STARTED Open the file SC_AC19_CS1-3a_FirstLastName_1

Subject:MS AccessPrice:19.99 Bought3

Share With

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

Economic Development Conference

Creating Tables, Queries, Forms, and Reports in a Database

• GETTING STARTED

• Open the file SC_AC19_CS1-3a_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_CS1-3a_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.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
  • Support_AC19_CS1-3a_Advertisers.xlsx
• 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 registration.
   
   Create a new table in Datasheet View with the following options:
    
   1. Rename the default primary key ID field to AdvertiserID and change the data type to Short Text. (Hint: AdvertiserID should remain the primary key.)
   2. Add a new field with the name AdvertiserName and the Short Text data type.
   3. Add a third field to the table with the name ContactLName and the Short Text data type.
   4. Add a fourth field to the table with the name ContactFName and the Short Text data type.
   5. Add a fifth field to the table with the name Cost and the Currency data type.
      
      Save the table with the name Advertisers and close the table.
2. Use the Import Spreadsheet Wizard to import the data from the Support_AC19_CS1-3a_Advertisers.xlsx file and append to the Advertisers table. Do not save the import steps.
3. Create a new table in Design View with the following options:
   1. Add a field with the name SponsorID and the Short Text data type. Change the field size to 4.
   2. Set SponsorID as the primary key for the table.
   3. Add a field with the name SponsorName and the Short Text data type.
   4. Add a third field with the name Amount and the Currency data type.
      
      Save the table with the name Sponsors and switch to Datasheet View.
4. With the Sponsors table open in Datasheet View, add the record shown in Table 1. Resize the SponsorName field so that the field value is completely visible. Save the changes and close the Sponsors table.

5. Open the Attendees table in Datasheet View and delete the record for the attendee with an AttendeeID of TL001.
6. Switch to Design View for the Attendees table and make the following changes:
   1. Change the name of the Street field using Address as the new field name.
   2. Add a new field to the end of the table with the name CellPhone and the Short Text data type.
      
      Save the changes and close the table.
7. Use the Simple Query Wizard to create a query based on the Attendees table with the following options:
   1. Include the AttendeeID, FirstName, LastName, and CellPhone fields in that order.
   2. Save the query using AttendeeContactInfo as the query name.
      
      View the query results and then close the query.
8. Use the Form Wizard to create a form for the Advertisers table with the following options:
   1. Include the AdvertiserID, AdvertiserName, and Cost fields.
   2. Select the Columnar layout.
   3. Assign the title AdvertiserCostData to the form.
      
      View the form and then close it.
9. Use the Report Wizard to create a report for the Attendees table with the following options:
   1. Include the LastName, FirstName, City, and State fields.
   2. Do not add any grouping levels.
   3. Sort the records in ascending order by LastName.
   4. Select the Tabular layout and the Portrait orientation.
   5. Assign the title AttendeeLocations to the report.
      
      View the report and then close it.
10. Create a query in Design View for the Attendees table with the following options:
    1. Include the LastName, FirstName, City, and State fields in the query design grid.
    2. Sort the records in ascending order by LastName.
    3. Add criteria to select only those records where Philadelphia is the City.
    4. Save the query using PhiladelphiaAttendees as the query name.
       
       Open the query in Datasheet View and then close it.
11. Open the GuestAndWorkshop query in Design View and make the following changes to the query:
    1. Add the RegistrationFee field to the query design grid immediately following the AttendeeID field.
    2. Add criteria to select only those records where the GuestFee field value equal 0 and the WorkshopFee equal 0.
       
       Open the query in Datasheet View and confirm that 16 records appear in the GuestAndWorkshop query results. Close the query, saving if necessary.
12. Create a query in Design View based on the Attendees and Registration tables with the following options:
    1. Add the Attendees table and the Registration table to the Design window. The tables should be automatically joined by the common field AttendeeID.
    2. Add the FirstName and LastName fields from the Attendees table to the query design grid
    3. Add the RegistrationID, RegistrationFee, and WorkshopFee fields from the Registration table.
    4. Save the query using AttendeeRegistration as the query name.
       
       Open the query in Datasheet View and then close it.
13. Open the OrStates query in Design View and make the following changes to the query:
    1. Move the State field so that it is the first field in the query design grid.
    2. Add criteria to select only those records where the value in the State field is NY or NJ.
    3. Sort the records in ascending order by State and then by LastName.
       
       Open the query in Datasheet View and confirm that it matches Figure 1. Close the query, saving if necessary.

14. Use the Crosstab Query Wizard to create a crosstab based on the Attendees table with the following options:
    1. Use the State field for the row headings.
    2. Use the City field for the column headings.
    3. Use a Count of AttendeeID as the calculated value for each row and column intersection.
    4. Save the query using State_CityCrosstab as the query name.
       
       View the query and then close it.
15. Open the AttendeeFirstName query in Design View and add criteria to select only those records where the FirstName field value begins with Lau followed by any other letters. Save the changes to the query. Open the query in Datasheet View, confirm that two records appear in the query results, and then close it.
16. Open the StateAttendees query in Design View. Add parameter criteria to the State field to replace the current "PA" criteria. The new parameter criteria should prompt the user with Enter desired state as the text. Save the query, then view it in Datasheet View. Enter IL when prompted. Confirm that three records appear in the query results and then close it.
17. Open the TotalFees query in Design View. Modify the query by creating a calculated field that sums the total of RegistrationFee, GuestFee, and WorkshopFee. Use TotalFees as the name of the calculated field. Save the query. Open the query in Datasheet View and then close it.
18. Open the ConferenceFees query in Design View. Modify the query by sorting the records in descending order by the calculated field ConferenceFees and display only the top 5% of the records. Save the query. Open the query in Datasheet View and then close it.
19. Open the SortedStates query in Design View. Modify the query to sort the State field in ascending order. Each state should appear only once. Save the query. Open the query in Datasheet View and then close it.
20. Create a query in Design View for the Registration table with the following options:
    1. Add the RegistrationFee, GuestFee, and WorkshopFee fields to the query design grid.
    2. Add the Total row to the query design grid.
    3. Calculate the sum of each of the three fields.
    4. Save the query using AggregateFees as the query name.
       
       Open the query in Datasheet View, confirm that it matches Figure 2, and then close it.

21. Open the Exhibitors table in Design View and perform the following tasks:
    1. Make ExhibitorID the primary key of the table.
    2. Change the field size of the ExhibitorID field to 4.
    3. Change the data type for the BoothCost field to Currency with 0 decimal places.
    4. Add the text Basic cost to rent a 10 x 10 booth as the description for the BoothCost field.
       
       Save the changes to the table and close it. (Hint: Because a field size was reduced, a warning message appears asking if you want to test the data, The data is valid, so ignore this message and continue saving the table.)
22. Open the Attendees table in Design View and perform the following tasks:
    1. Add a field with the name Status to the end of the table.
    2. Select LookupWizard at the data type.
    3. Select the option to type in your own values.
    4. Type in the following three values (in the order shown) as the list of possible values for the field: Member, NonMember, and Student. Limit the field values to only the items in the list and do not allow multiple values for the field.
       
       Save the changes to the table and close it.
23. Most of the attendees are members of the Economic Development Professional Organization. Use an Update query to update the values in the Status field of the Attendees table to Member for all records. Run the query and save it using UpdateStatus as the name of the query.
     
24. Open the Attendees table in Datasheet View and change the Status value for the attendee with an AttendeeID of CB001 (Cynthia Brown) to Student. Add the caption PostalCode to the ZIP field. Close the table.
25. Create a split form for the Registration table. Save the form using Registration Split Form as the form name. Close the form.
26. The PotentialDonors table contains records that should be appended to the Sponsors table. Create an Append query with the following options:
    1. Select all the fields from the PotentialDonors table in the same order that they are listed in the field list.
    2. Select Sponsors as the destination table in the Append dialog box.
    3. Save the query using AppendPotentialDonors as the name, run it, and then close it. The query should append 10 records.
27. Open the Relationships window and add the Attendees table and the Registration table to the Relationships window. Create a relationship between the AttendeeID field in the Attendees table and the AttendeeID field in the Registration table. Make the relationship enforce referential integrity. Save the relationship and close the window.
28. Use the Find Unmatched Query Wizard to find attendees who do not have a matching registration.
    1. Select the Attendees table as the table to display in the query results.
    2. Select the Registration table as the related table.
    3. Select the AttendeeID as the common field in both tables.
    4. Display all fields in the query result.
    5. Save the query using UnmatchedRegistration as the query name.
       
       View the query results, confirm there is one record, and then close it.
29. Members who registered early paid a reduced registration fee. Create a Make Table query that selects all fields from the Registration table in the same order that they are listed in the field list. Select the fields individually for the query design grid, do not use the (*) asterisk.
    1. Add criteria to select only those records where the RegistrationFee field value equals 175.
    2. In the Make Table dialog box, assign the name EarlyRegistration to the new table.
    3. Save the query using MakeEarlyRegistration as the query name, run it, and then close the query. The new EarlyRegistration table should contain 13 records.
30. Open the Registration table in Design View and perform the following tasks:
    1. Enter a validation rule for the RegistrationFee field to ensure that values in the field are greater than or equal to 100.
    2. Enter the text, Must be greater than or equal to $100 in the Validation Text property box.
       
       Save the changes to the Registration table and close it. (Hint: Because a validation rule was added, a warning message appears asking if you want to test the data. The data is valid, so ignore this message and continue saving the table.)
31. Create a Delete query for the Sponsors table with the following options:
    1. Select the SponsorName field from the Sponsors table.
    2. Add criteria to delete only those records where Comor is the SponsorName.
    3. Save the query using DeleteSponsor as the query name, run it, and then close it. The query should delete one record.
32. Open the Sponsors table in Datasheet View and perform the following tasks:
    1. Sort the records in ascending order by SponsorName.
    2. Add the Total row to the datasheet and calculate the sum for the Amount field.
       
       Save the changes to the table and close it.

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.