Lesson 19: Intermediate Data Selections

	Intermediate Data Selections

Data Junctions

Introduction

Data relationships provide the techniques of making data from one table available to the records of another table. This proves to reduce data duplication and mistakes. Another issue that involves the combination of tables consists of creating records from more than one table and making the result into a single list. This is the basis of data joins.

A data join is a technique of creating a list of records from more that one table, using all columns from all tables involved, or selecting only the desired columns from one or all of the tables involved. This means that a data join is essentially created in three steps:

Choosing the tables that will be involved in the join
Selecting the column that will create the link in each table
Formulating a SQL statement that will produce the records

Practical Learning: Introducing Data Junctions

Start Microsoft Access
Open the FunDS1 database from the previous lesson

The Tables of a Join

Before creating a join, you must have the tables that would be involved. The tables are created using the techniques we have seen in previous lessons. It is also important to create a primary key for the parent table that would be used to "link" it to a child table.

When creating the child table, remember to create a column that would serve as the link with the parent table. Here are examples of the necessary tables:

Private Sub cmdCreateTables_Click()
    DoCmd.RunSQL "CREATE TABLE Departments(" & _
                 "DeptCode char(5) PRIMARY KEY NOT NULL," & _
                 "Department varchar(50));"
    MsgBox "A table named Departments has been created."
    
    DoCmd.RunSQL "INSERT INTO Departments VALUES('HMNRS', 'Human Resources');"
    DoCmd.RunSQL "INSERT INTO Departments VALUES('RESDV', 'Research & Development');"
    DoCmd.RunSQL "INSERT INTO Departments VALUES('ITMNG', 'Information Technology & Management');"
    
    DoCmd.RunSQL "CREATE TABLE Employees(" & _
                 "EmplNbr char(7) PRIMARY KEY NOT NULL," & _
                 "FirstName varchar(20)," & _
                 "LastName varchar(20) NOT NULL," & _
                 "DeptCode char(5) NULL);"
    MsgBox "A table named Employees has been created."
    
    DoCmd.RunSQL "INSERT INTO Employees VALUES('204-517', 'Albertine', 'Walley', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('792-826', 'Rick', 'Bowden', 'HMNRS');"
    DoCmd.RunSQL "INSERT INTO Employees(EmplNbr, FirstName, LastName) " & _
                 "VALUES('274-759', 'Lisa', 'Brayer');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('729-475', 'Wally', 'Bastion', 'HMNRS');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('759-794', 'Sylla', 'Nguyen', 'ITMNG');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('824-004', 'Donald', 'Wallace', 'HMNRS');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('279-405', 'Hermine', 'Khan', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees(EmplNbr, FirstName, LastName) " & _
                 "VALUES('248-295', 'Jamie', 'Thomas');"
    DoCmd.RunSQL "INSERT INTO Employees(EmplNbr, FirstName, LastName) " & _
                 "VALUES('979-514', 'Campbell', 'Barns');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('297-924', 'Paula', 'Barners', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('249-920', 'Chrissie', 'Dentd', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('204-058', 'Ernestine', 'Essiane', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees(EmplNbr, FirstName, LastName) " & _
                 "VALUES('828-294', 'Hallio', 'Randt');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('279-750', 'Helene', 'Cranston', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('294-759', 'Hoanga', 'Klein', 'ITMNG');"
    DoCmd.RunSQL "INSERT INTO Employees(EmplNbr, FirstName, LastName) " & _
                 "VALUES('974-003', 'Phaolin', 'Krazucki');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('208-095', 'Frank', 'Burditt', 'HMNRS');"
End Sub

Inner Joins

Imagine you have two tables that can be linked through one's primary key and another's foreign key. When creating a query of records of the Employees table, if you want your list to include only records that have an entry, you can create it as an inner join. To do this, you would use the INNER JOIN expression. Here is an example:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.LastName, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.DeptCode, " & _
                   "       Departments.DeptCode, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode"
                
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtEmployeeDepartment.ControlSource = "Employees.DeptCode"
    txtDepartmentCode.ControlSource = "Departments.DeptCode"
    txtDepartment.ControlSource = "Department"
