Fran Edwards is the proud owner of several yoga studios in the midwest. Her 5 studios are part of a franchised, nationwide chain called, YogaMe. Fran got hooked on the power of yoga and fitness, after she was injured in a terrible car accident 12 years ago. Yoga helped her regain her mobility and has been a part of her daily routine ever since. Fran opened her first studio in 2014. Her clients were immediately draw to her and her unique classes. Within a few years, Fran decided to open more locations. Now, she owns 5 studios spread out in the U.S. midwest states. Because Fran wants her clients to keep or regain flexibility, her studios work on memberships. Like the other studios in the YogaMe franchise, for a flat rate, her clients can join unlimited classes every week at any of her studios. To promote her business, she sells gift cards to current clients to have them bring friends and family to the studio. The YogaMe studios offer yoga classes that are open to both women and men. The available classes include gentle yoga, chair yoga, prenatal yoga, restorative yoga, and all levels flow yoga. Case Scenario Although Fran’s studios are successful, she realizes that she must use technology to make better decisions. Fran’s next step is to open a 6th location in the midwest and wants to evaluate her studios to see where the 6th studio should be located. YogaMe headquarters has given Fran some summary spreadsheets, but they are too cumbersome to extract any meaningful data. Now Fran turns to you, her new analyst, to finish the database and make this a useable system. Within the database, Fran has membership data for each of her studios since each studio opened. Additionally, the franchise headquarters has given Fran monthly averages of the other studios in her state. Quickly, Fran realizes that there isn’t a way to connect these tables or provide basic information. She gives you the following information thinking that if this data is input to the database, you might be able to create the system she needs.YogaMe case – page 2 2 Fran’s current Studio List: StudioID City State 201 Geneva IL 202 Carmel IN 203 Leawood KS 204 Northville MI 205 St. Louis Park MN You need to create a table to house the Studio List information above. Additionally, you will need to use the YogaMe Monthly Data workbook to create two more tables. Import both the “All Fran’s Studios” Excel worksheet and “Statewide Monthly Data” to the database as two more tables. When importing, allow Access to create a primary keys for both tables. For “Studio List”, “Statewide Data” and “All Fran’s Studios” tables, be sure to select appropriate data types and field sizes. Knowing that you want to query data residing in the various tables, you will need to create the appropriate relationships between the “Studio List” (table shown above), “All Fran’s Studios” table (imported from Excel), and the “Membership Rates” table (provided in the YogaMe.accdb student file). Fran wants to gather the following information from the data: 1. She wants to gather the annual revenue for each studio (number of members and prospects multiplied by appropriate membership rate and add in the gift card sales.) Be sure to include the City, State, Studio Number, Year, and the Annual Revenue in the query. So, that Fran can run this query again, save and name this query “Annual Revenue by Studio”. 2. Fran wants to cull down the results for the “Annual Revenue by Studio” query by only including data for studios 201, 202, and 203. Name this new query “Annual Revenue for 201-203”. 3. Fran also would like to look at her annual studios’ data for one specific year. Refining the query for “Annual Revenue by Studio” yet again, this new query should prompt Fran for which year she would like to view before displaying results. Run this query for 2019 and save as “Annual Revenue for single year”. 4. Fran also would like to look at the all the statewide revenue data for all studios. She would like to see the revenue totals for each year by state. Running this query should create another table for the database. Name this table “Statewide Annual Revenue”. Once the new table is created, you will need to clean up the column names, data types, and select primary key(s). Save this query as “Statewide Annual Revenue Query”. 5. Furthermore, Fran would like a query that includes her studios’ annual revenue by state, alongside the statewide only data, and add a column to compare her studios’ under or over performance to the state. The under/over performance should be a dollar figure of how much better or worse the studio did when compared to the statewide totals. Run this query only for 2019. This query should be named “Annual Studio vs State Revenue”. Be sure to also show the state and year fields. 6. Additionally, Fran is concerned with the growth of her existing studios. She wants to know which of her studios are performing the best with membership sales. Create a single query to calculate member conversion rate (number of memberships sold divided by number of prospects/opportunities) by studio. Sort by best performing to worst performing. Run this for YogaMe case – page 3 3 2019 only. Which studio had the highest average rate of sales to prospective members? Return the studio number, geographic location (city and state in one field), and percentage. Name this query “Studio Membership Performance”. 7. Given the information provided in the last few queries, which state would you recommend for the 6th YogaMe studio? Why? 8. Create a form for data entry for the All Fran’s Studios table. Include the fields: StudioID, Year, Month, Current Members, Members Sold, Attrition, and Gift Card Sales. Make data entry easier by using combo boxes (drop down boxes) for the StudioID field. You should display a unique list of studio IDs without duplicates. (Hint: If you didn’t create the relationships earlier in the Relationship window, this will not work. Second hint: The StudioID field does NOT pull from the All Fran’s Studios table.) Include buttons on this form for saving the record, adding a new record, and closing the form. Also find a picture to include as a logo for YogaMe on this form. Name and title this form “Monthly Sales”. Example of form is shown below:YogaMe case – page 4 4 9. Create a second form with an embedded subform. Use the tables Studio List (StudioID, City, State) and All Fran’s Studios (Year, Month, Current Members, Members Sold, Net Gain Loss). Use a combo box to allow users to search for all records for a specific studio ID. The dropdown list should include both the StudioID and the state fields. Each StudioID should appear only once. The form should also include a logo, the form title “Studio History”, a button to print the form and another to search for a specific record. Name the main form “Studio History” and the subform “History subform”. Example of form and subform is shown below: 10. To make this system more user friendly, Fran would like you to create a switchboard that will run all the queries above and allow the user to exit the system. When the database is opened, the switchboard should be the first screen the user will see. See the example below: