Mastering Microsoft Access 2019 and 365 Training Tutorial

When Access opens, it show the “Home” window, which lets you either create a new database file or access a previously-opened database. An Access database file is a container that holds all the tables, forms, reports, queries, macros, and modules required by the database. You use Access to create database files for use on a single computer or to share on a local network. This course focuses on creating and designing database (.accdb) files in Access 2019 or Access for Microsoft 365. Learn about this and more during this lecture.

In Access, you are manipulating a contained collection of smaller objects within the database file. Although the terms “database” and “table” are often used interchangeably, you should refer to the entire collection of tables, queries, forms, reports, macros, and modules as the “database” and only refer to tables as “tables” for clarity’s sake. Learn about this and more during this lecture.

Unlike many other Microsoft applications, there are three different areas in which you perform tasks in the Access interface. The first area to discuss is the “application window,” which is the outer frame of the program that contains the Ribbon. Learn about this and more during this lecture.

Access has a “Touch Mode” that provides easier access to the buttons and commands in the Ribbon and Quick Access toolbar. When touch mode is enabled in Access, the Ribbon and Quick Access toolbar are enlarged. Learn about this and more during this lecture.

As mentioned earlier, a database is the entire collection of tables, queries, forms, reports, macros, and modules. In Access, each database file you open appears within its own application window instance. So, you can only open one database file at a time within a single instance of Access, although you can open multiple instances of Access, if needed. Learn about this and more during this lecture.

To reopen a database you have already created and saved, open Access to view its “Home” screen. In the listing at the right side of the window, click the name of the recently opened database to reopen, shown under the “Recent” tab. To open a database file in Access not listed here, click the “More databases” link towards the lower-right corner of this list or click the “Open” button at the left side of the screen. Learn about this and more during this lecture.

Access is a relational database application. So, what does the term relational mean, and how is this important? The term relational describes the method used for storing data within the database tables. However, it may be easier to understand the relational model of data storage by contrasting it with another method of storage that you may be more familiar with, the “flat-file” method. Learn about this and more during this lecture.

The relational model of data storage lets you more easily and effectively model a complex entity or subject, like sales. The relational model of data storage eliminates redundant data entry and creates less data to store, making the relational database model smaller and faster than the ‘flat-file.’ When you create a relational database, you must first perform some data modeling. Learn about this and more during this lecture.

While there are no “hard and fast” rules about creating relational database tables, there are a few tips that you should try to follow when beginning database design. First, examine all current documentation used to collect and store the information that you now want to store in the new database. Learn about this and more during this lecture.

Tables are so commonly thought of when one speaks of a database that the terms are practically interchangeable. A table is an organized structure that holds information. It consists of “fields” of information into which you enter your “records.” Learn about this and more during this lecture.

In Access, you should assign a primary key to each table you create. A primary key is a field or group of fields that acts as a unique identifier for each table record. So, you should use a field or group of fields that will always contain a unique value as your primary key. Learn about this and more during this lecture.

While you can create a table in datasheet view in Access, it is not recommended. It is a poor place to design tables due to its lack of control over the data types assigned to the fields, and its complete inability to change the properties of fields. Learn about this and more during this lecture.

To move from left to right through the rows when in a table’s datasheet view in Access, press either the “Tab” or “Enter” keys on your keyboard. To move from right to left, press the “Shift” + “Tab” keys on your keyboard. You can also use the arrow keys on your keyboard to traverse the records, if you like. Learn about this and more during this lecture.

In datasheet view, you will see a blank row that shows an asterisk (ã) in the row selector box at its left end. That is the “New Record” row. When you enter a new record into the table, it is added to the bottom of the table in the “New Record” row. Learn about this and more during this lecture.

To edit a record in a table’s datasheet view in Access, click into the desired field of the record to edit to place the insertion point into the field. Then edit the field information as you would in a text document. To save the changes, exit the cell. Changes to the data in a table are automatically saved as you make them. Learn about this and more during this lecture.

After creating tables in Access, you may need to modify their structures later by inserting new fields. You should make structural changes in the table’s design view. One way to open a table in design view is to select the name of the table in the Navigation Pane. Learn about this and more during this lecture.

With Access, you have the flexibility to rename fields you already created. You should be extremely careful when doing this, as any changes you make to field names are not necessarily updated in all the previously-created and related reports, forms, or queries, which may still refer to the “old” field names. Learn about this and more during this lecture.