End Sub

This would produce:

Join

Practical Learning: Using a Data Join

In the Navigation Pane, right-click InventoryAnalysis and click Design View
In the Properties window, click All and, in the Record Source, click the ellipsis button

Left Outer Joins

Instead of showing only records that have entries in the child table, you may want your statement to include all records, including those that are null. To get this result, you would create an outer join. You have three options.

A left outer join produces all records of the child table, also called the right table. The records of the child table that don't have an entry in the foreign key column are marked as NULL. To create a left outer join, you can replace the TypeOfJoin of our formula with LEFT JOIN or LEFT OUTER JOIN. Here is an example:

Private Sub cmdDataSource_Click()
    Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.DeptCode, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "LEFT OUTER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartmentCode.ControlSource = "Departments.DeptCode"
    txtDepartment.ControlSource = "Department"
End Sub

This would produce:

Join

Right Outer Joins

A right outer join considers all records from the parent table and finds a matching record in the child table. To do this, it starts with the first record of the parent table (in this case the Genders table) and shows each record of the child table (in this case the Persons table) that has a corresponding entry. This means that, in our example, a right outer join would first create a list of the Persons records that have a 1 value for the GenderID column. After the first record, the right outer join moves to the second record, and so on, each time listing the records of the child table that have a corresponding entry for the primary key of the parent table.

To create a right outer join, you can replace the TypeOfJoin factor of our formula with RIGHT OUTER JOIN. Here is an example:

Private Sub cmdDataSource_Click()
    Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.DeptCode, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "RIGHT OUTER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartmentCode.ControlSource = "Departments.DeptCode"
    txtDepartment.ControlSource = "Department"
End Sub

This would produce:

Join

Notice that the query result starts with the first record of the parent table, also called the left table, and lists the records of the child table, also called the right table, that have the entry corresponding to that first record. Then it moves to the next value.

Data Filtering and Joins

Just as done for a SQL statement that involves only one table, you can filter records using any field(s) for one or more of the tables in the statement. To do this, after the join statement, add your WHERE clause with the appropriate expression. Here is an example:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode " & _
                   "WHERE Employees.LastName = 'Klein';"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub

Filtering Record in a Join

As mentioned already, you can use any available column(s) of the tables. Here is an example that uses a field of the parent table:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode " & _
                   "WHERE Departments.Department = 'Human Resources';"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub

Filtering Record in a Join

Topics on Field Selection

Hiding a Column

Consider a table of employees. Imagine that you want to create a list of employees using their names and you want to show their work department. Imagine that you want the list to include only the employees who work in human resources; that is, employees whose Department value is HMNRS from the Employees or Human Resource from the Departments table. You can create a SQL statement as follows:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode " & _
                   "WHERE Departments.Department = 'Human Resources';"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub

I this case, the Department column is included in the SELECT statement. Since the list includes employees who work in human resources and we know that this is what the query would produce, it becomes redundant, quite useless, to include the Department column in our list. Therefore, we can hide it. To create such a query, omit the column in the SELECT statement but include it as part of your WHERE condition. Here is an example:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode " & _
                   "WHERE Departments.Department = 'Human Resources';"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
End Sub

Notice that the Department column is not included as part of the SELECT statement. This would produce:

Employees

The Alias Name of a Column

In your SELECT statement, after specifying the column(s) as we have done so far, when you execute the query, the name of each column would appear as the column header. When creating the table, if you had explicitly specified a caption for the column, the caption would display also when the query shows its result. On a query, instead of the default name used as the caption, if you want, you can display any string of your choice for a column header. To specify a column header other than the name of the column, if you are visually creating the SQL statement in the Select Query window, in the box that receives the name of the selected column, type the desired string, followed by a colon ":", followed by the actual name of the column from the table.

If you are manually writing the SQL statement, type the actual name of the column, followed by the AS keyword, followed by the desired string. If the desired column header is in one word, you can simply type it. Here is an example:

SELECT DateHired AS EmployedSince, LastName, HourlySalary
FROM Employees;

