Access

Busn 216
M365 Video #12

1
Access

• Access = Database
• Define Database: = Database is a collection of
related tables and is organized in a way that
allows access, retrieval and use of that data.
• What Access does:
1. Stores raw data
• Store data in small parts: Address City State Zip
• Instead of storing “925 10th St., 925 10th St Youngtown AZ 85031-5358
Youngtown, AZ 85031-5358” as one
piece of data, store it as 4 pieces of
data:

2. Creates useful information from raw

data to help make decisions
• Raw Data  Useful Information

2
Examples of Databases

Highline: Google:
• Highline stores raw data about: • Google stores raw data about every click
• Students you ever make:
• ID, Name, e-mail, grades, DOB, Address • What you typed into search engine
• Instructors • What you clicked on
• ID, Name, e-mail, Address
• How you misspelled words
• Useful information Highline might • Useful information Google might create:
create: • What ad to display
• GPA for business students • Most frequent links clicked on after the
• List of student names and e-mails for a word “Seattle” is typed
class • Correct spelling or phrase for a mistyped
• Graduation List search request

3
 Store Raw Data  Useful Information
Excel Access
• Proper Data Set in Excel • Proper Table (Data Set) in Access
• Field names in first row
• Field names in first row • Add Data Type and Field Properties so that bad raw data does
• Field names say what sort of data not enter the table
can go in the column • Note: In Excel, we saw an example of "Data Type" in Power Query

• Records in subsequent rows • Each record must have unique identifier (Primary Key)
• Record = row = collection of bits of • In order to prevent duplicate records
raw data = set of related data • Examples: Student ID, Invoice Number, Product ID
• In Excel, we saw an example of "Primary Key" when we created
• Data Analysis in Excel: Relationships between tables for our PivotTable reports.
• Create useful information from raw • Records in subsequent rows
• Record = row = collection of bits of raw data = set of related data
data to help make decisions
• Data Analysis in Access:
• We used:
• We will use: Queries and Reports
• Formulas like SUMIFS • In Excel, we saw an example of "Queries" in Power Query
• Sort, Filter, PivotTables, Excel • We will create relationships between tables so that we can
Tables, Relationships & Data Model create useful information from more than one table at a
& Power Query time.
• In Excel, we saw an example of “Relationships" when we create
Relationships between tables for our PivotTable reports. 4
Access or Excel for: Raw Data  Useful
Information?
Access Excel
• Create more robust data validation (data • Simple queries such as sorting, filtering and
types and field properties) to prevent bad PivotTables are easier to do in Excel.
data from getting into the tables. • “On The Fly” Data Analysis can be easier in
• Easier to build relationships between tables. Excel.
• Although we have Relationships and the Data • Most people know how to use Excel, at least a
Model, those are used for PivotTables little bit.
• Complex queries can be easier to create in • Some Data Modeling done with Power Query
Access than in Excel. and Excel’s Data Model can be:
• Easier to accomplish than Access
• Queries automatically update when raw
• Can hold more data more efficiently than with
data changes. Access
• If you have Queries that you run often, it • Busn 218 & BI 348 will explore theses topics in
may be easier to do in Access. more detail
• Power Query (Get and Transform) can do some • The combination of the two is good:
querying in Excel • Store data in Access
• Send raw data to Excel as needed
5
Four Important Objects In An Access
Database:
• Tables (Heart of any database)
• Forms (User interface)
• Queries (Ask a question of the database)
• Reports (Useful information created from database)

6
Some Of The Differences Between Access & The Other
MS Programs:

• While in Access, you can only have one database open at a time
• To view multiple databases, you can use Windows Explorer to open another database while you
have another one open.
• Save:
• When you enter raw data into database, Access saves the data automatically
• Use the Save button only when you are creating or changing the structure of:
• Tables
• Forms
• Queries
• Reports
• Undo/Redo
• Only works when you are working on:
• A record before it is saved (moved on to next record)
• Creating or changing an object (Table, Form, Query, Report)
7
Two Views For Each Object
• Regular View
• Table: Datasheet view
• Form: Form view
• Query: Datasheet view
• Report: Print Preview
• Design View (“Underneath view”)
• Design view allows us to change all structural elements in the object
• Although some structural elements can be added or changed in, Regular view,
Design allows you to change all elements