Home

Details on Creating Queries

 

Details on Column Selection

 

Hiding a Column

Consider the following table in a database:

 

Imagine that you want to create a list of employees using their names and you want to show their work department. You can create the following query:

Select Query

This would produce:

Based on this list, imagine that you want the list to include only the employees who work at the corporate office, that is, employees whose Department value is Corporate. From what we learned in data filtering, in the Criteria corresponding to the Department column in the Select Query window, you can simply type "Corporate"

The corresponding SQL statement is:

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

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

Notice that all filtered employees display Corporate. Since we are creating a list of employees who work at the corporate office 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. The problem is that we need it to pose the condition. To do this, we can add the column in the query to specify the condition but we would hide it from the result. The Select Query window provides a means of doing this visually. To do it, you can clear the check box of the Show row corresponding to the column. Here is an example:

The corresponding SQL statement is:

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

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

Practical Learning Practical Learning: Hiding a Column in a Query

  1. Start Microsoft Access and open the Yugo National Bank that you started in Lesson 18
  2. In the Database window, click Queries and double-click Create Query in Design View
  3. In the Show Table dialog box, double-click Customers and AccountTypes
  4. Click Close
  5. In the Customers list, double-click DateCreated, AccountNumber, and AccountName
  6. In the AcountTypes list, double-click AccountType
     
  7. To view the result, right-click the Select Query window's title bar and click Datasheet View
  8. To return to the design, right-click the title bar again and click Query Design
  9. Clear the check box of AccountType and, in the corresponding Criteria field, type "Checking"
     
  10. Switch the query to Datasheet View to see the result
     
  11. Save the query as CheckingAccounts and close it
  12. To create a new table in the Yugo National Bank database, on the main menu, click Insert -> Table and, in the New Table dialog box, double-click Design View
  13. Set the first column as follows:
    Field Name: TimeSheetID
    Data Type: AutoNumber
    Caption: Time Sheet ID
  14. Right-click it and click Primary Key
  15. Set the second Field Name to EmployeeID and its Data Type to Lookup Wizard
  16. When the wizard starts, accept the first radio button and click Next
  17. In the second page of the wizard, click Employees and click Next
  18. In the third page of the wizard, in the Available Fields list, double-click EmployeeNumber, FullName, and Salary
  19. Click Next and click Finish
  20. Save the table as TimeSheet
  21. Complete the table with the columns as follows:
     
    Field Name Data Type Field Size Format Input Mask
    PayrollID        
    EmployeeID        
    WeekStartDate Date/Time   dd-mmm-yyyy 00\->L<LL\-0000;;_
    WeekEndDate Date/Time   dd-mmm-yyyy 00\->L<LL\-0000;;_
    WeeklyHours Number Double Fixed  
  22. Save the table and switch it to Datasheet View
  23. Create a few entries as follows:
     
    Payroll ID EmployeeID WeekStartDate WeekEndDate WeeklyHours
    1 DX-6288-K4 3/31/2002 4/6/2002 38.50
    2 FH-1984-K2 3/31/2002 4/6/2002 42.50
    3 GT-4825-L2 3/31/2002 4/6/2002 40.00
    4 BM-0082-H2 3/31/2002 4/6/2002 36.00
    5 FO-2784-G0 3/31/2002 4/6/2002 40.50
    6 TL-3825-G4 3/31/2002 4/6/2002 32.00
    7 FF-2799-G2 3/31/2002 4/6/2002 42.00
    8 GG-6626-D3 3/31/2002 4/6/2002 38.50
    9 HD-3938-F4 3/31/2002 4/6/2002 40.00
    10 OR-0026-Z6 3/31/2002 4/6/2002 40.00
    11 FF-2799-G2 4/7/2002 4/13/2002 36.00
    12 HD-3938-F4 4/7/2002 4/13/2002 37.50
    13 TL-3825-G4 4/7/2002 4/13/2002 40.00
    14 DX-6288-K4 4/7/2002 4/13/2002 41.00
    15 BM-0082-H2 4/7/2002 4/13/2002 38.00
    16 FO-2784-G0 4/7/2002 4/13/2002 40.00
    17 PL-2783-G7 4/7/2002 4/13/2002 34.00
    18 GG-6626-D3 4/7/2002 4/13/2002 32.00
    19 FH-1984-K2 4/7/2002 4/13/2002 39.00
    20 GG-6626-D3 4/14/2002 4/20/2002 41.00
    21 FF-2799-G2 4/14/2002 4/20/2002 38.00
    22 KS-1114-Y2 4/14/2002 4/20/2002 40.00
    23 UD-4050-X2 4/14/2002 4/20/2002 42.00
    24 DX-6288-K4 4/14/2002 4/20/2002 38.50
    25 TR-7728-G5 4/14/2002 4/20/2002 36.00
    26 FH-1984-K2 4/14/2002 4/20/2002 42.50
    27 TL-3825-G4 4/14/2002 4/20/2002 35.00
    28 BM-0082-H2 4/14/2002 4/20/2002 40.00
    29 BM-0082-H2 4/21/2002 4/27/2002 32.00
    30 DX-6288-K4 4/21/2002 4/27/2002 40.00
    31 FF-2799-G2 4/21/2002 4/27/2002 40.00
    32 FH-1984-K2 4/21/2002 4/27/2002 36.00
    33 FO-2784-G0 4/21/2002 4/27/2002 38.70
    34 GG-6626-D3 4/21/2002 4/27/2002 32.50
    35 GG-6626-D3 4/21/2002 4/27/2002 36.00
    36 HD-3938-F4 4/28/2002 5/4/2002 38.00
    37 KD-8230-H1 4/28/2002 5/4/2002 40.00
    38 KD-9377-H6 4/28/2002 5/4/2002 38.00
    39 KS-1114-Y2 4/28/2002 5/4/2002 36.00
    40 LS-9293-L3 4/28/2002 5/4/2002 32.00
  24. Close the table
  25. In the Database window, click Tables and click TimeSheet
  26. On the main menu, click Insert -> Query and, in the New Query dialog box, double-click Design View
  27. To add another table, right-click the empty top section of the window and click Show Table...
  28. In the Show Table dialog box, click Employees
  29. Click Add and click Close
  30. In the Employees list, double-click EmployeeNumber, FullName, and Salary
  31. In the TimeSheet list, double-click WeekStartDate, WeekEndDate, and WeeklyHours:
     
    Time Sheet
  32. To see the result, on the Query Design toolbar, click the Run button
  33. Save the query as PayrollPreparation
  34. To switch it back to Design View, on the main menu, click View -> Design View

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. Here are two examples:

