Details on Creating Queries
|
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:
|
|
|
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; |
|
|
|
|
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. |
|
|
|
|
|
||
| Previous | Copyright © 2005-2016, FunctionX, Inc. | Next |
|
|
||
Practical Learning: Hiding a Column in a Query
