Hands-On Microsoft Access 2019

Microsoft Access queries will be the heart of your database, answering questions of the data, introducing users to the query by example grid, the tools, and how to apply criteria and general sorting. Simple rules and checks will also be covered here, and it will give users the confidence ready for reporting out.

   •  Explore the query by example grid interface with some terminology

   •  Work with some basic example of sorting and criteria

   •  See how to check the results and ensure it is accurate

An overview of the common criteria conventions for all data types will be covered here. An overview of the Expression builder tool will also be covered to allow users to locate functions, objects and build formulae. Finally, covering some typical examples and rules when calculating in a query.

   •  An overview of the criteria conventions for all data types

   •  See what the Expression builder tool is and how we use

   •  Work through some typical criteria-based examples with calculated expressions

Introduce users how data can be grouped and calculated out using the built Access functions, know the difference of Group By, some of the functions, and their impact. Also, learn how criteria are applied and the impact of the WHERE verses HAVING clauses and view the SQL view mode.

   •  Work in the design, applying the Groups and Totals mode

   •  Introduce users to the functions and Group By option

   •  Look at the examples using the functions and using WHERE and HAVING clauses

See what a parameter query is and where it can be used. See how a parameter query is created and test for it. See how it is using more than one parameter and understand the order and impact. Use some examples of using parameters queries and locate some potential error conventions and avoid conflicts.

   •  See what, where, and why parameter queries will serve you well

   •  Build a single parameter, followed by working on multiple parameters

   •  Cover the pitfalls, potential errors, and some of the conventions

Introduce users to all four Action based queries, show their uses, their benefits, and be aware of the Run command. Also, work on the ‘safer’ two Action queries and how to check and backup data, working on the ‘risker’ other two Action queries and their potential dangers surrounding the data.

   •  Cover the four Action queries of Access and Run command

   •  See the two ‘safer’ Action queries that will duplicate data

   •  Explore the remaining ‘riskier’ Action queries that change and delete data

Create a form using the simple one-click step option, including the subdatasheet table option and how it impacts a form, covering other methods of creating forms using the predefined templates provided and introducing the form wizard tool. It also manages records via a form and how they link with tables.

   •  Simple form template option generated from a selected table

   •  Cover other form creation methods including the form wizard tool

   •  Data management via a form and the impact with tables

Working with a blank canvas form in design view assigning properties that connect with other objects. Understand the key components for a form between the sections, the controls, and essential properties. The property sheet is explained and how it is dynamic to the form’s canvas and active control.

   •  Introduce a blank form in design view covering some properties

   •  Further explore key properties for a form and their controls

   •  Look at the form’s property sheet with more detail

Cover more detailed information of the property sheet for the entire form and a section. Change the tab order in a form, explore the toolbox and ribbon bar icons to assist in form designs. Also, see how to drill down into a form’s control properties and they can be collectively managed.

   •  Focus on the property sheet for a form and section

   •  Work with tab order between sections

   •  Work with collective control’s property sheet and their options

Here, look at what popup forms are and where they are typically used. Use the standard dialog box form template option and cover some of the key properties. Know what modal means and how it changes the form’s behavior. Also, see how the two command buttons work using the default embedded macros.

   •  Cover how popup forms are different and used in Access

   •  Cover the property sheet for a dialog box form

   •  Discover the modal property and the two embedded macro buttons

Get an overview of what navigation forms are, how you could create a navigation form, and look at the properties that connect with other objects. Also, see how to manage and edit navigation forms using the right-mouse click action. See how to make the navigation form your opening object as the database opens.

   •  Understand and create navigation forms with the defined templates

   •  Look at the navigation form properties and edit the design

   •  See how to set navigation form as the default opening object

Create a report using the simple one-click step option, look at the four view modes, and what they are. Work with the page setup and other configurable options for print preview and its dedicated ribbon bar. This is a quick way to export and publish your data.

   •  Create a report using the simple one-click step option

   •  Look at the four view modes including print preview

   •  Work through the page setup and other options including exporting

Work with a blank canvas report in design view assigning properties that connect with other objects. Understand the key components for a form between the sections, the controls, and essential properties. See how the property sheet is covered again and how this time, it links to the report sections and active controls.

   •  Introduce a blank report in design covering the common properties

   •  Further explore key properties for a report and their controls

   •  Look at the report’s sections with their properties

Cover more detailed information of the property sheet for the entire report and a section. Add the field list window for connected tables or queries. Explore the toolbox and ribbon bar icons to assist in form designs. Also, add a calculated field and functions that impact across different sections.

   •  Focus on the property sheet for a report and section

   •  Add the field list and work through some toolbox controls

   •  Add calculated fields and the impact between sections

See the page setup controls and marker fields for headers and footers. Insert the page numbers and a logo and build multiple page reports. Add a parameter field control to a report. Work with the ‘Group and Sort’ feature with totals. Add a sub report and modify properties to link a report.

   •  Work with header and footer sections; pre-defined markers fields

   •  Explore the parameter field control to a report - multiple page reports

   •  See the Group and Sort windowpane and work with sub reports

