Latest Stories

database: A collection of related information stored in a structured format. Database is often used interchangeably with the term table (Lotus Approach, for instance, uses the term database instead of table). Technically, they’re different: A table is a single store of related information; a database can consist of one or more tables of information that are related in some way. For instance, you could track all the information about the students in a school in a students table. If you then created separate tables containing details about teachers, classes and classrooms, you could combine all four tables into a timetabling database. Such a multi-table database is called a relational database.

data entry: The process of getting information into a database, usually done by people typing it in by way of data-entry forms designed to simplify the process.

dbms: Database management system. A program which lets you manage information in databases. Lotus Approach, Microsoft Access and FileMaker Pro, for example, are all DBMSs, although the term is often shortened to ‘database’. So, the same term is used to apply to the program you use to organise your data and the actual data structure you create with that program.

field: Fields describe a single aspect of each member of a table. A student record, for instance, might contain a last name field, a first name field, a date of birth field and so on. All records have exactly the same structure, so they contain the same fields. The values in each field vary from record to record, of course. In some database systems, you’ll find fields referred to as attributes.

flat file: A database that consists of a single table. Lightweight database programs such as the database component in Microsoft Works are sometimes called ‘flat-file managers’ (or list managers) because they can only handle single-table databases. More powerful programs, such as FileMaker Pro, Access, Approach and Paradox, can handle multi-table databases, and are called relational database managers, or RDBMSs.

foreign key: A key used in one table to represent the value of a primary key in a related table. While primary keys must contain unique values, foreign keys may have duplicates. For instance, if we use student ID as the primary key in a Students table (each student has a unique ID), we could use student ID as a foreign key in a Courses table: as each student may do more than one course, the student ID field in the Courses table (often shortened to Courses.student ID) will hold duplicate values.

index: A summary table which lets you quickly look up the contents of any record in a table. Think of how you use an index to a book: as a quick jumping off point to finding full information about a subject. A database index works in a similar way. You can create an index on any field in a table. Say, for example, you have a customer table which contains customer numbers, names, addresses and other details. You can make indexes based on any information, such as the customers’ customer number, last name + first name (a composite index based on more than one field), or postal code. Then, when you’re searching for a particular customer or group of customers, you can use the index to speed up the search. This increase in performance may not be noticeable in a table containing a hundred records; in a database of thousands of records it will be a blessing.

key field: You can sort and quickly retrieve information from a database by choosing one or more fields to act as keys. For instance, in a students table you could use a combination of the last name and first name fields (or perhaps last name, first name and birth dates to ensure you identify each student uniquely) as a key field. The database program will create an index containing just the key field contents. Using the index, you can quickly find any record by typing in the student’s name. The database will locate the correct entry in the index and then display the full record.

Key fields are also used in relational databases to maintain the structural integrity of your tables, helping you to avoid problems such as duplicate records and conflicting values in fields (see primary key and foreign key).

normalisation: The process of structuring data to minimise duplication and inconsistencies. The process usually involves breaking down a single table into two or more tables and defining relationships between those tables. Normalisation is usually done in stages, with each stage applying more rigourous rules to the types of information which can be stored in a table. While full adherence to normalisation principles increases the efficiency of a particular database, the process can become so esoteric that you need a professional to create and understand the table design. Most people, when creating a database, don’t need to go beyond the third level of normalisation, called third normal form. And there’s no need to know the terminology: simply applying the principles is sufficient.

The first three levels in normalising a database are:

First Normal Form (1NF): There should be no repeating groups in a table.

For example, say you have a students table with the following structure:

student ID
date of birth
advisor’s telephone
course ID 1
course description 1
course instructor 1
course ID 2
course description 2
course instructor 2

The repeating course fields are in conflict with first normal form.To fix the problems created by such repeating fields, you should place the course information in a separate course table, and then provide a linking field (most likely student ID) between the students table and the course table.

Second Normal Form (2NF): No non-key fields may depend on a portion of the primary key.

For example, say we create a course table with the structure:

student ID
course ID
course description
course instructor

We can create a unique primary key by combining student ID + course ID (student ID is not unique in itself, as one student may take multiple courses; similarly, course ID is not unique in itself as many students may take the same course; however, each student will only be taking a particular course once at any one time, so the combination of student ID + course ID gives us a unique primary key).

