Exploring Microsoft Office Access Finding Your Way Through

Exploring Microsoft Office Access Finding Your Way Through a Database 1

Objectives Definitions/terms ¢ Explore, describe, navigate the objects in an Access Database ¢ file management ¢ Backup, compact, and repair Access files ¢ Create filters ¢ Sort table data on one or more fields ¢ 2

Objectives (continued) Work with different views in Access objects ¢ Know when to use Access or Excel to manage data ¢ Use the relationship window ¢ 3

What is a Database ¢A database describes a collection of data organized to allow storage, access, retrieval and the use of data. ¢ Contains at least one table but typically contain many more.

Who Uses a Database(DB)? A Database Administrator (DBA) maintains the DB. ¢ Programmers write routines to save and store data to the DB. ¢ Report writers write queries to build reports off the data. These reports are used to make business decisions. ¢ End users interact with the DB through the application. ¢ Feeds other systems via a push or pull. ¢

What is Microsoft Access? ¢A database management system. ¢ DBMS -software tool that allows you to use a computer to create databases, add, change, and delete data in a database. ¢ Some Other popular DBMS’s include Oracle, SQL Server and My. SQL…. . Bigtable

Why Access ¢ Easier than other DBMS’ to learn on ¢ Relative low cost /Availability ¢ DB principles can be applied to other database management systems. ¢ Pull information into Access/import ¢ Can be used as a reporting tool ¢ Can develop full application in Access

Why Not Access Used for small business or specific departments within a larger company. ¢ Limited Size (2 GB but can be worked around via ¢ using multiple linked DB’s. ) ¢ Limited amount of Users (255).

Objects Tables ¢ Forms ¢ Queries ¢ Reports ¢ Objects 9

Database Terminology Database ¢ Table ¢ Record ¢ Field ¢ Individual fields Individual tables in a database Records ¢ A database consists of one or more tables. Each table consists of records which contain information about a single entity. An example of one complete record would be the name, author, isbn#, published date and publisher of textbooks. Each set of information regarding one book is considered to be one record. The name, author isbn#, published data and publisher in the above example are the individual fields that make up one record. 10

Database Terminology ¢ ¢ A database consists of one or more tables. Each table consists of records which contain information about a single entity (person, place or thing). ¢ The name, author isbn#, published data and publisher in the previous example are the individual fields that make up one record. 11

Table Views ¢ Different Views of tables: 1. Design -view used to create fields 2. Datasheet view -used to add, edit, or delete records

Work with Table Views Design View Datasheet View – used to add, modify, delete and view records ¢ Design View – used to create and modify the fields in a table ¢ 13

Datasheet View Primary key field Navigation bar Navigation buttons Scroll bar 14

Filters Create a subset of records ¢ Do not change underlying table data ¢ Two types ¢ Filter by Selection l Filter by Form l 15

Filter by Selection Table before filter by selection Results of filter Filter by selection being applied from pre-determined criteria ¢ Selects only the records that match preselected criteria 16

Filter By Form Inequity setting used in a Filter by Form process Selection of criteria during Filter by form process Allows the user to select criteria with which to filter by ¢ Allows the specification of relationships in the criteria ¢ 17

Applying and Removing a Filter icon in the Sort and Filter group Toggle Filter icon Once a filter is applied, the Toggle Filter icon will be available ¢ The Toggle Filter icon can be used to apply and remove the current filter as many times as desired ¢ 18

Sorting Table Data Last Name field sorted ascending ¢ Last Name field sorted descending Lists records in ascending or design order according to one or more fields 19

Design View Key symbol identifies primary key field 20

Primary & Foreign Keys ¢ Primary Key (PK)- Uniquely identifies each record in a table. It is needed for searching the database. l Some times if there is no unique value in a table the database designer may use a auto number in Access to use a unique serialized number as the primary key. ¢ Examples: SIN, Student Number, heath card number. ¢ Foreign Key (FK)- A field in one table that is a primary key in another table. It is with these values we can build relationships between tables. ¢ Non Key- a regular field in a database.

Forms, Queries, and Reports Report ¢ Query Forms, queries, and reports are all based upon data contained in a table 22

Forms Form First record from table visible in form ¢ Underlying table Forms allow us to create an interface that can be more user friendly and attractive than Datasheet View 23

Queries Criterion restricting dataset to show records that have a job title of Sale Representative Query results showing only employees who are Sales Representative ¢ ¢ ¢ Queries allow us to question data The answer to the query is a dataset The question asked is formed using criteria – the rules or norm that is the basis for making judgments 24

Relational Database RDBMS ¢ ¢ Relational database management systems allow data to be grouped into tables and relationships created between the tables More efficient than the opposite of an RDBMS which is a flat file. l Flat files store data in one single file with no special groupings or collections 25

Using the Relationship Window Relationship window ¢ Show Table dialog box Add the tables or queries from the Show table dialog box 26

Establishing Relationships Click and drag to create a relationship Primary Key Foreign Key ¢ In the Relationship window, click and drag a field name from one table to a field name in a related table 27

Establishing Relationships Infinity symbol notes referential integrity has been applied ¢ ¢ ¢ Enter the appropriate settings in the Edit relationships dialog box Click Create A join line will appear when one table is joined to another 28

Referential Integrity Enforce Referential Integrity ¢ Referential integrity ensures that the data in a relational database maintains consistency when the data changes 29

Compact and Repair ¢ Fixes problems due to inefficient file storage and growth of a database Should be performed everyday l Often decreases the file size by 50% or more l 30

Access or Excel? Use Excel when: n n n Your data is of a manageable data size There is no need for relationships between data You are primarily creating calculations and statistics Use Access when: ¢ ¢ ¢ You are working with large amounts of data You need to create relationships between your data You rely on external databases to analyze data 31