You can save yourself a lot of grief if you follow a few simple rules when building Access queries. See how to prevent errors, bottlenecks, and lost data.
Tables may be the foundation of a database, but queries are the gears that make everything work. Fortunately, most queries are easy to create and require just a bit of specialized knowledge. However, because queries are so versatile, it's easy to make mistakes and not even realize it until it's too late. The following tips will help you avoid performance issues, hard-to-troubleshoot errors, and accidental loss of data.
1: Don't use SELECT * datasource
Don't retrieve all the fields in the data source using the * character. If you use the SQL window, don't use SELECT *. If you use the query design grid, don't drag the * item from the field list to the design grid. Admittedly, SELECT * seems efficient because it's quick and easy to write, but don't let that fool you. The performance hit comes later when users must wait while the query retrieves all those fields across a busy network.
For the sake of performance, specify only the fields the query actually needs. Apply this rule to all of your queries. In fact, using SELECT * when you want to retrieve all of the fields isn't such a great idea, either. There are functions that don't like *. For instance, you can't change a SELECT * query to a Totals view. In other words, you can't use aggregates in a SELECT * clause. Unless you're writing a quick, temporary query, avoid using SELECT *.
2: Avoid read-only queries unless that's what you really want
Chances are that the majority of your queries allow changes to existing data. You can tell with a quick glance if a query is updateable. Simply open the query in Datasheet view. If there's a blank row at the bottom of the datasheet, it's updateable. If you're dealing with a bound form, check the navigational toolbar. If the New Record button is disabled, the form's underlying query probably is not an updateable query. (That's not an absolute, just the most likely conclusion.)
You can apply the following guidelines to avoid writing queries that aren't updateable:
- Don't use a GROUP BY clause.
- Don't use a TRANSFORM clause.
- Don't include an aggregate function in the SELECT clause or a Totals view.
- Don't use the DISTINCT predicate.
- Don't use the UNION operator.
- Don't include a subquery in the SELECT clause.
- Don't use a SQL Pass-Through query.
- Don't join three (or more) tables in a many-to-one-to-many relationship.
- Don't base a query on a read-only table.
3: Don't use a delete query to delete data
A delete query deletes entire records, not individual entries, which can be a bit confusing. SQL adds to the confusion by allowing you to specify individual fields in the DELETE clause, but it ignores the list. For instance, you might write the following DELETE statement, intending to delete just the Salary values from the Employees table:
DELETE Employees.Salary FROM Employees
Unfortunately, this query would result in an empty table. The query will warn you that you are about to delete records, but let's face it, we often click through warnings without even reading them. To delete entries, use an update query, not a delete query.
4: Don't delete your original data source when creating a new table
SQL's SELECT INTO statement (Access' s Make Table query) copies data from an existing source into a new table it creates. This routine can lead to trouble if a table with the same name as your new table already exists. Jet will delete the existing table of the same name before it creates the new one. For instance, the following statement will delete the existing Employees table before it creates the new one:
SELECT Employees.ID, Employees.LastName, Employees.FirstName INTO Employees FROM Employees
If something goes wrong or you abort the process, you'll still lose the existing Employees table. Before running a Make Table query or a SELECT INTO statement, make a copy of the existing table.
5: Know your aggregateAggregate functions evaluate a set or group of records. They group and summarize your data. You can specify aggregates via the SQL window or by using a Totals view in the query design window. Unfortunately, they don't all evaluate null values the same. If you don't allow for this behavior, your queries might not return the correct results -- returning nulls, or not, regardless of your intent. Table A lists how the SQL aggregate functions evaluate nulls.
Erroneous data resulting from incorrectly evaluated null values are hard to troubleshoot -- it's best to avoid the problem altogether. Know how the aggregate you're using evaluates null values and accommodate that behavior in your query or code.