Joining Tables in a Query (“Query Joins”)

When you add multiple “tables” to a query, the “relationships” between those tables that you have entered in the query, allow you to retrieve the necessary information from any table in your query processing.  As mentioned earlier, the tables that you use in your query have to have a direct relationship with each other.

If you’re trying with unrelated tables, for example if you take two tables from an Excel document in a query process, you must first establish a temporary relationship between these tables in the query itself.

You may do this in the same way as you linked two tables in the “relationships” window.

Simply click on the element in the first table and drag it to the element in the second table that relates.

You can then edit the relationship to get the desired results in the query.

To do this we double click on the “relationship line”

(“Join Query”)

Between the two tables.
And we see the “Join Properties” window:

We have three options to establish a relationship between the tables.

Determine which option best fits your desired results, and click OK

“Query Joins” are particularly important since the “type” of Join you select will drastically affect the results.

Adding criteria to the query grid

If we want to access specific records from a table based on values from a specified query, we must enter the criteria in that field.

So if we want to see employees with the name Fuller only, we type this name in the “Criteria” box in the “LastName” column .

Result:

“Run” A Query (“Run”)

In the query “Design View”, click the “Run” button .

Or from the Database window, select the query and click the “Open” button.

Writing SQL Code

In Access, when you compose the query in query “Design view”, what you are really doing is writing SQL code.

SQL stands for “Structured Query Language” and is a widely used programming language in various database programs.

If you want to learn SQL, it can be interesting even to look at the code of the query that we just created.

Click on “View” in the menu bar and choose “SQL View”.

If you have enough knowledge of SQL, you can manually make changes in the SQL window.

Results of a Query can be sorted in the “Query By Example” (QBE) grid.

In Access we can sort the results of our query.

In order to apply the sort, we select the field and place our cursor in the “Sort”.

We can use the DropDown arrow and select “Ascending” or “Descending”.

Hide fields in the result

Sometimes when we create a query on a specific field in the QBE grid, it may be necessary to hide some fields that are applied in the criteria.

If we do not want that these fields also to be visible in the result, we hide these fields.

On the “Show” line, we check the field that we do not want to display, i.e.”de-select” it.

The result of the query, it shows all employees with the title “Sales Representative”, but the Title of the column itself does not get displayed.

Using “Comparison operators” (Comparison Operators)

Comparison Operators are used to search for data that is not necessarily the same as the inserted data.

We use the following symbols:

greater than                      >

is greater than or equal to  > =

less than                            <

less than or equal to            <=

equal                                   =

not equal                              <>

To add a “Comparison Operator” to our criteria, we open the query in “Design View” and in the criteria we type the comparative criteria that we want to use.

Please note if you type this in your criteria type this text within quotes

“”.

For dates type between

# #

characters.

And for table fields between

[ and ].

Let’s take an example:

Suppose we want to show our employees who have been working in our company from 01/01/94.

Then we give the following criteria in our criteria field of the column “Hire Date”:> # 31/12/1993 #.

As a result we get only the workers who started on 01/01/94 and later.

The use of “AND” and “OR” conditions.

In our next example we look at how we can filter the various criteria.

More than likely, it will happen that we want to filter both x and y values of different fields, or we want to show records with the value x OR y of the same or different fields.

We use the “AND” condition if it is required that both criteria must be met.

We use the “OR” condition if it is required to meet only one of the criteria.

We use the QBE grid for the “AND” or “OR” condition. We need to use multiple lines in the “Criteria” row for “OR”.

Inorder to give a “AND” condition to a criteria, the criteria must be placed in the same row.

To give an “OR” condition to a criteria, we must use different rows.

 

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats