Home

Details on Creating Queries

 

Details on Column Selection

 

Hiding a Column

Consider that you have the following table in your database:

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

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. Based on this, 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. Fortunately, the Select Query window provides a means of doing this visually. To do it, 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 16
  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, AccountName, EmailAddress
  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
  12. To create a new table in the Yugo National Bank1 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: PayrollID
    Data Type: AutoNumber
    Caption: Payroll 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:
     
  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
 
 

Previous Copyright © 2005-2009 FunctionX, Inc. Next