If the string is in more than one word or contains a symbol that could be confused with an operator, you should include the whole string between an opening square bracket and a closing square bracket. In fact, you should always include the string between square brackets. Here are two examples:

SELECT DateHired AS [EmployedSince], LastName, HourlySalary AS [Pay Rate]
FROM Employees;

You can also include the string in single-quotes. Here are two examples:

SELECT DateHired AS [EmployedSince], LastName, HourlySalary AS 'Pay Rate'
FROM Employees;

Practical Learning: Using Alias Names

Right-click in the title bar of the Query1 window and click SQL View

SELECT StoreItems.ItemNumber,
       StoreItems.DateEntered,
       Manufacturers.Manufacturer,
       Categories.Category,
       SubCategories.SubCategory,
       StoreItems.ItemName,
       StoreItems.UnitPrice,
       StoreItems.DiscountRate,
       IIf(IsNull([DiscountRate]),'',FormatNumber([UnitPrice]*[DiscountRate])) AS DiscountAmount,
       IIf(IsNull([DiscountRate]),'',FormatNumber([UnitPrice]-[DiscountAmount])) AS AfterDiscount
FROM   ((Manufacturers INNER JOIN StoreItems ON Manufacturers.ManufacturerID = StoreItems.ManufacturerID)
       INNER JOIN Categories ON StoreItems.CategoryID = Categories.CategoryID)
       INNER JOIN SubCategories ON StoreItems.SubCategoryID = SubCategories.SubCategoryID;

Close the Query Builder
If asked whether you want to save, click No

A Combination or Expression of Columns

When creating a query, instead of having separate columns, you can combine two or more columns to create a string or a value that is in fact an expression. For example, you can combine a first name and a last name to create a full name.

An expression that combines columns can be performed on text-based columns. such as a first name being added to a last name to get a full name. To create this type of expression, you can use the + operator to concatenate the string as in FirstName + " " + LastName. After creating the expression, because the result is not part of the table (or the query) from which the query is based, you must give an alias name to the result. Here is an example:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.LastName + ', ' + " & _
                   "       Employees.FirstName As FullName, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode "
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFullName.ControlSource = "FullName"
    txtDepartment.ControlSource = "Department"
End Sub

Filtering With an Expression

Instead of the addition operator, you can use the ampersand & operator to perform the same operator. Instead of the single-quotes used to add a string in the expression, you can use double-quotes.

Besides strings, you can create a type of expression that uses a date on the table, add a number to it to get a date on another day. An expression can also be used to perform a calculation on two or more columns such as employees weekly hours multiplied by their hourly salary to get their weekly salary.

Pattern Operator: LIKE

Introduction

Most or all of the criteria we have specified with the WHERE keyword had to exactly match the specified criterion. In some cases, you may not remember the exact desired value of records but you want to specify some type of approximation. To do this, you use the LIKE operator.

If you are visually creating the statement, in the Select Query window, click the Criteria box corresponding to the column on which the condition would be applied and type. In a SQL statement, the LIKE operator is used in a formula as follows:

Expression LIKE pattern

The Expression factor is the expression that will be evaluated. This must be a clear and valid expression.

The pattern factor can be a value to be found in Expression. For example, it can be the same type of value used in a WHERE statement. In this case, the equal operator would be the same as LIKE. For example

SELECT Employees.DateHired, Employees.FirstName,
       Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.LastName)="Scott"));

is equivalent to

SELECT Employees.DateHired, Employees.FirstName, 
       Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.LastName) LIKE "Scott"));

The idea of using a LIKE operator is to give an approximation of the type of result you want. The available wildcards to se with the LIKE operator are:

LIKE Any Character *

If you want to match any character, in any combination, for any length, use the * wildcard. If you precede it with a letter, as in S*, the condition would consist of finding any string that starts with S. Imagine that you want to create a list of employees whose first names start with H. You would type the condition as LIKE "H*" (or LIKE 'H*'). Here is an example

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode " & _
                   "WHERE Employees.FirstName LIKE 'H*';"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub

This would produce:

