page 1 ismt e-120 desktop applications for managers introduction to microsoft access
TRANSCRIPT
![Page 1: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/1.jpg)
Page 1
ISMT E-120Desktop Applications for Managers
Introduction to Microsoft Access
![Page 2: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/2.jpg)
Page 2
• We’ll Cover the Minimum• Relational Databases• Access Objects• Creating a Database• Designing Databases
Introduction to Microsoft Access
![Page 3: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/3.jpg)
Page 3
Introduction to Microsoft Access
• The Minimum• Relational Databases• Access Objects• Creating a Database• Designing Databases
![Page 4: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/4.jpg)
Page 4
The Absolute Minimum
• Data Contained in Tables• Tables have fields or columns • Tables have rows or records• Record is a row in a table, a set of fields• Table is made up of a set of records• Query > which fields from which rows• Sort and Group records
![Page 5: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/5.jpg)
Page 5
The Minimum
• MSQuery– connect to data source– choose from tables or queries saved in
database• Access
– drag-and-drop to write queries– Or write SQL
![Page 6: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/6.jpg)
Page 6
Introduction to Microsoft Access
• The Absolute Minimum• Relational Databases• Access Objects• Creating a Database• Designing Databases
![Page 7: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/7.jpg)
Page 7
What’s “Relational”?
• Simple Database = 1 Subject• Complex Database = Many Subjects• 1 Subject = 1 “Entity” 1 Table• “Relationships” Link Tables
![Page 8: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/8.jpg)
Page 8
What’s an Entity?
• Subject of a Database and table• Noun (person, place thing)• Important Topic• Something To Be Described
![Page 9: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/9.jpg)
Page 9
Examples of Entities
• Grade Database– Instructors– Classes– Assignments– Students– Enrollments– Results
• Music Collection– Music genres– Music mediums– Artists– Songs– Instruments
![Page 10: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/10.jpg)
Page 10
What’s a Relationship?
• A verb describes a relationship• They are linkages between entities• Types of relationships
one-to-one one-to-many many-to-many
![Page 11: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/11.jpg)
Page 11
Examples of Relationships
• Instructors Teach Classes• Students Enroll in Classes• Classes Require Assignments• Students Submit Assignments• Students Receive Results
![Page 12: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/12.jpg)
Page 12
Introduction to Microsoft Access
• The Minimum• Relational Databases• Access Database Objects• Creating a Database• Designing Databases
![Page 13: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/13.jpg)
Page 13
Access Objects
• Tables• Queries• Forms• Reports• Macros• Modules
![Page 14: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/14.jpg)
Page 14
Tables
• One Table, One Entity/Topic/Subject• Can Have Base or Reference Tables• Made up of Fields and Attributes• Fields May Have Many Properties• Table Must Have a Primary Key• Relationship = same key in more than
one table to link them – Primary key in one table and foreign key in second table
![Page 15: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/15.jpg)
Page 15
Queries• Types of Queries
– Select - get data out– Append - put data in– Update - change data– Delete - delete data
• SQL does the work
![Page 16: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/16.jpg)
Page 16
Reports
• Based on Table or Query• Format Query Results• One Query, Many Reports• One Report, Many Queries
![Page 17: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/17.jpg)
Page 17
Forms
• Based on Table or Query• Forms help users to:
– View data– Insert data– Update data– Delete data
• Customary User Interface
![Page 18: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/18.jpg)
Page 18
Macros
• Stored Sequence of Operations• Can be executed any time
![Page 19: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/19.jpg)
Page 19
Modules
• Specialized Software Program• Beyond the scope of ISMT E-120
![Page 20: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/20.jpg)
Page 20
Introduction to Microsoft Access
• The Minimum• Relational Databases• Access Objects• Creating a Database• Designing Databases
![Page 21: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/21.jpg)
Page 21
Creating a Database
• Define Tables and Keys– Begin with pencil and paper or a white
board• Link Tables• Write Queries
– data input, reports• Develop Forms• Write Reports
![Page 22: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/22.jpg)
Page 22
Example: Project Tracking
• Entities– projects– clients– status reports– This is highly simplified
• Tasks• Milestones
• Relationships– client sponsors project– project accountable to client– status report updates project– project documented by status report
![Page 23: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/23.jpg)
Page 23
Project Entities
• ID• Name• Client• Service Code• Start Date• End Date• Complete
• Manager• Deliverables• Core Technology• Budget• Billing Method• Notes
![Page 24: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/24.jpg)
Page 24
Establishing Relationships
• Tools Relationships…• Normally Include All Tables• Relationship Properties• Relationships Carry Over to Queries
![Page 25: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/25.jpg)
Page 25
Writing Queries
• Choose Tables• Choose Fields• Sort• Filter with Criteria• Parameters
![Page 26: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/26.jpg)
Page 26
Creating Forms
• Based on Tables– walk through table one row at a time
• Based on Queries– more powerful– update several tables at once
![Page 27: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/27.jpg)
Page 27
Creating Reports
• Based on Tables
• Based on Queries– more powerful– link several tables at once– select fields– add calculations, etc.
![Page 28: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/28.jpg)
Page 28
Introduction to Microsoft Access
• The Minimum• Relational Databases• Access Objects• Creating a Database• Designing Databases
![Page 29: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/29.jpg)
Page 29
Designing Databases
• Understand the Application• Identify Data Elements• Normalize Data• Design the Interface
![Page 30: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/30.jpg)
Page 30
Understand the Application
• [Document Sequence of Steps]• [Map Information Flows]• [Identify Decision Points]• [View From Audience Perspectives]
![Page 31: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/31.jpg)
Page 31
Identify Data Elements
Might use:• Manual Input Forms• Reports• Memos
![Page 32: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/32.jpg)
Page 32
Normalize Data
• Crucial Design Process• Art and Science• Requires In-Depth Application
Knowledge
![Page 33: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/33.jpg)
Page 33
Benefits of Normalization
• Easier to Maintain Information• Easier to Query Database• Extensible Design• Promotes Data Integrity
![Page 34: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/34.jpg)
Page 34
What is Normalization?
• Eliminating Redundancy• Grouping Data Items into Manageable
Collections• Verifying Completeness of data
![Page 35: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/35.jpg)
Page 35
Normalization: Step 1
• List Every Data Item in One Table– columns are fields– rows are “records”
• 1 Cell = 1 Piece of Data• Add Duplicate Information as Needed• Find or Create a “Key”
– field or combination of fields uniquely identifying each row (primary key)
• First Normal Form
![Page 36: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/36.jpg)
Page 36
Normalization: Step 2
• Which Keys Determine Which Fields?– “functional dependencies”– one key value determines one field value
• Could Be > 1 Set of Keys• Break Into Separate Tables According to
Functional Dependencies• Second Normal Form
![Page 37: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/37.jpg)
Page 37
Normalization: Step 3
• Look for Functional Dependencies Among Non-Key Fields
• Remove Any Dependent Non-Key Fields
• Make Sure No Loss of Information– i.e., fields should be in some other table
• Third Normal Form
![Page 38: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/38.jpg)
Page 38
Normalization: Sanity Check
• Make Sure No Information Lost• Make Sure Tables “Connect”
– one-to-one– one-to-many– NOT many-to-many
• Make Sure Keys are Unique• Referential Integrity
![Page 39: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/39.jpg)
Page 39
Normalization: More Realism (project database)
• ID• Name• Client• Service Code• Start Date• End Date• Complete
• Manager• Deliverables• Core Technology• Budget• Billing Method• Notes
![Page 40: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/40.jpg)
Page 40
What Else Should We Track?
• Multiple Clients per Project• Multiple Deliverables per Project• Multiple Tasks per Deliverable• Status Reports
• Due Date Extensions & Changes• Budget Extensions & Reductions• Staff Assigned to Tasks, Deliverables• Where Does It End?
![Page 41: Page 1 ISMT E-120 Desktop Applications for Managers Introduction to Microsoft Access](https://reader036.vdocuments.us/reader036/viewer/2022081516/56649dd45503460f94acbd74/html5/thumbnails/41.jpg)
Page 41
Design Interface
• The 4th part of database design• Designing the user interface• Beyond this Course