Query: find the title, author and year of the oldest books in the collection.

The easiest way to do this is just to specify an ascending chronological sort.:

Open the books2003.mdb database; select the Queries window

Click on Create Query in Design View

The Design Query Window Will Open

Teh top part of the window will contain relationships (which are the same relationships that we had in our relationship window for the tables.)

The bottom half of the window is called the Design Query Grid. this contains option boxes, similar to those we saw when designing for tables. The fields are:

• Field - This displays the field name.
• Table - The table that the field is drawn from.
• Sort - This allows you to sort your data, by column (field.)
• Show - this is the option of whether to show or hide any column that is included. If you select hide column will still be included in the query, but it will not be visible in the Query View.
• Criteria - this is where you write the criteria in parameters to use when selecting data. You will notice that you can have multiple criteria within each column (field.)

The Query Design View interface is very similar to the Table Design View.

We will need the following tables:

• Authors
• Books

Select the Authors table; click Add

Select the Books table; click Add

Save Query as Oldest Book

From the Authors table:
drag Author to the first column in the Query Design Grid

From the Books table:
drag Title to the second column.
drag Year to the third column

Sort the Query by the Year Field:

• Click Sort in the Year Column
• Select Ascending from the dropdown box.

Click the Run Button in the Toolbar to test this query

You will see the records displayed in ascending order - including three with a date of 0! This is an example of dirty data. All books should have dates.

We need to add a criteria that won't show any dates of 0

Click the Design View button to return to Design View

In the Criteria row for Year, type >0

Click the Run Button to test this query.

We can now see the oldest books, in chronological order, (without the incorrect data.)

Close the query.

TallyByYear Query sorts the years in ascending order and then provides o count of how many records there are for each year.

To review this query, open the Design View

The TallyByYear Query uses the same field (year) in two query columns:

Year provides the publication date, in ascending order.
Year is also Grouped by Count to give a total count for each year.

To access the Totals row, click the Totals button in the toolbar.

This will bring up the Group By dropdown box.

The adjacent chart shows some of the options for grouping.

Note: You must have some way to group, before you can apply a total

Example below, using the cdstore example.
Group by Genre and apply a total

Group by Genre
Find avg price

Group by Genre
Find total sales