SQL-specific Queries

SQL-specific queries are specialized queries that can only be created in SQL view. The most commonly used SQL-specific queries are union queries, but the others may occasionally be useful. Pass-through queries, which send SQL statements to ODBC (Open DataBase Connectivity) databases, are much less useful than in past years, because Automation code has replaced the older ODBC data exchange technology. Data-definition queries are used to create, delete, and alter tables. The last type of SQL-specific query is the Subquery, which is a SQL SELECT statement inside another query. In the following sections, I’ll discuss the most useful of the SQL-specific queries.

Union Queries

Union queries combine data from two or more tables (or other queries) into a single non-updatable query. In the Toy Workshop sample database, the main customer address is in tblCustomers, and the shipping addresses are in tblShippingAddresses. For the frmOrders form, I needed a row source for the cboShipAddressID combobox, including both the main address and any shipping addresses for that customer, so I created a union query to combine address data from these two tables.

To start making a union query, make a select query based on one of the tables, and create any concatenated fields you need in Design view (you won’t have this opportunity once you convert the query to a union query). In this case, I made a select query based on tblCustomers, with CustomerID, address fields, and a calculated ShipName field, as shown in Figure 4.40.

Figure 4.40

Next, switch to SQL view. The initial SQL statement of the query is:

SELECT tblCustomers.CustomerID, [ContactFirstName] & “ “ & [ContactLastName]  AS ShipName, tblCustomers.BillingAddress, tblCustomers.City, tblCustomers.StateOrProvince,  tblCustomers.PostalCode FROM tblCustomers;

Although it isn’t necessary, I like to remove the table names (and dividing period) before the field names, since they aren’t necessary, and make the SQL statements more cumbersome. The trimmed SQL statement is:

SELECT CustomerID, [ContactFirstName] & “ “ & [ContactLastName] AS ShipName,  BillingAddress, City, StateOrProvince, PostalCode FROM tblCustomers;

Important

There is no Search and Replace in query SQL view, so if you need to do significant editing in a query SQL statement, copy and paste it to a Notepad text document or a Word document, make any necessary changes there, and then copy and paste it back into the query.

The syntax for a concatenated or aliased field is in the opposite order from that used in Design view: the expression comes first, then the AS keyword, then the field name ([ContactFirstName] & “ “ & [ContactLastName] AS ShipName).

Next, create another select query based on the second table (in this case, tblShippingAddresses), including all the fields except ShipDepartment, and switch to SQL view. After trimming out the table names, the second SQL statement is as follows:

SELECT ShipAddressID, CustomerID, AddressIdentifier, ShipName, ShipAddress,  ShipCity, ShipStateOrProvince, ShipPostalCode FROM tblShippingAddresses;

To convert the query into a union query, in the first query’s SQL statement, remove the final semicolon, and type in UNION followed by a space, on a new line under the SQL statement. Switch to the second query, copy its SQL statement, return to the first query, and paste the text in after UNION. The resulting SQL statement is listed below:

SELECT CustomerID, [ContactFirstName] & “ “ & [ContactLastName] AS ShipName,  BillingAddress, City, StateOrProvince, PostalCode FROM tblCustomers UNION SELECT ShipAddressID, CustomerID, AddressIdentifier, ShipName, ShipAddress, ShipCity,  ShipStateOrProvince, ShipPostalCode FROM tblShippingAddresses;

There is still some work to do. The component queries in a union query must have the same number of columns (fields), and the matching fields must be of matching data types (however, they don’t have to have the same names). There is no ShipAddressID field in tblCustomers, so I created a dummy field with a value of 0 for this purpose. Similarly, I made a dummy field for AddressIdentifier, with a value of “Billing Address.” I also aliased BillingAddress as Address, to make a more generic field name in the union query (the field names of the union query are the ones in the first component query). The final SQL statement is as follows:

SELECT 0 AS ShipAddressID, CustomerID, “Billing Address” AS AddressIdentifier,  [ContactFirstName] & “ “ & [ContactLastName] AS ShipName, BillingAddress AS Address, City,  StateOrProvince, PostalCode FROM tblCustomers UNION SELECT ShipAddressID, CustomerID, AddressIdentifier, ShipName, ShipAddress, ShipCity,  ShipStateOrProvince, ShipPostalCode FROM tblShippingAddresses;

Now the query can be saved with the tag quni; a portion of the union query in Datasheet view is shown in Figure 4.41.

Figure 4.41

Data-definition Queries

Data-definition queries are SQL-specific queries that use Dynamic Data Language (DDL) statements to create, delete, and alter tables, or to create indexes. Most of these actions can be performed more easily using make-table, delete, and update queries, which have the significant advantage of having a Design view you can use to create or modify query structure. However, if you need to make, delete, or modify tables from SQL statements run from VBA code, data-definition queries can occasionally be useful.

CREATE TABLE