Work with the report wizard tool building richer reports. See how the Report Wizard tool will vary and options that can be added. Apply Sorting and Grouping via the Report Wizard tool. See how to add summarized calculations during the creation process. Also, create a crosstab query and report via wizards.

   •  Use the Report Wizard tool to build your reports

   •  Handle different screens for linked table and queries

   •  Add summarized calculations and crosstab query and report wizards

What are primary and secondary keys? Why we have any keys at all in a table? How to set them in table and working with the Index property? Also, look at the Indexes dialog box and know the pros and cons of setting indexes.

   •  Get introduced to and explain what Primary and Secondary keys are

   •  See how to set up Primary and Secondary keys in table

Introduce the process to database normalization. What is the status before the ‘First Norm’ state? Understand the first, second, and third norm state and know the key differences.

   •  Get introduced to the process to database normalization, some theories work

   •  Get the database ready for normalization and work with data

Learn the rules and prepare for creating joins including their data types. Cover the three types of joins and their impact. Expand on the join types and know their pros and cons.

   •  Cover the preparation tips and know the rules of joins

   •  Break down the three types of Access joins

Know where to apply Access joins and their differences. Cover the scope of an Access join and how it will impact the data. Setup and modify the join types in a query. Create the ‘Find Unmatched’ query using joins and the Null function.

   •  Know where to apply Access joins and understand their differences

   •  Understand the scope of a join in a query

Locate and open the Access relationship window. Navigate and introduce the tools in this window. Establish joins between tables and understand and set the referential integrity. Delete a join and know the differences between hiding a join. Also, see the working with the ‘Cascade Update Related Fields’ and ‘Cascade Delete Related Records’.

   •  Introduce the Access Relationship Window and tools

   •  Add the tables and connect between fields using the Referential Integrity

What are Microsoft Access Macros? Why do we need to consider using Macros in your Microsoft Access database. What are the pros and cons of using Macros? Identify the three different types of Macros and their scope.

   •  Introduce Macros and understand why use Microsoft Access Macro automation

   •  See the pros and cons when using Macros

   •  Identify the three different types of Macros and scope

Introducing the Macro Designer interface and their features. Locate various list views and their conventions. Create your first Macro, learn who to save a macro and assign it to an Access object. Cover some terminology for a Macro and understand variables and sub-Macros. See how to edit, delete commands, and run a Macro.

   •  Introduce the Macro designer interface and some of their features

   •  Create a Macro using simple commands and saving it

   •  Maintain a Macro and how to trigger it

Look at the Action Catalog pane view, work through some of the key commands and conventions used. Learn about the parameters (known as arguments) for key commands, including constants and building expressions to pass through a command. Using logical control flows in a Macro using the If condition.

   •  Introduce users to the Action catalog and find key commands

   •  Learn about parameters for key commands and set constants

   •  Use the If command and logically control Macro flow

Understand the two pre-defined Auto Macro names in Access. Also, see how to set them up and trigger them automatically. The tools to debugging a Macro and options including the single step process and using the single step command. Handling errors using a message box command interface.

   •  Using the two Auto-Macros with the pre-defined names

   •  How to set them up and trigger them automatically

   •  Debugging a macro using the tools and message box

Learn what Data Macros are and how they work with Access tables. Know the five before and after events to trigger a Macro. Handle errors using RaiseError in a Macro with a Data Macro. Cover Named Data Macros in a table and understand the difference between this and Event Data Macros.

   •  See the distinction of using Access Data Macros in tables

   •  Cover the five events to trigger a Macro

   •  See what Named Data Macros do and how they differ

Learn what Access VBA is and how it fits in with the other objects. Get an overview of VBA code (the Access modules) are covered here to really add some top-level automation to your database. The key differences between Macros and VBA code is explained so users know which to choose.

   •  Get an introduction to Access VBA and how it fits in the database

   •  Get a quick overview of Access modules and VBA code

   •  Understand the key differences between Access Macros and VBA

Work with the Visual Basic Editor alongside Access’s Interface. Look at the key components and introduce you to some terminology. See what the references tool is about and how it works to setting them up. It will also Introduce you to the Object Browser tool and how to get more help on a keyword.

   •  Get introduced users to the Visual Basic Editor and switch applications

   •  Cover the key components and some terminologies with shortcuts

   •  Learn about and using the References and Object Browser tools

Create a module and first VBA program code. Understand some of the syntax used in VBA to master the language and gain the confidence. Add comments and look at the some of the conventions used including arrays. Cover some of the control flows, making logical decisions and loops.

   •  Build your first program using VBA code in a module

   •  Understand the language syntax and know the code rules

   •  Add comments and learn about arrays plus the control flows

Work with Access forms and their events, responding to events. Moving to controls in a form and the cursor positioning. Change the appearance and functionality of an Access form and take control of their properties.Populating data to form controls and adding a related record from another form.

   •  Work with Access forms and respond to their events

   •  Work on more of the form’s properties using VBA code

   •  Populate data to a control and worki with related records

Understand what compilation of VBA code means and how to test the code. Know the types of errors in VBA code and how to handle them. Deal with logical errors and cover some of the error tools available. Handle and trap the runtime errors and step through VBA code.

   •  See what VBA errors are and the types that are generated

   •  Deal with logical errors and how tom handle them

   •  Handle runtime errors and the debug tools available