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?
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.
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.
signs, symbols or words used in MS Access to define criteria and to perform
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.
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.
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
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.
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.
To access any of these query views, click the View drop down arrow under the Views group in the Home tab as shown below.
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.
if you wish to add another data source later, click the Show table icon in the Query Setup group under the Design tab.
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.
7. Save your work when you are through. My query output is shown below.
In a Simple
Select Query, you can only specify one criteria. Else it is no more a simple
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
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
See you over
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.