Developing a database application -- queries

Queries are the heart of a DBA’s work. Just collecting data is worthless if you cannot analyze the information. Relational databases are better for analysis because they function faster and use less memory. Most relational databases are SQL-based, including Oracle, SQL Server and MS Access. For this reason they can interchange information easily. Microsoft Access enables you to write coded queries in MS SQL or use its Query object, which is sufficient for the majority of work you will do.

Queries enable you to filter data on just about any criteria you want, check database integrity, clean up lists and generate reports of all kinds.

There are several types of queries, including:

1. the select query – this is the one you are most familiar with; you select particular data to be displayed, from one or more tables; this can include created fields and/or calculations
2. the summation query – this will group, add, count, and several other functions to one or more linked tables
3. the append query – adds new records onto an existing table
4. make-table query – creates a new table
5. update query – updates existing information on an existing table
6. delete query – deletes records on an existing table that meet specified criteria (be careful with this one)
7. parameter query – enter a message into the criteria section of a field wrapped in square brackets. This pops up a message box prompting the user for search data

There is rarely justification for a one-table query – you can select information to be displayed in a report directly. So even to make select queries, you will want to relate two or more tables. Perhaps you have created a base table and you want the coded field to display as the verbal value. Perhaps you are creating an invoice and you want to bring in the customer’s name and address. These are the minimal queries.

As you may have noticed, I don’t tend to develop in Access according to the normal setups. Therefore I do not create relationships until they are needed; I do not assign ID numbers to every table (often don’t use a key at all) and I create queries before forms. Granted, you may want to create data-entry forms for the tables’ initial population. And if you created a data type of Lookup on a table, the form automatically gives you a drop-down list for those fields. But the beauty of forms goes further than this – it allows us to make our queries user-friendly too.

Query fields can be set up to ask for information automatically. The problem with this is that the user needs to know what information is acceptable to begin with – a daunting task once the database gets going. So I create a Parameters table of a single record. This allows the user to enter a date, date brackets, or any other field definition. I then set up a query using that field as a parameter by linking the optional field to the data table

Update queries are great when you create a new field in a table. You would need a known relationship (say, device serial number to new asset number) for the update. This is also a good way to change inventory data – decrement the number of an item that are in stock, or increment by the amount received from the supplier.

Append queries are a great way to archive information – move outdated information into an ‘archive’ table based on some flag. You would then use a deletion query to remove those same flagged records from the active data. You may also want to save selections like recipes to a ‘favorites’ table.

Make-table queries are usually one-time queries, unless you are setting up a complex amount of data for an analysis. Access will only handle queries 3-deep. So, for instance, when I need to do a quarterly report for a client, I collect all the relevant raw data into a make-table query first, then analyze, decode it and create the reports.

The summation query is very handy for counting occurrences under different criteria, and sorting it. I usually set up the fields I need first, then click the summation button (looks like a capital E, or an epsilon). You can then select what you want to do with each field – Group By means just that – it is a sort, but if you are counting occurrences, it will not repeat this field but rather increment the count each time the value is encountered. You can use the Where parameter, which allows you to set up a condition to be met. The Expression lets you write a formula to be calculated. The Count lets you count the number of instances. For instance, if you wanted to know how many cities are in each county of your state, you would group the Cities table by counties, then count the City field. This is a very versatile query format, as you will see.

To create a query, select the query section and click New. There are some automatic queries available for you and I use two of them regularly for checking data integrity – Find Duplicates (check a table for duplicate values in a particular field) and Find Unmatched (compare two tables and list those items which are not in field A of table A but are in field B of table B). The query wizard is a waste of time – it only makes simple select queries. The best bet – select Design View. This will give you a grid for creating any type of query. Select the tables you want to use (you can also select existing queries). Define the relationships between the tables if there are any, such as connecting the serial_number of one table to SN of another. Anything being displayed will have the same value in both fields. You can create multiple relationships like this but then all values must be equal. Drag the fields you want displayed down to the grid (or double-click on them). Set up any criteria you may need, such as Is Null (empty field) or <1/1/2004 (all dates before 1/1/04). Check the datasheet view to see if you are successful. Of course, you need to populate the tables with at least 10 records (I use 25) of real or test data to be sure the queries work. Manually figure what your test data should display, then compare that to the results of the query.

If you are using a primary key from one table to find a field in another table, you need to 'link' these, or as it is called by MS, create a relationship. For instance, you have an invoice table and a customer table; you would drag the CustID from the Invoice table in the upper part of the grid to the CustID field in the Customer table. Now double-click all fields you need for the query and they will appear on the first line of the grid. For instance, if you chose InvoiceID (Invoice table) and LastName (Customer table), you will display all invoices with a corresponding CustID. Note, if the Cust ID is not found in the Customer table, the invoice will not display.

Next check the Show box on any field you want to display in the query datasheet. Times when you won't want to show a field -- when the field is used in a calculation such as a total. Third line on the grid is where you put any constraints on the information, such as all invoices after a particular date, or only Last names that begin with S, or a specific sale item. Remember that this line reads as "AND", so a record will only show if it meets ALL constraints defined on this row. The next lines on the grid are "OR" constraints; for instance you might put "Mystic" on the third line Customer City field, "Norwich" on the same field on the fourth line, and "Greenville" on the fifth line. This query would show only those invoices for customers who live in either Mystic, Norwich or Greenville.

If you've never created queries before, I recommend trying it out in steps. Start with one table and display maybe 4 fields. Then set up a single criterion in one field and see how many records are shown, then add and link another table and see how may records are shown. And so on.