Transcript - Catalyst

INTRODUCTION TO
ACCESS 2010
Winter 2013
Basics of Access
• Data Management System
• Allows for multiple levels of data
• Relational Database
• User defined relations allows for simple data input and export
• Allow multiple levels of data to easily be identified
• Microsoft Product
• Easily translatable to and from Excel
• Allows for forms for ease of input and queries for ease of export
• Best tutorial I could find (mostly using sample data from
Microsoft)
• http://www.functionx.com/access/Lesson01.htm
• A Google search will get you less thorough, though faster, ones
Opening Existing Database / Files
• Any Access file
• File, Open, filename
• Microsoft sample datasets
• Open access, samples, Northwind Sales
• Browse a bit now, but it exists for your leisure
• Importing Excel files to Access
• Open Access, Choose blank database
• External data, Excel, File name, Import as table
• Similar steps for text files
• Can append to existing tables, which I will go over later.
Creating New Databases
• Playing with existing databases only teaches you so
much.
• To really learn it, need to make new dataset from scratch.
• Make this something that makes sense to you and is easy
(database of books you own, family history, etc.)
• Want the difficulty to come from learning a new program, not in the data
itself
• Our example today will be a database of some sci-fi and fantasy quests.
Like I said, what makes sense to you. Feel free to make up data.
• To make a new database, open Access, choose blank
database
• Now, we can create a new database from scratch,
including relations, tables, and forms.
Before Beginning, Structure
• Unit(s) of observation
• Often have multiple units of observation, with each table only having 1
unit of observation
• Relations
• Need a way to relate information across tables
• What it will be used for
• Helps you figure out the structure
• It helps to draw things out on a scratch piece of paper.
Sample Database Structure
• Tables
• Characters: Gives information on the characters themselves
• Character ID, Universe ID, Company ID, Name (Last and First), Rank, Gender, Phone,
Email, City of Origin, Good Character Designation, Notes, Catch Phrase
• Quests: Gives information regarding the specific Quest
• Quest ID, Task ID, Character ID, Description of Quest, Why This Character, Length of
Time to Quest Completion, Success of Quest, Character Live
• Tasks: Give information on type of task the Quests could be
• Task Id, Name of Task, Description, Legality, Good
• Universe: Gives information regarding the specific Universe
• Universe ID, Universe Name, Universe type, Media type, Magic, FTL, Creator of
Universe
• Company: Gives information regarding the company of the quest
• Company ID, Universe ID, Company Name, Address, City, State, Country, Planet,
• Actors: Gives information about the actors playing the characters
• Actor ID, Name (last and first), Gender, Notes, Country
• Relations
• Need to link the main ID (in red) with the other tables (in purple)
Tables
• Tables are the way data is stored in Access
• To create a new table: Create, Table
• Each observation in a table needs a unique identifying number
• Best to do automatic numbering that Access does unless you have
valid reason not to (for example, get data from US Census and use
FIPS number or have predefined identification numbers for individuals
in a study).
• Each variable in the table describes at that unit of observation
• So, for our quest data, we can have character name but not the name
of all the people in their company.
• Each variable has a data type
• Number, text, currency, data/time, yes/no, etc.
• Lets create some new tables 
Relations (also called relationships)
• Relations:
• Make filling out tables from forms easier (things populate
automatically once the relation is defined)
• Gives structure to the database
• Relations need to be made after tables and before forms
• Tables do not need to be finished filled out (ie, all the observations
or variables that are not related) but they do need to have the same
general structure (ie, you know which values relate across tables).
• Database tools, relationships
• Add all the tables you want to relate
• Double click Main ID on Main table, relate that ID to the Id that
means the same thing on the other tables. Do for all relations.
Forms
• Instead of directly inputting into tables, input into
something user friendly and it will automatically populate
the appropriate tables
• Also allows multiple tables to be populated at once
• Create form, choose all the variables you want.
• You can spend time to make it pretty (the link I gave you has a lot
of info on how to do that).
• Useful once database set-up as it allows for an easy way
to input more observations / new data
Queries
• Now that we have a intact dataset, what can we do with
it?
• Queries are a sophisticated search (if, and, etc)
• You can save the results of a query into a Excel file
• Allows for ease of transfer of data to another program (Stata, R, etc.)
• You can change something in the query and thus change ALL
entries
• Like if you spelled Bilbo Baggins as Baginses and want to correct all
instances of the mistake
• Edits in a query reverberate to the database as a whole
• You can also run a query on an Access database from Excel
• So, we can look for things like: female characters, all quests by
Malcom Reynolds, all sci-fi universes, etc.
Prettiness
• It can be useful for some people to make the tables and
forms more readable.
• Some basics:
• Table design
• Form design
• Beyond this, look at the source.
• Shows how to make a form that shows up on a webpage that looks
like a pizza that orders the pizza at the same time it records the
order in the database. Pretty cool.
Access Versus …..
• Excel
• Cons
• Higher learning curve and upfront structure determination\
• Pros
• Allows for different levels of observations that relate easily to each other
in a manner that is intuitive
• Queries allow for ease of data extraction
• Stata/SPSS
• Cons
• Need to export to these programs to do the actual statistical
• Pros
• No need to worry about if data in long form or short form
• Easier to see different levels of observations