You can also delete table fields you do not use from tables in Access. As when renaming a table field, make sure there aren’t any queries, forms, reports or macros that refer to the field or use its data before you delete it. Learn about this and more during this lecture.

You can set the properties of the table fields you create when using the table’s design view. When you open tables in design view, you name the fields and assign them a data type using the top half of the screen which is called the table design grid. Learn about this and more during this lecture.

You can use the “Field Size” property of a text field to set the number you type as the maximum allowable number of characters in the selected field. This can help you restrict the amount of data a user can enter into the field. Learn about this and more during this lecture.

You can set the format property for date/time fields in Access tables to change the way they display dates and times stored in the field when shown in datasheet view or within forms. Learn about this and more during this lecture.

You can set the format property for logical fields to change the field values that appear for the field in forms and reports. Learn about this and more during this lecture.

You can set the default value property of a field in an Access table to specify a value the field should contain when a new table record is created. Learn about this and more during this lecture.

You can create an input mask for a table field in Access to dictate a pattern to use for the field’s data entry. You can manually create input masks for most text, date, and number data type fields in Access, except the “Long Text” data type. Learn about this and more during this lecture.

You use the validation rule and validation text field properties for a selected table field in Access in tandem. Setting the “Validation Rule” field property lets you either manually type an expression or use the “Expression Builder” dialog box to create an expression that only allows data entry that meets the specified expression into the field. Learn about this and more during this lecture.

To require data entry within a table field in Access when entering a new record, set the “Required” field property in the selected field in the table’s design view to the “Yes” value. You can set the “Required” field property for a selected field in the table’s design view in Access to either “Yes” or “No” to either require entry into the field, or not. Learn about this and more during this lecture.

You can set the “Allow Zero Length” field property for a selected table field in the table’s design view in Access to either “Yes” or “No” to either allow zero-length field values or not. When set to the “No” value, this field property prevents users from entering zero-length field values, like a series of “spacebar” characters, for example. Learn about this and more during this lecture.

As you create tables in Access, you want to relate the tables to access information from them through the “shared” or “common” fields by which they are joined. In Access, you create relationships, also called table joins, between tables in the Relationships window. Learn about this and more during this lecture.

As you create the relationships between the tables in your database, you need to set the properties of the table joins to ensure they are set as you would like. The main join property to set is the “Referential Integrity” of the join. Learn about this and more during this lecture.

Access can also create “lookup” fields in a table that lookup the values in another table, query, or hand-typed list from which it then draws its values. If the field is looking up data from another table, versus a query or list, it automatically creates a join between the two tables, which also appears in the Relationships window. Learn about this and more during this lecture.

When you create an index for a table in Access, you define a way to sort the table’s data, using the available fields. Indexing a table is simply a way of organizing the table’s data to allow Access to complete query searches and sorting more rapidly. Indexing can help speed up the time that it takes to complete queries in Access, given a few criteria are met first. Learn about this and more during this lecture.

When you are creating indexes, you want to try and use field values that will identify each record in your table as uniquely as possible. If you are a good database designer, there will already be a single field in your table that already does this: your primary key field. Learn about this and more during this lecture.

To delete tables indexes in Access, open the database table that contains the indexes to delete in table design view. Then click the “Indexes” button in the “Show/Hide” button group on the “Design” tab of the “Table Tools” contextual tab in the Ribbon to open the “Indexes” dialog box. In the “Indexes” dialog box, click the row selector at the left end of the index to delete to select it. Learn about this and more during this lecture.

You use a query to answer a question that you have about the information stored in the database tables. You can then further analyze the results the queries pull to produce even more information than the query itself displays. Reports are often based on query results, upon which they can then perform additional mathematical and statistical calculations. Learn about this and more during this lecture.

To make a query in query design view in Access, click the “Query Design” button in the “Queries” button group on the “Create” tab of the Ribbon. The first thing to note in the query design view in Access is the “Add Tables” pane that appears at the right side of the window. Learn about this and more during this lecture.

When you add multiple tables to a query in the query design view, the joins you established between tables within the “Relationships” window also appear in the query, allowing you to access information from any related tables. Learn about this and more during this lecture.

To show table records based on the values in a selected query field in Access, you need to enter the criteria for record selection into the desired query field or fields. To add query criteria for a field in the QBE grid within the query design view in Access, type the criteria into the desired query field’s “Criteria” row. Learn about this and more during this lecture.

