by a random chaosboy

Created on: January 23, 2007   Last Updated: April 19, 2007

Access has a great visual query builder, and with a little understanding of SQL you will be able to use it to great effect.

SQL, which stands for "Structured Query Language," is the language in which databases communicate. Each SQL statement is a command asking the database to perform some action, possibly returning results. The query builder in Access will assemble SQL statements for you based on what tables you drag in, what fields you select, etc.

There are relatively few SQL commands. The most important ones are SELECT, which retrieves information from a database; UPDATE, which changes information in a specified table; and DELETE, which deletes rows from a table.

Each of these commands allows specification of what you want to select, update, delete, etc. In raw SQL, this is done by indicating the names of tables and fields, and by providing filtering criteria in the WHERE clause. With the visual query builder, you will specify these details through the graphical system.

For example, when you choose "Create query in Design view" from the database window, the default is to create a SELECT query. Immediately Access will bring up a dialog asking what tables and queries to use. This initial dialog does not choose the columns in the result set. That is next, but only fields from the tables you choose with the dialog will be available to choose from. If you forget to include one in the dialog you can do it later by right-clicking in the background of the area above the grid, where the included tables are shown, and choosing "Show table..."

The grid with rows named Field, Table, Sort, etc. is where you specify the details of your query. Each column in the grid will generally correspond to a column in your result set, though you can hide columns with the "Show" checkbox. The "Field" and "Table" selections indicate where the data for that column in your results, should come from.

You can also change the query type using one of the pull-down buttons on the toolbar.

Now you can start to build simple queries. However, you will ultimately need to learn SQL to get the most out of your database. While the visual query builder is a great tool, it is also limited, especially when you start doing JOINs. There are many great SQL tutorials and references all over the internet, and as you start to work with it you likely find SQL easy to learn. But as a word of caution, there are slight variations in syntax between the different database systems. Simple SELECT, UPDATE, and DELETE statements are generally portable, but different systems have different conventions for parenthesizing JOINs of multiple tables, and data definition language statements (those used to create and alter tables, etc.) are generally system-specific. Therefore if something you saw online isn't working on your system, it could be because the syntax was specific to a different database.

Good luck, and enjoy SQL's power to make great wealths of information useful and accessible right at your fingertips.

Learn more about this author, a random chaosboy.
Click here to send this author comments or questions.