Beauty to Go
Sue Miller needs to modify a few records in the Beauty2 database and analyze the data for customers that subscribe to her business. To help Sue, youâ€™ll update the Beauty2 database and create queries to answer her questions. Complete the following:
Case Problem 1 (pp. 172 – 1173)
Data Files needed for Case Problem 1: Beauty2.accdb
1. Download Beauty2 database from Blackboard and Save them in your CS245 folder.
2. Open the Beauty2 database, then Open the Member table in Datasheet view, and find the record for MemberID 2163, and then change the Street value to 844 Sanford Ln and the Zip to 32804. Next, find the record for MemberID 2169, and then delete the record. Close the Member table.
3. Create a query that lists customers who did not have to pay a fee when they signed up for their current option. In the query results, display the FirstName, LastName, and OptionBegin fields from the Member table, and the OptionCost field from the Option table. Sort the records in ascending order by the option start date. Select records only for customers whose fees were waived. (Hint: The FeeWaived field is a Yes/No field that should not appear in the query results.) Save the query as NoFees, and then run the query.
4. Use the NoFees query datasheet to update the Member table by changing the Last Name value for Gilda Packson to Washington.
5. Use the NoFees query datasheet to display the total Option Cost for the selected members. Save and close the query.
6. Create a query that lists the MemberID, FirstName, LastName, OptionBegin, OptionDescription, and OptionCost fields for customers who signed up with Beauty to Go between January 1, 2017 and January 31, 2017. Save the query as JanuaryOptions, run the query, and then close it.
7. Create a query that lists all customers who live in Celebration and whose options end on or after 4/1/2017. Display the following fields from the Member table in the query results: MemberID, FirstName, LastName, Phone, and OptionEnd. (Hint: The City field values should not appear in the query results.) Sort the query results in ascending order by last name. Save the query as CelebrationAndEndDate, run the query, and then close it.
8. Copy and paste the CelebrationAndEndDate query to create a new query named CelebrationOrEndDate. Modify the new query so that it lists all members who live in Celebration or whose memberships expire on or after 4/1/2017. Display the City field values in the query results following the Phone field values and sort the query results in ascending order by city (this should be the only sort in the query). Save and run the query.
9. Change the size of the font in the CelebrationOrEndDate query datasheet to 14 points. Resize columns, as necessary, so that all field values and column headings are visible.
10. Change the alternate row color in the CelebrationOrEndDate query datasheet to the Theme Color named Green, Accent 6, Lighter 80%, and then save and close the query.
11. Create a query that calculates the lowest, highest, and average cost for all options using the field names LowestCost, HighestCost, and AverageCost, respectively. Set the Caption property for each field to include a space between the two words in the field name. Run the query, resize all columns in the datasheet to their best fit, save the query as CostStatistics, and then close it.
12. Copy and paste the CostStatistics query to create a new query named CostStatisticsByZip.
13. Modify the CostStatisticsByZip query to display the same statistics grouped by Zip, with Zip appearing as the first field. (Hint: Add the Member table to the query.) Run the query, and then save and close it.
14. Compact and repair the Beauty2 database, and then close it.