To negate the condition, you can precede the criterion with NOT. That is type the NOT operator just after WHERE. Here is an example:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode " & _
                   "WHERE NOT(Employees.FirstName LIKE 'H*');"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub

This would produce:

As you can see, this results in the list of employees whose first names don't start with H.

You can type the NOT operator before the LIKE expression. Here is an example:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode " & _
                   "WHERE Employees.FirstName NOT LIKE 'H*';"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub

When you precede the * character with a letter, only that letter would be considered. Alternatively, you can specify a group of characters that would precede the * symbol. For example, if you have some last names that start with San in a list but you don't remember the end of the name you are looking for, to create the list, you can specify that the first name would start with San and end with whatever. In this case, you would use San* as follows:

The corresponding SQL statement is:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode " & _
                   "WHERE Employees.FirstName LIKE 'Wall*';"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub

This would produce:

Instead of ending a letter or a group of letters with *, you can begin the LIKE statement with *. An example would be LIKE "*on". In this case, all strings that end with on, such as Bastion or Cranston, would be considered.

If you remember neither the beginning nor the end of a string you want to search for but you know a sub-string that is probably included in the type of string you are looking for, you can precede it with * and end it with *. An example would be LIKE "*er*". In this case, all strings that include er anywhere inside, such as Berg or Merck, would be considered.

Like the other SQL statements, you can also negate this one.

Practical Learning: Selecting Records LIKE

In the Controls section of the Ribbon, click the Text Box and click the Form Footer section of the form
In the Controls section of the Ribbon, click the Button and click the Form Footer section of the form.
If the wizard starts, click Cancel
In the Controls section of the Ribbon, click the Button and click the Form Footer section of the form.
If the wizard starts, click Cancel

Complete the design of the form as follows:

Control	Name	Caption
Text Box	txtManufacturer	Show items made by
Button	cmdShowManufacturers	Show
Button	cmdClose	Close

Right-click the new Show button and click Build Event...
In the Choose Builder dialog box, double-click Code Builder

Implement the event as follows:

Private Sub cmdShowManufacturers_Click()
On Error GoTo cmdShowManufacturers_Click_Error

    Me.Filter = "Manufacturer LIKE '*" & txtManufacturer & "*'"
    Me.FilterOn = True
    
    Exit Sub
    
cmdShowManufacturers_Click_Error:
    MsgBox "There was an error when trying to show the selected list of manufacturers. " & _
           "Please report the error as follows." & vbCrLf & _
           "Error #: " & Err.Number & vbCrLf & _
           "Description: " & Err.Description & vbCrLf & _
           "Please contact the program vendor if " & _
       "he is not sleeping at this time."
    Resume Next
End Sub

Return to Microsoft Access
Switch the form to Form View
In the Show Items Made By text box, type klein
Click its Show button
Close the form
When asked whether you want to save it, click Yes

LIKE a Range of Characters []

The * wildcard is used to precede or succeed a specific character or a group of characters, that is, any character. If you want to consider only a range of characters from the alphabet, you can include the range in square brackets. To do this, type [, followed by the lowest character of the range, followed by -, followed by the highest character of the range, followed by ]. For example, to get a list of employees whose last names start with letters between E and H, you would specify the criterion as LIKE "[E-H]*". Here is an example:

The SQL statement of this query is:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode " & _
                   "WHERE Employees.FirstName LIKE '[K-N]*';"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub

This would produce:

Once again, remember that you can negate this expression by preceding it with NOT.

LIKE an Unknown Character ?

Imagine that you know a certain pattern in the string but you are forgetting only one letter. For example, if you know that the employee's first name sounds like Saul, Haul, or Paul. In other words, you don't know for sure what the first letter is but you are sure about the other characters. To match a character in a string, you can use the ? symbol as the wild card. Here is an example:

SELECT 	Employees.DateHired, Employees.FirstName,
	Employees.LastName, Employees.Department
FROM Employees
WHERE Employees.FirstName Like "?aul");

Logical Conjunction

Introduction

In the previous sections or lessons, we stated the conditions one at a time. This made their interpretation easier. Sometimes, you will need to test a condition that depends on another. Boolean algebra allows you to combine two conditions and use the result, or to test two conditions but consider if either is true.

