Excel Module 11BAssignment OverviewComplete the project assignment.Read each step completely before attempting it. Some steps include multiple instructions.

Question 1YO19_Access_Ch03_Prepare - Turquoise Oasis Spa Data Management, Part C 1.0Project Description:The Turquoise Oasis Spa has been popular with resort clients. The owners have spent several months putting spa data into an Access database so they can better manage the data. You have been asked to help show the staff how best to use the database to find information about products, services, and customers. For training purposes, not all the spa records have been added yet. Once the staff has been trained, the remaining records will be entered into the database.Steps to Perform: Step Instructions Points Possible 1 Start Access. Download and open the file named Access_Ch03_Prepare_Spa_C.accdb. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files. 0 2 If you need to see a quick snapshot of statistics for a table or query, you can use the total row. The total row is a special row that appears at the end of a datasheet that enables you to show aggregate functions for one or more fields. The manager would like to quickly find the total of all unpaid invoices and count the number of unpaid invoices. Using Query Design, create a query that returns InvoiceDate, InvoiceTotal, and Paid from tblInvoice, as well as, CustFirstName and CustLastName from tblCustomer (in that order). Limit the results to only those records where the invoice has not been paid. Sort the results in descending order by InvoiceDate. After running the query, add the TotalRow to the dataset, sum the InvoiceTotal column and count the First Name column. Name the query qryUnpaidInvoices Save and close the query. 20 3 Aggregate functions can be used in queries to perform calculations on selected fields and records. One advantage to using aggregate functions in queries, rather than just a total row, is that you can group criteria and then calculate the aggregate functions for a group of records. The manager would like to see how many products are offered, the average product price, and the minimum and maximum product prices. Using Query Design, create a query that lists the Price field from tblProduct four times. Apply the Count, Avg, Min, and Max aggregate functions on each of the Price fields (in that order from left to right). Name the query qryProductStats Save but DO NOT close the query. 20 4 Field names in aggregate queries are a composite of the selected aggregate function and the table field name. The field names assigned in an aggregate query can easily be changed either before or after the query is run. However, you must keep the original field name in the query design grid so Access knows on what field to perform the calculation. Edit the qryProductStats query so that the Price field where the Count aggregate function is being applied is named NumberOfProducts Edit the other fields in a similar fashion: AveragePrice LowestPrice and HighestPrice Use AutoFit to resize the columns so that all fields names are visible. Save and close the query. 10 5 Not only can you find statistical information for selected records using aggregate functions in a query or for all records using the total row, you can also calculate statistics for groups of records. Create a group to calculate statistics works the same way as an aggregate query but must include the field to group by. The additional field will not have a statistic selected for the total row, but instead, it will have the default Group By entered in the total row. The spa manager would like to see how the statistics calculated in the previous query for each product category. Copy the qryProductStats query and name the copy qryProductStatsByCategory Edit the query so that the statistics are grouped by each Category of product. Place the Category field as the first field in the query. Run the query, adjust the column widths so that all data are visible. Save and close the query. 10 6 The manager tried to create an aggregate query to calculate the total number of items and average number of items purchased by different methods: phone, online, and in person. The results made no sense, and she has asked you to help her figure out why. Open the qryPurchaseMethods query in Design view, make the necessary correction so that the query displays correct information. Run the query, adjust the column widths so that all data are visible. Save but DO NOT close the query. 10 7 An aggregate query may give you the correct results, but the formatting may not be what you expected. The fields used in a query that come from a table use the formatting defined in the table design. However, when the field is aggregated, it must be formatted in the query design grid using the Field properties sheet. In the qryPurchaseMethods query, change the formatting of the AverageSalesVolume calculation to be Fixed with 0 decimal places. Save and close the query. 10 8 In addition to statistical calculations using aggregate functions, you can perform an arithmetic calculation within a query to create a new field. The result of the calculated field is displayed each time you run the query. However, this new field is not part of any other table. The spa manager would like a query to show her the total value of the inventory, using the Quantity in Stock and Price fields for each product. Using Query Design, create a query that returns ProductDescription, Category, QtyInStock, and Price from tblProduct. Sort the results in ascending order by ProductDescription. Save the query as qryProductInventory In the fifth column, use the Expression Builder to create a calculated field that multiples QtyInStock by the Price field. Name the calculated field TotalInventory. Run the query, adjust the column widths so that all data are visible. Save and close the query. 20 9 Close all database objects. Close the database and then exit Access. Submit the database as directed. 0 Total Points 100 Question 2:YO19_Access_Ch06_PrepareB - Red Bluff Golf Club, Part B 1.0Project Description:The Red Bluff Golf Club needs useful information in order to run its business efficiently. Barry Cheney, the manager, has asked you to create queries to track the golfers who have scheduled tee times and private lessons. Additionally, you need to create queries for decision making, such as scheduling golf pros for private lessons or determining how many golf caddies are needed on a busy day. To keep the file small while you work with the database, Barry removed most of the data and left only some sample data for you to manipulate. Once Barry has accepted your changes, he will load all the data and implement the new database.Steps to Perform: Step Instructions Points Possible 1 Start Access. Download and open the file named YO19_Access_Ch06_Prepare_Golf1_Part_B.accdb. Grader has automatically added your last name to the beginning of the filename. Enable the content, if necessary. 0 2 The IIf function allows you to return different values based on a criterion. The form of the function is IIf(expression,true,false). The expression is a criterion that can be only true or false. For example, [Price]<=10. If the Price is less than or equal to ten, it will return the true argument. If it is greater than 10, it will return the false argument. For example, IIf([Price]<=10,”Low Price”, “High Price”). The true and the false arguments can be text, like the example, it can be a number, or it can be a calculation. Create a query based on the tblEmployee table that will display FirstName, LastName, Salary, Raise Assessment, Raise Option 1, and Raise Option 2, where RaiseAssessment, RaiseOption1, and RaiseOption2 are new fields using the IIf function as described below and in the next steps, in that order. Management is trying to decide on raises and these calculated fields will help management make that decision. RaiseAssessment should return Give Raise if the employee salary is $40,000 or less. Raise Assessment should return Maybe Raise if the employee salary is over $40,000. 7 3 RaiseOption1 should return a 4.5% increase in salary for any employee whose salary is $40,000 or less. All other employees should return $0.00. Format Raise Option 1 as currency. 6 4 If you have more than two possible outcomes, you can put an IIf function inside the false part of another IIf function. For example, IIF([Price]<=10,”Low Price”,IIf([Price]<=20,”Medium Price”,”High Price”)) This function would return Low Price when less than or equal to 10, Medium Price for values greater than 10 but less than or equal to 20, and High Price for anything over 20. RaiseOption2 should return a 4.5% increase in salary for any employee whose salary is $40,000 or less. For any employee whose salary is greater than $40,000 but less than or equal to $75,000, Raise Option 2 should return a 2% increase in salary. All other employees should return $0.00. Format Raise Option 2 as currency. Run the query, and then save as qryIIf. Close the query. 7 5 In Access, you can return records where values of a field are empty using Is Null as the criteria. Create a query based on the tblEmployee table that will display FirstName, LastName, and Photo in that order. In the Photo field, use Is Null to display only employees who do not have a photo on file. Run the query, and then save it as qryPhotoIsNull. Close the query. 8 6 In Access, you can return records where values of a field are not empty using Is Not Null as the criteria. Create a query based on the tblEmployee table that will display FirstName, LastName, and Photo in that order. In the Photo field, use Is Not Null to display only employees who do have a photo on file. Run the query, and then save it as qryPhotoIsNotNull. Close the query. 8 7 You can use the IsNull function inside an IIf as the expressions/criteria. For example, IIf(IsNull([Price]),”Price Omitted”,”Price Shown”) would return the true value of Price Omitted if the Price field value is empty and Price Shown for all prices that have any value – are not empty. Use the data in tblEmployee to create a query that will display whether the employee has a photo on file or not. The query results should list FirstName, LastName, and PhotoStatus in that order, where PhotoStatus is a new field that displays whether a photo has been taken or not. If the Photo field in tblEmployee is empty, the PhotoStatus field should display Needs Photo. If the Photo field contains a value, the PhotoStatus field should display Has Photo. Run the query, and then save it as qryIsNullFunction. Close the query. 10 8 The DateDiff function allows you to determine the number of months or days and other intervals between two dates. See your book for a table of acceptable intervals. The DateDiff function takes the following form DateDiff(Interval,EarlierDate,LaterDate). For example, DateDiff(”D”,[TransactionDate],[ShippingDate]) would calculate how many days it took to ship after the transaction date. Create a query based on the tblPayments table that will display MemberID, PaymentDate, and MonthsSincePayment where MonthsSincePayment is a new calculated field, in that order. MonthsSincePayment should calculate the number of months since the last payment using the DateDiff function. Run the query, and then save it as qryDateDiffMonths. Close the query. 15 9 The DateAdd function allows you to add a specified number of days, weeks, or other interval to a date. See your book for a table of acceptable intervals. The DateAdd function takes the following form: DateAdd(interval,number,date). For example, DateAdd(“yyyy”,4,#5/1/2020#) would return the date of 5/1/2024. Create a query based on the tblMember and tblPayments tables that will display FirstName, LastName, Amount, PaymentDate, DueDateUsing“y” and DueDateUsing“ww” where DueDateUsing“y” and DueDateUsing“ww” are new fields using the DateAdd functions as described below, in that order. Due Date Using “y” should display the date the payment is due using the “y” interval and 365 days. Due Date Using “ww” should display the date the payment is due using the “ww” interval and 52 weeks. Run the query, and then save it as qryDateAdd. Close the query. 18 10 The DateSerial function returns a date based off giving the year, month and day. The DateAdd function take the following form DateSerial(year,month,day). For example, DateSerial(2019,4,5) would return the date of April 5, 2019. You can also use the Year, Month, or Day function with the DateSerial. For example, Year([ShippingDate]) returns the year of the shipping date. The DatePart function can also return part of a date. The DatePart function takes the following form DatePart(interval,date). For example, DatePart(“yyyy”,#5/1/2020#) would return 2020. Create a query based on the tblEmployee and tblPosition tables that will display FirstName, LastName, HireDate, BenefitsStart, and 5-YearAnniversary where BenefitsStart and 5-YearAnniversary are new fields using the DateSerial and DatePart functions as described below, in that order. Add the PositionType field to the last column of the query. Restrict the results to only Full-Time employees. Do not show the PositionType field. BenefitsStart should display the date for when each employee’s benefits start – which is 91 days after the HireDate. Your calculated field should use the DateSerial function. 5-YearAnniversary should display the year that the employee reaches five years of employment. Your calculated field should use the DatePart function. Run the query, and then save it as qryDateSerialAndPart. Close the query. 17 11 The Round function will round to the value to the nearest whole number. For example, round(12.51) would return the value of 13. Open the existing qryRound query. Add a new field after the New Salary field. Name the new field FinalSalary. Final Salary should display the New Salary rounded to the nearest dollar using the round function and formatted as currency. Run the query, and then save qryRound. Close the query. 4 12 Close all database objects. Close the database and then exit Access. Submit the database as directed. 0 Total Points 100 Question 3:YO19_Access_Ch05_Prepare - Golf: Modifying Database Tables 1.0 Project Description: The Red Bluff Golf Club & Pro Shop generates revenue through its golfers, golfer services, and pro shop sales. The current database tracking this data has erroneous data from a lack of good table design. Barry Cheney, the Golf Club manager, has given you a copy of the database and has asked you to modify the tables used to track employees and club members. This will make data entry easier and more consistent. To keep the file small while you work with the database, he removed most of the data and left only some sample data. Once Barry accepts your changes, he will load all of the data and implement the new database. Steps to Perform: Step Instructions Points Possible 1 Open the downloaded Access file named Access_Ch05_Prepare_golf.accdb. Grader has automatically added your last name to the beginning of the filename. Enable the content, if necessary. 0 2 An input mask creates a typing template that helps data to be consistently entered. This also makes entering data easier and faster by providing common symbols such as parentheses. Open the tblMember table in Design view and create an input mask for the Phone field to make it easier to enter data. Select the appropriate setting so that the data is stored with the symbols in the mask. It is very important to always test that any changes work correctly. Switch to Datasheet view of the tblMember table, save it, and enter the following record to test the changes: FirstName LastName Address City State PostalCode Phone JoAnn Pollack 124 6th Street Spring Hill NM 87588 5055552010 Close tblMember. 3.5 3 Open the tblEmployee table in Design view and create a short date input mask for the HireDate field. Switch to Datasheet view of the tblEmployee table, save it, and enter the following record to test the changes. FirstName LastName Salary HireDate Position Lilly Baine 21380 07052021 Golf Caddy Close tblEmployee. 3.5 4 Input masks help ensure data consistency. However, not all of the input masks you may need are provided in the wizard. Sometimes a custom input mask is needed. Open tblMember in Design view and create a custom input mask for the State field so that data entered is required to be 2 capital letters. Refer to the table below for specifics. Description: State (2-character abbreviation) Input Mask: >LL Placeholder: _ Sample Data: NM Mask/Type: Text/Unbound Switch to Datasheet view of the tblMember table, save it, and enter the following record to test the changes. FirstName LastName Address City State PostalCode Phone Skylar Hunter 372 Bigalow Boulevard Santa Fe nm 87594 5055554772 Close tbMember. 3 5 ID numbers can be confusing when you have several different ID numbers and they all look like plain numbers. Adding a prefix to the number can help make the data more easily discernable. Open tblEmployee in Design view and apply a custom format to the EmployeeID field so that all employee IDs entered appear with the letters EMP before the ID number. 3 6 Color can also help differentiate a field and make data easier for users to understand. Create a custom format for the Position field, in the tblEmployee table, so that data in this field appear with a red font color. Apply a Medium Date format to the HireDate field, in the tblEmployee table. 4 7 Switch to Datasheet view of the tblEmployee table, save it, and enter the following records to test the changes. FirstName LastName Salary HireDate Position John Matrell 18300 08122021 Golf Caddy Carlos Ruweeze 37900 08242021 Pro Shop Attendant 2 8 Validation rules help verify that entered data is accurate given a business rule. When the user enters a value, it evaluates it against the rule. If the data does not follow the rule, then the data is entered. If the data does not follow the rule, the users receives an error with the text in the Validation Text property. Switch to Design view of the tblEmployee table and modify the Validation Rule property of the HireDate field so that when employee records are added to the table the hire date cannot be entered after 14 days from the current date. 3 9 The Validation Text property is the text for any data that does not meet the validation rule. If no validation text is entered, only a generic – and not very helpful – error message is given. Modify the Validation Text property of the HireDate field, in the tblEmployee table, to display the following message if a hire date entered is more than 14 days from the current date: This employee began working more than 14 days ago. Please call the Corporate Office at (800) 555-4022. (include the period). Save the changes and click No when Access prompts you to confirm whether you want to test the existing data against the new rules. 2 10 Modify the Validation Rule property for the Salary field, in the tblEmployee table, so that no salary can exceed $200,000. 3 11 Modify the Validation Text property of the Salary field, in the tblEmployee table, to display the following message if an amount entered exceeds $200,000: The maximum salary an employee can earn is $200,000. Please re-enter this employee's salary.(include the period). Save the changes and click Yes when Access prompts you to confirm whether you want to test the existing data against the new rules. 3 12 Switch to Datasheet view of the tblEmployee table and enter the following records to test the changes to the properties. FirstName LastName Salary HireDate Position Dolly Hunt 24600 08252029 Golf Technician Allie Madison 80000 08252029 Golf Professional 2 13 Field names are the proper names seen by Access. The caption will show to the user but not change the actual field name used by Access. Switch to Design view of the tblEmployee table and modify the caption properties for the following fields. Field Name Caption FirstName First Name LastName Last Name HireDate Date of Hire Position Job Title Save and close tblEmployee. 3 14 An index is automatically created for Primary Keys. When a field is an index it helps increase speed of searching and sorting – similar to a page in a book and the index in the back of a book. If too many fields are indexed, then it will slow down adding and updating fields. Thus, you should only index commonly searched fields and where there are more different entries than duplicate entries. The purpose of indexing is only to increase efficiency. Open the tblPosition table in Design view and create a single-field index using the following information. Index Name Field Name Sort Order Position PositionType Ascending Index Properties: Primary: No Unique: No Ignore Nulls: No Save and close tblPosition. 3 15 Open the tblMember table in Design view and create a multiple-field index using the following information. Index Name Field Name Sort Order MemberName LastName Ascending FirstName Ascending Index Properties: Primary: No Unique: No Ignore Nulls: No 3 16 Records with empty values may be ok depending on the data. However, many fields – such as Last name – should be required at all times. When set to Yes, the Required property will prevent a field from being blank when entered for the first time. If that is the only property set, then after a record has been entered the field could still be erased. Access then stores an empty set – or Zero String Length. Thus, when the Allow Zero String Length property is available, it also needs to be set to No to make the field required at all times. In Design view of the tblMember table, make the following fields required by setting the Required property to Yes and the Allow Zero Length property to No: FirstName, LastName, Address, City, State, and PostalCode. 3 17 The Default value property puts an initial value into the field. A user saves time when that is the correct value. If the value is different, the user can override the value and type in the correct value. If the value will be the same 50% of the time or more, a Default value is appropriate and increases data entry speed. Using the following information modify the appropriate property of the tblMember table so that when new records are added, data automatically appears in the fields. Field Name Default Value City Santa Fe State NM 3 18 Switch to Datasheet view of the tblMember table, save it, and enter the following record to test the changes. FirstName LastName Address City State PostalCode Phone Karen Meyer 72 First Avenue Santa Fe NM 87594 5055552787 Harry Shay 60285 Wildwood Road Snowflake AZ 85937 9285551638 Close tblMember. 2 19 Particularly with foreign field keys, the person may not know the value for the data entry – such as a MemberID number. A Lookup data type creates a drop-down list of the ID values and other fields – such as first and last name – to make selecting the proper member easier. Open the tblMemberLessons table in Design view. Modify the MemberID field to make it a lookup field that will display the MemberID, LastName, and FirstName fields, in that order, from the tblMember table. Sort in Ascending order by LastName and then by FirstName. Be sure to hide the key column in the field, enable data integrity, and restrict delete. 3 20 When there are multiple fields in the drop-down list, column headings will indicate which fields are which. However, headings are not listed by default. The Column Heads property must be set to Yes to get column headings to appear. Modify the Lookup field property for the MemberID field so that column headings are visible. Save and close tblMemberLessons. 3 21 Open the tblPosition table in Design view. Modify the PositionType field to make it a lookup field that will display the following values: Full-time, Part-time. Be sure to limit to list and DO NOT allow multiple values. 3 22 Switch to Datasheet view of the tblPosition table, save it, and enter the following information into the PositionType field. Position PositionType Caddy Master Full-time Golf Caddy Part-time Golf Course Manager Full-time Golf Technician Full-time Head Golf Professional Full-time Part-time Golf Professional Part-time Pro Shop Attendant Part-time Pro Shop Manager Full-time Close tblPosition. 2 23 Traditionally, calculations are completed in queries per data normalization rules. The field type of Calculated is useful for calculations that will be used in almost every object using the table and when the calculation will not change over time. The data type of Calculation does the calculation using the expression builder and creates a read-only field. Open the tblEmployee table in Design view. Create a new field named NameTag with a data type of Calculated. In the Expression box, use the & symbol to combine the FirstName and LastName fields separated by a space character. In the Description property, type Used for printing name tags. 3 24 Switch to Datasheet view, save the changes, and enter the following records to test the changes. FirstName LastName Salary HireDate Position Ken Colon 67725 08252029 Assistant Golf Professional 1 25 A Yes/No field is appropriate whenever the data should only be Yes/No or True/False. Switch to Design view of the tblEmployee table. Create a new field named Orientation with a data type of Yes/No. In the Description property, type Is new hire orientation complete?. 3 26 Switch to Datasheet view of the tblEmployee table, save it, and click the Orientation check box for the following employees to indicate that their orientation has been completed. John Schilling Robert Lange Ginger Liu Juan Martinez Samual Dancer Paul Kimons Aleeta Herriott Lilly Baine John Matrell Carlos Ruweeze Ken Colon Close tblEmployee. 1 27 An Autonumber data type creates a surrogate key – a short numeric ID that never changes – which can be either incremental or random. The Autonumber automatically assigns the number as new records are added. Once an autonumber is used, it will never be assigned again, even if the original record is deleted. ID numbers with the Autonumber data type are never reused. Create a new table in the database that will be used to track when members have paid their annual dues. Use the following information to create the fields in the table. Field Name Data Type Description Field Properties PaymentID AutoNumber Surrogate key for each payment (primary key) Primary Key PaymentDate Date/Time Payment date Short Date Format AmountPaid Currency Payment amount 3 28 Continue creating the new table by adding a new lookup field named MemberID that uses the MemberID, LastName, and FirstName fields, in that order, from the tblMember table. Sort the lookup field in ascending order by LastName and then by FirstName. Hide the key column, enable data integrity, and restrict delete. In the Description property of the MemberID field, type This is the member who made the payment. (include the period) Save the table as tblPayment. 3 29 Switch to Datasheet view of the tblPayment table and enter the following records. PaymentDate AmountPaid MemberID 1/26/2021 1200 Duke 2/23/2021 1500 Risotto Delete the record where the PaymentID is 1 and enter the following record. PaymentDate AmountPaid MemberID 3/5/2021 500 Pollack 2 30 Random Autonumber IDs can be either positive or negative numbers. While in practice most designers prefer incremental, random assignment works the same way where no number will be reused. Switch to Design view of the tblPayment table. Modify the New Values property of the PaymentID field from Increment to Random. 3 31 Switch to Datasheet view of the tblPayment table, save it, and enter the following records. PaymentDate AmountPaid MemberID 4/8/2021 850 Allen 5/25/2021 250 Britt Close tblPayment. 3 32 A hyperlink data type is appropriate for web addresses, email addresses, and local file references. If a user clicks on the hyperlink, Access will try to open it in a browser, email client, or file manager. An OLE (Object Linking and Embedding) Object allows you to embed an object as an image. It consumes a great deal of space in the database but is needed to be compatible with legacy – or outdated – database system. Attachments are preferred to the OLE Object datatype. An attachment allows a file to be attached – similar to email attachments – and the user can view or edit the document depending on the property settings. Attachments still increase the size of the dataset and should only be used when attachments will be on the smaller size. Open the tblEmployee table in Design view and add the following new fields. Field Name Data Type Description EmailAddress Hyperlink E-mail address Photo OLE Object Golf photo OrientationSignOff Attachment Form verifying orientation completion 3 33 Switch to Datasheet view of the tblEmployee table, save it, and enter the email address for the following employee. EmployeeID NameTag EmailAddress EMP4 Ginger Liu liug@paintedparadise.com 3 34 In the Photo field of Ginger Liu’s record, insert the Liu.jpg file. Be sure and check the Link check box before closing the dialog box. 3 35 In the OrientatioSignOff field of Ginger Liu’s record, insert the Liu.pdf file. Save and close tblEmployee. 3 36 Access has serval robust ways to filter the data in a table. The Advanced Filter allows you to specify several criteria for the desired records. Further, the results of the filter can be saved as a query for later use using Save Object As. In Datasheet view of the tblMember table, create an advanced filter that will display only the records where the city equals "Santa Fe" or "Cowles". Save the filter as a query object with the name qrySantaFeMembers. Close qrySantaFeMembers and tblMember. 3 37 By using the Table Analyzer, you can divide a table – such as data created from an import – into several tables and automatically create the relationships you need between them. Use the Table Analyzer Wizard to reduce redundancy in the tblMember table. Let the wizard deicide how to modify the table. The wizard will create two tables. Rename Table1 as tblMemberData and Table2 as tblCityZip. There should be no typographical errors and there is no need to create a query. Close tblMemberData and tblCityZip. 3 38 Exit Access, and then submit your file as directed by your instructor. 0 Total Points 100