Working with databases/Create a database/Records and tables

Working with Databases

Basic skills applicable to planning, creating and using an Access database.
An OP Online course from the National Certificate in Computing (Level 2) programme.

About this course  |  Getting started  |  Course schedule  |  Assessment overview  |  Help

Sort a table

We can sort a table - that is, we sort the records into numeric or alphabetical order.

Activity

Please note: the following tutorial will open in a new window/tab. When you have finished the tutorial, simply close the window/tab and you'll return to this page. Before you go on, please work through the following tutorial: Sorting Records

In the heading row that contains the field names, locate the small black triangle. Click on this.

A menu will pop up. From the menu choose how you would like to sort your table: Sort A ⇒ Z or Z ⇒ A. This also works in fields that contain numerical data.

Although you will not be assessed on this, you can also use this menu to filter out and display only certain information. In our example we could choose to display only students from Glenfield.

The same principle applies to sorting a query (You will learn how to create a simple query later on) which is displayed on the screen like a table.

To clear and reset sorting, go to Home Tab ⇒Sort and Filter Group ⇒Clear All Sorts

Activity

Open your bookshop database, and open the table that you have created Using what you have covered above, sort your table by book title, A ⇒ Z. We are going to print your sorted table: In Office 2007 open the Office Button, then click on Print. In Office 2010 click on the File tab, then click on Print. Tables that are very wide may be best printed in Landscape Format. Once you have printed your table, clear all sorting from your table. Now sort your table by year published, Z ⇒ A. Print your new sorted table.

Please note

Can we edit or change a table once it has data in it? Yes you can, but with a few provisos: You can safely add and edit a new field to an existing table. You can delete a field, but you may break anything that relies on this field being there. You can edit a field and change its properties or data-type, but you most likely will lose all information stored in that field across all records.

Activity

We are going to add a new field into our table called Book in Stock. This will be a Yes/No type field. Every time we sell a book we will untick this field. This way we can reorder new copies of popular books and indicate that we have them without having to delete and re-enter data all the time. Open your database. Open the table and change to Design View. Enter in the new field details at the end of the list of fields displayed. If you are unsure how to do this, you might want to review the section on Table design In the Field Properties pane, change the default value from 0 (zero) to 1 (one). This will make Access automatically tick the box for you when you enter in new books. Check your database to make sure that Book in Stock is ticked for all books except for ones on order. Please add the following 3 new book titles to our database: Please check your database against your source information.

|