Homework answers / question archive / Shelly Cashman Access 2019 | Modules 1-3: SAM Critical Thinking Capstone Project 1c Economic Development Conference CREATING TABLES, QUERIES, FORMS, AND REPORTS IN A DATABASE GETTING STARTED Open the file SC_AC19_CT_CS1-3c_FirstLastName_1

Shelly Cashman Access 2019 | Modules 1-3: SAM Critical Thinking Capstone Project 1c Economic Development Conference CREATING TABLES, QUERIES, FORMS, AND REPORTS IN A DATABASE GETTING STARTED Open the file SC_AC19_CT_CS1-3c_FirstLastName_1

MS Access

Share With

---

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

Economic Development Conference

CREATING TABLES, QUERIES, FORMS, AND REPORTS IN A DATABASE

GETTING STARTED

• Open the file SC_AC19_CT_CS1-3c_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_CT_CS1-3c_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_CT_CS1-3c_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 as AdvertiserID and change the data type to Short Text.
   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. Import the data from the Support_AC19_CT_CS1-3c_Advertisers.xlsx file and append it 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, the Short Text data type, and a field size of 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.
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 table.

 

SponsorID	SponsorName	Amount
S020	Dynamic Growth	$5,000

 

5. Open the Attendees table in Datasheet View, and then delete the record for the attendee with AttendeeID TL001.
6. Switch to Design View for the Attendees table and make the following changes:
   1. Change the name of the Street field to Address.
   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. 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. 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. 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 the LastName field values.
   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 for the Attendees table with the following options:
    1. Include the LastName, FirstName, City, and State fields.
    2. Sort the records in ascending order by the LastName field values.
    3. Select only those records where the City field value is "Philadelphia".
    4. Save the query using PhiladelphiaAttendees as the query name. View the query results, and then close the query.
11. Make the following changes to the GuestAndWorkshop query:
    1. Add the RegistrationFee field to the query design grid immediately following the AttendeeID field.
    2. Select only those records where the GuestFee field value equals 0 and the WorkshopFee field value equals 0. Confirm that 16 records appear in the GuestAndWorkshop query results. Close the query, saving if necessary.
12. Create a query based on the Attendees and Registration tables with the following options:
    1. Add the Attendees table and the Registration table. The tables should be automatically joined by the common field AttendeeID.
    2. Add the FirstName and LastName fields from the Attendees table.
    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. Make the following changes to the OrStates query:
    1. Move the State field so that it is the first field in the query.
    2. Select only those records where the State field value is "NY" or "NJ".
    3. Sort the records in ascending order first by the State field values and then by the LastName field values. Open the query in Datasheet View and confirm that it matches Figure 1. Close the query, saving if necessary.

14. Create a crosstab query 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. Add criteria to the AttendeeFirstName query 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. 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, and then view it in Datasheet View. Enter IL when prompted. Confirm that three records appear in the query results, and then close the query.
17. Modify the TotalFees query by creating a calculated field that sums the total of the RegistrationFee, GuestFee, and WorkshopFee field values. Use TotalFees as the name of the calculated field. Save the query. Open the query in Datasheet View and then close it.
18. Modify the ConferenceFees query by sorting the records in descending order by the calculated ConferenceFees field values, and display only the top 5% of the records. Save the query. Open the query in Datasheet View and then close it.
19. Modify the SortedStates query to sort the State field values 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 for the Registration table with the following options:
    1. Add the RegistrationFee, GuestFee, and WorkshopFee fields to the query.
    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 complete 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 zero 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 complete the following tasks:
    1. Add a field with the name Status to the end of the table.
    2. Select LookupWizard as 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 then save it using UpdateStatus as the name of the query.
24. Open the Attendees table in Datasheet View, and then change the Status value for the attendee with AttendeeID 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 then 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 as follows:
    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 field as the common field in both tables.
    4. Display all fields in the query results.
    5. Save the query using UnmatchedRegistration as the query name. View the query results, confirm there is one record, and then close the query.
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. Complete the following tasks:
    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 complete 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 the SponsorName field value is "Comor".
    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 complete the following tasks:
    1. Sort the records in ascending order by the SponsorName field.
    2. Add the total row to the datasheet, and then 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.