Now, in 2NF, no non-key fields (course description, course instructor) may depend on a portion of the primary key. That, however, is exactly what we have here: the course instructor and course description are the same for any course, regardless of the student taking the course.

To fix this and put the database in second normal form, we create a third table, so our database structure now looks like this (with key fields in italics):

Student table

student ID
date of birth
advisor’s telephone

Student courses table

student ID
course ID

Courses table

course ID
course description
course instructor

Third Normal From (3FN): No fields may depend on other non-key fields. In other words, each field in a record should contain information about the entity that is defined by the primary key.

In our students table, for example, each field should provide information about the particular student referred to by the key field, student ID. That certainly applies to the student’s name and date of birth. But the advisor’s name and telephone doesn’t change depending on the student. So, to put this database in third normal form, we need to place the advisor’s information in a separate table:

Student table

student ID
date of birth
advisor ID

Student courses table

student ID
course ID

Courses table

course ID
course description
course instructor

Advisor table

advisor ID
advisor name
advisor telephone

primary key: A field that uniquely identifies a record in a table. In a students table, for instance, a key built from last name + first name might not give you a unique identifier (two or more Jane Does in the school, for example). To uniquely identify each student, you might add a special Student ID field to be used as the primary key.

query: A view of your data showing information from one or more tables. For instance, using the sample database we used when describing normalisation, you could query the Students database asking “Show me the first and last names of the students who take both history and geography and have Alice Hernandez as their advisor” Such a query displays information from the Students table (firstname, lastname), Courses table (course description) and Advisor table (advisor name), using the keys (student ID, course ID, advisor ID) to find matching information.

rdbms: Relational database management system. A program which lets you manage structured information stored in tables and which can handle databases consisting of multiple tables.

record: A record contains all the information about a single ‘member’ of a table. In a students table, each student’s details (name, date of birth, contact details, and so on) will be contained in its own record. Records are also known as tuples in technical relational database parlance.

relational database: A database consisting of more than one table. In a multi-table database, you not only need to define the structure of each table, you also need to define the relationships between each table in order to link those tables correctly.

report: A form designed to print information from a database (either on the screen, to a file or directly to the printer).

SQL: Structured Query Language (pronounced sequel in the US; ess-queue-ell elsewhere). A computer language designed to organise and simplify the process of getting information out of a database in a usable form, and also used to reorganise data within databases. SQL is most often used on larger databases on minicomputers, mainframes and corporate servers.

table: A single store of related information. A table consists of records, and each record is made up of a number of fields. Just to totally confuse things, tables are sometimes called relations. You can think of the phone book as a table: It contains a record for each telephone subscriber, and each subscriber’s details are contained in three fields – name, address and telephone.
Read More ...

Latest Stories

The look of your database is as important as its structure, because it’s the interface that affects how people work with it. Aim for a cleanly structured, consistent look with complimentary colours and few distractions, as in this sample main menu for a banjo tablature application.

It’s staggering how many shareware and commercial database applications have appalling data entry screens. Many developers seem to think that well-oiled inner workings are all that’s needed to sell an application, when any user knows that, when you get down to it, the interface is the app.

When you’re designing a database application, you’re taking on the role of a developer. As you do so, remember to keep the soul of a user. While the brain work in building a database comes during the design stage, the hard slog comes when you – or other people – start adding data to that structure, especially when there are copious amounts of data to add. You can alleviate much of the tedium of data entry by ensuring your data entry forms are logically organised, easy on the eyes and efficient. If you alleviate the tedium and keep the users of your system happy, you’ll automatically increase the accuracy of the data entered into the database.
Data entry guidelines

Exactly how you design your data entry screens will depend on the database program you’re using, the amount of data you’re dealing with, the needs and likes of the data entry personnel, and any application-specific requirements which may exist.

If you’re designing a database purely for your own use it’s still worth designing well-thought-out data entry screens. After all, why should you make life harder for yourself when a little effort in the design phase will make using your database easier to use for all time?

The following guidelines will help you design attractive and easy-to-use data entry screens.
i. Organise fields logically