The CREATE TABLE statement creates a new table, with fields and (optionally) an index. The SQL statement in the Sub procedure listed below creates a new table called tblMerchants with several fields and a primary key, which is shown in Design view in Figure 4.42.

 Sub DDLCreateTable()    strSQL = "CREATE TABLE tblMerchants (MerchantName TEXT CONSTRAINT MNIndex"        & " PRIMARY KEY, ContactName TEXT, Phone TEXT)"    DoCmd.RunSQL strSQL End Sub 

Figure 4.42

In addition to the CREATE TABLE DDL statement, you can create tables programmatically by three other methods:

* The CopyObject method of the DoCmd object, in VBA code

* A make-table query

* The Add method of the TableDefs collection in the DAO object model, in VBA code

ALTER TABLE

The ALTER TABLE statement can rename a table, or add, delete, move, or rename a field in a table. The SQL statement in the Sub procedure listed below adds a Yes/No field to the tblMerchants table just created.

 Sub DDLAlterTable()    strSQL = "ALTER TABLE tblMerchants ADD COLUMN Special YESNO;"    DoCmd.RunSQL strSQL End Sub 

DROP

The DROP statement deletes a table or an index. The SQL statement in the Sub procedure that follows deletes tblMerchants.

 Sub DDLDropTable()    strSQL = "DROP TABLE tblMerchants;"    DoCmd.RunSQL strSQL End Sub 

In addition to the DROP DDL statement, you can delete tables programmatically by two other methods:

* The DeleteObject method of the DoCmd object, with the acTable object type argument, in VBA code

* The Delete method of the TableDefs collection in the DAO object model, in VBA code

CREATE INDEX

The CREATE INDEX DDL statement creates an index for a table, with several variants. The Sub procedure that follows first creates a table called tblMerchants, with no index, and then creates an index that allows duplicates on the ContactName field.

 Sub DDLCreateIndex()    strSQL = "CREATE TABLE tblMerchants (MerchantName TEXT,"        & " ContactName TEXT, Special YESNO, CreditLimit CURRENCY,"        & " Phone TEXT)"    DoCmd.RunSQL strSQL    strSQL = "CREATE INDEX CNIndex ON tblMerchants (ContactName);"    DoCmd.RunSQL strSQL End Sub 

If you don’t see a table created from code in the database window immediately after running the DDL code, click another object in the Objects Bar, and then click on Tables again to refresh the display.

Subqueries

Subqueries aren’t a separate query type, but just a query within another query. They may provide a more efficient structure, replacing multiple nested queries, but you can’t create a subquery in Design view, and subqueries don’t always perform better than nested queries (one query based on the results of another query). If you want to try replacing a nested query with a subquery, here are the steps in outline:

1. Open the base query (the one that will become the subquery) in the SQL window, highlight the SQL statement, and copy it to the clipboard with Ctrl-C.

2. Open the main query in the SQL window, and paste the subquery’s SQL statement into it, usually in a WHERE clause.

3. Replace references to the base query in the main query’s original SELECT statement.

4. To test the query, try to run it. If you get syntax errors, return to SQL view, and make any necessary corrections until the query runs.

As always, the devil is in the details, in this case exactly where in the main query the subquery’s SQL should be inserted. As an example, let’s consider a typical scenario where a nested query could be used—a totals query used as the data source for a select query. qminOldestPromisedOrder uses the Min function to get the oldest date in the PromisedByDate field of tblOrders. It returns a single record. However, what you really want here is the OrderID of that record. You can’t place OrderID in the totals query itself, because then you will just get the date of every order, so you have to create another query with qminOldestPromisedOrder and tblOrders linked on the PromisedByDate field to get the OrderID and any other information you might want to see. This nested query is shown in Figure 4.43.

Figure 4.43

The SQL statement for the totals query is:

SELECT Min(tblOrders.PromisedByDate) AS OldestPromisedOrder FROM tblOrders;

The nested query’s SQL statement is listed below:

SELECT tblOrders.OrderID, tblOrders.OrderDate, tblOrders.PromisedByDate FROM qminOldestPromisedOrder INNER JOIN tblOrders ON qminOldestPromisedOrder.OldestPromisedOrder  = tblOrders.PromisedByDate;

To convert these two SQL statements into a single SQL statement with a subquery, start a new query and paste the nested query’s SQL into it, then replace the original FROM clause with FROM tblOrders. Next, create a WHERE statement with the PromisedByDate field from tblOrders, the In operator, and a SELECT statement based on the original totals query’s SQL, replacing all references to the totals query with tblOrders. The finished SQL statement is:

SELECT tblOrders.OrderID, tblOrders.OrderDate, tblOrders.PromisedByDate FROM tblOrders WHERE tblOrders.PromisedByDate In (SELECT Min(tblOrders.PromisedByDate) AS OldestPromisedOrder FROM tblOrders); 

The query with subquery is shown in Design view in Figure 4.44, and in Datasheet view in Figure 4.45. Unlike other SQL-specific queries, you can open a query with a subquery in Design view, but any editing of the subquery SQL statement has to be done manually.

Figure 4.44

Figure 4.45