Shelly Cashman Access 2019 | Modules 1-3: SAM Capstone Project 1b The Preserve   Creating Tables, Queries, Forms, and Reports in a Database     GETTING STARTED Open the file SC_AC19_CS1-3b_FirstLastName_1

Subject:MS AccessPrice:20.99 Bought3

Share With

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

The Preserve

 

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

• GETTING STARTED

• Open the file SC_AC19_CS1-3b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_CS1-3b_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-3b_MemberTypes.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 Preserve is an outdoor space with walking trails, vegetable gardens, trees, plants, and flowers. You have been asked to create a database for The Preserve to ensure that details on memberships, events, vendors, and other related data are easier to access and maintain.
   
   Create a new table in Datasheet View with the following options:
   1. Rename the default primary key ID field as MemTypeID and change the data type to Short Text. (Hint: The MemTypeID field should remain the primary key.)
   2. Add a new field with the name Description and the Short Text data type.
   3. Add a third field to the table with the name AnnualFee and the Currency data type.
   4. Add a fourth field to the table with the name GuestPasses and the Number data type.
      
      Save the table with the name MemberTypes and close the table.
2. Use the Import Spreadsheet Wizard to import the data from the Support_AC19_CS1-3b_MemberTypes.xlsx file and append the data to the MemberTypes 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 SupporterID and the Short Text data type. Change the field size to 4.
   2. Set SupporterID as the primary key for the table.
   3. Add a field with the name SupporterName 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 Supporters and switch to Datasheet View.
4. With the Supporters table open in Datasheet View, add the record shown in Table 1. Resize the SupporterName field so that the field value is completely visible. Save the changes and close the Supporters table.

5. Open the Members table in Datasheet View and delete the record for the member with MemberID SU001.
6. Switch to Design View for the Members table and make the following changes:
   1. Change the name of the ZIP field using PostalCode as the new field name.
   2. Add a new field to the end of the table with the name Mobile 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 Members table with the following options:
   1. Include the MemberID, FirstName, LastName, and Mobile fields in that order.
   2. Save the query using MemberMobileInfo as the query name.
      
      View the query results and then close the query.
8. Use the Form Wizard to create a form for the Supporters table with the following options:
   1. Include the SupporterID, SupporterName, and Amount fields.
   2. Select the Columnar layout.
   3. Assign the title SupporterDonations to the form.
      
      View the form and then close it.
9. Use the Report Wizard to create a report for the Members table with the following options:
   1. Include the State, City, FirstName, and LastName fields in that order.
   2. Do not add any grouping levels.
   3. Sort the records in ascending order by the State field.
   4. Select the Tabular layout and Portrait orientation.
   5. Assign the title MemberLocations to the report.
      
      View the report and then close it.
10. Create a query in Design View for the Members table with the following options:
    1. Include the LastName, FirstName, City, and State fields in the design grid.
    2. Sort the records in ascending order by the LastName field.
    3. Add criteria to select only those records where Glassboro is the city.
    4. Save the query using GlassboroMembers as the query name.
       
       Open the query in Datasheet View and confirm that two records appear in the query result. Close the query, saving if necessary.
11. Open the NoFeeEvents query in Design View and make the following changes to the query:
    1. Add the EventID field to the query grid immediately following the RegistrationID field.
    2. Add criteria to select only those records where the ChildCost field value equals 0 and the AdultCost field value equals 0.
       
       Open the query in Datasheet View and confirm that 7 records appear in the NoFeeEvents query results. Close the query, saving if necessary.
12. Create a query in Design View based on the Members and Registration tables with the following options:
    1. Add the Members and Registration tables to the design window.
    2. Add the LastName and FirstName fields from the Members table to the query grid.
    3. Add the RegistrationID, NumChild, and NumAdults fields from the Registration table.
    4. If necessary, join the Members and Registration tables. MemberID is the common field in both tables.
    5. Save the query using MemberRegistration 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 grid.
    2. Add criteria to select only those records where the value in the State field is DE or MD.
    3. Sort the records in ascending order by the State field and then by the LastName field.
       
       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 Members table with the following options:
    1. Use the State field for the row headings.
    2. Use the MemTypeID field for the column headings.
    3. Use a count of the MemberID field as the calculated value for each row and column intersection.
    4. Save the query using State_MemTypeCrosstab as the query name.
       
       View the query and then close it.
