The word database can be intimidating, conjuring up images of tape-driven computers from the 1970s, but the reality is that they can be both simple and very useful. Databases are the electronic equivalent of index cards and can be used to store any information from music CD titles to recipes, addresses to birthday presents. The beauty of storing this information on your computer is that it is much more versatile.
You could, for example, use a recipe database to automatically create a shopping list for a dinner party. It could include all the ingredients needed rather than several entries for the same thing and then use the same information to create a menu.
In this feature we will demonstrate a simple database project Using Microsoft Works to catalogue a collection of music CDs and then show how this method can be used in other ways and in more advanced database applications such as Microsoft Access and the spreadsheet Excel.
The database language
The key to creating successful databases is understanding a few basic terms and concepts. A single piece of information is called a field. Each field has a name and has room for a piece of information. A field might have the name ‘County’ and contain the information ‘Yorkshire’ or it might be called ‘Number of times visited’ and contain the information ‘6’. Fields also know what kind of information they store, whether it is a number or some text. A collection of fields relating to one thing are collected together into a record. An address book would contain a record for each person, each of which would contain fields for each line of the address and their name.
All the records can be seen in the List view, which looks like a spreadsheet with the field names along the top. The List view is hard to print and can only show the field name and contents. A Form shows one record at a time and can include formatting, pictures and extra text to help fill in the form correctly.
Starting a database
Careful planning is the key to making an effective database; leaving something out at this stage can lead to a lot of work later on. The most common mistake is to use one field for a name and not separate fields for first name and surname. In this case, it is impossible to sort the records by surname as the database software cannot determine what the surname is. It is best to make a list of all the possible fields on a piece of paper before starting, splitting the information into as many categories (fields) as possible.
To show how databases work, we are going to create one to organise a CD collection. It will include the title and artist, track list and genre information. There will also be a field for the location of the album. If an album has been lent to a friend, it can be marked as such, as well as who has it.
Start Microsoft Works Database and select Blank database in the first window that appears. Database will start and the Field wizard (an automated assistant that helps with complex tasks) will start automatically. Shortly, we will enter the name of the field, select the Format and then click on Add and repeat this process until all of the fields have been entered and then click on Exit. Most of the time, it is perfectly acceptable to leave fields as General and let Database decide how to view them but if you are likely to try and sort or perform any other operations on the information, it is best to be as precise as possible now. It is not possible to change the format of a field later on – the only way to make changes is to delete the field and start again.
For the CD database enter the following fields, all with the Format set to text: Album name, Artist 1st Name, Artist 2nd Name, Genre, Total Time, Location and Track 1, Track 2 through to Track 12. Enter Total Time with the format set to Number and 19:03:29, for hours, minutes and seconds, selected. Finally, enter Location with the Text format, check the box at the bottom labeled Automatically, enter a default value and type Home in the box beneath. This tells the Database to always enter Home in that field unless told otherwise. This will save a lot of time when entering CD information. Click on Done to finish the wizard.
Now that the database is set up and ready to use, it should be saved to avoid losing work. Click on the File menu and select Save. Select a location and name for the database and click on Save.
Lists and Forms
To enter data in the List view, click in the first field for the record and press Tab to move right by one field. The Form view is far more attractive and allows the information to be presented in a way that can be printed out. To switch to the form view, click on the View menu and select Form or press
It is possible to edit the Form so that it looks better when printed or is easier to enter the information, for example, the track listing could be arranged in two columns so all the information can be seen at once. To do this, click on the View menu and select Form Design. Each field is surrounded by a dotted box. Click on one and then move the mouse around the edges and text will appear underneath the arrow to show whether clicking and moving the mouse will move or resize the field box. To add a text label, simply left-click where you want it to appear and start to type. Inserting clip art is just as easy; left-click where you want the image, click on Insert and then on Clip Art. Select a piece of clip-art from the library and click on the Insert button. The image can now be moved and resized like a field box.
Another good way to organise fields is to draw a box around a group, such as the track listing, to make it stand out. Right-click on an empty part of the form and select Insert Rectangle. Move the rectangle over the group of cells and resize it so that the edges are clear of the fields. Right click on it again and select Border. Choose a line style and colour and click on OK. To give the box a background, click on the Shading tab and pick a colour and pattern. When you click on OK, the shaded box will now cover all of the fields so they cannot be seen. Don’t worry, as there is a quick solution for this. Click on the box and then the Format menu. Click on Send to Back and the fields will be visible again.
Reporting on fields
Forms and lists only store the information; they don’t do anything with it; for that you need to generate a report. Creating a report asks a question about the database and presents the result. It might be a listing of all the jazz CDs in the collection or all those by a particular artist. Reports can include more than one question so it can make sure that it only lists the CDs that have not been lent to friends.
To create a report showing the rock CDs in the collection but only those actually at home, click on the Tools menu and select ReportCreator. Give the report a name and click on OK. The Title tab gives control of the title at the top of the report and the font that will be used. Click on Next and select the information that will be included on the report. Click on the following fields and then on Add: Album name, Artist 1st Name, Artist 2nd Name and Genre. Click on Next and select Sort by Artist 1st Name or Then by Album Name. Click on Next twice and then on the Create New Filter button. This is how the report knows what to display. Click on the Create New Filter button, type Rock CDs at Home and then click on OK. In the top menu for Field name select Location, leave Comparison as ‘is equal to’ and then enter Home in the Compare To box. In the next line, select Genre is equal to Rock. Make sure this filter is highlighted, click on OK and then on Done. Click on Preview and the report will be shown.
Once you have mastered the basics of databases the possibilities are endless. Other projects could include taking stock of plants in the house and in the garden, with a report for each month showing what needs replanting or feeding. They can also be useful for keeping track of your possessions for insurance claims and there are templates for this in Microsoft Works.
More advanced databases
Works Database is a great way to get started, but it is not the only program for creating databases. Microsoft Excel can also be used to create databases by using different worksheets for lists, reports and forms. Excel can also create forms automatically to speed data entry. They are rather basic, but are good for getting the job done. To enter information using a Form on a worksheet where the column headings have already been set up, select the column headings, click on the Data menu and select Form. Click on OK and the form will appear. Move through the different fields by pressing Tab.
For the most advanced databases, Microsoft Access introduces the concept of relational databases, a scary sounding term that simply means that there can be more than one table in a database and that there can be links between them. This can save time by removing the need to repeatedly enter information – for example, if you wanted to record the addresses of the people who have borrowed your CDs so you could automatically write a letter or email. In Works Database, this information would have to be entered for every CD every time. With a relational database, the contact information would be in a separate table. The tables are linked by a common field. The beta version of Open Office 2 includes a program like Access that you can download at no cost from our Cover CD ROM. You should be comfortable with creating databases before trying this as it assumes you know what you are doing.
Tip:
Working with other programs
Works Database uses its own file format but it is still possible to use the information with other programs. The most useful option is to use a Works Database and the source of information for a mail merge. Once the basic document has been written in Word, click on the Tools menu, then Letters and Mailings and then Mail Merge. The Mail Merge wizard will appear in the Task pane on the right of the screen. Click on Next twice and then on Browse in ‘Use an existing list’. Find the document, click on Open and then select all the records to be used. Finish the wizard in the normal way.
If the other program cannot open a Works database file, the solution is to save it as a .csv file. This is a text version of the list using commas to mark the end of each field.This cannot save formatting but does include all the information. To save the information from a report in this way, create the report and then click on Cancel on the preview screen. Works will show the design of the report. Now save the file as .csv and only the records found by the report will be saved.








Ltd