Lesson 8: Advanced Queries

The use of the “Between … And” Condition

You use the “Between … And” condition if you want to look up between X and Y values.

Instead of writing all those characters as “> =” and “<=” as you saw in the previous image in the previous lesson, we can simply use the “Between .. And” condition:

It’s just more convenient and easier to read if we just want to read our criteria later.

The use of random characters (“Wild Cards”) in “Queries”.

We use wildcard characters to give additional flexibility to our “Queries”.

There are two wild card characters that we need to remember:

the asterisk character (*)
and the question mark (?)

The asterisk represents multiple unknown characters.
for example. the criteria “N *” would find all N-words like never, No, Never someone …
You can also use the asterisk before and after the letters as * Paul*, it will find Peter Paul Rubens.

The question mark represents only a single character.
For example, the criteria p?ter would all find words like peter, pater.
You can also use two or more question marks eg: ??en, shows then, when.

Field calculations

A field calculation is actually a field that is composed of functions with values from other table fields, or functions introduced by a person.
This does not always mean an actual calculation but it could as well be the merging of two fields, like eg FirstName and LastName.

The data resulting from these calculations are only displayed when the query is “Run” (“Run”), and are not stored in tables.

Calculations can be almost all functions (most of the Excel functions can be used), and can be applied in any field that is available in the query, or data that is entered manually as a base for the operations.

In order to give a calculation to a field in the query, we first open the query itself.

In the “Field Name” text box, in the first available column, we type a name for the “field calculation”, followed by a colon (:).

Now you can type the formula you want to evaluate and demonstrate in the new field.

If you use the fields in the formula you need place the name of the field in square brackets [].

We first give a name, then the field between the square brackets, then the ampersand (the fields will be merged), then ” ” (a space between the two fields to create), then again an & sign, and then the second field in square brackets.

This gives the result:

If your fields have the same name in the tables that you use in your query, you must specify the table in square brackets [] followed by a period, then the field in square brackets.

vb.: name: [table1]. [First Name] & ” ” & [Table2]. [Last Name]

A formula like this: Product Sales: ([Order Details]. [UnitPrice]* [Quantity])

In a query like this:

Would give us the following results:

Fields with the highest values (“Top Value”)

We may want to show result of the above example query a with little more detail, so that we show only the top 10 of the highest product sales amount.

How we do it?
In the query “Design view” we choose our first column we just created (“Product Sales”).

Clicking on the field and we choose Sort as “Descending”.

The Top Value field choose a number or percentage, or type it in yourself.

This gives us the following result:

Function Queries

Thus, we can create our query functions with mathematical formulas that we have seen in the example above.

This is the formula that we had used for this:
Product Sales: ([Order Details]. [UnitPrice]* [Quantity])

Perhaps a simple example of a formula:

These short formulas are often used in “Reports”

Parameter Queries

We can also set parameters in our query to ask for values when we run our query. This is very helpful because it helps us to be flexible in creating our query.

Suppose we want to know the sales of a particular vendor between a certain date: