Turn off all noise-making phones, pagers, etc. (Teacher too!)
Microsoft Access is a relational database management system program. It is primarily used for building databases and collecting/integrating data from them into tables and reports for output. It has support for use in a MS Office workflow with Word, Excel, and Outlook, and is usually used in administrative functions to add, sort, and report data. Most admin pros never create or manage full databases. This class will focus on Access as a basic database, report, and table tool.
Get to know the basic functions of MS Access.
Open, Review screen:
Template categories, Getting Started, New and Open
New blank database
Save, save as, file naming
Explore Menu/Ribbon tabs:
Home: Basic text functions - formatting, positioning, styles
Create: tables, reports, forms, queries
External Data: Importing and exporting data
Database Tools: For developers and database mechanics – not admin/clerical use.
Acrobat: For PDF workflow.
Format: A context-tab for formatting functions of reports, forms only. *
Arrange: A context-tab for arrangement of data of reports, forms only. *
Datasheet: A context-tab for table data only. *
Set your preferences for workflow and productivity.
MS Office Button:
Same layout as Word, Powerpoint, and Access.
Allows personalizing, print, save, and publish.
Preferences: Access Options:
Customize editing, saving, and other program options
Personalize a mini-toolbar for minimizing ribbon
Databases are made up of tables of data, queries on that data, forms for inputting data, and reports for exporting the data.
Database: A relationship between multiple tables of related data that allows you to add, modify or delete data from the database, ask questions (or queries) about the data stored in the database and produce reports summarizing selected contents.
Made up of several tables
Table information can be related to each other with a key.
Table: Tables are like Excel tables – rows and columns of data you are collecting about something.
Query: Combine information from multiple tables in your database. Queries provide the capability to combine data from multiple tables and place specific conditions on the data retrieved.
Form: User-friendly forms interface allows you to enter information in a form that passes that info to the database tables.
Report: Provides the ability to quickly produce formatted summaries of the data contained in one or more tables and/or queries of the database.
Tables (Home tab)
Access allows you to create multiple tables so you can store specific information separately instead of inside one long large table.
A table should be created with only the required info that is NOT share in another table.
A table can and should have a specific field (column data) set as a Key so that item can be referenced in other tables.
Datasheet: The default table info insertion view.
Records: Each record in the database is based on a row of data you input, like name, address, phone, etc.
Columns: Each column stands for a specific type of data being collected for each record.
Rows: Each row is one record entry on the database.
Headers: The tables need header rows to state what data is being collected in the columns.
Table View: Design
Set the Datatype of fields (text, number, etc.)
Set other field info - size, default values, formats, required (Y/N)
Set the Indexing (default=Yes, easier org)
Add the Primary Key
Keys (Home tab, Design View)
Keys in a database are, no pun intended, key to establishing relationships between tables so that you can query them for data.
Primary Key: Every table in your database should have a primary key — a field with a unique value for each record stored in the table. Often is some kind of identifier number. You use the primary key to identify and refer to each record. One of the reasons to create a primary key is to use it to create table relationships. A primary key is a field or set of fields that:
Has a unique value for each record - no two records in the table have the same value for the key.
Identifies the record
When you create a new table in Datasheet view, Access automatically creates a primary key field for you, names the field ID, and gives it the AutoNumber data type. The field is hidden by default in Datasheet View, but you can see the field if you switch to Design View.
Candidate Key: If you have/choose another field in the table that would serve as a Primary key instead.
Foreign Key: A foreign key is another table's primary key. The values in a foreign key field match values in the primary key, indicating that the two records are related — for example, a customer and an order that she has placed. You create a foreign key when you use the Lookup Wizard to create a field. Unlike primary keys:
A table can have more than one foreign key.
A foreign key does not necessarily have unique values.
A foreign key cannot reliably identify a particular record.
Relationships (Database Tools tab, Relationships)
Relationships between your database tables are what allow you to query multiple tables for information in them and end up with a query table that consolidates what you want to know. This is how you can take information from a table of thousands of customers, another table of their orders, and a third table of shipping preferences, and query a list of orders, last names, addresses, and shipping preferences set into a new query table.
One of the goals of database design is to remove data redundancy (duplicate data). To do that,, you divide your data into many subject-based tables so that each fact is represented only once. You then provide your database with the means by which to bring the divided information back together as needed — by placing common fields in tables that are related to each other, and by defining relationships between these tables.
A table relationship works by matching data in key fields — often a field with the same name in both tables.
In most cases, these matching fields are the primary key from one table, and a foreign key in the other table.
There are three types of table relationships:
One-to-many relationship: For instance, for any customer represented in a Customers table, there can be many groom ers represented in the Groomers table.
Many-to-many relationship: Consider a Products table and an Orders table. A single order can include more than one product. A single product can appear on many orders. Therefore, for each record in the Orders table, there can be many records in the Products table. In addition, for each record in the Products table, there can be many records in the Orders table.
One-to-one relationship: Each record in the first table can have only one matching record in the second table, and each record in the second table can have only one matching record in the first table.
You can view your table relationships by choosing Database Tools Tab/Relationships.
When you design a database, you divide your information into tables, each of which has a primary key. You then add foreign keys to related tables that reference those primary keys. These foreign key-primary key pairings form the basis for table relationships and multi-table queries. It is important, therefore, that these foreign key-primary key references stay synchronized. Referential integrity helps ensure that references stay synchronized and is dependent upon table relationships.
You can create a table relationship by using the Relationships window. When you create a relationship between tables, the common fields are not required to have the same names, although it is easier to understand. Rather, those fields MUST have the same data type.
Relationships Window: Choose Database Tools Tab/Relationships, then choose the tables you want and click Add, then drag the Primary Key from one table to the Foreign Key of the other.
Queries (Create tab)
To work with records from more than one table, you need to create a query that joins the tables. The query works by matching the values in the primary key field of the first table with a foreign key field in the second table. Queries offer the ability to retrieve and filter data, calculate summaries (totals), and update, move and delete records in bulk.
Have your tables opened and in Datasheet View.
Choose Create/Query Wizard.
Choose type of query, such as Simple
Follow the wizard steps.
To restrict the records that are returned in the query results, you can specify one or more criteria.
Your results will open into a new combined info table.
Save/Name the query table to revisit later.
Forms (Create tab)
Access allows you to create a simple and attractive interface so that you can enter information into one or more tables at the same time.
Single Table Form: Choose Create/Form, which will create a basic form based on an existing table.
Multiple Table Form: Choose Create/More Forms/Wizard, which will create a basic form based on more than one existing table based on your wizard choices.
MUST be tables with an existing relationship to each other.
Use the Design View to adjust layout, colors, fonts, etc.
You don’t want your users navigating your database on their own. Instead, when they open the database, you want them to access the data, reports, and queries through controls on the opening form — NOT the navigation panel:
Office button/Access Options.
Select Current Database from the left pane.
Under Applications Options, click the Display Form drop-down list and select the form you want to display when the database is opened.
Under Navigation, clear the Display Navigation Pane check box and click OK.
For entering new data, when form opens, choose New Record at bottom of form to add a new form page.
Reports (Create tab)
Access allows you to generate reports of information you receive from queries to table data so that you can sort and see it in an attractive printable format.
Basic Report: Choose Create/Report, which will create a basic form based on all fields in an existing query.
Specific Report: Choose Create/Report Wizard, which will create a basic report based on your wizard choices.
Use the Design View to adjust layout, colors, fonts, etc.
You can test and adjust your documents and review your content before it goes live.