[PDF] Microsoft Access 2016 Access Database Tutorial – Microsoft Access

View - Download Microsoft Access 2016

Previous PDF

Next PDF

Microsoft Access 2016

Thank you for your purchase and commitment to learn and master the tools and features of

Microsoft Access 2016.

This comprehensive user guide covers the essential objects, techniques and concepts to get you started in the best possible way that I know and have taught to my many students over the years. There are two ways you could use this reference guide. It can be used as a general reference guide jumping between sections in any suitable order to fill any gaps in your knowledge and establish an understanding as taught in my training courses or if you're a complete new beginner then simply start from the beginning and read the whole guide first and then go over each section again applying the examples shown to help manage and build a database application. At first glance, learning about Microsoft Access may be deemed as a steep learning curve but by using the examples and understanding the theories covered in this guide, I aim to flatten that curve to the easiest and painless way possible. At the end of day, it will require your investment of time and perseverance and having a open and positive mind set as at times it may seem all too much to take in. If this happens, take a break, go for a walk, do something else for a few hours and then revisit this at a later point

It will sink in, I promise!

All the examples used in this user guide can be tested with sample data which is available in the box below:

FREE TO DOWNLOAD

If you require some sample data to test the techniques and illustrations as shown in this document, you can go and download my sample Access database file consisting of six potentially related tables which these examples are all based on. Sample Access Database File (ACCDB Format) - Zip File

DISCLAIMER OF WARRANTIES/DAMAGES

All software data files is proǀided "as-is," without any express or implied warranty. In no event shall the author be held liable for any damages arising from the use of this software. The user must assume the entire risk of using the software. PLEASE DO NOT DOWNLOAD UNLESS YOU HAVE AGREED TO THIS DISCLAIMER. I hope you find this user guide of value and welcome your comments on ways this user manual could be improved or if you wish to get in contact with me, please do so at ben@AccessDatabaseTutorial.com Once again, many thanks for your commitment - enjoy!

Disclaimer

The information provided within this eBook is for general informational purposes only. While we try to keep

the information up-to-date and correct, there are no representations or warranties, express or implied, about

the completeness, accuracy, reliability, suitability or availability with respect to the information, products,

services, or related graphics contained in this eBook for any purpose. Any use of this information is at your

own risk.

DISCLAIMER OF WARRANTIES/DAMAGES

All software data files is proǀided "as-is," without any edžpress or implied warranty. In no event shall the author be held liable for any damages arising from the use of this software. The user must assume the entire risk of using the software. PLEASE DO NOT DOWNLOAD UNLESS YOU HAVE AGREED TO THIS DISCLAIMER.

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by

any means, electronic, mechanical, photocopying, recording, scanning or otherwise without the prior written

permission of the author Ben Beitler (ben@AccessDatabaseTutorial.com) or his company; Access Database

Tutorial © 2016 https://www.accessdatabasetutorial.com/

CONTENTS

UNDERSTANDING ACCESS DATABASE RELATIONSHIPS (RDBMS) ........................................ 1

Introduction ....................................................................................................................................................... 1

Primary & Secondary Keys (Indexing) ................................................................................................................ 2

What's a Primary Key? .................................................................................................................................... 2

What about Data Types? ................................................................................................................................ 2

Setting the Primary Key .................................................................................................................................. 3

What's a Foreign Key? .................................................................................................................................... 4

Setting the Foreign Key .................................................................................................................................. 5

A note about indexing .................................................................................................................................... 7

Relational Database Concepts & Terminology ................................................................................................... 8

One-to-One .................................................................................................................................................... 9

One-to-Many / Many-to-One ....................................................................................................................... 10

Many-to-Many ............................................................................................................................................. 10

Types of Joins ............................................................................................................................................... 12

Equi-Join ...................................................................................................................................................... 12

Left/Right-Joins ............................................................................................................................................ 13

Applying Access Relationships ......................................................................................................................... 15

The Relationship Window............................................................................................................................. 15

