Excel and Access

• Download this file.
  • This is an access database created from the pokemon data.
  • Double click on the database to get started.
• I just want to take a quick tour, not "learn" this software.
• Notice the tables on the left
  • Clicking on them will give us a spreadsheet view of the data.
  • There is also a query built in, you can click on that as well
  • You can see the sql command if you wish that produced the table.
• You can build user interfaces in access, custom reports, and other things.
• You can also import access databases directly into excel.
• Start excel
  • Go to the Data tab, Get External Data and From Access
  • 
  • Select the pokemon database.
  • It will ask about logging into the database
    • This database is not protected, so the default options should work.
  • Eventually you will get to the Select Table dialog
    • 
    • You probably don't want the result of the query.
    • But you probably do want all three tables
      • So enable multiple table selection
      • And select the three tables.
    • Note you can just select a single table or a subset of tables if you wish.
    • Please keep access open.
  • I want to explore the data
  • The Inport Data screen
    • 
    • Note it wants to add this to the Data Model automatically.
    • Click on properties at the bottom
    • 
    • Note, we can change how the worksheet is updated from the database.
    • Create the pivot table.
  • Notice the data relations are already imported.
  • Notice, we are working with queries, not tables in the pivot
    • 
  • Let's start simple
    • Put the TypeName in Rows
    • Put the ID (from the CardTypeTable) in the values
      • Change this to be Count
    • 
    • This would be nasty SQL

    • SELECT COUNT (*) AS BugCount
          FROM CardTypeTable,TypeTable 
          WHERE CardTypeTable.TypeID = TypeTable.TypeID 
                AND TypeName="Bug";
      

  • Start a new workbook.
  • Import the data base BUT
    • This time bring in the tabls
    • Select Multiple, and all three tables (not the query)
  • Notice the tables were named on inport
  • 
  • I renamed the worksheets (CardSheet, TypeCardSheet, TypeSheet)
  • Go to the Table_CardTable
  • Build a pivot table.
    • Keep the data model for this worksheet.
  • Try to find the Average Hit Points for each Type.
    • Grumble.
  • Let's modify the Card table to have the names of the types
    • Go to the TypeSheet and name the two columns, TypeID and TypeName
    • Go to the CardTypeSheet and name columns B and C CardTypeID, TypeCardID
    • Go to the CardSheet and add two columns, Type1 and Type2
    • 
    • We might be able to do this with normal lookups but I used an array formula.
    • Give it a try.
      • Deepest inside is an if
      • =if($A2=CardTypeID, TypeCardID, 0) AS AN ARRAY
        • But this will return a vector, so we need to wrap it in LARGE
      • =large(if($a2=CardTypeID, TypeCardID, 0),1) AS AN ARRAY
        • Looks good, so copy this over to the next column and change the 1 to a 2 in the large function.
        • Still good.
      • Lets do a match on the TypeTable
        • =MATCH(LARGE(IF($A2=CardTypeID, TypeCardID, 0),2),TypeID,0)
      • Finally an Index
        • =INDEX(TypeName,MATCH(LARGE(IF($A2=CardTypeID, TypeCardID, 0),1),TypeID,0))
    • Arg, I have some bad lookups.
      • I could wrap it in an if, but let's take advantage of the database.
      • Go into Access, to the TypeTable
      • Type in 0 and NA into the last row
      • 
      • Save this by clicking on the Tab and clicking on Save
      • 
      • Go back to excel and on the Data tab click on Refresh All
      • 
  • Now can you find the average speed for each type as type 1?
  • Can you find the average speed for each type?
    • I couldn't do this in a pivot table.
    • I ended up modifying the TypeSheet to do this
      • A column to count the number of each type as type 1
      • A column that averages the speed each type as type 1
      • A column that counts the number of each type as type 2
      • A column that averages the speed of each type as type 2
      • A column that computes overall average.
      • 
    • Verify with SQL

    • SELECT AVG(speed)
          FROM CardTable,CardTypeTable
          WHERE      CardTable.ID = CardTypeTable.CardID
                 AND CardTypeTable.TypeID=1;