This would produce:

In Microsoft Access (unlike many other database environments), if the column has a caption that was set in the table, creating the alias would not have any effect in the query.

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 SocSecNbr AS EmployeeNumber, 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 SocSecNbr AS [EmployeeNumber], LastName, HourlySalary AS [Pay Rate]
FROM Employees;

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

SELECT SocSecNbr AS [EmployeeNumber], LastName, HourlySalary AS 'Pay Rate'
FROM Employees;

Practical Learning Practical Learning: Creating Alias Names of Columns in a Query

  1. To create aliases of some of the columns, in the lower section of the window, click EmployeeNumber and edit it to display Empl #: EmployeeNumber
  2. In the lower section of the window, edit FullName to display Employee Name: FullName
  3. In the lower section of the window, edit FullName to display Start Date: WeekStartDate
  4. In the lower section of the window, edit FullName to display End Date: WeekEndDate
  5. In the lower section of the window, edit FullName to display Weekly Hours: WeeklyHours
  6. Switch the query to Datasheet View to see the result:
     
  7. Switch the query back to Design View

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:

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 string, 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.

Practical Learning Practical Learning: Combining Columns in a Query

  1. To Create an expression in a query, click the top box of the first empty column in the lower section of the window
  2. Type Weekly Salary: [Salary]*[WeeklyHours] and press Enter
     
  3. To see the result, switch the query to Datasheet View
     
  4. Save and close the query

Introduction to Logical Operators

 

Negation Operator: NOT

So far, when addressing a condition, we assumed that it was true. The SQL provides an operator that, considering a given condition, would be negated. In other words, this operator finds the contrary of the stated condition. This operator is called NOT. An example would be

NOT (Gender = "Female")

Consider the query we saw earlier that allowed us to get a list of employees names and their departments. In fact, we wanted to include in our list only the employees who work at the corporate office:

Imagine that, instead of getting the list of employees who work at the corporate office, you are interested in the employees who don't work at the corporate office. To get this list, you can negate the "Corporate" condition that was stated earlier. This is done by preceding the condition with NOT. Here is an example:

This would produce:

The corresponding SQL statement is:

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

The IS Operator

To validate something as being possible, you can use the IS operator. For example, to acknowledge that something is NULL, you can use the IS NULL expression. To get a list of records where the student's email address is not specified, you can use a statement as follows:

SELECT LastName, FirstName, Gender, EmailAddress
FROM Students
WHERE EmailAddress IS NULL

In the same way, to validate that something is not null, you can use the expression IS NOT NULL. To see a list of only the students whose records have an email address, you can use a statement as follows:

SELECT LastName, FirstName, State, EmrgName, EmrgPhone
FROM Students
WHERE EmrgPhone IS NOT NULL

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"));

This would produce:

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 last names start with S. You would type the condition as LIKE "S*" (or LIKE 'S*'). To do this visually, in the Criteria field of the column, type the condition. Here is an example:

LIKE Any Character

The SQL statement is this query is:

SELECT Employees.DateHired, Employees.FirstName,
       Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.LastName) Like "S*"));

This would produce:

LIKE

You can negate this condition by preceding it with NOT. Here is an example:

NOT LIKE

The SQL statement of this query is:

SELECT Employees.DateHired, Employees.FirstName
       Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.LastName) Not Like "S*"));

This would produce:

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

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 first names that start with Paul 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 Paul and end with whatever. In this case, you would use Paul* as follows:

The corresponding SQL statement is:

SELECT 	Employees.DateHired, Employees.FirstName, 
	Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.FirstName) Like "Paul*"));

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 "*son". In this case, all strings that end with son, such as Johnson or Colson, 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 Practical Learning: Using the LIKE Operator

  1. In the Database window, click Tables and click Employees
  2. On the main menu, click Insert -> Queries
  3. In the New Query dialog box, double-click Design View
  4. In the Employees list, double-click LastName, FirstName, Title, and CanCreateNewAccount
  5. In the Criteria box corresponding to the Title column, type LIKE "*Manager*" and press Enter
  6. In the Sort field of the LastName column, select Ascending
  7. Save the query as Managers
     
  8. Switch to the Datasheet View to see the result:
     
  9. Close the query

LIKE a Range of Characters []

The * wildcard is used to make it 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 D and M, you would specify the criterion as LIKE "[D-M]*". Here is an example:

The SQL statement of this query is:

SELECT 	Employees.DateHired, Employees.FirstName,
	Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.FirstName) Like "[D-M]*"));

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 know 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:

The corresponding SQL statement is:

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

This would produce:

Notice that this produces the employees whose first name ends with aul regardless of the first character.

 

 

Previous Copyright Yevol, 2007 Next