Tables and Queries   «Prev  Next»
Lesson 1

Advanced, Tables, and Queries using Microsoft Access

Welcome to Advanced Tables, Queries, Forms, and Reports, the third course in the Microsoft Access® 2000 Series.
This course is the third part in a four-course series that gives you a hands-on introduction to the basic through advanced features of Microsoft Access 2016, Microsoft’s popular database program. Microsoft Access is a very powerful database system, giving you features like no other program can. As with other systems, with power comes complexity. You can quickly get Access up and running using wizards. But once you have gone beyond where the wizards can take you, making Access perform more advanced tasks can become troublesome. If you have been using Access for a while but feel you should be able to get more out of your database, this is the course for you.

Course Objectives

After completing the course, you will be able to use Access to:
  1. Set up validation for fields
  2. Create custom validation messages
  3. Work with input masks
  4. Create and modify queries and forms
  5. Work with reports and subreports
  6. Add charts and graphics to forms and reports
  7. Work with the various kinds of controls, including ActiveX

The latest version of Microsoft Access is Access 2021. It was released on October 12, 2021. Access 2021 is available as part of a Microsoft 365 subscription or as a one-time purchase.
Access 2021 includes a number of new features and improvements, including:
  1. A new dark theme
  2. Extended precision date/time data type
  3. Support for Microsoft 365 Groups
  4. Improved performance and scalability

Here are some of the key features of Microsoft Access 2021:
  1. Create and manage databases: Access makes it easy to create and manage databases. You can create tables, queries, forms, reports, and macros to store, organize, and analyze your data.
  2. Connect to other data sources: Access can connect to a variety of other data sources, including SQL Server, Azure SQL Database, and SharePoint. This allows you to bring data from other systems into your Access databases.
  3. Develop applications: Access can be used to develop custom applications. You can use Access's built-in tools to create user interfaces, write code, and deploy your applications.
  4. Share data: Access makes it easy to share data with others. You can export data to a variety of formats, including CSV, XML, and PDF. You can also share databases with others through Access's built-in sharing features.

If you are looking for a powerful database application, Microsoft Access 2021 is a great option. It offers a wide range of features and capabilities that make it ideal for a variety of tasks.
Question: How do I set up validation for fields in Microsoft Access?
Setting up field validation in Microsoft Access is an essential part of data entry control, ensuring that only the right type of data gets stored in your tables. Here's a step-by-step process on how to do it:
  1. Open the relevant table in Design View. Click on the table you want to add validation to, then click on "Design View" in the toolbar at the top of Access. This will allow you to modify the structure of the table.
  2. Choose the field to validate. Click on the field (column) in your table that you want to add validation to. This will open the "Field Properties" pane at the bottom of the screen.
  3. Input the validation rule.
    In the "Field Properties" pane, there is a row labeled "Validation Rule". Click on this row, then type in the rule you want to use. For example, if you want to ensure a numerical field always receives a value of 5 or more, you'd enter ">4".
  4. Provide a validation text. Access provides a way for you to specify what message will be displayed when the validation fails. This is done in the "Validation Text" row in the "Field Properties" pane. Here, you could input a message like "Please enter a value of 5 or greater."
  5. Save the table.
    Once you've inputted your validation rule and validation text, save the table. Access will now enforce these validation rules anytime someone tries to input data into this field.

Here's an important note: validation rules in Access use a syntax similar to the SQL language, and they're not case sensitive. For example, for text fields, "LIKE '[A-Z]*'" would enforce that the entry starts with an upper case letter. For date fields, "<#01/01/2023#" would enforce the date entered to be before January 1, 2023. You can also use logical operators like And and Or to combine conditions.
Remember, it's important to test your validation rules to make sure they work as expected and deliver the right user experience. This will help to maintain data integrity in your database.