The Query Window ...................................................................................................................................... 15

Setting via Relationship Window .................................................................................................................. 16

Breaking the Join .......................................................................................................................................... 20

Modifying a Join ........................................................................................................................................... 20

Setting via the Query Window ...................................................................................................................... 21

So which view do you choose? ..................................................................................................................... 23

A c c e s s D a t a b a s e T u t o r i a l - M i c r o s o f t A c c e s s 2 0 1 6 R e l a t i o n s h i p s

https://accessdatabasetutorial.com/ 1

Understanding Access Database

Introduction

When designing a database, you need to make important decisions (early on) how best to any business (ultimately improving the profitability of the business). The end result may be intuitive to end-users responsible for viewing, editing and maintaining such systems but the design, planning and implementation is another story! A well designed database takes time and effort to apply and needs to include Optimisation techniques (the process of speeding up and increasing performance), Normalisation and following the principles applied to an RDBMS (Relational Database Management System). This will become more important over a period of time as your volume of data grows. The larger the data information in Access, the slower it will perform without some form of

Indexing.

In this document we are going to learn about the primary and secondary key indexes in Access to understand the benefits and why it is important to implement. With the core structure of your Tables and Fields in place, the next step is to actually create Relationships between the tables making Access a true relational database management system (RDBMS). We are going to investigate the different types of Relationships in Access and understand the links available before joining any tables together. So when you are ready to implement and join the Tables to form a relational database, this will be the foundation for future processes that can (and probably will) be added at a later stage. Mastering the above processes first will also give you a solid foundation for your back-end database before moving onto other important design objects namely Queries,

Forms and Reports.

A c c e s s D a t a b a s e T u t o r i a l - M i c r o s o f t A c c e s s 2 0 1 6 R e l a t i o n s h i p s

https://accessdatabasetutorial.com/ 2

Primary & Secondary Keys (Indexing)

"The key, the whole key, and nothing but the primary key!"

Why it is important to use and apply indexes

in an Access database!" Every table should have a Primary Key. It's the key that uniquely identifies one record from another and can be applied to either a single field or across several fields to make it unique and distinctive for that record. In addition, you also have Foreign Keys (also known as 'Secondary Keys') which are both forms of indexing for a field in a table.

What's a Primary Key?

As already mentioned, it's the field (or combination of fields) that uniquely identifies a record. All tables really should have this type of key set even if there is no obvious field available by simply creating an additional field to act as the counter/ID value to that record. It helps by keeping records truly unique and therefore avoids any possible duplication. Therefore, using a field like Company Name in a Customers table would not be a good choice because the Company Name value may be used more than once. Yes, it can! It may be possible that a company name value is repeated for different regions (i.e. across countries). The same could be said for the Surname field in an Employees table where it is more probable that two or more individuals share the same surname. Avoid using fields that could have repeated values even if you believe at first during the designing and testing phase you have found a field that may be unique. I've seen the Invoice Date field being used as this key in an Invoices table only for the designer to discover at some later point in time that the system needed to generate more than one invoice for the same day. The primary key set for a field also validates it as Not Null. This means you cannot leave it blank and therefore makes this automatically a mandatory field (for obvious reasons!).

What about Data Types?

Any key can be set to any data type of your choice but it goes without needing to explain further to avoid the likes of Long Text or Memo (long narrative, comments etc.) and OLE Object (embedded objects like a picture or an image) data types. The best data types to consider are Number, Short Text and AutoNumber. These also allow you to change field sizes and control memory allocation making it more efficient which is a step towards optimisation.

A c c e s s D a t a b a s e T u t o r i a l - M i c r o s o f t A c c e s s 2 0 1 6 R e l a t i o n s h i p s

https://accessdatabasetutorial.com/ 3 Ideally, the AutoNumber is the natural candidate for the primary key as it is left to Access to auto set this value which is unique and cannot be edited by the user. Therefore, when adding a new field to support existing records so that you can set this key, choose the

