Query is one of the objects of a database that is used to view, change and analyze data in different ways. It can also be used to perform calculation and can also serve as source data for forms and reports in MS Access. It can also be used to retrieve some records in one or more tables based on a specified criteria.

This can be very useful especially if there are so many records in a table and you wish to retrieve only the records that met the specified criteria. To achieve such a task manually would take much of your time, but with MS Access database queries, you can achieve it within few minutes.

This is chapter 4 of the MS Access tutorials. This chapter has 5 parts and this is the first part (part 1). In this part, I will explain all the features of query as an object of a database in Microsoft Access. I will also discuss the various views available in query object, the various operators used in query operation and the various methods of querying or filtering a database in MS Access.


In the previous chapter (chapter 3), I explicitly explained Form as an object of a database in MS Access. See it at http://www.microsofttut.com/2017/01/forms-manipulations-and-some-powerful-built-in-functions.html

You can take a look at the previously treated chapters in this MS Access tutorial at http://www.microsofttut.com/p/course-content-for-microsoft-access.html.

QUERY – A POWERFUL OBJECT OF A DATABASE

Queries enable you to extract data from your database tables and allow us to answer questions we have about the data. Queries may combine data from multiple tables and manipulate data output through the use of expressions, formulas, and functions.

Knowing the appropriate questions to ask and retrieve from your database in a business environment is just as important as knowing how to build that question into a database query. Most people know the questions to ask their company’s database but they don’t know how to ask such questions.

This chapter will give you a deep knowledge about Microsoft Access operators and their operations, simple select query and filter query or query by filter. The queries we will discuss in this chapter are the simple or ones, then in the next chapter, we will discuss advanced queries in detail.

We will also demonstrate the use of functions such as Like, In, Between, Null, and Parameters. Also in this chapter, will also illustrate some of the practical applications of query in the design of students’ result system and workers’ payroll system

What Can You Ask and Retrieve from your Database?
When working with databases in a business, there are numerous questions you may want to your company’s database. Assume you are managing a retail store with numerous sales employees working on sales commission. Here are some basic examples of questions you may ask your retail store database:

• Which are the best-selling products?
• Which employees are the most productive?
• What day and time of week is busiest in your store?
• What products have the highest profit margin?
• What are the total dollar sales by product each day this week?
• What is the price for a particular product?
• What products in inventory have never sold?

It is important to realize that vast amounts of information may be queried from the database if you know the right questions to ask—and you know how to formulate that question into a database query. Knowing the right questions to ask comes with managerial experience and will not be covered as a topic in this chapter. Instead, this chapter will explain how to translate a question or request for information into a Microsoft Access query.

WORKING WITH QUERIES IN MS ACCESS
Any query can be saved and rerun at a later time. Each time a query is run, it will retrieve the most recent data from the database tables to generate the output. As a result, queries are often used as the basis for database forms and reports.

It is also important to understand that there is a direct “connection” between the query output and the table data. Any change you make to data in the query output is actually modifying the table data at the same time. This is similar to the connection we saw earlier between forms and tables.

Before going into query types and their designs, it is necessary that you know and familiarize yourself with the different types of OPERATORS and when they should be used because you will make use of them when querying your database.

OPERATORS
These are signs, symbols or words used in MS Access to define criteria and to perform calculations.

TYPES OF OPERATORS
There are 3 main types of operators used in MS Access. They are:
• Mathematical Operators
• Relational Operators
• Logical Operators
Now let me elaborate them one by one.

1. MATHEMATICAL OPERATORS

These are the most commonly used operator. They are used to perform calculations. Examples include:
• Addition (+)
• Subtraction (-)
• Multiplication (*)
• Division (/)

2. RELATIONAL OPERATORS

These are the operators used to create or define criteria. They are sometimes used together with logical operators to combine two or more criteria. They include:
• Equal to (=)
• Not equal to (<>
• Greater than (>
• Less than (<
• Greater than or equal to (>=)
• Less than or equal to (>=)

3. LOGICAL OPERATORS

These are the operators usually used to combine two or more criteria. They are used together with relational operators to define complex criteria. They include: AND, OR, NAND, NOR, EOR (XOR) and NOT.

• AND returns true when the two (or all) conditions are true, else false (i.e. returns false).

• OR returns true when either of the two or both of the conditions are true, else it returns false.

• NAND is the negation (reverse or opposite) of AND (pronounced NOT AND). It returns false when both conditions are true, else it returns true.

• NOR is the negation of OR (pronounced NOT OR). It returns false when either of the two or both conditions are true, else it returns false.

• EOR (XOR) (pronounced EXCLUSIVE OR) returns true when either of the conditions is true (i.e. when both conditions are not the same), else it returns false.

• NOT is used in a case of one condition. It returns true when the condition is false, else it returns true. In electronics programming, it is called an INVERTER.

The figure below contains the truth table of some common logical operators in MS Access.

Visit http://www.microsofttut.com/2017/03/introduction-to-queries-in-ms-access-2013-2016.html to see full post with screen to screen images.