USING SELECT QUERIES: POWER IN THE QUERY DESIGN GRID

Access developers must learn the query design grid and learn it well. After you learn it, it becomes a valuable tool with which you can build applications quickly and get a jump on the development process.

The query design grid in Query Design view is the fastest way to get a query going, and to see visually that you're selecting and sorting on the right fields. However, Query Design view also has the SQL view window for accessing the SQL statement directly, as well as the datasheet view for testing.

For Select queries, you can update data right in the grid without programming.

The folks who say, “Real programmers don't use the query design grid. They use SQL,” have way too much time on their hands. The query design grid is the fastest and most intuitive way available to build queries against complicated data structures. In less than a minute, you can build a complicated query with many tables and many output fields. A few more seconds is time enough to use the File menu's Send command, and off the results go to a customer or manager. OK, so File, Send isn't quite query by example, but it does provide a way to use the information created with the query design grid.

Using the query design grid to generate SQL statements deters misspelling table and field names. It also keeps you from having to deal with entering SQL keywords or parentheses. VBA provides no syntax checking on SQL statements, so the query design grid is a great environment to develop SQL statements in before moving the statements to VBA.

To get the most out of the query design grid, you should use it for doing ad hoc mass cleanup of data, viewing and changing individual records, and generating VBA code. Most queries are easy to build in the query design grid. What's more, you can move them to VBA by using the View menu's SQL command, and then copying the SQL SELECT statement displayed in the window. Figure 8.7 shows a query using the query design grid; Figure 8.8 shows the same query in SQL view.

To create the code in VBA, use the zsfrmSQLVBA form, which is a code generator. Copy the SQL statement from the SQL view window to the code generator form. Click Generate, and you have code that can be pasted into VBA (see Figure 8.9). This form is available in Chap08.mdb, located in the ExamplesChap08 folder on the CD-ROM.

The query code generator allows you to build SQL statements that can be pasted right into your code. Now you can replace the criteria in the WHERE clauses with more complicated values or values derived from formulas. With the generator, you can take advantage of the query design grid for most of your work and then code the rest in VBA.

Joining Tables

Defining the relationships between tables in the query design grid makes the process of building the SQL much faster than coding it from scratch. Left and right joins allow you to select all records from one table and only the records from another table where the joined fields match. An inner join includes only records that exist in both tables used in the join.

Access online help provides specific information on the capabilities of the different join operations, but a few caveats are worth noting:

• When you create inner joins in which you have multiple records in one table and a single matching record in another, the single record will be repeated for each record in the multiple side. For example, joining Customers to Invoices repeats the customer record for each invoice.

• When you can't fill in the join field on one side of the relationship, consider using a left or right join to make sure that the record is still included in the resultset, even though the joined field is null. An example of an outer join would be if you wanted to see all customers who have no invoices. You could do so by setting the criteria of a invoice number equal to Null. Because the customer record is displayed regardless, the criteria would limit the recordset to only those customers who have no invoices.

• When joining large numbers of records, consider performance issues and refer to the sections on performance later in this chapter, starting with “Understanding Optimization Techniques.”

Using the Same Table Twice (Self Joins)

Developers run into many situations that require including the same table in a query multiple times. Figure 8.10 shows a scenario in which the phone number table is used for companies and employees. To get company and employee phone numbers, the phone table needs to be joined into the query twice.

To obtain a list of all companies with employees, you need to proceed as follows by using the Chap08.mdb database (in the ExamplesChap08 folder on the accompanying CD-ROM):

SELECT tblEmployee.*, tblCompany.*, tblPhone_Company.*,
tblPhone_Employee.* FROM ((tblCompany INNER JOIN tblPhone
as tblPhone_Company ON tblCompany.PhoneID =
tblPhone_Company.PhoneID) INNER JOIN tblEmployee
ON tblCompany.CompanyID = tblEmployee.CompanyID)
INNER JOIN tblPhone AS tblPhone_Employee
ON tblEmployee.PhoneID = tblPhone_Employee.PhoneID;

Running the query produces a list with all the companies that have phone numbers and also have employees with phone numbers. You can modify the query to remove all the unused fields, or you can save it as is to be used as a basis for a report.

Using the Access AutoLookup Feature

Access links records in the datasheet view of a query by keeping track of pointers to the records in each table. As information is changed in the link field on the many side of a one-to-many relationship, the fields from the many side are updated to reflect the new link. This feature is known as AutoLookup. The Select query in Figure 8.12 shows how the feature works.

When the query is opened in Datasheet view, you can enter the invoice information into the table. Figure 8.13 shows the datasheet after 18 is entered in the CustomerID field and before Tab is pressed to move to the next field. After Tab is pressed, Figure 8.14 shows how the last and first name information is automatically located and displayed.