Homework 4
NBA Access Database
 

 Instructions:

  • Using Microsoft Access, create a new database named NBA_Your_Name  (Access 2007/2010 will place .accdb for the file extension, older versions of Access will place .mdb for the extension).
     
  • Follow the instruction below to create tables, queries, and reports.

  • Submit the .accdb or .mdb file on eCampus.
     
  • For a video on doing this assignment using Access, see my YouTube channel:
    https://www.youtube.com/watch?v=rOv-dJHLwOI
     

 

 Completed Database:

 


1.  Create a new database named NBA_Your_Name (Access will add the .accdb extension).  In Access, you name a file and select its location (e.g. your flash drive) when its created.

2.  Download and import the NBA Players Excel Spreadsheet using the instructions below.

Importing:  In Access, click on the External Data tab, and select Excel on the menu bar.  In the window, browse and select your downloaded Excel file.

It will now take you through the Import Spreadsheet Wizard.  Verify these selections in the wizard:
(a)  Show Worksheets - The Players sheet should be selected.
(b)  First Row Should Contain Column Headings - This option should be checked.
(c)  No primary key - No primary key is needed for this table.
(d)  Import to Table:  Name the table NBA Players.

Your imported table should look like this:



Here's what the column abbreviations mean:

GP = games played MPG = minutes per game TOr = turnovers per possession FTA = free throws attempted FTA = free throws attempted FT% = free throw percentage 2PA = 2 point shots attempted 2P% = 2 point shots percentage
3PA = 3 point shots attempted 3P% = 3 point shots percentage TS% = total shooting percentage PPG = points per game RPG = rebounds per game SPG = steals per game BPG = blocks per game  


3.  Enter yourself as a player in the NBA Players table.  Enter 18 for your age.  Enter Dallas Mavericks for your team.  The rest of the information is unimportant.


4.  Create an NBA Teams table and enter data for all NBA teams.  On the Create tab, select Table.  Under the View option on the left side of the menu bar, select Design View.  Name the table NBA Teams.  Create the fields as shown below.  Note that the Team should be the primary key.



Enter the data for all NBA teams.  Make sure you name the teams identical to the way they're named in the NBA Players table so that the two tables can have a relationship.  Wikipedia is a good source for the team information.


5.  Create a query showing the free throw percentage for players with over 100 free throws.  Sort it in descending order of FT%.

Under the Create tab, select Query Design.  In the Show Table window, add the NBA Players table.  Add the fields and criteria shown below.  Run the query.  Right-click the tab and rename it to FT% for Players with over 100 FT's.




6.  Create a query for players over 35 years old.  Add both tables as shown below so that you can list the player's Division from the Teams table.  Note that the tables should have a relationship between the Team field.  Sort it in descending order of Age.




7.  Create a query for players under 21 years old.  Show the fields shown below.  Sort it in ascending order of Age.  Notice that your name will appear at the top of this query.





8.  Create a query showing players who scored more than 20 points per game.  Show the fields shown below.  Sort it in descending order of PPG.




9.  Create a query named Teams with most Championships.  Use the Teams table only.  Show these fields: Team, Division, Head Coach, and Championships.  Sort it in descending order of Championships.


10.  Use the Report Wizard to create the report for Team Player Stats.  Using the NBA Players table, select these fields:  Player, Team, Age, FT%, TS%.  Select Team for the grouping.  Under Summary Options and select the following:



Your report should look like this:




11.  You're finished!!!