Creating the textbook database:

I went into access and selected to create a new database - when it asked for file, I entered textbook - this becomes the name of the database. Within the database I can then select tables. I then clicked on the Tables tab and the button New. From the pop up window shown below, I selected Design View.

A form comes up that allows me to layout a new table - first I will do the table for the textbooks. I am calling it book.


I can now add text to this design. I do it by selecting open when the table tab is selected. An empty layout comes up and I can key in data. If data is already in the table, the table with data would come up and I can add to the table at the point shown by the *.

Queries of the database:

To query this database, go the query tab and select design view. A window will appear asking you to select what table to use in the query. Highlight the table and press add. When you have added the table for the query use, click close on the popup window. In the query shown, I want to show all records where the yrpub is greater than 1998. When I show the records I want to see the ISBN, the title and the yrpub. Notice the information that is shown: the field, the table that it is from and the criteria which in the case is > 1998.

When you do a query in Access, you can also view the query as a SQL statement. The SQL that Access would generate for this query is:

SELECT book.ISBN, book.title, book.yrpub
FROM book
WHERE (((book.yrpub)>"1998"));

Notice that the SQL includes the table name followed by the field name in the SELECT and in the WHERE condition. If you actually wrote the SQL yourself, you would not have to include this since only one table is being used. If multiple tables are used you would only have to include the table name in the case where the field name appeared in more than one table. The field yrpub is a text field so it is compared against the 1998 in quotes.

For more information on SQL, go to my notes at my site and also link to the SQL tutorials that I have noted at my site.

The results of the query are:

The next example shows a simple AND. I want all records where yrpub > 1998 and price is greater than 40. Notice that AND criteria go on the same line.

The SQL for this query is:

SELECT book.ISBN, book.title, book.yrpub, book.price
FROM book
WHERE (((book.yrpub)>"1998") AND ((book.price)>40));

The next example shows a simple OR - setup and SQL. I want records where yrbub > 1999 or price > 40. Notice that the criteria are on different lines for the OR.

SELECT book.ISBN, book.title, book.yrpub, book.price
FROM book
WHERE (((book.yrpub)>"1999")) OR (((book.price)>40));

Creating a form and report using the Form Wizard.

Click on the Form tab and select New. When the window comes up, select the Form Wizard and the table or query that you want to use. In other words, you can use the whole table or you can use only the material you selected with a query.

Now you will be asked to pick fields as shown below. Originally all of the fields will be shown on the left - after your selection, the ones selected will be shown on the right.

Now click on the Next button. At the next screen, you will be asked to the format to use. I used the default of Columnar. Then press next and choose the style/background for your form. I used the default of clouds. After pressing next again, you will be allowed to choose a title for your form. I chose Text Book Form. You are also asked on this screen if you want to modify the form or open the form to view or enter information. I kept the default which is open the form. The form with the first record is shown below.

Now, I want to produce a report using the Report Wizard. I go through the initial steps of choosing the Report Tab and then selecting Report Wizard and book as the table. When I am asked to select field, I again select ISBN, title, yrpub and price. The next screen will ask about grouping. For this report I am going to group by yrpub. This is basically creating a minor break based on yrpub. Originally the screen shown below simply listed the fields on the right side, because highlighted yrpub and used the > to bring it over, the right side now shows grouping.

The next window asks about sorting, I decided to sort on title which means the report will be sorted on title within the grouping of yrpub.

The next screen lets you decide on layout of the report - I kept the default which is stepped. On the next screen, you get to pick style and again I went with the corporate default. Finally, you enter a title - mine was Textbook Report and choose to preview.