databasedev.co.uk - database solutions and downloads for microsoft access

SQL in Microsoft Access

Using SQL in Access: Changes in Data Groups

In a relational database like Microsoft Access, you often want to be able to treat your data in chunks, not as discreet entities. For instance, if you're working in a grocery store's back-end database system and want to eliminate or purge the database of unused items, it is considerably more effective to delete all items that have had an inventory value of zero for the last six months, than to delete each item in your boss's list one at a time.

You can also extract this data into another table by using the SELECT command in operations, or change a specified value.

The SELECT Command

SELECT by itself allows you to retrieve zero or more rows in any given table or multiple tables in your database. By itself, that's a pretty powerful tool; you can run a command like SELECT WHERE x >0 when you want to pull up entities that have a value included in a specific field. In the above example, where you're trying to eliminate or move to a new table the entities that have been inactive in your database, your SQL command would be:

 SELECT WHERE "inventory" = 0 

SELECT by itself does not perform any operation; it merely sorts out your database to return your desired data. And if you change data in a SELECT query, it won't change the data in the original table.

SELECT's Syntax

You can use the following syntax for various operations with the SELECT command:

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1]
    [, [table.]field2 [AS alias2] [, ...]]}
FROM tableexpression [, ...] [IN externaldatabase]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]

In this SQL syntax,

  • Predicate defaults to ALL, but other options are DISTINCT, DISTINCTROW, or TOP. The predicate restriction limits the number of records returned.
  • * specifies all fields from table.
  • Table is replaced by the name of the table you're using.
  • Fieldn is replaced by specific fields you're using.
  • Alias allows you to replace the column headers of your fields with alternate names for your returned date.
  • Tableexpression is the name of table containing data you want to retrieve, and
  • Externaldatabase is the name of the external database containing this table.

Not all these syntax pieces are used every time you use the SQL SELECT command; in fact, you'll generally leave out most of them.

Minimal syntax for this command is:

SELECT fields FROM table

"fields" can be replaced with "*" if you want to select all fields. For instance,

SELECT * FROM Donors

selects all fields from the Donors table in your database.

If you're retrieving a field with a common name in your database, like Title or Date, use syntax:

Table.field (for instance, donors.title)

Select multiple fields by putting commas between each field. Select fields in the order you want them to appear in your new Select table.

An Example

You need to compile a recordset from your large auto parts inventory database of parts that are currently used in GM cars, and that you have used at least once in the shop. You never put a part into your "current" table that you haven't used. And you have a large table compiled from your suppliers that contains a complete list of all auto parts available to you right now, including a field for GM parts.

Your query would say:

SELECT * FROM inventory INNER JOIN current
WHERE current.GM <>0 AND inventory.part = current.part

INNER JOIN is a way of specifying that wherever records are common between two database tables, they should be joined and not duplicated.