ICAB4136B Use structured query language to create database structures

Create and use advanced database queries

Overview

The management of information is an increasingly important feature of workplaces today. As an IT professional working in this environment, you'll need to know how to provide the most flexible, efficient solutions to a variety of information management tasks. Advanced database queries allow you to prepare and present data in a variety of useful forms. In this topic you'll learn how manipulate data using advanced database queries.

Case study

The database of BookshopA2k.mdb was developed for a small business, the Busy Bee Bookshop, to record information about its products, sales, customers, and staff. It has been operation at the store for several months. During this time the owner of the shop, Mandy Simons, has come to realise that the database does not meet all the data management needs of the store. The main problem areas Mandy has identified include:

• The integration of data provided in non-database files by the store's book suppliers
• The transfer of information between the database and other software packages used in the store
• The need to make data that's stored outside the database available for particular database functions
• The retrieval of detailed sales, customer and book information for reports, mail-outs and catalogues.

Bookshop database layout

The Busy Bee Bookshop database consists of 6 tables:

The database also contains a number of queries and reports that are used by staff at the shop to produce sales, customer, and stock reports.

Creating advanced queries

In this case, queries are mostly used to display the static results of relatively simple questions like:

• How many books were sold in January 2002?
• Which customers living in the postcode 3032 have bought a book on gardening in the past six months?

however, queries can do a lot more than this. They can perform actions that include calculations and the modification, addition or deletion of records from a table. You can even make an entirely new table from a query or display query results in a spreadsheet-like column and row format. In this section we'll look at a range of used query types and how they can be used.

Appending data sets

The append query, one of several action queries available in Access, allows you to select a set of records from a table (or from the output of a query) and add them to an existing table.
In the following example we'll be appending records to an empty table but keep in mind that records can be added to a table that already contains data. You might want to work through this exercise as practice. If you do, you'll be creating a backup copy of a table.

Creating an Append query

1. The first step is to open the relevant database. In our demonstration here it's the bookshop database.
2. Create a copy of the tblBooks table, choosing the Structure Only option. Be sure to choose the option to copy the table structure only, not the data, and name the new table OriginaltblBooks. (If you are not sure how to copy a table, consult the Access online Help.) Note that the tblBooks table has 100 entries.
3. Create a new query based on the tblBooks table, adding all fields to the query design grid as shown below.

4. Select Query / Append Query from the application menu bar.

5. The Append dialogue box will appear. Select the table Original tblBooks table from the dropdown list and click the OK button.

6. The query design grid has changed showing the originating field names in the Field row and the receiving field names in the Append To row. Because we are making an exact copy of the table (the structure of the tables is identical) you'll see that Access has supplied the correct field names in both rows.

7. If Access can't find a matching field name in both tables it will leave the Append To area blank. You can then select the field by clicking on the relevant field area in the Append To row and selecting a field name from the drop down list a shown below.

8. Click on the Run Query button to append the data from one table to the other. You will see a message box warning that you are about to append the data. Take note of the warning message!

9. Save the query as qryAppendOriginal. You'll notice that the Append query symbol (shown below) precedes the query name in the database window, indicating that it is an Append query.

Deleting data sets

A delete query is called an action query because it performs an action (delete) on the selected records. Note that the delete query acts upon records - you place fields onto the query design grid to specify the criteria for record deletion, but you do not actually delete the fields.

You need to be very careful when deleting records in a relational database! Records in one table may be linked to many other tables, so it's important that you understand how the relationships have been set up between tables before making any irreversible changes. You also need to know if the Cascade Delete Related Records setting has been selected for the relationships. If this setting has been activated any related records will be deleted at the same time. To check if the Cascade Delete Related Records option has been set:

• open the relationships window (select Tools / Relationships from the menu bar)
• right-click on each relationship link to display the Edit Relationships window.
  The example below shows how you can delete all records from a table. You might like to work through this example as practice.

Creating a Delete Query

1. The first step is to open the relevant database.
2. Select the Queries tab from the objects bar in the database window.
3. Choose Create Query in Design View from the database window, and add the table. In this example we're working with the OriginaltblBooks table.
4. Select Query / Delete Query from the menu bar.

5. If you look closely at the query design grid you'll see it now displays a Delete row

6. You can delete ALL records by adding the entire table to the first field cell in the design grid. To do this, double-click the asterisk at the top of the OriginaltblBooks table. All fields in the table will added as shown below.

7. Alternatively, you can delete selected records by setting criteria for a particular field. In the screen shot below a single field was used but you can set criteria in multiple fields.

8. Click on the Run Query button. You will see a message box similar to that shown below, warning you are about to delete the data. Take note of the warning message!

9. Save the query. You'll notice that the icon shown below precedes the query name in the database window to indicate it's a delete query.