Microsoft Access

A visual tour of the main ideas

behind Access
Microsoft Access is a database management system from Microsoft that combines the relational Access Database
Engine with a graphical user interface and software developed tools. It is member of the Microsoft 365 suite of
Applications, included in the Professional and higher editions or sold separately.
Initial release date: November 1992
Developer(s): Microsoft
License: Trialware
Programming Language: C++
Microsoft Access

Microsoft Access is a popular information management tool that helps

you store all kinds of information for Reporting, analysis, and
reference. With Microsoft Access, you can manage data more
efficiently and analyze large amounts of information.
Microsoft Access
Concepts

A visual tour of the main ideas

behind Access
“…relational database technology offers dramatic
improvements in productivity both for end users
and for application programmers.”
E.F. Codd
Used by 95% of “We’re using it to run our
business. We’re an 80 million

Fortune 500 dollar company.”

What is it?
“An operational database that stores, manages and tracks real-time
business information and that is often mission-critical.”

After 25 years, still no alternatives

“What I think Access does, it does extremely well and is the best
bang for the buck.”
Tables, columns, data types
A database structures data like a matrix and adds constraints to keep it that way

Tables and columns are well-defined A data type indicates the kind of data and the
way it’s stored
Data Type
A table represents a single Each field has a value that
subject, such as a person represents a single fact <= 255 characters Short Text

<= 64,000 characters Long Text

ID Last Name First Name Address City 1, 2, 4, 8, 16 bytes Number

1 Freehafer Nancy 123 1st Avenue Seattle 8 bytes Large Number

2 Cencini Andrew 123 2st Avenue Bellevue 8 bytes Date/Time

3 Kotas Jan 123 3st Avenue Redmond 8 bytes Currency

4 Sergienko Mariya 123 4st Avenue Kirkland

4 bytes AutoNumber

5 Thorpe Steven 123 5st Avenue Seattle

1 byte Yes/No

<= 2 GB OLE Object

6 Neipper Michael 123 6st Avenue Redmond
<= 8,192 characters Hyperlink
7 Zare Robert 123 7st Avenue Seattle
Varies Attachment

Varies Calculated
Each row (or record) is unique All values in a column are Varies Lookup Wizard…
and has a primary key the same in meaning and
format

Fundamental relational database principles

To be efficient, remove To be truthful, make To be thorough, create To be informative, create

redundant data data accurate table relationships wide-ranging queries
Relationships are fundamental
Good database design = data integrity

Schema design is a step-by-step Relationships join tables based on

approach called normalization primary and foreign keys
Normal form Description Action Type Symbol Example

Zero Some columns contain repeating Collapse values into one One-to-one 1:1 An employee has one office and each office
values column has one employee

First Some columns contain Remove redundancy One-to-many 1:M An employee is within a department, but a
redundant data department has many employees within it

Second Some columns are not based on Move those columns to Many-to-many M:M Each employee is assigned many projects and
the primary key other tables each project has many employees assigned to
it
Third All columns are based solely on Ready for business
the primary key

Well-designed schemas make complex ORDERS PRODUCTS

requests doable OrderID CustomerID ProductID ProductName

10248 WILMK 11 Queso Cabrales

Primary key 1:M Foreign key
42 Singaporean Hokkien Fried Me
10311 DUMON
69 Gudbrandsdalsost
72 Mozzarella di Giovanni
Order Details Orders
Products ID Order ID
ID Order ID Employee ID ORDER DETAILS
Product Code Product ID Customer ID
Product Name Quantity Order Date OrderID ProductID UnitPrice Quantity
Description Unit Price Shipped Date 10248 11 21.00 12
Standard Cost Discount Shipper ID 10248 45 14.00 10
List Price Status ID Ship Name
10248 72 34.80 5
Reorder Level Date Allocated Ship Address
Target Level 10311 42 14.00 6
10311 69 28.80 7
Queries provide answers
At the heart of Access is the query, which retrieves data or performs data actions

Products
ID
Product Code Order Details Created By Product Quantity
Product Name ID
Description Order ID Andrew Cencini Northwind Traders Green Tea 125
Standard Cost Product ID
List Price Quantity
Andrew Cencini Northwind Traders Coffee 100
Reorder Level Unit Price
Target Level Discount
Status ID Andrew Cencini Northwind Traders Beer 60
Date Allocated
Andrew Cencini Northwind Traders Chai 40
Field: Created By Product ID Quantity Purchase Order ID Purchased To Inventory
Table: Purchase Purchase Order Purchase Order Purchase Order
Purchase Order Details
Sort: Orders Details Details Details
Show:
Criteria: 90 Yes

Query Type Definition

Select Retrieves only the data that you want, combines data from tables, and defines form and report data sources
Parameter An ad hoc query that prompts you for field values, and then uses those values as criteria for your query
Totals A select query that lets you group and summarize data, such as total sales per product
Crosstab Creates a matrix to re-orient fields in rows and columns, and apply aggregate functions such as Count, Sum, Max
Make Table Creates a new table from a select query
Append Retrieves data from one or more tables and adds that data to another table
Update Changes data in a table based on criteria to specify which rows to update
Delete Removes data from a table based on criteria to specify which rows to remove
Pass-through Runs a query remotely on a database server to help improve performance
Expressions add value
Calculate values, combine/extract text, specify defaults, validate data

