Introduction To Queries In Microsoft Access 2013 And 2016 – Part 1

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.
MS Access Tutorial: query

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.


You can take a look at the previously treated chapters in this MS Access tutorial.

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. 

Truth Table for Logical Operators

NOTE:

Be very careful when choosing the right logical operator to use because their error debugging are very hard to deal with. There are other ones like XNOR, etc.

AVAILABLE VIEWS IN MICROSOFT ACCESS QUERY

Once you have a query created, you can view it in the Datasheet View, SQL View, or the Design View. While building and testing database queries, you will mostly switch between the Datasheet View and Design View.

Datasheet View: 

This is the end-user view that displays the results of the query after it is run. Any changes and updates made to the data in the Datasheet View will update the table automatically

Design View: 

This is the view used to build and design queries. Using this view, you can set criteria, select specific fields to output, and sort the data, among other things. In this view, you cannot work directly with any of the query data.

SQL View: 

This is an advanced view that allows you to edit the SQL (structured query language) code for the query.

SQL is a full topic itself and therefore I will treat it in a full chapter later. I will be mentioning and using SQL in the advanced query in the next chapter, but I will try to make everything simple until we fully treat SQL.

NOTE:

To access any of these query views, click the View drop down arrow under the Views group in the Home tab as shown below.
Query views
Also note that queries use data contained in a table or previously created queries as their data source. Therefore, you must first create a table and add some records before you run a query or save a previously created query.

QUERY DESIGN METHODS

In MS Access, you can create your query using any of the two methods depending on the type of query you want to create:

1. DESIGN QUERY METHOD:

This method is recommended for creating simple queries because it helps you to design a query from the scratch. It can also be used to create advanced queries like Crosstab Query, Find Duplicates Query and Find Unmatched Query, but it is very tedious and always results in mistakes which will not allow the query to run properly.

2. QUERY DESIGN METHOD:

This method is easier but does not allow you to understand the working principles of queries. It is recommended for the three advanced queries I mentioned above because it does most of the difficult tasks for you.

TYPES OF MS ACCESS SIMPLE QUERIES

We will discuss and explain the two types of Access simple queries, which are:
1. Simple Select Query
2. Query by Filter

SIMPLE SELECT QUERY

This is the most commonly used type of query. It retrieves records from one or more tables (using criteria you specify) and displays the results in a datasheet where you can update the records. You can also update the records and perform calculations with some Access built in functions like SUM, COUNT, AVERAGE, etc.

To create a simple select query:

1. Click the Create tab and then click Query design under the Queries group.

2. A query design dialogue box appears. Select the tables/queries you wish to use as your data source by clicking on the appropriate data source tab.

3. Click the Add button and then click the Close button when you are through as shown below.

NOTE:

if you wish to add another data source later, click the Show table icon in the Query Setup group under the Design tab. 
Show table dialogue
4. For each table, select the fields whose data you want to retrieve. You can select more than one field at a time by holding down the ctrl key and then clicking on the fields needed.

5. Click and drag the selected fields into the first field in the Field row (first row). Once you drop them in the first field, Access automatically fixes the fields for you. The Table row displays the table/query name from which the field came.

You can also change any field by clicking the drop down arrow beside the field in the Field row and then select the desired option.

6. Set the query criteria inside any field of the Criteria row. For example, in my own case, I want the query to display the records of only engineering students. So under the COURSE column for the Criteria row, I will type (in quotes) “engineering” and then click the Run in the Results group command as shown below.
Simple select query
7. Save your work when you are through. My query output is shown below.
Simple select query output

NOTE:

In a Simple Select Query, you can only specify one criteria. Else it is no more a simple select query.

QUERY BY FILTER

Filter is a type of query used to extract or retrieve a set of records that met the specified criteria. Here the condition set (criteria) may be one or more than one unlike the simple select query which must be one. When you query a database using this method, the query filters the matching records based on the set criteria.

For example, you might want the query to return the records of all male students in the database. You can further filter the returned record by also telling your query to only return those whose age falls within a particular range. You can also tell your query to exclude records that met the specified criteria and return the rest of the records.

This is the end of the part 1 of this chapter 4 tutorial. 

Recommended MS Access Textbook

ms access textbook kindle format


Click Here to know more about the book.

In the next part (part 2) of this chapter, I will show you the various methods of filtering or querying a database (query by filter) and how each method can be achieved.

See you over there!


Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.

Also click Here to subscribe for free so that you will get our latest game updates in your email.

No comments:

Post a Comment

WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!