Group related fields together and use boxes or colour coding to make it easy for users to zero in on information quickly.
ii. Don’t clutter

Space fields on the screen so users can easily spot the field they need to edit.
iii. Don’t force users to scroll

This database uses a tabbed data entry screen to group fields logically and avoid clutter. Note the tabs across the top (Title, Product Details, Requirements, etc.); data entry instructions included on the form; the use of asterisks to mark fields where data entry is compulsory; and the navigation bar at the bottom which is consistent between tabs.

If possible, make sure all your fields are visible simultaneously, so users don’t have to keep switching between the keyboard (for data entry) and the mouse (for scrolling), and so users can see all information at a glance. It’s also worth avoiding scrolled data entry screens as people have a tendency to forget about those invisible fields lurking off the bottom of the screen and leave them blank.

If you have a large number of fields in your database and you don’t want to create clutter but still want to avoid scrolling, try using a tabbed interface. With a tabbed interface, you can separate fields into logical groupings, with each group on its own tab. This approach avoids clutter while keeping everything on the screen.
iv. Go easy on the fonts

At the most, your data entry screens should use three fonts – one for the headings, one for field labels, and one for the field contents themselves.

For best readability, use sans serif fonts such as Arial, Verdana and Trebuchet MS (sans serif fonts lack the little ‘feet’ or extra small lines at the end of characters that you’ll find in serif fonts such as Times New Roman). If large amounts of text will be entered in text fields, you may wish to use Times New Roman or another serif font for those fields. Choose standard fonts, especially if your database will be used on a variety of computers. Make sure all text is of a readable size.
v. Go easy on the colours

Avoid lairy colours. Instead choose soft colours or those that provide good contrast for the fields and captions. If a number of people will be using your database application, keep in mind that some of them may be colour blind or suffer from other eyesight difficulties.
vi. Abandon the defaults

Dump the default data entry screens created by Access, Alpha and most other databases. They are usually poorly organised and either look boring or hard to read. Access is particularly bad in this respect, while FileMaker offers the best standard screens. This is particularly important if you’re using an older version of any of these database programs; more recent versions tend to provide a bit more spit and polish.

Use the default screens as a starting point and tweak them or, if you don’t mind the extra work, build your screens from scratch.
vii. Check your tab order

Ensure that Tab order is correct. During data entry, you move from field to field using the Tab key. It’s important you ensure the Tab order is consistent, so that the insertion point moves from the first field on the screen to the last without jumping about. Incorrect tab order will not only frustrate your users, but it may cause them to enter data into the wrong field.

In Access, open the form in Design View and choose Tab Order from the View Menu. If you’re using Access 2010, switch to Design View, click the Design tab and click Tab Order in the Tools group.
viii. Be consistent

If you application has more than one data entry screen, use the same look and the same organisational principles for each screen. If, for example, you place a Close button on one data entry screen so users can close the screen with a mouse click, include the same box in a similar location on other screens.
ix. Include help

On this data entry form, help is provided both on the form itself and in the form of pop-up tooltips. Microsoft Access makes it particularly easy to add this sort of help to data entry screens.

Add descriptive help where possible. Your data entry forms should be as self-explanatory as possible, with commonsense labels on each field.

You can make data entry even easier by providing help for each field. In Microsoft Access, for example, you can add a pop-up field tooltip (called a ControlTip in Access 2007 and 2010) which will appear when the user lets their cursor linger on a field.

If your database doesn’t support such tooltips, you can include instructions directly on the data entry form itself. For instance, if you want people to enter phone numbers without including any special characters (such as hyphens or parentheses) you can add a message to that effect beside the phone number field, thus: Enter numbers only, eg. 0291237745.
x. Provide shortcuts

You can speed up data entry dramatically by providing shortcuts such as drop-down boxes with predefined choices, auto-filled fields, multiple choice fields and so on. Some of these features you can add during the database definition stage, although we kept things simple in the previous tutorial and avoided such options. Others you can add when you design your data entry forms. Still others you can introduce by using lookup tables or related tables, which let users ‘look up’ information – such as the postal code for a city – and have it entered automatically.