To run a query to view its result set when in the query design view in Access, click the “Run” button in the “Results” button group on the “Design” tab of the “Query Tools” contextual tab of the Ribbon. If the results aren’t what you expect, you may need to re-design the query. Learn about this and more during this lecture.

When you are visually creating the query in the query design view in Access, what you are really doing is visually constructing SQL code. SQL stands for Structured Query Language. SQL is a multi-platform language used to access and retrieve data in many different relational database programs. Learn about this and more during this lecture.

You can sort the results of a query in Access by any field shown in the QBE grid when viewing the query in query design view. To sort query results in the query design view in Access, click into the “Sort:” row of the field in the QBE grid by which to sort the result set. Then use the drop-down that appears to select either “Ascending” or “Descending” order. Learn about this and more during this lecture.

Sometimes, when creating queries in query design view in Access, you need to add a field to the QBE grid for criteria purposes only, and don’t particularly want the field itself to appear in the query result set. Learn about this and more during this lecture.

You can use comparison operators for query criteria you type into the “Criteria” row for field in the QBE section of the query design view of Access to search for criteria values that are not necessarily “equal to” a field value. Learn about this and more during this lecture.

Access also lets you filter the result set of a query using multiple field criteria. Most often when you have multiple query criteria, you either want the query to show records that contain both “value X” AND “value Y” in different fields or show records that contain “value X” OR “value Y” within the same or different fields. It’s unusual to use an AND condition within a single field, but it isn’t unheard of either. Learn about this and more during this lecture.

You can use the “BETWEEN…AND” condition in Access to look for values in a date/time field that are between and inclusive of “Value X” and “Value Y,” as specified. Learn about this and more during this lecture.

You can use wildcard characters in Access to add flexibility to query criteria. Wildcard characters in Access represent unknown values. There are two main wildcard characters you need to know: the asterisk “*” and the question mark “?.” Learn about this and more during this lecture.

You can create calculated fields in queries. A calculated field is a field derived by performing a function upon values gathered from other table fields or entered by hand. The data appears only for the duration of the query and is not actually stored in the tables. Calculated fields can perform almost any function and can use any available query field or data entered by hand as the basis for their calculations. Learn about this and more during this lecture.

You can also create top value queries in Access that will return the top or bottom results of a query instead of all results, depending on how the query results are sorted. Learn about this and more during this lecture.

You can create summary queries in Access that perform a mathematical function on a field, called an “aggregate function,” based on groups of values found in another grouped query field or grouped query fields. These are usually shorter queries, often used for reporting. Learn about this and more during this lecture.

You can also create parameters in your query criteria in Access that will prompt you to enter a value which is then used as the query criteria value for the query before returning its result set. This is tremendously helpful, as it prevents many hours of editing and changing query criteria. Learn about this and more during this lecture.

The make table queries in Access let you run a query and save its result set as a permanent table. A make table query creates a new base table from the output of a query, instead of displaying a query result set. This new base table can be useful in a variety of ways. Learn about this and more during this lecture.

Updates queries in Access let you make large-scale updates to table data if the records match specified criteria. Learn about this and more during this lecture.

Append queries in Access let you copy and paste records from one table to another, based on whether the records match a specified criteria. You can only append data from table fields to other table fields that share the same, or a compatible, data type. Learn about this and more during this lecture.

Delete queries in Access let you delete records in a table that match a specified criteria. Deleting unnecessary records can speed up the performance of queries, reduce redundancies, and make for more smoothly operating databases. Learn about this and more during this lecture.

Crosstab queries in Access answer questions about how field data in a single table relates to each other. Crosstab queries show one table field down the left side of the result table, and another table field across the top of the table. Learn about this and more during this lecture.

The “Find Duplicates” query type in Access lets you to find duplicate records within a table. To create a find duplicates query, click the “Query Wizard” button in the “Queries” button group on the “Create” tab of the Ribbon. In the “New Query” dialog box that appears, select the “Find Duplicates Query Wizard” choice and then click the “OK” button. Learn about this and more during this lecture.

To remove duplicate records from a table in Access, right-click the table from which to remove duplicates in the Navigation Pane. Then choose “Copy” from the pop-up menu that appears. Then right-click into the table list in the Navigation Pane and choose “Paste” from the pop-up menu that appears. Doing that opens the “Paste Table As” dialog box. Learn about this and more during this lecture.

In a relational database, you aren’t supposed to have records in a child, or related, table which have no reference to a matching value in a related record in a parent table. Learn about this and more during this lecture.