Suppose you get an assignment to create a database used for a company that rents apartments to customers. The operations would consist of registering customers who become tenants and allocating them an apartment based on their needs and the availability.

Logically Combining Conditions

When you perform filtering on a form, it fires the On Filter event. When you apply the filter on a form, it fires an On Apply Filter event.

Here is an example:

Private Sub Form_Open(Cancel As Integer)
    Filter = "(PropertyTypeID = 1) AND (OccupiedVacant = 'Vacant')"
    FilterOn = True
End Sub

Logical Conjunction in SQL Statements

To express the logical conjunction, the SQL uses the AND operator. To create a logical conjunction in SQL, type one condition on the left and the other condition on the right sides of the AND keyword using the following formula:

SELECT WhatColumn(s)
FROM WhatObject
WHERE Condition1 AND Condition2

The WhatColumn(s) and the WhatObject factors are the same we have used so far. The AND keyword is the new one. Each condition is written as a SQL operation using the formula:

Column operator Value

In this case, the WHERE operator resembles the If conditional statement. The Condition1 is the first that would be examined. Remember that, from our discussion earlier, if the first condition is false, the whole statement is false and there is no reason to examine the second condition. If the first condition is true, then the second condition would be examined. Based on this, the SQL statement used to get a list of movies released in 1994 but rated PG-13 is:

SELECT Title, Director, CopyrightYear, Rating
FROM Videos
WHERE CopyrightYear = "1994" AND Rating = "PG-13";

The equivalent SQL statement of the above query in SQL as written by Microsoft Access is:

SELECT Videos.Title, Videos.Director, Videos.CopyrightYear, Videos.Rating
FROM Videos
WHERE (Videos.CopyrightYear)="1994") AND ((Videos.Rating)="PG-13");

Logical Disjunction

A logical disjunction is used to check that either of two outcomes is true. The comparison is done using the OR operator.

Other Logical Operators on Queries

Selecting Values BETWEEN

If you have a logical range of values and you want to know if a certain value is contained in that range, you can use the BETWEEN operator. The BETWEEN operator is combined with AND to get a list of records between two values. The basic formula of this operator is:

Expression BETWEEN Start AND End

The Expression placeholder of our formula is usually the name of the column whose values you want to examine. The Start factor is the starting value of the range to consider. The End factor is the highest value to consider in the range. After this condition is executed, it produces the list of values between Start and End. Here is an example:

SELECT Videos.Title, Videos.Director, Videos.CopyrightYear, Videos.Rating
FROM Videos
WHERE (Videos.CopyrightYear) Between 1994 And 2004;

IN a Series of Values

If you have a series of records and want to find a record or a group of records among them, you can use the IN operator. The IN operator is a type of various OR operators. It follows this formula:

IN(Expression1, Expression2, Expression_n)

Each Expression factor can be one of the values of a column. This is equivalent to Expression1 OR Expression2 OR Expression3, etc. Here is an example that shows the list of movies directed by either Oliver Stone or Ron Howard:

SELECT Videos.Title, Videos.Director, Videos.CopyrightYear, Videos.Rating
FROM Videos
WHERE (Videos.Director) In ("Oliver Stone","Ron Howard");

Parameterized Queries

A parameter query is one that requests a value from the user and displays its result based on the user's choice. As its name implies, this query expects a parameter, like the arguments we reviewed for procedures. This means that, when creating such a query, you must prepare to display a request to the user.

To create a parameterized query, you can use the BETWEEN, LIKE, NOT, or IN operators. For example, to let the user enter part of a name of a director, you could set the Criteria of the Director to:

LIKE "*" & [A director name that includes] & "*"

When the query runs, if the user enters a name such as Phillip, the list would include the 6th and the 12th videos. Instead of requesting just one value as a parameter, you can request more than one. To do this, you can use the BETWEEN operator that requests an additional AND. For example, to ask the user to specify a range of years whose videos you want to see, you would set the Criteria of a CopyrightYear to

BETWEEN [Enter a starting year] AND [Enter an ending year]