Drop-down lists and pre-defined choices speed up data entry. These choices can be defined either during database definition or when you design the data entry form itself.

We’ll look at all these options in the future.
xi. Validate data as it’s entered

You should design your forms so they check for invalid data and give users a chance to correct mistakes they’ve made. Much of this validation can be done during database definition; some you’ll add during form design.

We’ll look at data validation in more detail in the future as well.
Experimenting with forms

Each database program provides different tools for designing data entry forms. These tools are sometimes very fiddly to work with, so it pays to practice. One of the best ways to do that is to use the standard data entry form created by the database and then mess around with it. Don’t do this experimenting on a ‘live’ database. Create a sample one from scratch, if you like, and work with it.

To do this in Microsoft Access 2000, select a table in the Tables section of the database objects window, click the New Object: Autoform button on the toolbar to create a new form, and then click the View button on the toolbar to switch to Design View. Click the Properties button on the toolbar and select different objects on the form to adjust the settings.

If you’re using Access 2007 or 2010, select a table from the Supporting Objects list, click the Create tab and click the Forms button to create a default form. Click the View button to switch to Design View, click Property Sheet in the Tools section and then click different objects on the form to see their properties.
Read More ...

Latest Stories

In the previous tutorial, we explored how to create simple parameter queries in Microsoft Access which prompt the user for criteria when run. These are real bread-and-butter queries you'll find yourself using over and over. The only drawback with such queries is that you have no control over the look of the prompt dialog boxes displayed and, with complex queries, you may end up presenting your users with prompt after prompt after prompt.
Your database application will be much more enjoyable to use if you eliminate this dialog box proliferation and spruce up the dialogs. To do so, you'll need to create a custom dialog box which consolidates all the prompts.

The task

