Hands on: Taking a structured approach

Access has a great graphical query builder, which blew the socks off the opposition when first implemented and continues to provide an extremely efficient way of writing queries. One excellent feature is that it can show you the SQL code that equates to a query you’ve built graphically (choose SQL View from the first button in the bar during query design).

SQL (Structured Query Language) is another highly useful querying tool, even more flexible than the query builder. Knowing a little SQL can greatly extend your query-building skills.

As we run through these example queries, note that if you write one as shown and save it, Access will sometimes rewrite it for you. It will run either way - it’s just that Access’ version is more verbose and bracket-strewn.

SELECT queries
Very often, queries simply reduce the volume of data that we pull back from a table. Imagine a table of 20 fields (columns) and 1,000 rows. A SELECT query can reduce the number of columns we see in the answer table rows using this syntax in SQL:
SELECT (column name 1, column
name 2)
FROM (table name);

and, as an example,

SELECT FirstName, LastName
FROM People;

This selects two columns, FirstName and LastName, from the People table. You can also include the table name in the SELECT line:

SELECT People.FirstName,
People.LastName
FROM People;

This is the more verbose syntax displayed by Access if you create this query in the builder. The answer table contains a subset of columns from the original table. To reduce the number of rows, we add a filter by means of a WHERE clause.

For example, if we want to see just the rows where the entry in FirstName is Bill:

SELECT People.FirstName,
People.LastName
FROM People
WHERE (((People.
FirstName)=”Bill”));

Access is also mighty free with its brackets, as you can see above. In fact, none are obligatory and the last line also works written thus:
WHERE People.FirstName=”Bill”;
or even:
WHERE FirstName=”Bill”;