Using a combination of Excel and Access, analyze and present the data in the Popular Kids study.
Carefully read and follow the directions found in the attached “Popular Kids” document.
Use your analytical and critical thinkings skills to complete the project – you must create spreadsheet pages and database files using the given base PopularKids Excel file file and the illustrated Documentation file.
Remember – The illustrated instructions are a guide not a tutorial. They do not show everything you must do in the illustration. They do show the necessary tabs in the workbook even if you do not see further examples.
Case in point – The Rank analysis of the last 4 columns of Data. You must create a Rank Analysis worksheet and analyze the rankings of responses for each of the topics listed in the columns Grades, Sports, Looks and Money. Create a results table and the appropriate charts in the Rank Analysis worksheet (You only see the worksheet tab in the popularkidsDocumentation file) find the descriptive statistics for each topic and each gender (Max, Min, Average, by topic and by gender).
Review the Example Rank Analysis PowerPoint for ideas on how to do the rank analysis sheet for this problem.
All you need to do this assignment has been covered by the course assignments and the readings. Use the e-text readings as a reference for things you may not know how to do.
Submit both the completed Excel .xlsx and the Access.accdb files here by clicking on the Design Solution title above.
- Save popularkids.xlsx (on your M: drive) from Blackboard
- Read Overviewworksheet
- In a browser, open the URL given in cell A1 of the Overviewworksheet
- Highlight the data on the Web page and Copy
- Paste the data into a text file (Notepad) and save it
- Insert a new worksheet RawData
- Populate worksheet with data from file: Data > From Text (Delimited > Tab > Finished)
- Copy RawDatato a new worksheet named Data
- Add range names for each column:
Gender, Grade, Age, Race, UrbanRural, School, Goals, Grades, Sports, Looks, Money - Insert a new worksheet RangeNamesto document the names that you have defined
(Formulas > Use in Formula > Paste Names)
Access:
- Open Access 2013 and create an empty database called popularkids
- Import the DataExcel worksheet into a table called data
External Data > Excel
Choose Import the source data into a new table in the current database
Browse to your Excel file and click OK
Choose Data worksheet to import and click Next>
Select box First Row Contains Column Headings and click Next
General format is OK for now, click Next>
Let Access add primary key; Next>
Import to Table: Data; Finish; Close
- Explore Access:
Home tab
Sort & Filter
Advanced > Filter by Form > Toggle Filter
