CIS 3380 (Kelley) Activity #1: Large Databases (Access/Excel) Mining Business Intelligence from a Large Operational Database *Last Updated 1/13/2020* Due Dates: Data Files: Setup Steps: The Scenario: Grading: (50 pts

max) Refer to Semester Schedule! Refer to Assignment Tab: Activity #1: Large Databases(Access/Excel) Right click each file below and save to your local drive/device: 1. Foodmart4c.accdb 2. HW1-LnameFname.doc (Note: This is your solution/answer sheet) *Rename Lname & Fname to YOUR Last Name & First Name! *Open and replace "(My Name is ???)" with "Your Name" in the Header You have been appointed to a management committee at Foodmart tasked with examining strategies to improve sales at the chain. At the kick-off meeting, it was observed that Foodmart had not attempted to construct profiles of their customers or their purchases let alone analyze how the data varies by country. You drew the short straw and were charged to do some research and analyze the data given to you and make some recommendations at the next meeting. Specifically, you are charged to do the following: 1. Identify the top 10 products sold given the data we have available 2. Identify the top 10 customers for 2017 fro our USA stores and comment on any common characteristics or traits like Income Group, Age, Location Showcase Foodmart total sales by store 3. 4. Profile Foodmart sales by various customer traits from total customer sales data by these data dimensions: › CustomerID, Country, Age & Income Groups, Gender, Marital Status 5. Do some high level customer analysis of your findings and make a recommendation on how to increase sales from our non & low-spending customers -Answers/data for EACH task on your answer sheet -Label ALL Excel tabs appropriately -Follow all directions for maximum points -Refer to the answer sheet for the breakdown of the points per task/sub-task 5 Files (Deliverables) to submit to Assignment Tab: Activity #1: Large Databases(Access/Excel) Page 1 of 8 CIS 3380 (Kelley) Activity #1: Large Databases (Access/Excel) Mining Business Intelligence from a Large Operational Database * You do not need to upload the Access Database to the LMS since it is rather large and, at times, when using a slower internet connection - will time out your session BUT if you have issues and want me to review the Access DB, it "cannot" be emailed since it is blocked so you will need to upload it to the LMS with your other files. Deliverable #1: HW1-LnameFname-Top10Products.pdf Deliverable #2: HW1-LnameFname-Top10Custs.pdf Deliverable #3: HW1-LnameFname-SalesByStore.pdf Deliverable #4: HW1-LnameFname.docx Deliverable #5: HW1-LnameFname.xlsx Optional Deliverable #6: Foodmartb.mdb or accdb Task 1*: Open the Foodmart4c.accdb with Microsoft Access then Update the Customers table: *Note: If you have a Mac or a "lighter version" of MS-Office on your PC - just go to a campus lab and complete the Access portions of the Assignment and save it to a USB drive to later import it to Excel on the device of your choice! 1. Open the 'Customers' table 2. Go to the last record in the table 3. Replace the following 3 field contents for the last record as follows: # Field Name Current Value 1 2 3 Annual Income 4. Save & Close the table. 5. As a result of doing this step, your name will appear in the query results as well as one of the income brackets in some of the pivot tables that will be constructed in the subsequent steps. 6. NOTE: Failure to do this step or task will result in -10 points to your grade ● ● LastName FirstName *** Task 2: DATABASE QUERIES: You will be creating 4 different queries Task 2a: Query #1: Top 10 Products SOLD (best practices: use Design Mode/View): 1. Click Create then Query Design 2. Add/Select the Products and the Facts tables 3. In the QBE grid, build a query with the following Tables & Fields (in sequence): TABLE FIELD(s) Products ProductName, ProductCategory, ProductSubCategory Facts: 4. Sort the data by Revenue (Descending) so as to show the highest product sales first 5. Now turn your detail query into a summary query: ??? ??? your name here *** Revenue NEW Value Your Last Name Your First Name Your First & Last Name Click the "Summation Symbol" at the top (far right) of the Query Design screen In the New "Totals:" row that appears in the QBE grid, we need to indicate how we want the results of our query to be summarized so that we don't have detail instances but summarized data by our stated criteria. Page 2 of 8 CIS 3380 (Kelley) Activity #1: Large Databases (Access/Excel) Mining Business Intelligence from a Large Operational Database * Click the chevron then select the option "Sum" so as to summarize the values of this field as it is unique (aka Group By) ALL the other fields in our query Make the query reveal only the Top 10 results and you do this by finding the Query Setup Comments area and click the dropdown next to the Results Option select 10 6. Save your Query as Top10Products 7. Run your Query to view the results and adjust as needed to correct the results (resave as necessary) 8. Record your "observations" about any common traits about the data in the answer document 9. Save the Query Results as a PDF to later submit to the Assignment tab as follows: ● For the "Revenue" field from the Facts Table, select the phrase "Group By" in the Totals row and you will notice a chevron appears 1. Click Create then Query Design 2. Add/Select the ALL but the Products table Click: File, Save As, Save Object as, PDF or XPS, Save As, navigate to where you want to save it plus name it properly as: HW1-LnameFname-Top10Products.pdf Task 2b: Query #2: Top 10 Customers for 2017 from our USA stores: 3. In the QBE grid, build a query with the following Tables & Fields (in sequence): FIELD(s) and Optional Criteria denoted by (xxx) TABLE ● Stores Dates The Year (select 2017 only) Revenue Facts: 4. Add Selection Criteria to our query so that we ONLY see these results: USA Stores AND for the year 2017 ONLY 5. Sort the data by Revenue (Descending) so as to show the highest customer sales first 6. Just like you did in the previous query (Task 2) Customers CustID, LastName, FirstName, City, State/Province, Country, Gender, AnnualIncome, BirthDate, Marital Status ● Country (select USA stores only) Sort the Revenue Descending so that the highest Revenue will show first Make it a summary query for the Revenue field selecting SUM 7. Review the data and zero in on the Highest & Lowest spending customer (ignoring the record for yourself) and record your answers in the answer sheet 8. Next, adjust your query to show only the Top 10 results then save it as Top10Custs 9. Run your Query to view the results and adjust as needed to correct the results and resave as needed 10. Record your "observations" about any common traits about the data in the answer document 11. Save the Query Results as a PDF to later submit to the Assignment tab as follows: Click: File, Save As, Save Object as, PDF or XPS, Save As, Navigate to where you want to save it naming it properly as: HW1-LnameFname-Top10Custs.pdf Task 2c: Query #3: Sales by Store: 1. Click Create then Query Design 2. Add/Select the ALL the tables Page 3 of 8 CIS 3380 (Kelley) Activity #1: Large Databases (Access/Excel) Mining Business Intelligence from a Large Operational Database * 3. Knowing what you know NOW in terms of query building - create a query that will reflect Total Sales by Store showcasing the highest store total sales 1st and lowest last 4. Save your Query as SalesByStore 5. Record your observations about the highest and lowest store sales wise in the answer document 6. Save the Query Results as a PDF to later submit to the Assignment tab as follows: Click: File, Save As, Save Object as, PDF or XPS, Save As, Navigate to where you want to save it naming it properly as: HW1-LnameFname-Sales ByStore.pdf ● Task 2d: Query #4: Summary Query (best practices - in Design Mode/View): 1. Create our last Query and select ALL the tables 2. In the QBE grid, build a query with the following Tables & Fields (in sequence): TABLE Customers FIELD(s) a. CustomerID, Gender, Birthdate, AnnualIncome, Marital Status b. Stores Country c. Facts: Revenue 3. *Optional Query Check: Run your query to verify it works and if you did it correctly, you will have 251,397 records (actually Sales Transactions) 4. Just like you did for the previous2 queries: Summarize the results by Revenue 5. *Optional Query Check: Run your query again to verify it works properly (8,738 records) 6. Save your Query as SalesData 7. Now IDENTIFY the one HIGHEST & LOWEST Spending Single Customer a. Click the Chevron Tool Next to the field SumOfRevenue and select "Largest to Smallest" sequence b. Ignoring the record for yourself – note the CustomerID for BOTH the absolute highest and lowest spending customers (just scroll down to the bottom for lowest) c. NOW OPEN the Customers table and you can merely scroll up/down to find the single record for the highest & lowest customer or more efficiently, click the chevron next to CustomerID and select Number Filters entering your target CustomerID's! d. Navigate to the 2 targeted records (specific CustomerID's) and reecord the specific data fields for the Highest & Lowest spending customers on your answer sheet in the appropriate table cells e. BONUS Extra Credit +3 points: i. What single product did the lowest spending customer buy? ii. Use the Access table drill down features (hint + symbol) to find it and record your answer in the answer sheet and a screen shot of the details 8. Close all the open tables and queries (do not save anything since creating/saving the SalesData query as it was only sorting/filtering data on the fly 9. Close and Exit Access Task 3: Importing Access Query Results/Data into Excel for further analysis Page 4 of 8 CIS 3380 (Kelley) Activity #1: Large Databases (Access/Excel) Mining Business Intelligence from a Large Operational Database * 1. Start Excel 2. We "need data" so we will import the Access Database Query Sales Data results you just created: a. Make command selections so as to get ready to “Get External Data from an Access Database" b. Navigate to the data source (where you have saved your Foodmart4b database with your query) and click "Open" c. Select your Sales Data query from the list (as a Table into the Existing Worksheet) d. Verify that all 8,738 records made it (plus 1 for the header row/column headings) 3. Rename the tab "Data" to make it easy to find/locate 4. Observation: Did you notice what format/style the imported data is in and what features or benefits it offers the savvy Excel user? (Record your answer on the worksheet: Task 5) Task 4: Work The Data: We are going to create Age Groupings! 1. New Field: Enter the phrase "Age" on Row 1 and the 1st column to the right of the data 2. Format time: Right click the "Age" COLUMN, click "Format Cells", Number and then set it to 0 decimal places. Failure to do this properly will generate "strange" numbers at times! 3. Now our formula/function to calculate Age (Row 2 - Cell H2): Enter as you see it below: a. =YEAR(TODAY()) *You see the current year appear in the column b. Now APPEND the formula you just typed with " – YEAR( ) And you will subsequently see: =YEAR(TODAY()) - YEAR([@Birthdate]) *Note: Cell C2 is the Customer BirthDate field and it will translate it to @Birthdate *Note: Magically - we have a calculation for their Age in ALL cells! 4. Next, we introduce our age categories: a. Click the Sheet2 tab at the bottom and Rename it "AgeCategories" b. Copy the table below into this tab (Cell A1) *or* Just enter it as is: age group 0 Under 20 20 20 to 29 30 30 to 39 40 40 to 49 50 50 to 59 60 60 to 69 70 70 to 79 80 80+ c. Now we are going to use this table to categorize our data by Age Group d. Go back to the Data Tab and in Column I, enter the header "AgeGroup" Page 5 of 8/n Assignment Instructions You have 5 deliverables which MUST be submitted/attached to this Canvas Assignment which include (Lname & Fname are YOUR Last & First Names): last name: first name: 1. HW1-LnameFname-Top10Products.pdf 2. HW1-LnameFname-Top10Custs.pdf 3. HW1-LnameFname-Sales ByStore.pdf 4. HW1-LnameFname.docx or doc 5. HW1-LnameFname.xlsx or xls Have fun with Excel and Access - and re-live those CIS1323. Also, it does not matter which version of Office use as the functions are ALL there. Remember to view the mini-lectures shown below for refreshers that is focused on successfully completing this homework activity/assignment. They were recorded a few years ago and don't 100% match the current version of the assignment but still give you a great refresher on both Access & Excel focused on the skills and tasks for this homework assignment. you NOTE: When grading it, if you make anything less than the max score, there will be either some comments via the feedback tab or a separate grading feedback worksheet document available. NO printouts are required "at all" - just the files submitted to TRACS via the Assignments tab. Additional items of note: 1. TxState offers FREE Microsoft Office 365 to all students but Access is "only" available on Windows Computers. http://www.tr.txstate.edu/software/office-students.html 2. McCoy 336 is the home for CIS tutors for selected courses including CIS3380 in the event that you need additional help/assistance and you don't want to or can't ask me for it or I'm not on campus/in my office during office hours. This is more focused on CIS courses than the SLAC lab in Alkek but in either case - they are not there to do your homework for you but to answer your questions and perhaps see/determine if you are on the right track. 3. When in doubt about something you did, were told to do or something.....ask ME as I am here to help and support you succeed in the class and the skills. VLOOKUP Additional Hints (and refer to the Vlookup jpg/pic file too that a past student did) 1. Copy/Paste the age chart/table onto a separate tab in XLS (from the Word document) 2. start the =VLOOKUP function in the 2nd row of the data tab (under the heading) 3. for the VLOOKUP components of the formula: -You are looking up the age of that row of data (click that cell) -You then define the table (but exclude the header rows) on the other tab (highlight it all and hit F4 to make it an absolute cell references) -The cell you want returned from the table is column 2 (the words)