i need help with assigment
Clients MemberIDJoinedRepIDPlanIDLastNameFirstNameAddressCityProvPcodeGenderDateofBirthStartWeightEnd WeightHeight (in)Plan DateKilometers 100112/31/1022S1SellDebby7500 Almansa St.White CitySKS4L0A1F6/18/71156151631/8/212149 100212/31/1026S1YoungHenry9421 SW 103 StreetReginaSKS4N1A1M3/6/802151987411/2/204245 100312/31/1029P1MillsLeroy5250 SW 76th StreetReginaSKS4N1A2M5/30/502202157610/20/203971 100412/31/1029S1RobertsNed7080 SW 107 TerraceReginaSKS4N1A3M5/7/481901947010/8/204511 10299/13/1122G1WattCheryl905 Roderigo AvenueWhite CitySKS4L0A2F5/30/861671676512/12/204363 10329/15/1126G1McGrathJennifer5225 SW 99th TerraceReginaSKS4N1A4F5/11/842202126510/10/203670 10419/19/1129S1BobbittKelly11970 SW 210 TerraceReginaSKS4N1A5F10/10/83145140641/16/212816 110212/31/1133B1TamerHeidi8245 SW 64th StreetGrand CouleeSKS4P2Z2F8/21/84210190708/12/20349 11201/11/1222B1PostenPat6815 Pallazzo StreetWhite CitySKS4L0A3M12/12/83225208741/20/214453 11382/4/1226P1LaneCharles4510 SW 74th StreetReginaSKS4N1A6M8/11/85160150722/1/21735 11563/11/1222S1FairbairnLisa2985 Jackson AvenueBalgonieSKSOGOEOF3/27/861221116312/6/203551 11744/8/1226P1VallsSteve8525 SW 96th StreetGrand CouleeSKS4P2Z3M4/7/82235225731/29/212450 11924/18/1229S1KrisselCarl4415 Anderson RoadWhite CitySKS4L0A4M4/3/81280256742/8/213841 12105/5/1233P1SmithJulie7960 SW 52nd CourtReginaSKS4P2P3F7/31/85113118611/14/21704 12286/13/1222B1WalkerJan8820 SW 52nd AvenueReginaSKS4P2P4F5/14/802001826810/15/202717 12468/10/1226S1NicholsAlan6105 Granada Blvd.White CitySKS4L0A5M6/30/80310299751/9/212954 126410/3/1222S1NewmanSusannah6045 Rolling Road DriveReginaSKS4P2P5F12/2/8211098501/1/21220 128210/25/1226S1DuffyMitchell2211 Segovia CircleWhite CitySKS4L0A6M6/22/70165152703/11/21646 130011/13/1229S1LobreeBilly4800 North Kendall DriveReginaSKS4P2P6M11/24/84210208693/18/213269 13182/20/1322G1SpillisGeorge7970 SW 141 TerraceReginaSKS4P2P7M5/25/79245223761/18/212859 13362/26/1326P1ParkDeborah6373 SW 87th LaneReginaSKS4P2P8F7/3/57128125633/25/214879 13546/5/1329P1WoodVictor935 Escobar AvenueWhite CitySKS4L0A7M9/9/73160143702/17/213592 13726/6/1333S1MoranJodi5740 Michelangelo StreetWhite CitySKS4L0A8F6/30/74130118671/16/21977 139010/22/1322G1BarnesBecky10801 SW 88th CourtReginaSKS4P2P9F3/17/83138137661/3/213651 14082/28/1426S1MollMary5711 Michelangelo StreetWhite CitySKS4L0A9F2/1/791121136312/7/204339 14266/9/1429G1ZacharSarah9568 SW 67th CourtReginaSKS4N0X1F5/19/63156136671/15/211384 14447/10/1433G1HopkinsManny4000 Malaga AvenueBalgonieSKSOGOEOM11/23/84215207708/12/201188 14628/10/1429S1GilsteinJamie322 Dyer RoadReginaSKS4N0X2M1/5/552652707112/12/201021 148012/11/1433G1BattleAna Marie1115 Country Club PradoWhite CitySKS4P3O4F4/4/57160142673/20/213981 14981/5/1522P1PorterGarth6697 SW 70th AvenueGrand CouleeSKS4P2Z4M10/5/81218194671/19/211843 15163/11/1526B1SpinaleSonia80 Leucadendra DriveWhite CitySKS4P3O5F8/30/751671616712/14/204926 15343/18/1522B1MunterLorna5961 SW 46th TerraceReginaSKS4N0X3F10/22/90148138659/28/203797 15524/7/1526S1ParkerSaul6420 SW 114th StreetReginaSKS4N0X4M6/12/63179165682/4/211741 15704/12/1522S1SappFrank7289 SW 53rd CourtReginaSKS4N0X5M3/13/821541546810/15/204309 15884/25/1522B1JohnsonJeffrey417 BarbarossaWhite CitySKS4P3O6M5/23/791761557011/4/204155 16065/4/1526P1BermontSara1129 Palermo AvenueWhite CitySKS4P3O7F6/30/71104986410/21/204371 16246/28/1529S1ColeClaudia1510 Ferdinand StreetWhite CitySKS4P3O8F6/27/83180173682/15/213772 16428/19/1533B1MunroeSusan5830 SW 85th StreetReginaSKS4N0X6F4/17/841781616411/22/20634 16601/6/1626G1HeatleyMaria3920 Durango StreetWhite CitySKS4P3O9F11/3/7511096623/21/211555 16783/30/1622P1DillDavid2000 South Bayshore DriveBalgonieSKSOGOEOM2/24/73156155691/2/21429 16966/21/1626G1RudyLarry7201 SW 47th CourtReginaSKS4N0X7M6/24/58190167752/19/214593 17147/15/1629S1SussmanDenise8244 SW 179th TerraceReginaSKS4N0X8F12/8/72165140652/15/21999 17327/18/1622S1EvansKelly2915 Jackson Ave.BalgonieSKSOGOEOF5/12/83129125602/17/211327 17508/3/1626G1EdmundsDebbie2809 Bird Avenue PMB 215BalgonieSKSOGOEOF10/26/831081036411/22/201321 17689/28/1622P1DavidsonRichard1720-B Wakeena DriveBalgonieSKSOGOEOM9/23/851881737212/26/203505 178610/1/1626B1SmithBob7843 SW 166th StreetEmerald ParkSKS4L1A1M2/27/851941867412/25/201319 180411/14/1622G1NorthAbner616 CamiloWhite CitySKS4L0A9M9/3/83172152702/6/214388 182211/21/1626B1CusakDebbie17050 SW 83 CourtReginaSKS4T3R2F3/4/481101046210/2/204760 18401/2/1729B1TobinBarbara850 BeneventoWhite CitySKS4L0A8F2/2/38125115622/18/213578 18585/14/1733G1CenterMichelle14051 SW 82nd PlaceReginaSKS4T3R3F7/15/87137133658/9/202123 18766/19/1733P1ArmstrongRobert10200 Sabal Palm AvenueWhite CitySKS4L0A7M3/24/581451386510/20/201081 18947/17/1722S1NagelJames5990 SW 86th StreetReginaSKS4T3R4M1/18/83175168681/7/21220 191211/1/1726P1TraneJoe975 BelleviewWhite CitySKS4L0A6M11/10/731951997011/5/203893 193012/10/1729P1AndersonTerry1017 Hardee RoadWhite CitySKS4L0A5M5/5/85185164701/29/213371 19481/1/1833B1BrasingtonPaul12121 S.W. 64th Ave.Emerald ParkSKS4L1A2M4/11/77192184708/11/20446 19661/22/1826P1MorrisonLaura5760 SW 35th StreetReginaSKS4T3R5F3/24/82145140652/27/214724 19843/20/1822G1KellyDavid4305 Toledo StreetWhite CitySKS4L0A4M9/12/831671597010/21/201392 20024/6/1826S1WoodsKeith9015 Essex AvenueWhite CitySKS4L0A3M7/3/84191196682/13/212953 20204/12/1829G1SharpKaren7521 SW 64th CourtReginaSKS4T3R6F1/2/731351246412/17/204960 20384/18/1833P1HarrisonCindy3611 Granada Blvd.White CitySKS4L0A2F1/1/442102086511/24/202785 20567/18/1822G1PowellPeter6822 Mindello StreetWhite CitySKS4L0A1M8/22/612202187310/10/201305 207411/24/1822B1LeslieLeonard4615 Santa Maria StreetWhite CitySKS4L0A0M8/21/44194181742/1/214681 SalesReps RepIDRepFnameRepLnameRepAddressRepCityRepPCode 33SarahPerkins2242 Elphinstone St.ReginaS4T 2W1 26TanisJenkins100 Central StreetReginaS4X 0Z7 29DestinyFlavel2858 Francis StreetReginaS4S 5Y7 22ChanceBear12 Aspen PlaceWhite CityS4L 0A4 COAP 138: COMPUTER SUITE APPLICATIONS INTEGRATED PROJECT--ABC FITNESS THIS IS AN INDEPENDENT PROJECT. YOU ARE EXPECTED TO DO YOUR OWN WORK. YOUR PROJECT SHOULD BE A TRUE REFLECTION OF YOUR KNOWLEDGE AND ABILITY. YOU ARE ENCOURAGED TO SHOWCASE YOUR ABILITY WHILE ANSWERING EACH QUESTION. EACH PROJECT SHOULD BE UNIQUE. CREATE A DESKTOP FOLDER AND CALL IT YOURNAME INTEGRATION. ABC Fitness Inc. is a Regina-based fitness center that has been operating since January 1, 2011. ABC wants to celebrate eleven years in business! They want to encourage members to continue with them and also, new members to join. ABC has been keeping track of their memberships using only Excel. ABC wants to develop a database of its clients and membership sales to better understand where it should be directing its efforts. The file ABC Fitness Data File Regina.xlsx contains a worksheet showing its Members and a worksheet showing SalesReps who signed up the member. Part One:Create a Database from an Excel Workbook (25 marks) Required: 1. Create a database called Yourname ABC Fitness. 2. Import the two Excel worksheets into the ABC Fitness database as two new tables—Yourname Clients and Yourname SalesReps. Make any decisions as necessary so that the data is imported correctly and can be used as Access database tables. 3. ABC offers five different membership plan levels: Bronze, Silver, Gold, Platinum, and Legacy. Each plan has a unique set of features and are priced accordingly. Create a Yourname Plans table with the data below to add to the database. The checked boxes represent Yes values in a Yes/No field. PlanID PlanName Cost Equipment Running Track Training Plan Whirlpool Sauna Tennis Meals Locker Upgrade B1 Bronze $550 Tote Silver S1 Silver $950 Half Gold G1 Gold $1,800 Full Platinum P1 Platinum $2,750 Deluxe Legacy L1 Legacy $3,300 Deluxe Legacy+ 4. Create relationships between the tables. Troubleshoot any error messages if you encounter them during this process, ex. data type error. 5. Create and save the Relationship Report. 6. Create a form so that it will be very easy to add new members. Insert the logo and add a print record button. Adjust so that the form is attractive. Save the form as Client Form to your database. Part Two: Data Analysis and Summaries (30 marks) 7. Create the following Access queries to solve the following requirements: a. Load RepFname, RepLname, Cost, and a calculated field called Commission that is 15% of the Cost. Make this an aggregate query called Commission Query. b. Create an aggregate query to show the number of members in each Plan, and the total dollar value of memberships by each Plan. Memberships by Plan. c. Create an aggregate query to show the number of members for each City, and total dollar value of memberships by City. Memberships by City. d. Create a query called Renewal Letter Query to be used for a personalized promotional letter. It will contain: all the necessary contact information for a letter, as well as the Plan Name, Joined, Plan Date, StartWeight, End Weight, Cost, Upgrade fields. e. You are interested in finding out in which plan do the members have the highest average weight loss. Create a crosstab query to show this information. Save it as Weight Loss Crosstab. You may prefer to create a query that contains the necessary information and use it to create your Crosstab query. 8. Create a report from the Clients table. Use the fields LastName, Firstname, City, Joined, DateofBirth, and PlanID. Sort on DateofBirth – ascending. Save as Clients Report. Part Three: Export to Excel and Word (10 marks) 9. Export all the queries from Part Two to Microsoft Excel to the single file name called ABC Fitness Queries. 10. Export the Clients Report to Word. Format the report using the features of Word with a title and adjust the margins fonts, and tabs or columns so that it is attractive. Save a Clients Report in .docx format. Part Four: Promotion Calculations (18 marks) 11. Open the Renewal Letter Query worksheet in Excel. You are going to use this worksheet to generate your data for a personalized letter to your clients using a mail merge. The information below will help you to modify the worksheet to give you the necessary information for the mail out. 12. Insert a column after the PlanDate column called Renewal Date. In this column create a formula that adds one year to the PlanDate. 13. Insert a column called Renewal Discount. To calculate this discount, each client will get a $50 discount, plus $0.10 for each day they have been a member as of Nov. 1, 2021, plus additional $5 discount for each pound lost (i.e. the difference between start weight and end weight. If a client has gained weight, they will only get the $50 discount plus $0.10 per day of membership from date joined to Nov. 1, 2021. As a result, this column will require an IF function for it to be effective. 14. Calculate the Renewal Cost amount for each client by subtracting the Renewal Discount from the Plan Cost. 15. You also want to give your clients the chance to upgrade to the next higher Plan (i.e. the Upgrade field) at a significantly reduced cost. Create a new column called Upgrade Cost, which is calculated at 10% above their current Plan Cost. For example, if the client’s Gold Plan is $1800, the Upgrade to the Platinum Plan would be $1980 (i.e. $1800 plus 10%). Part Five: Promotional Letter (27 marks) 16. Create a personalized merged promotional letter for membership renewals. This letter will be a direct, “good news” letter containing the following: · it will be a two-page letter, properly formatted according to Office Administration Visual Identity Guide. Use Nov. 1, 2021 as the date of the letter. · a company letterhead with the name “ABC Fitness” (you create) · use the Excel worksheet Renewal Letter Query as the data source for the merge; include relevant fields from the query to personalize the letter to the client. · an opening paragraph explaining the purpose of the letter (i.e. plan renewal and upgrade opportunity) · a paragraph describing the benefits of ABC Fitness and the savings the client will receive as a result of this renewal and the upgrade promotion. · paste the Plan table into the letter. Remove the PlanID field. Format the table for appearance. Sort the table by Cost in ascending order. · include a brief closing paragraph encouraging the member to renew by December 31, 2021. · money amounts in the letter should be formatted to currency and two decimal places (use merge codes to format these values). · limit this mail out to members whose renewal date will be between November 1, 2021 and January 31, 2022. 17. Save the letter with the merge field codes as Renewal Main Letter. 18. Save the letters that has been merged as Renewal Merged Letters. Do not print. ABC Fitness Project1 ABC Fitnessp. 3 Part Five: Brochure (15 marks) 19. Make mailing labels for the letters. Save as ABC Labels. 20. Create a promotional brochure advertising the special classes offered at ABC Fitness. Here is an opportunity to be creative and show your hidden talents. You can create the brochure in Word, Excel, PowerPoint, or Publisher. It should be one page double sided. It is meant to encourage people to join these classes. Save as ABC Brochure. 21. Make sure you have saved all your files to your desktop folder. Zip the folder. 22. Drop off your completed files to the assigned dropbox.