CIS 123 - Access Demo

Access Demonstration

Our task is to create a database containing statistics for a selection of players in Major League Baseball. The sample data for this project comes from Yahoo! Sports.

  • Create a new database named Stats.accdb
  • Create a new table named Stats by importing the Excel spreadsheet stats.xls. Remember to change the name of the table from "stats" to "Stats" (I care about the upper case S).
  • Open the Stats table in design view and delete the ID field. Then change the size of the PlayerName field to 30, and the size of the Team field to 3.
  • Look at the Stats table in datasheet view, find all the records that have KAN in the Team field/column, and change KAN to KC.
  • Resize all the columns in datasheet view to automatically fit the data. Then close and save the Stats table.
  • Create a new table using design view. The table should be named Team. There should be three fields:
    • TeamID, a text field with size 3 - make this field the primary key
    • City, a text field with size 15
    • TeamName, a text field with size 15
  • Use the datasheet view to add the following record to the Team table: TeamID = CHC, City = Chicago, TeamName = Cubs
  • Close and save the Team table.
  • Import the table in the Teams database and copy all the records in that table into the Team table you already created using copy and paste. Access should tell you that you are pasting in 29 records.
  • Resize the fields in the Team table to automatically fit the data and then close the Team table.
  • Use the Form Wizard to create a columnar format form named Team which uses all the fields from the Team table. It doesn't matter what style you choose.
  • Use the form you just created to change the following information:
    • TeamID: KC, change the City from KC to Kansas City
    • TeamID: PIT, change the City from Pitzberg to Pittsburgh
    • TeamID: CIN, change the team name from Bengals to Reds
    • add the record: TeamID=CWS, City=Chicago, TeamName=White Sox
    • delete the record for the Boston Beaneaters (TeamID = BB)
  • Close any open tables or forms and then create a new columnar form based on all the fields in the Stats table. Use the "Access 2003" style. Save the form with the name Statistics.
  • Create a new query named Players. It should display the Team and PlayerName fields from the Stats table, and be sorted in ascending order by Team.
  • Create a relationship between the two tables using the Team field from the Stats table and the TeamID field from the Team table. Select all three options for enforcing referential integrity.
  • Create a new query named PlayersByAverage using design view. Display the TeamName field from the Team table, and the PlayerName, Average, and AtBats fields from the Stats table. Sort the query in descending order by average. Set the criteria so the query only displays records where the AtBats field has a value greater than 20.
  • Create a report named OnBasePercentages using the Report Wizard. The report should include the TeamName field from the Team table, and the PlayerName and OBP fields from the Stats table. The report should sort the data in descending order by the OBP field. Choose the Stepped Layout with the Solstice style. Make sure all the data shows up properly on the report.
  • Save your database.