Building Databases

Welcome to lesson one - a simple flat-file database

In this lesson you will learn to...

• create a table to store and enter your data.

• get information out of the database by…

• sorting, filtering and searching the data.

• printing a report, listing all the contents in a certain order

By the time you’ve finished this lesson you’ll have a feel for Access and be ready to learn more.

The Data Table...
The database in today’s lesson is a simple two-dimensional table – a “flat file” database containing members of a Cricket Club...

Table 1. The information: a cricket club records for its team

Data Types...
Now we know the data we’re using we make a list of field names and data types. Like this:

Table 2. Field names and rows

Giving fields a data type helps the database to process the data properly.
For example if the database was not told that "8/2/2004" and "10/12/2004" were dates and were simply sorted alphabetically (as if they were text) the later date would come before the earlier one.

Notice that we've used shortened names for our fields, with no spaces.
This is considered good practice and makes some tasks easier to perform later on.

Step 1: Creating a table

1. Start Access and choose "Blank Database".

2. Give your database a name (CricketClub) and select a folder to store it in.

3. You’ll see the Database dialogue appear on the screen and you’re ready to begin.

4. You’ll create your table in Design view, which enables you to enter all the field descriptions yourself.
   To do this, choose Table from the Objects list and double click Create table in Design view.

5. You’ll see the table Design view on the screen which enables you to enter the field names, the data type and a description for each field.

6. Click in the first area and type the field name FirstName, press Tab and you’ll see Text appears as the default data type, press Tab and type a description for this field, press Tab to move to the second field. Continue adding the data for each of the fields – making sure you select the correct data type from the drop-down list.

Step 2: Adding captions to our fields

The shortened names we’ve used for the fields maybe good practice but they won’t look very attractive if they appear in reports. We can solve this at this stage by adding captions for our fields. When we print reports from the database, these captions will be used instead.

To do this...

1. Click in the MembershipNo field and in the General tab, at the foot of the dialogue, locate the Caption area. In there type "Membership Number"  which is a clearer indication of what the field contains.

2. Repeat this and add Captions for the other fields: First Name, Last Name, Current Grade, Fees Paid?, Years a member and Date of Birth.

Step 3: Setting the primary key

Every table in a database need to have a primary key - the key field that serves as the unique identifier of a specific row in a database table.

To set the Primary key...

1. Click in the margin opposite the MembershipNo field to select it.

2. Right click and choose Primary Key from the menu.

3. You’ll see a key appear in the column to the left indicating the key has been set.

4. Now save the table by clicking the Save button on the toolbar and call the table "Club Members" and click OK.

Step 4: Entering data

Now that the table is created, you can enter your data into it.

To do this...

1. Switch to the Datasheet view by choosing View, Datasheet View.

2. Enter the data for your table – one item per cell.

3. Use the Tab, Enter or Right Arrow key to move to the next field in the row, type your dates in the format 6/02/2004 (using slashes) and, to enter the data in the Yes/No field, press the Spacebar for Yes and leave the checkbox unchecked for No.

4. When you’ve entered the data, close the table by clicking the Close button in its top right corner.

5. You’ll be returned to the Database dialog and, you’ll see that the Tables tab now contains your new club members table.

Step 5: Working with your data

You can open the club members table at anytime by selecting it and clicking the Open button or by simply double clicking its name in the list.

Sorting

To change the order of the data using the Sort buttons on the toolbar...

1. Choose the column containing the Last Name by clicking the column heading.

2. Click the Sort Ascending button (it has AZ and an arrow on it).

3. Watch as the data is sorted in last name order.

You can do this at anytime for any field in the data table.

Searching

To use the Find button on the toolbar to locate a person’s record in the table...

Find everyone with the last name Southdown by...

1. Select the LastName column

2. Click the Find button (it has a pair of binoculars on it).

3. In the Find What text area, type Southdown

4. From the Match drop-down list, choose Whole field

5. Click Find First.

6. You’ll be taken to one of the Southdown’s information.

7. Click Find Next and you’ll move to the next person.

8. Click Find Next again and note that there is not another matching record.

Filtering

At this stage you can also Filter your data so it displays only a small subset of the information it contains.

You do this using the Filter button....

1. Move to the Grade field and click on the entry for one of the players who plays A grade.

2. Click the Filter by Selection button on the toolbar (it’s a funnel with a lightening bolt to its right).

3. You’ll see only those records for members who play ‘A’ grade.

4. To return to viewing all the data in the table click the Remove Filter button on the toolbar (it is the button which is currently highlighted).

At this stage you’re only working with a small amount of data so you could probably have located this information very easily just by looking at it, however, when you are working with a lot of data, tools like these are invaluable.

Have you tried each part of Step 5, or have you just read it?
Go back and try each of the three parts, sorting, searching and filtering.
No slacking!

Step 5: Printing the data (a report)

An Access report is a way of committing the data to paper.
You can set up a report so it contains all the data in the table or simply a subset of it.

To create a report listing the members grouped together by grade...

1. Choose Reports in the Objects list and double click the Create report by using wizard option.

2. From the Tables/Queries drop-down list, choose club members (if not already selected) so that the data will be sourced from the entire table.

3. Use all the fields in the report by clicking the double chevron (››) to move all fields from the left window to the right.

4. Click Next.

5. Choose the field that the data will be grouped by, this is the Grade field. Select it and click the right pointing arrow to move it to the window on the right.

6. Click Next and choose the fields LastName and then FirstName as the sort fields from the drop-down lists. This will ensure that the members will appear in alphabetical order within the grade groups – they will be ordered firstly in last name order and, if two players have the same last name, they’ll be sorted in first name order.

7. From the layout options choose Align Left 1, Landscape and enable the ‘Adjust the field width so all fields fit on a page’ checkbox and click Next.

8. Choose a style for your report, click Next.

9. Give the report the name ‘Club members grouped by Grade’, choose the ‘Preview the report’ option and click Finish.

10. Wait while the report is compiled and the results displayed.

11. Print this report, make sure your name is on it and hand it to your teacher.

A report’s design is saved so you can create a report using these settings anytime by simply double clicking the report name in the Reports area of the database.

Epilogue...
In this first lesson we used a very big hammer to crack a very small nut.
Access is an incredibly powerful and complicated database program and using it for a very simple database like this is overkill. A spreadsheet would be much more appropriate for such a small amount of data. But let's face it, there’s no better way to begin learning about a program than by actually using it.

Ray Middleton, Feb 2003 (revised Feb 2004)