The Alias Name of a Table or Query
The alias is another name for the object used as the source of a query. You can create an alias when designing a query or in the SQL code. To visually create an alias, click anything in the top list of items. In the Property Sheet, change the value of the Alias field:
To create an alias in the SQL code, on the right side of the name of the table or query in the FROM clause, add a space and type a name of your choice. As an alternative, you can put the AS keyword before the alias. The alias can be in more than one word. If that's what you want to use, you must include it between [ and ].
After creating the alias, you can use it to qualify the name of a field in the SQL statement. You use the alias as if it were the name of the table or query applied to the field. Here is an example:
SELECT houses.PropertyNumber, houses.City, houses.PropertyType, houses.Condition, houses.Bedrooms, houses.Bathrooms, houses.FinishedBasement, houses.IndoorGarage, houses.Stories, houses.YearBuilt, houses.MarketValue FROM Properties AS houses;
You can use the alias to qualify some or all fields. You can also use the alias to qualify the * field. Here is an example:
SELECT kids.* FROM Students kids;
Practical Learning: Creating an Alias for a Query
The Caption of a Field
If you create a query and display its result, by default, each column displays its caption as the name of the column. If you want, you can display a different caption for any column of your choice. To do this, display the query in Design View. Add or select a column. Right-click the column and click Properties. In the Property Sheet, click Caption and type the desired caption. Of course, the caption ccan be made of many words.
In SQL, as seen for queries, the alias of a field can be created using the AS keyword as in the following formula:
column-name AS Alias
The column-name factor is the name of the column in the table (or query). The AS keyword is required. The Alias factor is the caption you want the column to display. Here are examples from the above query:
SELECT PropertyNumber AS [Prop #], PropertyType, Bedrooms AS Beds, Bathrooms AS Baths, [Year Built], [Market Value] FROM Properties;
Practical Learning: Specifyng the Caption of a Field of a Query
The Alias Name of a Field
To visually create an alias for a field, in the Design View of a query, on the left side of the column name, type the caption of your choice, followed by :, and followed by the name of the column. Here is an example:
Primary Operations on Fields Selections
Concatenating Some Strings
When creating a query, you can combine two or more columns to display them together as one. To concatenate two column names, you can use the + (or & (the ampersand)) operator. To concatenate more than two strings, enter their names separated by + (or &) operators.
Practical Learning: Concatenating Some Strings
An Expression for Data Selection
An arithmetic operator can be used to create an expression for the value of a field of a query.
Practical Learning: Using an Expression for Data Selection
Calling a Function for Data Selection
You can call a function to specify the values of a field of a query.
Practical Learning: Calling a Function for Data Selection
This expression says, "If the the value of the Gender field is Male, display M, otherwise display F". If this expression is entered in the Field box of a query, when the query is run, a column named Sex would display and its values would result from the expression.
SELECTing Fields From Different Tables
If you have more than one table in your database, you can use a statement that selects any field(s) you want from those tables. Neither the tables nor the columns need to have anything in common. The formula to follow is:
SELECT what-field(s) FROM Table_1, Table_2, . . . Table_n
You start with the SELECT keyword followed by the list of fields from the tables. If the tables have columns with different names, you can simply list the name of each column. Here is an example:
SELECT AccountNumber, EmergencyName, EmployeeNumber, Title, HourlySalary FROM Customers, Employees;
When you select fields from different tables, in the result, each of the records of the first table would display, each showing the first record (combination of the selected columns) of the second table. Then each of the records of the first table would show again, followed by the second record (combination of the selected columns) of the second table. This will continue until all records of the second table have displayed. Consequently, the resulting query would contain (Number of Records of First Table) * (Number of Records of Second Table). For example, if the first table contains 4 records and the second table contains 2 records, the statement would produce 4 x 2 = 8 records. Therefore, the above statement would produce:
If the tables have fields with the same name, you must qualify at least the column(s) with the same name. This can be done as follows:
SELECT AccountNumber, Customers.FirstName, Customers.LastName, EmergencyName, EmployeeNumber, Employees.FirstName, Employees.LastName, Title, HourlySalary FROM Customers, Employees;
If you don't qualify the common names, you would receive an error when you execute the query.
Managing the Fields in a Query Window
Selecting a Column
Some operations require that you select a column from the bottom section of the query window:
Since selecting a column in the Query window is a visual operation, there is no equivalent in SQL.
Removing a Column From a Query
If you don't need a column anymore on a query, you can either delete it or replace it with another column:
To remove a column from a SQL statement, simply delete it. An example would be:
SELECT EmployeeName, DateHired, Title FROM Employees;
SELECT EmployeeName, Title FROM Employees;
Replacing a Column
To replace a column, click the arrow on the combo box that displays its name and select a different field from the list:
To replace a column from a SQL statement, simply change its name to the name of another existing column of the same table or query. An example would be:
SELECT EmployeeName, DateHired, Title, Salary FROM Employees;
SELECT EmployeeName, DateHired, EmailAddress, Salary FROM Employees;
Moving a Column
Columns on a query are positioned incrementally as they are added to it. If you don't like the arrangement, you can move them and apply any sequence of your choice. Before moving a column or a group of columns, you must first select it. Then:
Since moving a column in the query window is a visual operation, there is no equivalent in SQL. Otherwise, in the SQL statement, you can either edit the statement or delete the field in one section to put it in another section. An example would be:
SELECT EmployeeName, DateHired, EmployeeNumber, Salary FROM Employees;
Practical Learning: Moving a Column
A Query as a Datasheet Object
A query uses the same approach of a table to present its data: it is made of columns and rows whose intersections are cells. Although the main purpose of a query is to either prepare data for analysis or to isolate some fields to make them available to other database objects, as done on a table, data can be entered in a query.
Data entry on a query is done the same as on a table: data is entered into cells. The Enter, Tab and arrow keys are used with the same functionality as the table. Like a table, a query provides the navigation buttons on its lower section, allowing you to move to the first, the previous, the next, the l ast or any record in the range of those available.
Practical Learning: Performing Data Entry on a Query
Like tables, queries provide you with a fast means of printing data. Once again, this should be done when you need a printed sheet but not a professionally printed document. Data printing on a query is done with the exact same approaches and techniques as for a table.
Practical Learning: Printing a Query
Query Aesthetic Formatting
The Datasheet View of a query appears exactly like that of a table. It is aesthetically formatted using the characteristics we reviewed in Lesson 5.
Practical Learning: Aesthetically Formatting a Query