AutoNumber and Access will do the rest for you.

Note: You can combine two or more fields to make this the primary value but personally by adding a new field and making

this the single unique field (AutoNumber) is a better practice. The Access database application also loves primary keys, as functionality within this piece of software will utilise and auto-set the links in relationships especially when relying on the wizard and auto tools to build objects for you.

Setting the Primary Key

This requires you to be in Design View mode for a table. Step 1: Right-click the table that is in the Navigation Pane and choose Design View. Step 2: Decide on the field that will be the primary key and place the cursor anywhere in the row of the field name (upper half pane of the design window). Step 3: From the Ribbon Bar, locate the Primary Key icon and click it. You will see a golden key icon attached to the selected field's row and this automatically sets the Indexed property (in the lower half pane of the window) to 'Yes (No Duplicates)'.

A c c e s s D a t a b a s e T u t o r i a l - M i c r o s o f t A c c e s s 2 0 1 6 R e l a t i o n s h i p s

https://accessdatabasetutorial.com/ 4

Close and save changes.

Note: You can set the primary key with or without data in a table. If there is data however, it will validate if the field chosen

as the primary key can be applied. This is checked when closing, saving the changes and running the table in normal view.

This is the first step (at the very least) prior to creating the relationships, joins and setting your database as RDBMS.

What's a Foreign Key?

It is the inclusion of a primary key into another table that acts as the lookup for the unique value and its record. If you have two tables; Customers and Orders which has a relationship and is joined by the common Customer ID field, from the Customers table the primary key is set as the unique value (known as the 'one' side) and from the Orders table the same field is set as the foreign key because it is possible for the same customer to place more than one order (known as the 'many' side) and therefore the Customer ID field here cannot be unique - This is the foreign key (sometimes referred to as the 'secondary key').

A c c e s s D a t a b a s e T u t o r i a l - M i c r o s o f t A c c e s s 2 0 1 6 R e l a t i o n s h i p s

https://accessdatabasetutorial.com/ 5 This key is optional and will not affect Access functionality in general. The advantage of setting this type of key is to enforce relationships between tables and will actually speed up reporting (running those all-important queries) because it also sets this field as indexed. When setting foreign keys, because they are to be related to the primary key field of another table, they must be of the same data type and where applicable the same field size (or at least the minimum size). They do not however need to be the same field name (though it helps from a human association to keep it the same too).

Setting the Foreign Key

This requires you to be in Design View mode for a table. Step 1: Open the table in design view mode by clicking (right mouse) the table in the

Navigation Pane and choose Design View.

Step 2: Decide on the field that will be the foreign key and place the cursor anywhere in the row of the field name (upper half pane of the design window). In the lower half, locate the Indexed property and from the drop-down box choose 'Yes (Duplicates OK)'.

Close and save changes.

You can always switch off an indexed field by simply choosing the 'No' option from the

Indexed property at any time.

A foreign key can also be set to act as an important field (which is also indexed) for general reporting purposes and does not have be used as part of a join (field) between tables.

A c c e s s D a t a b a s e T u t o r i a l - M i c r o s o f t A c c e s s 2 0 1 6 R e l a t i o n s h i p s

https://accessdatabasetutorial.com/ 6 For example, take a Country field from a Customers table that could be considered as a foreign key because this field may be deemed a popular field in Queries when filtering by a single country. By marking it as foreign key (which is why it is also called 'secondary'), this will speed up your Queries too. Other types of fields that would be good candidates are fields in which you would sort, filter and/or group together that could be for example an invoice date, a type of product, company location by region or surname of individuals. Personally, I like to see clearly both types of keys (primary or foreign) set (with the golden key symbol against each field) so that later on in other parts of Access, I can clearly identify which field is indexed as either a primary or foreign key. This is purely cosmetic but the following method requires all the keys to be set together and therefore you will need to know which fields are to be included from the outset. Step 1: Open the table in design view mode by clicking (right mouse) the table in the

