E-A-G-L-E-S

1. E-A-G-L-E-S

2. Go to the Philadelphia Eagles site – the roster page (http://www.philadelphiaeagles.com/team/default.jsp)

3. Copy the roster from the web page and paste it into Excel

4. Heights become dates? • Excel has interpreted the height 5-11 as the date May 11. • One is tempted to say that Excel is just formatting the expression as a date and that one can simply choose not to format the cell containing the data in this manner. But changing the format yields the number 38118. ??? • The default expression for a date in Excel is the number of days that between the date and January 1, 1900.

5. Further complication • Whereas 6-1 got converted to 38139 (June 1, 2004) 6-0 got converted to 36678 (June 1, 2000). It could not interpret the 0 as a day so it interpreted it as a year. • We can recover using the Month( ), Day( ) and Year( ) functions.

6. Enter the formula =Month(D2) in cell I2 to recover the “month” which is really the feet portion of the height.

7. Copy the formula down the column • If you place the cursor into the lower right hand side of the cell, the cursor changes from a thick cross to a thin cross. • When the cursor is a thin cross, drag down the column releasing when you reach the bottom of the region containing data.

8. SpreadSheet after formula copied down

9. Note that the formula in I3 (copied from I2) refers to D3 whereas the formula copied had D2. This is known as relative addressing and is desired in this case.

10. Recovering the inches portion • Because 0’s got interpreted as years but everything else got interpreted as days, the next part requires an IF. • If the year is 2000, we want a zero. • If the year is not 2000, we want the result of the day function. • This is achieved by entering the Excel formula = IF(Year(D2)=2000,0,Day(D2))

11. Enter formula to recover inches.

12. Copy formula down column.

13. While we are at it, let us multiply the feet by 12 and add it to the inches to express the height just in inches. Then we only have one number to store.

14. Copy the formula down.

15. Insert a new column.

16. Highlight the data in the new column, right click and choose Copy.

17. In the newly inserted column, right click and choose Paste Special.

18. Choose Values in the Paste Special dialog box. Recall before we copied formulas now we want to copy the results of formulas not the formulas themselves.

19. Spreadsheet after Paste Special.

20. Delete all of the old height columns. (If you can come up with a better way to handle this unwanted conversion problem, let me know.)

21. Position is not single valued. • Note that some of the eagles play more than one position. • Single pieces of data in databases should be just that single pieces. We say the data should be single-valued not multi-valued. • Position will be a separate entity. And the relationship between players and positions will be many-to-many. • A player may play many positions. • A position may be filled by many players.

22. Eliminate the Position column. • We are setting up to import some data into an Access table. The position data does not belong to this table so we eliminate that column.

23. Years Experience  Year Started • The table holds data indicating the number of years the player has been in NFL but has an R if the player is a rookie. • It is better to store the year the player started instead of the number of years played because number of years played requires updating. • (There is an issue here. What if the player does not play every year? Then the number of years played cannot be calculated from the year started.)

24. Another IF formula to deal with the “R” that should be a 0.

25. Copy it down. Insert a new column, do a copy-paste special (values).

26. Then eliminate the Exp column and the last column.

27. Change some of the column headers.

28. Not ready to import yet. • One can import data into Access from Excel, but we do not want to do that. • The last name and first name are in the same cell, and we would like them to be separate fields in Access. • We might want to search for players with the same last name. • Similarly, the birth city and state are in the same cell and we would like to separate them. • We might want to search for players from the same state. • Try to make sure the data is “atomic.”

29. Go to File/Save As … In the Save as type select CSV. Two questions arise.

30. OK

31. Yes

32. Close Excel and open the CSV file in Notepad. The names are in quotes. It is trying to distinguish between the old commas and the new commas.

33. Go to Edit/Replace. Enter “ in Find what, leave Replace with blank and click Replace All.

34. Change the first line to reflect that name is now Lname and Fname, etc.

35. One last fine point is to eliminate any blank lines at the end of the file.

36. Follow the procedure we used before to import the data to Access.

37. Change Fla. To FL

38. Create a query that determines the average height and weight of the players.

39. Average Height and Weight in DataSheet View

40. Average Height and Weight in SQL View

41. Create a query that finds the highest weight of a player.

42. Highest Weight in DataSheet View

43. Highest Weight in SQL View

44. Heaviest Player • To write a query to determine the heaviest player, we take our previous query to find the maximum weight and use it as a subquery – making it the criterion in the weight field. • It should be in parentheses.

45. Using maximum weight query as a subquery to find heaviest player.

46. Heaviest Player Query in DataSheet View

47. Heaviest Player Query in SQL View

48. Make a parameterized query to list the players from a given state.

49. Group the players by the state they are from

50. Players grouped by state in DataSheet View