Databases - MS Access Queries

There are basically 2 ways to write queries

Use the Query Builder GUI
Write SQL (View / SQL View)

I frequently use both to generate a single query. Anything changed in one view is automatically changed in the other. (Errors made in the SQL view can be a problem - be careful.) When executed via code, you always use an SQL statement. However, you don't need start from scratch - let MS Access help you write the SQL.

First create the basic query in the GUI
Switch to the SQL view
Copy the query to the clipboard
Paste the SQL into your code
In the SQL, replace all the double quotes with single quotes (" -> ')
Edit the query string to add variables

Placing abcd in the Criteria of SomeField is equivalent to

WHERE SomeField="abcd"

in SQL.

In general, basic SQL syntax is discussed elsewhere. This page covers some quirks which are applicable to MS Access.

Types of Queries

When you create a new query, the default is a select query (appropriate values are returned from the database). The Query Type button (or the Query menu selection) allows you to select one of 6 different types.

Aggregate Functions

In order to perform aggregate functions (min, max, average, ...), on the toolbar, you must press in the Sigma (Totals) button (from the menu, View / Totals).

In order to specify a where clause, set Total equal to Where (scroll the pick list) and make sure that the Show check box is cleared.

When the Total is set to Expression, then aggregate functions can be used in the Field definition.

Expr1: (Max([Odometer])-4388)/Sum([Gallons])

Dates

In order to find dates in a query, you need to use pound signs.

#07/01/2001#
>#07/01/2001#
Between #07/01/2001# And #07/01/2002#

CheckBoxes

When testing a checkbox (Yes/No field), the correct values are 0/-1 or True/False or Yes/No (your choice).

WHERE ((([CheckBox Test].CheckBox)=False))
WHERE ((([CheckBox Test].CheckBox)=Yes))
WHERE ((([CheckBox Test].CheckBox)=0))

Renaming a Field

It is possible to rename a field (or computed value) in the query's result set. Notice that this does not affect the name of the field in the table itself.

In the GUI, simply place the new name and a colon in front of the fieldname.

NewName: FieldName

(I have seen queries where some fields can be renamed and other can not - in the same query. I have no idea why. Perhaps it only affects date fields.)

In the SQL view, use the AS operator

[TableName].[FieldName] AS NewName

In general, AS and as are equivalent. In "real" SQL, AS is optional - however, in MS Access 97 it is required.

All computed values are named using this method. To change the default name (Expr1), simply type in a new one.

Expr1: FieldName * 2
[FieldName]*2 AS Expr1

Author: Robert Clemenzi - clemenzi@cpcug.org