Databases Queries

In many respects tables and forms are merely the shell or case of the database. The working "engine" comes in the first place from the queries. Queries allow all the data that is stored in the database to be searched. The simplest sort of search is when you look for an exact match. In the case of the british birds database used here you may want to find the wren so you can type the name wren into the criteria cell of the name field shown on the right and click search. Even though the name is in the database as Wren this will work and the bird will be found (Access is case insensitive so WREN and wreN will all work.

If you go back to the search you will find the wren has been placed in speech marks because access auto corrects your entry. Now suppose you see a bird in a wood and want to know what it might be you might try a search for woods by putting the word "woods" into the habitat column. Disappointingly this finds nothing despite the fact that many examples of birds seen in woods are in the database. This is because the words entered are not an exact match for the word woods. Woods may be at the start in the middle or at the end of the field, with several other words included.

This can be overcome by using the wild-card characters ? and * they can be used to represent one letter ( ?) or any number of letters and spaces (*). So if you type in *woods* you might hope to find all the birds. Although you will get some you wont get them all. This is because your search wont find woodland or woodedges. To overcome this you need to enter *wood*This will find them all an it also highlights another problem - that of data entry notice that woodedges and wood-edges appears in the database. You could argue one is a spelling mistake or that it is a data entry error.

Large databases have a data dictionary that includes style rules that ought to prevent this type of inconsistency - the the data entry clerk follows the rules! Not all of them will.

Queries can also be entered in two fields so if you know your bird is smaller than 12cm you could leave the *wood* in place and place <12 in the size column. This will tell you that the only bird is the wren. (at least in this database).

A copy of the british birds database is available here.

© LEV