Navigation Pane and choose Design View.

Step 2: Decide on the fields that will be either the primary or foreign key and select the multiple rows for each field (upper half pane of the design window) by holding down the

CTRL as you click with the mouse each row.

Step 3: Each field will then need to be checked for the correct Indexed property and modified to either the value of 'Yes (No Duplicates)' for the primary key and 'Yes (Duplicates OK)' for the foreign key. Click on the individual field and in the lower half set the correct index required.

Close and save changes.

A c c e s s D a t a b a s e T u t o r i a l - M i c r o s o f t A c c e s s 2 0 1 6 R e l a t i o n s h i p s

https://accessdatabasetutorial.com/ 7

A note about indexing

Indexing should be mentioned here. Access looks for indexed fields when using objects that sort and find records. This creates an efficient use of memory and speeds up data processing. Think of indexes as in a book when you use the index to locate key words and a page number to quickly jump to the reference. Without an index in a book, how difficult would it be to search for a keyword? This is how Access deals with key values. Be reasonable with indexing! Some developers do not bother with indexing at all (that's fine for small database files) and others go overboard and set too many fields for indexing which will slow the general performance of Access as a whole. Therefore, strike a good balance and carefully identify which fields would be often used in searching and sorting and set those as indexed. There is a utility in table design mode to manage the indexes instead of locating each field one by one and setting the correct property accordingly. From the Ribbon Bar choose Indexes and the following screen appears: Make your changes and close the window (using the top right hand corner 'X' icon).

Close and save the table.

A c c e s s D a t a b a s e T u t o r i a l - M i c r o s o f t A c c e s s 2 0 1 6 R e l a t i o n s h i p s

https://accessdatabasetutorial.com/ 8

Relational Database Concepts & Terminology

"Creating Access Relationships creates a family of data information harmonising the use of queries and reports." Access Relationships enable data in one table to be joined to data in another table which generally serves as the basis for your reports (typically via a Query). Without relationships in place, you are restricted as to how data is managed from the reporting point of view and each table sits as a 'stand-alone' file. As part of the database normalisation process, setting Access relationships keeps to the rules and general good practice of RDBMS Relational Database Management System. Joining tables together can be set in one of three ways:

1. Table level via the 'Relationship' Window

2. Query level via the 'Query' Window

3. Both Table and Query levels

In order to join two tables together, there are rules that must be observed which are:

1. Field Data Types - the fields which are to be joined across two tables must be the same

data type. Therefore, a 'Short Text' data type cannot join to a 'Number' data type field. The exception to the above is a 'Number' data type can join to a 'AutoNumber' data type providing both are set to the same size (i.e. Long Integer).

2. Field Sizes - applies to only the 'Short Text', 'Number' and 'AutoNumber' data types

where the size or length of a field can be modified. For the numeric data types, the size must be the same and therefore a 'Number' data type field size in one table which is set to 'Double' cannot join to a field whose size is set for example to 'Integer'. The 'Short Text' data type is a little more relaxed as the joining field must be at least the same length (or greater) of characters (bytes).

Note: It is possible to join different field sizes with 'Number' data types provided the field can be cast and fit into the larger

size. This is not good practice and in any event shouldn't the data type be the same across tables for the same data value?

3. Field Names? - this is not essential as field names are not checked when joining tables

together. But for user-friendliness, having the same field name makes life just a little bit easier! Make sure you review data types and sizes before setting any primary or foreign key

A c c e s s D a t a b a s e T u t o r i a l - M i c r o s o f t A c c e s s 2 0 1 6 R e l a t i o n s h i p s

https://accessdatabasetutorial.com/ 9 Setting the correct keys (primary and foreign) must be considered and applied before joining tables together. The type of key applied will lead to the type of Access relationship you can have and in essence there are four:

1. One-to-one

2. One-to-many

3. Many-to-one

4. Many-to-many

The 'one' side is associated to a unique value for a field and is referred to as the primary key. The 'many' side is an indexed field which allow for duplicate values in a field and is known as the foreign or secondary key.

One-to-One

This type of Access relationship is seldom used but it allows you to join two tables together whereby each row (record) in table A has a related row (only one record) in table B. Types of uses for this setup could be a way to overcome some management, performance or sensitive data issues. For example, if you had two tables dealing with employee information where one table Personnel stores general profile information including surname, ID and contact information and the other table Payroll containing salary and other sensitive data, then there would only be one record per individual in each table (joined by a common unique value - 'Employee ID'). Other times this type of Access relationship may be used is when overcoming limitations and performance problems. For example, for a table to hold over 100 fields (maximum is

255) will require a lot more memory than a table with far less. If you had a client table which

held a lot more profile information than the normal, consider splitting into two tables; table A to hold the main essential fields and table B holding the less frequently used data (especially when using 'Memo' and 'OLE Object' data types). The common field would be unique in both (i.e. Customer ID).

A c c e s s D a t a b a s e T u t o r i a l - M i c r o s o f t A c c e s s 2 0 1 6 R e l a t i o n s h i p s

https://accessdatabasetutorial.com/ 10

One-to-Many / Many-to-One

This is the most common type of Access relationship where a single record in one table is joined to one or more records in another table with the same matching data value from the unique value of the first table. There are many examples to highlight but imagine if you have a table called Customers which contains the client's details including name, address and contact and another table of transactions called Orders where a customer can place more than one order over a period of time. The common field between the two is the Customer ID and is unique from Customers but duplicated in the Orders table for each instance an order was placed for the same customer. This type of Access relationship is sometimes referred to as parent-child or master-detail. Terminology sometimes used when talking about a customer having never placed an order is referred as a widow record (a parent with no children). The other way around where an order does not have a known customer is referred as an orphan record.

Many-to-Many

This type of Access relationship cannot be created directly in Access and therefore will require three tables. It is where in table A, a record can refer to one or more records in table B and table B can be linked to one or more records in table A (a two-way 'one-to- many/many-to-one' link) but requires a third interim table C! This relationship happens by default if you have created multiple tables using the database normalisation techniques and have joined tables together using the 'one-to-many' relationships.

A c c e s s D a t a b a s e T u t o r i a l - M i c r o s o f t A c c e s s 2 0 1 6 R e l a t i o n s h i p s

https://accessdatabasetutorial.com/ 11 For example, each order stored in the Orders table which holds the header information including date, customer ID and shipping information has a second table which stores the item details that make up the order called Order Details, where the Order ID field is common to both tables causing a 'one-to-many' relationship. For each item in the Order Details table, a product is held identifying the item and its product information, which in turn is held in a separate table called Products containing name, stock levels and price. The relationship between Order Details and Products is a 'many-to-one' where the common field is Product ID. So we have three tables; Orders linked to Order Details linked to Products and therefore we have an indirect relationship between Orders and Products (the Order Details table sitting between the two). In the 'real world', this would be a typical relationship because you have one order with many products (items) and the product is sold across many orders. The middle table Order

Details handles the two-way relationship.

A c c e s s D a t a b a s e T u t o r i a l - M i c r o s o f t A c c e s s 2 0 1 6 R e l a t i o n s h i p s

https://accessdatabasetutorial.com/ 12

Types of Joins

In addition to Access relationships, you also have three different join types to choose from:

1. Equi-Join (Inner Join)

2. Left-Join (Outer Join)

3. Right-Join (Outer Join)

Equi-Join

By default, when you join two tables together, it sets it as an Equi-Join also referred to as the Inner Join. It tells Access to show only records from the two tables where they both have the matching values. Any record in either table that does not have a corresponding record in the other table is simply suppressed from view. For example, if we continue to use our Customers and Orders table relationship scenario of a 'one-to-many', it would only return records where the matching Customer ID field existed across the two tables. Therefore, it will display all customers that have placed at least one order and only display orders that have a known customer account. Any orders that do not have a matching Customer ID will be suppressed and in the 'real world' this would be unusual due to the fact you first would choose and assign a customer before placing the order! However, it is possible to have a customer as a new prospect that has yet to place their first order and therefore would also be suppressed with this type of join.

Note: Access relationships cause and effect - Before adding any criteria, you will find records have been filtered because of

this type of join. If you had 99 out 100 records in table A each having 3 records from table B with the remaining 1 record not

having any records from table B, then the result recordset would be 297 (99 X 3) suppressing the 1 record.

A c c e s s D a t a b a s e T u t o r i a l - M i c r o s o f t A c c e s s 2 0 1 6 R e l a t i o n s h i p s

https://accessdatabasetutorial.com/ 13

Left/Right-Joins

These are also known as Outer Joins which change the relationship link. Access will force (on one side only) records to be displayed whether there is a matching value or not. In Access, you can only set one direction of outer join (left or right) at a time and this tells the system from which direction to force the records to be all shown. For example, using our Customers and Orders table scenario, creating a Right-Join (pointing from Customers to Orders) will show all customers whether they have orders related or not. Where there are records from Customers which do not have any related orders, these are said to be Widow Records (parent with no children). Using the Left-Join example for the above tables will force all orders to be displayed and only related customers. This type of example may not be different from an Equi-Join if the relational database has been designed with care as in the 'real world' you wouldn't really have an order issue without first choosing a customer!

A c c e s s D a t a b a s e T u t o r i a l - M i c r o s o f t A c c e s s 2 0 1 6 R e l a t i o n s h i p s

https://accessdatabasetutorial.com/ 14 This type of join will be driven towards data that may have been imported from an external system where Access cannot deal with those orphan records (records without a parent).

There it is, Access relationships understood!

Don't get too wrapped with up different joins, keep it simple and as you develop the database you will learn to modify the rules.

A c c e s s D a t a b a s e T u t o r i a l - M i c r o s o f t A c c e s s 2 0 1 6 R e l a t i o n s h i p s

https://accessdatabasetutorial.com/ 15

Applying Access Relationships

"The Access Relational Database tool controls the joins types, enforce data integrity and sets rules between tables and queries." The Access Relational Database (RDBMS) is what Access is really all about in order to take advantage of the relationships between data files and the application's functionality. There are essentially two places that you can set a relationship between a Table or Query:

1. Relationship Window

2. Query Window

Note: In fact, there is a third option (for the more advanced user) which requires a knowledge of VBA (Visual Basic for

Application) where you can code setting indexes and relationships on the fly and maintain the Access relational database

structure with code.

The Relationship Window

This view provides all the options and functionality that is available when dealing with the

Access relational database model.

This tool sets a permanent relationship between tables and queries and the scope of this can be deemed as global. What I mean is that every time you call tables or queries that have a join between them, they automatically show through to the calling object whether it be a query, form, report or any other supporting object directly or indirectly and the rules of that relationship are always maintained.

The Query Window

This view provides the ability to join tables (and queries) together and set an Access relational database environment only for that query. This method sets a temporary relationship between tables and the scope here can bequotesdbs_dbs20.pdfusesText_26

[PDF] advanced microsoft access 2016 tutorial pdf

[PDF] advanced microsoft excel notes pdf

[PDF] advanced microsoft powerpoint 2007 tutorial pdf

[PDF] advanced ms excel 2016 tutorial pdf

[PDF] advanced ms excel book pdf

[PDF] advanced ms excel notes pdf

[PDF] advanced ms excel pdf download

[PDF] advanced multithreading concepts in java pdf

[PDF] advanced numerical analysis nptel

[PDF] advanced numerology pdf

[PDF] advanced oops concepts in java pdf

[PDF] advanced oracle pl/sql developer's guide pdf

[PDF] advanced oracle sql programming the expert guide to writing complex queries pdf

[PDF] advanced oracle sql queries examples with answers

[PDF] advanced oracle sql queries for practice