15. Phone messages sometimes include only the first name of a member, and the spelling of the name can be uncertain. Open the MemberFirstName query in Design View and add criteria to select only those records where the FirstName field value begins with Ma followed by any other letters. Save the changes to the query. Open the query in Datasheet View, confirm that three records appear in the query results, and then close it.
16. Open the StateMembers query in Design View. Add parameter criteria to the State field to replace the current "NJ" 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 NY when prompted. Confirm that two records appear in the query results and then close it.
17. Open the TotalAttendees query in Design View. Modify the query by creating a calculated control field that sums the total of the NumChild and NumAdults fields. Use TotAttendees as the name of the calculated field. Save the query. Open the query in Datasheet View and then close it.
18. Open the EventFees query in Design View. Modify the query by sorting the records in descending order by the calculated field EventFees and display only the top 5% of the records. Save the query. Open the query in Datasheet View and then close it.
19. The marketing manager has asked you for a listing of the different states in which members reside. 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 NumChild and NumAdults fields to the query grid.
    2. Add the Total row to the query grid.
    3. Calculate the sum of both fields.
    4. Save the query using AggregateNumbers as the query name.
       
       Open the query in Datasheet View, confirm that it matches Figure 2, and then close it.

21. Gardening-related businesses can rent space to sell their products at certain events. Open the Vendors table in Design View and complete the following tasks to calculate the rental costs for the vendors:
    1. Make the VendorID field the primary key of the table.
    2. Change the field size of the VendorID field to 4.
    3. Change the data type for the RentalCost field to Currency with 0 decimal places.
    4. Add the text Basic cost to rent a 10 x 5 space as the description for the RentalCost 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 Members 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 Lookup Wizard as the data type.
    3. Select the option to type in your own values.
    4. Type in the following two values (in the order shown) as the list of possible values for the field: New and Renewal. 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 then close it.
23. Most of the members are renewing rather than purchasing a new membership. Use an Update query to update the value in the Status field of the Members table to Renewal for all records. Save the query using UpdateStatus as the name, run it, and then close it.
     
24. Open the Members table in Datasheet View. For the member with MemberID DA002 (Sidney Davis), change the Status field value to New. Add the caption CellPhone to the Mobile 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 NewSupporters table contains records that should be appended to the Supporters table. Create an Append query with the following options:
    1. Select all the fields from the NewSupporters table in the same order that they are listed in the field list.
    2. Select Supporters as the destination table in the Append dialog box.
       Save the query using AppendNewSupporters as the name, run it, and then close it. The query should append 10 records.
27. Open the Relationships window and add the MemberTypes and Members tables to the window. Create a relationship between the MemTypeID field in the MemberTypes table and the MemTypeID field in the Members table. Make the relationship enforce referential integrity. Save the relationship and close the window.
28. Use the Find Unmatched Query Wizard to find members who do not have a matching registration as follows:
    1. Select the Members table as the table to display in the query result.
    2. Select the Registration table as the related table.
    3. Select the MemberID field 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 are 20 records, and then close it.
29. Members who attend certain events are entitled to discounts at participating businesses. Create a Make Table query as follows to determine which members are eligible for discounts:
    1. Select all fields from the Registration table in the same order that they are listed in the field list.
    2. Select the fields individually for the query grid. Do not use the (*) asterisk.
    3. Add criteria to select only those records where the AdultCost field value is greater than or equal to 50.
    4. In the Make Table dialog box, assign the name EligibleforDiscounts to the new table.
       
       Save the query using MakeEligibleforDiscounts as the query name, run it, and then close the query. The new EligibleforDiscounts table should contain 7 records.
30. Open the Events table in Design View and complete the following tasks:
    1. Enter a validation rule for the MaxAttendees field to ensure that values in the field are greater than or equal to 5.
    2. Enter the text Must be greater than or equal to 5 in the Validation Text property box.
       
       Save the changes to the Events 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 Supporters table with the following options:
    1. Select the SupporterName field from the Supporters table.
    2. Add criteria to delete only those records where Skore is the SupporterName field value.
    3. Save the query using DeleteSupporter as the query name, run it, and then close it. The query should delete one record.
32. Open the Supporters table in Datasheet View and complete the following tasks:
    1. Sort the records in ascending order by the SupporterName field.
    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.