Before going into the details of SQL syntax, this page will discuss the preliminaries of composing queries in Access.
Access implements a very powerful technology called Visual Query By Example (VQBE). This lets you compose a query visually by pointing and clicking for the most part. Underneath the hood, your actions are composing SQL. A query has three views:
Even if you’re a hardened pro who can write SQL in your sleep, you will still find yourself doing 90% of your work in Design View, switching to SQL view (if at all) only if you need to fine-tune the query. Once you get familiar with SQL, you will realize it is not a case of mastering the syntax (which is quite limited and easy to pick up), but of getting the table and column names correct. Clicking on columns to select them is much faster as well as less error-prone than typing them in.
10% of the time, you will need to work with SQL directly: you will almost never compose a query from scratch, but you may find it faster to tweak the text of a query than to redesign it visually. Access works in a two-way fashion: changing the SQL will change the visual representation of the query, and vice versa.
This means, for example, that you can copy the SQL into a word-processor, edit it and paste it back, then switch to design view and see your changes. You may need to use a word processor because Access's built-in SQL View text editor is a pathetic joke, unchanged since the last decade: even simple tasks like temporarily changing the font size for ease of viewing, or find and replace, are not possible.
The purpose of composing a SELECT query is to get data of interest out of the database. The simplest possible query fetches all columns from a table. e.g.,
SELECT * from Patients
You add tables to your query in one of two ways:
Obviously, you must first know whether the information that you want exists in the database, and if so, in which tables it lies.
For a database consisting of a few tables this is not much of a problem. For one that contains dozens of tables, it can be. Reading documentation of the system (if any exists) helps. For a database that you have built yourself, it is generally desirable to provide your own electronic documentation. For starters, this documentation can be a word-processor file that contains a Relationships diagram and descriptions of the tables, and the columns in each table, in terms of the purpose they serve. (We will discuss this later when we consider metadata.) In addition, screen shots of parts of your database in action help a great deal.
An important roadmap is the Relationships diagram. Access lets you create only a single diagram; some systems, such as Microsoft SQL: Server, let you create multiple diagrams: a given table may be part of one or more diagrams. In circumstances where different tables deal with different functional areas of the databases, multiple diagrams are useful for minimizing sensory overload. (If you are using Access, you can use tools such as Microsoft Visio to create as many diagrams as you need.)
In a normalized database design, it is highly unlikely that all the data that you need (to export to a statistical program or a spreadsheet, for example) is likely to be found in a single table. Therefore you will often need to pick multiple tables. One nice thing about setting relationships in advance is that Access will automatically link tables as you pick them.
You select a column by either double clicking on it, or by clicking on it and dragging to the columns list. The check box in the “Show” row is checked by default: clearing it will hide that column. (There are times when you want to use a column for sorting or filtering purposes, but you do not necessarily want to show it.)
When you pick one or more columns and peek at the SQL view, you will see that every column you have chosen has the table-name prefix in front of it, separated by a period. For example,
SELECT Patient.First_Name, Patient.Last_Name, Patient.Date_of_Birth from Patients
The prefix disambiguates in case there are multiple columns in different tables with the same name in the query: this common occurs where a foreign key column is given the same name as a primary key column.
If you are using a single table in the query, the table-name prefix is unnecessary, and deleting it along with the period would not hurt. Also, if you pick a column whose name is unique among the tables you have chosen for the query, it does not need to be disambiguated, because the database engine can always determine which table it lies in. Access always plays it safe, however, at the cost of some verbosity.
You can give both tables as well as columns aliases when you use them in a query. An alias is an alternative name for the same thing: its scope is temporary and limited to that query alone.
If you peek at the query's SQL in SQL View, you will find that it contains
phrases such as
tablename AS alias
Or
Columnname AS alias
Avoid using any punctuation (other than spaces) in aliases. As far as possible, confine yourself to the characters A-Za-z, 0-9 and the underscore (_). Sometimes, to make column aliases more end-user-understandable, you can use spaces in these aliases: Access will automatically surround each alias in square braces, e.g., [Country of Origin].
Remember that it is very rarely (especially when you are just starting to learn Access) that you will get a query right the first time. For queries that only fetch data (SELECT statements), remember that Access will not damage your database. The following strategies help.
If you create a SELECT query, test it to verify that it works, and save it with a meaningful name, you can later pick and use it inside another query exactly as you would use a table. (The Show Table... dialog lets you select other tables OR other queries.) In the Design View, a query that you have added looks exactly like a table.
This capability is not limited to Access. In high-end databases, you can define "views", which are basically SELECT queries that are stored in the database. One use of views is information-hiding. For example, a privileged user may be able to see all the columns in a Personnel table, while most users will access this table only through a view that hides confidential columns such as salary.
Using one or more queries inside another is very useful when you are building a highly complex query- you perform the task using a "divide and conquer" strategy, building simpler queries, getting them to work, and then combining them into a final query.
In Access, the blurring of the distinction between queries and tables is useful because you can build a Form or a Report on a query instead of on a table. (We will address this topic later when we consider the building of user interfaces.)