Editing and Creating Queries in SQL View

There are three types of queries that you must create in SQL view: data definition queries, pass-through queries, and union queries.

In a desktop application, Access supports a limited subset of the ANSI-Standard SQL language for data definition. You can execute basic CREATE TABLE and ALTER TABLE commands in a data definition query, but you cannot define any Access-specific properties such as the Input Mask or Validation Rule property. The syntax is so limited that we don’t cover it in Article 2, “Understanding SQL,” on the companion CD.

When you’re using linked tables to a server database system such as Microsoft SQL Server or Oracle, you might need to execute a query on the server that takes advantage of the native SQL supported by the server. You can do most of your work in queries you build in the Access query designer, but the SQL that the query designer builds is only a subset of what your server probably supports. When you need to send a command to your database server in its native syntax, use a pass-through query. If all your data is stored in SQL Server, you should consider building an Access project that links directly to the server data. You can learn about projects in Part 7, “Designing an Access Project.”

So, that leaves us with union queries that you might want to build in SQL view. When you create a query that fetches data from multiple tables, you see the related fields side by side in the resulting datasheet. Sometimes it’s useful to fetch similar data from several tables and literally stack the rows from different tables on top of one another. Think of pulling the pages containing your favorite recipes out of two different cookbooks and piling them on top of one another to make a new book. For that, you need a union query, and you must build it in SQL view.

Let’s say you need to build a mailing list in the Conrad Systems Contacts application. You want to send a brochure to each primary contact for a company at the company’s main mailing address. You also want to include any other contacts who are not the primary contact for a company, but send the mailing to their home address. Sounds easy enough to pull the primary contact mailing address from tblCompanies and the address for everyone else from tblContacts. But how do you get all this information in one set of data to make it easy to print your mailing labels in one pass?

SQL-The Basic Clauses

One way to begin learning SQL (and we strongly recommend that you do) is to take a look at any query you’ve built in the query designer in SQL view. You can see the SQL view of any query you’ve built by opening it in Design view and then clicking the small arrow below the View button in the Views group on the Home tab and selecting SQL View. You also find the View button in the Results group on the Design contextual tab below Query Tools. You can learn all the details in Article 2, “Understanding SQL,” but a quick overview of the main clauses will help you begin to understand what you see in SQL view.

Open table as spreadsheet

SQL Clause	Usage
SELECT	This clause lists the fields and expressions that your query returns. It is equivalent to the Field row in the query designer. In a totals query, aggregate functions within the SELECT clause, such as Min or Sum, come from specifications in the Total line.
FROM	This clause specifies the tables or queries from which your query fetches data and includes JOIN clauses to define how to link your tables or queries. It is equivalent to the graphical display of table or query field lists and join lines in the top of the query designer.
WHERE	This clause specifies how to filter the rows returned by
	evaluating the FROM clause. It is equivalent to the Criteria and Or lines in the query designer.
GROUP BY	This clause lists the grouping field for a totals query. Access builds this clause from the fields indicated with Group By in the Total line of the query designer.
HAVING	This clause specifies filtering in a totals query on calculated values. This clause comes from the Criteria and Or lines under fields with one of the aggregate functions specified in the Total line.

First, build a query to get the information you need for each company’s primary contact. In the ContactsDataCopy.accdb database, start a new query with tblCompanies. Add tblCompanyContacts and tblContacts and remove the extra join line between the ContactID field in tblContacts and the ReferredBy field in tblCompanies. In the first column on the Field line, enter:

 EmpName: (tblContacts.Title + " ") & tblContacts.FirstName & " " & (tblContacts.MiddleInit + ". ") & tblContacts.LastName & (" " + tblContacts.Suffix)

Add the CompanyName and Address fields from tblCompanies. In the fourth column on the Field line, enter:

 CSZ: tblCompanies.City & ", " & tblCompanies.StateOrProvince & " " & tblCompanies.PostalCode

Add the DefaultForCompany field from tblCompanyContacts, clear its Show check box, and enter True on the Criteria line. If you switch to Datasheet view, your result should look like Figure 8–50.

Figure 8–50: You can switch to Datasheet view to verify that you have correctly built the first part of a union query to display names and addresses.

OK, that’s the first part. You do not have to save this query-leave it open in Design view. Start another query with tblContacts and add tblCompanyContacts. Create an EmpName field exactly as you did in the first query. In the second column, enter:

 CompanyName: ""

Say what? Well, one of the requirements to build a union query is that the two recordsets must both have the exact same number of columns and the exact same data types in the relative columns. A mailing label sent to a home address doesn’t have a company name, but you need this field to line up with the ones you created in the first query. In Chapter 15, “Constructing a Report,” you’ll see how the Mailing Label Wizard eliminates the blank row that would otherwise be created by including this field.

Add the HomeAddress field from tblContacts in the third column and create this expression in the fourth column on the Field line:

 CSZ: tblContacts.HomeCity & ", " & tblContacts.HomeStateOrProvince & " " & tblContacts.HomePostalCode

Finally, include the DefaultForCompany field from tblCompanyContacts, and clear the Show check box but this time set a criterion of False. The Datasheet view of this query should look like Figure 8–51.

Figure 8–51: The second part of a union query to display names and addresses displays the home addresses for persons who are not the primary contact for each company.

Again, you don’t have to save this query. Now, you’re ready to assemble your union query. Click the Query Design button in the Other group on the Create tab. You’ll see a blank third query window with the Show Table dialog box open in front. Click the Close button to dismiss the dialog box. When Access sees that you haven’t chosen any tables or queries, it makes SQL the default option on the View button at the left end of the Ribbon. (This means that if you simply click above the View button, which now displays SQL, you open the SQL view for the query.) Click the SQL button in the Views group on the Home tab or the Results group of the Design contextual tab to switch to SQL view for this empty query. You should see a blank window with SELECT; displayed in the upper-left corner.

Go back to your first query, click the small arrow below the View button on the Home tab, and then click SQL View (or you can follow the same steps using the Results group on the Design contextual tab below Query Tools). You should see a window like Figure 8–52.

Figure 8–52: You’re going to copy the first part of your union query from the first query’s SQL view.

Inside Out-Changing Font Size for the Query Window 

By default Access sets the font size for text in the Query window to 8. In order to make Figure 8–52 and Figure 8–53 more readable in the printed book, we temporarily changed our font size to 12 for the Query window. Click the Microsoft Office Button, click Access Options, click the Object Designers category, and then select a font size in the Query Design Font Size text box in the Query Design section. Click OK to save this change.

Select all the text you see in this window and copy it to the Clipboard. Switch to the third empty query, and replace SELECT; with the text you copied. Remove the ending semicolon, place the insertion point at the end of the text, press Enter, type the word UNION, and press Enter again.

Go to your second query, switch to SQL view, select all the text, and copy it to the Clipboard. Go back to the third query and paste this text at the end. Your new union query should look like Figure 8–53.

Figure 8–53: You can assemble a union query by copying and pasting the SQL from two other queries.

Switch to Datasheet view to see the final result as shown in Figure 8–54.

You should save this query, but you can close and not save the first two queries that you used to build this. You can find this query saved as qxmplAddressesUnion in the sample database. If you want to learn more about SQL, see Article 2, “Understanding SQL,” on the companion CD.

Figure 8–54: The union query displays the company address for all primary contacts and the home address for all other contacts.