We're going to use the Countries database we've used in past tutorials (you'll find a link to download a copy at the end of this article) and design a dialog box for the parameter query with which we ended the previous column.
That query was designed to elicit the following information:
Show me countries that joined the UN between date x AND date y AND which have a population over n.
If we create such a query without using a custom dialog box, we'll have to badger our users with three consecutive dialogs, the first asking Joined the UN after what date?, the second asking And joined before which date?, and the final prompting And has a population over? With our custom dialog, we can consolidate these questions and also gain control over the appearance of the dialog box itself.
We'll build the form first, then recreate a modified version of the query which takes advantage of the custom dialog box.
I'll describe the process using Access 2000. Access 97's interface is a little different, but you should be able to adapt these instructions with very few changes.

When a box is a form

As far as Access is concerned, a dialog box is simply another type of form, like the data entry forms you encountered early on in this series. So that's where you'll start – by creating a form:
  1. Open the Countries database and, in the main database window, click the Forms button, then double-click Create Form In Design View. You'll see a blank form titled Form1: Form.
  2. If it's not already displayed, display the Toolbox by clicking the Toolbox icon (a crossed hammer and spanner) on the toolbar. Make sure the Control Wizards are enabled (the Control Wizards icon at the top of the Toolbox should be depressed). Drag the toolbox to the side so it's easily accessible but doesn't obscure the form window. Take some time to run your cursor over each of the Toolbox icons so you can see what it contains.
Access provides a variety of tools for working with forms.
Access provides an assortment of tools with which to customise forms. Click the image above to see a full-sized image and detailed caption.
  1. We want to add a data entry box for each of our three prompts. So click the Text Box tool in the Toolbox, then click-and-drag on the form to create a rectangular text box (position it a little down from the top and about two-thirds of the way from the left edge). You'll see that a label is created to the left of your form. Click the label to select it and resize it by dragging the resizing handle at its left edge towards the left of the form. Click within the label, delete the existing text and type Show countries which joined the UN after:, then click the form outside the label. You'll see that the Text Box contains the word Unbound. This indicates that the text box is not connected (bound) to data in a field, as is the case with our data entry forms. In fact, the whole form is called an unbound form because it is not based on any existing data or data structure.
  2. Repeat step 3 for the remaining two prompts, lining them up one beneath the other. Place the text and joined before: in the second prompt and and whose population exceeds: in the third prompt.
  3. Each text box needs an easy-to-remember name so we can refer to it easily in our query, so right-click the first text box and choose Properties from the pop-up menu. This displays the Properties box, which lets you adjust every facet of your text box. Position the Properties box to the side, so you can see it and the Toolbox while also viewing your form.
  4. In the Properties box, click the All tab and change the value of the Name property to FirstJoined.
  5. Now do the same for the other two text boxes. Click the second text box (the Properties box will now reflect the settings for that box) and change the Name property to LastJoined. Click the third box and change its Name property to Population.
Save your form at this point giving it the name Criteria.

Querying the form

So far, all we've done is cosmetic. We've designed a rather boring looking form that will display three prompts. You can preview your form by clicking the View button on the toolbar. This form needs a Command Button that will tell it to run our date and population query, but before we can do that, we have to have a query to run!
To create a query that grabs its parameters from our custom form:
  1. In the main database window, click the Queries button and double-click Create Query In Design View.
  2. Select the Countries table and click Add to add it to the query, then close the Show Tables dialog.
  3. Drag the fields Name and JoinedUN onto the query grid, then drag the Population field onto the grid to the right of the JoinedUN column.
  4. In the Criteria cell of the JoinedUN column, type:
Between [Forms]![Criteria]![FirstJoined] AND [Forms]![Criteria]![LastJoined]
Eek! I know this looks scary, but that [Forms]![Criteria]![FirstJoined] stuff simply tells Access to go check its collection of forms, locate one called Criteria, and then find the value entered into the FirstJoined text box on that form. You can think of it as the equivalent of telling Access the address where it can find the data, with each part of the address enclosed in square brackets and separated by an exclamation mark. Note that instead of using greater than (>) and less than (<) symbols, we've used the shorthand BETWEEN…AND operator, which you can use when searching for dates.
  1. You should be able to guess what's coming next. In the Criteria cell of the Population column, type:

What type of data?

Because each answer is being entered into a text box on our form, we need to tell Access whether the input is a date, number or something else by identifying its data type. That way, Access will know how to deal with the data:
  1. Right-click in the grey area above the query grid and choose Parameters from the pop-up menu.
  2. In the first Parameter cell, type:
then tab over to the Data Type cell and select Date/Time from the drop-down list.
  1. In the second Parameter cell type: 
and select Date/Time data type.
  1. In the third Parameter cell type:
and select Long Integer in the data type list.
  1. Click OK and close the query, saving it with the name UNPopulation.

At your command

Now that we have our query, we're going to return to our form and put a Command Button on it that runs that query:
  1. Open the Criteria form in Design view.
  2. Click the Command Button tool in the toolbox then draw a rectangle about three centimetres wide and one centimetre high at the bottom of your form. The Command Button Wizard will be displayed. (If it isn't, it means you haven't enabled Control Wizards in the Toolbox. Select the button you just created and press the Delete key to erase it, then enable the Control Wizards and try again.)
  3. In the Categories list select Miscellaneous and in the Actions list select Run Query, then click Next.
  4. Select the UNPopulation query from the list and click Next.
  5. Select the Text option, type Display Matches and click Next.
  6. Name the button RunQuery and click Finish.
  7. Save and close the form.

How'd we get here?

That's all the hard work out of the way. Let's summarise what we've done then take our customised parameter query for a spin:
  1. We created a form containing a text box for each parameter in our query.
  2. We gave each text box a recognisable name.
  3. We created a query containing criteria which derive their parameters from the values typed into the text boxes on our custom form.
  4. We assigned each parameter a data type to ensure Access knew how to deal with it.
  5. We added to our form a command button which runs the query.

Rock and roll

Now it's time to run the query. In this case, we actually run the form which, in turn, calls the query via the command button:
  1. Click Forms in the database window and double-click the Criteria form. (If the Properties box opens when you open the form, click its Close button to close it.)
  2. Type the following values in the three boxes on the form:
and click the Display Matches button. (Note, if you use a different date format on your PC, such as MM/DD/YY, adjust the second value accordingly.)
  1. Access will display a table containing the six countries which joined the United Nations during the sixties and whose population is greater than 20 million.
The end result of our labours: a custom dialog box.
Our custom dialog box and the results of running the query. Click the image to see a full-sized image and detailed caption.
Read More ...