Expressions help enforce business rules

Must be permanent Orders can only be Product codes must be

employee to attend event placed on items in stock valid when entered

Expressions create meaningful information from data

Task Expression Example

Sum line items in a =Sum([Unit Price]) Item Quantity Unit Price

report footer Mirror 3 $25.53
Desk 4 $12.32
Lamp 6 $589.38
Chair 4 $212.92
Computer 1 $354.89

$1,195.04

Find orders shipped in Between #1/1/2019# And

Q1, 2019 #3/31/2019#

Create a calculated field Extended Price: [Quantity] *

[Unit Price]
Forms over data
Organize and edit data through rich forms that are windows to your database

Controls are building blocks for forms Form types

Label Tabs Browser Chart Subform Toggle Check Box Attachment Form/ Navigation Multiple
subform form item form Datasheet

Text Button Link Navigation Option Combo Box List Box Image Split form Modal Popup
dialog box form

Define input masks Create a form/subform (1:M relationship)

Customer: 2
Input Mask !\(999) ‘000\-0000;;
FirstName: Tracey

9 Northwind Tra (123)555-0100 Surname: Smith

(New) (___)___-____ 18
Order Id

OrderDate 28/04/2011

Navigate records easily

productId ItemName Quantity
First Current Next New Filter
3 Stapler 1
2 Note Pad A4 1
Record: 20 of 29 No Filter Search
1 Box of 20 Pens 1
5 Whole Punch 1
Previews Last Find 8 Ring Binder 1
Reports over data
Use reports to answer questions, find alternatives, devise strategies, assess risks

PROFIT AND LOSS STATEMENT

COMPANY NAME
INVOICE

Bedecs, Anna Gratacos Solsona, Anton Axen, Thomas

123 1st Street 123 2nd Street 123 3rd Street
Seattle WA 99999 Boston MS 99999 Los Angeles CA 99999

Lee, Christina O’Donnell, Martin Perez-Olaeta, Francisco

123 4th Street 123 5th Street 123 6th Street
New York NY 99999 Minneapolis MN 99999 Milwaukee WI 99999

Xie, Ming-Yang Andersen, Elizabeth Mortensen, Sven

123 7th Street 123 8th Street 123 9th Street
Boise ID 99999 Portland OR 99999 Salt Lake City UT 99999

Distribute PDF reports on schedule or Report types by users

upon request
User Types Examples

Frontline Scheduled, easy-to-read, one- Quality control, invoice,

Outlook worker pager, attractive graphics inventory, stock
Send E-mail message
Information Budgets, forecasts, sales
Detailed, hierarchical, grouped
OneDrive worker summaries
Save in public folder
Access Business intelligence (BI),
Business Statistical analysis,
customized (filter, sort,
SharePoint analyst elaborate charts
parameters)
Add to report library
Mailing labels, product
All Pretty-printed, informative catalogs, reference
manuals, directories
Extract, Transform, and Load (ETL)
Data is always on a journey, and Access is the landing pad

Extract Transform Load

Import/link from: Then, alter data: Finally, export to:
Access Join sources Access
Azure SQL Server Cleanse (correct, convert, dBASE
validate, reconcile)
dBASE Excel
Shape data for other
Dynamics 365 programs HTML
Excel Aggregate, group, ODBC
HTML summarize SharePoint list
Microsoft Graph Bulk update, append, Text
delete
OData XML
Make tables
ODBC
Outlook contacts
Salesforce
Access is like a regional airport for data that: travels
SharePoint list from many places, makes changes and connections, and
then travels to other places
SQL Server
Text
XML
Automation
For advanced tasks, use macros and Visual Basic for Applications (VBA)

Macros
Automate tasks and add functionality to your forms,
reports, and controls without code.

If Then Else

VBA
Use code to iterate through objects, create functions, step through each record,
respond to events, call Windows APIs

Update All
Migrate to SQL Server
Combine the benefits of Access with the industrial strength of SQL Server

Get Run Convert Link Test and Optimize

Ready SSMA objects tables revise performance

It’s a natural evolution SQL Server benefits:

Keep your Access front-end (forms, reports, local More concurrent users
queries, macros, VBA)
Increased availability
Move data and remote queries to an SQL Server
back-end High performance, scalability
Improved security
Immediate recoverability

Hybrid cloud Azure SQL Azure

database Cloud Azure SQL Server benefits:
Dynamic scalability, no downtime
Intelligent optimization
On premises
SQL Server
database
Enterprise Global scalability and availability
Access
Elimination of hardware costs
Reduced administration
Resources

Subject Topics

Introduction to tables
Tables, columns, data types
Introduction to data types
Relationships are fundamental Database design basics
Introduction to queries
Queries provide answers
Examples of query criteria

Expressions add value Use Access expressions

Introduction to forms
Forms over data
Create a form that contains a subform
Introduction to reports in Access
Reports over data
Create a grouped or summary report

Extract, Transform, and Load (ETL) Importing, linking, exporting data in Access

Introduction to macros
Automation
Introduction to Access programming
Migrate an Access database to SQL Server
Migrate to SQL Server
Take an Access excursion through SQL Server

For more information, see

support.office.com/access