Access Database

John White

The purpose of this database is a simple address book which can be used for Christmas cards or, of course, any related purpose. It is based on a single table, with a form for convenient data input, a query (which sorts data) and two reports for labels. A small amount of automation is involved so as to make the database more user friendly. When planning a database, it is crucial to think what you want out of it before you start to construct it as this will affect much of the structure and the way you present information.

The principles are common to most databases, though more complex ones will have several tables linked together to avoid having to duplicate data. For example, one table could have the details of customers (names, addresses etc) and another the products they purchase from your company. There may be many products purchased by each customer at different times, but you don’t want to have to enter the same names and addresses time after time.

To create a new database from scratch.

Open MS Access and choose Blank database; give it a name and select the location e.g. My Documents.
Highlight Create table in Design View and choose New.
In the dialogue box which opens, choose Design View – OK (A design grid opens)

Enter the field names you want, bearing in mind what you want out of the database. In this case we are aiming for address labels, but we might also want to use the information for personal records. So you will want initials to appear on an address label, but you might want to add telephone numbers for personal reference.
The fields needed are:-
Title
FirstNames
Initials
Surname
Address1
Address2
Town
County
Postcode
Country
Xmas (Yes/No)

All fields are Text fields except: Xmas Labels – a Yes/No field and Notes, a Memo field.
Save the table and call it TblAddresses. (Note the use of capitals and that there are no spaces). If Access asks you to define Primary Key, choose No (because there are no other Tables to relate to).

To make an input form.

You can use a Wizard, incorporating all fields. We will make a form from scratch.
In the database window, choose the Forms tab and click New
In the dialogue box, choose Design View and in the drop-down list, select TblAddresses – it’s the only one for now, but more will be added later. Click OK.
In the design grid which opens, drag the grid edges out a bit to make some room.
On the page should also be a toolbox and a list of the fields in the table. If not, either use the toolbar or choose View/Toolbox and View/Field List.
In the Field List, move the fields one by one on to the grid and arrange them how you wish. The XmasCards one needs special treatment. Before dragging the name, click the check box in the Toolbox panel and then drag the field on to the grid. It produces a check box.
After arranging everything how you would like to see it, click the datasheet button at the top left of the toolbar or choose View/Form View. Save the form and name it FormInput.
You can now enter data directly into the Form, but it is stored in the table in the order you enter records. Sorting (e.g. into alphabetical order) is done separately and does not affect the underlying data in the table.

To create mailing labels, choose Reports in the database window.
Choose Create report in design view and click New.
In the dialogue box, choose Label Wizard and TblAddresses from the drop-down list. OK
The first step is to select the type of label you are going to use. I use Avery L7160 which gives 3 across and 7 down. Click Next
Accept the default settings for the font and click Next. A pro forma label appears.
Choose Title from the panel on the left and click the right arrow. Title appears in the right hand box. Press the spacebar to create a space between Title and em>Initials; choose Initials, click the arrow and enter a space; then Surname, but hit the Return instead of the spacebar to move to the next line.
Enter the others line by line and include everything except XmasCards. And FirstNames.
Sorting is helpful because you can find the labels more easily if they in alphabetical order – so sort by Surname (Highlight Surname and click the single arrow,; you can add Initials if you wish, them click Next
Name the report RptLabels and click Finish. The sheet of labels appears and is ready for printing.
But it includes all labels, and we might not want to send Xmas card to everyone. It is therefore necessary to sort those records you want from those you don’t. This is done through a Query.

To make a Query to extract only the Xmas Cards records.

In the database window, click Queries/New –the New Query dialogue box appears. If it not already highlighted, highlight Design View and click OK.
In the next dialogue box, select TblAdresses and click Add. Then click Close in the dialogue box. We have added the table to the top half of the Query grid.
The fields we need now have to be added to the grid part. Click and hold Title, and drag it to the blank space to the right of Field in the top line of the grid. Release the mouse. Do the same with the following fields, using the top line’s first blank space each time: em>Surname, Initials, Address1, Adress2, Town, County, Postcode. If you need to send Xmas cards to people in countries other than the UK, add the Country field as well.
Then add the Xmas Cards field.
In the Criteria row go to the Xmas Cards section and enter Yes in the box. This means that only those records with a Yes will be selected. As we made the field in the original table a Yes/No field, we can select the records and change them at will.
One more refinement would be helpful – put the labels in alphabetical order for ease of finding them. To do this, go to the Sort line in the second section (Surname) and click just inside the extreme right hand edge. The drop down box includes Ascending – click this and Ascending appears in the relevant part of the grid.
Save the Query by closing it, choosing Yes from the dialogue box asking if you want to save it, and name it QryXmasCards.
To select the lucky people who are on your Xmas Cards list, open the Input Form and for each record where you want to send a card, check the Xmas Cards box. For the others, leave them blank.
Create a new report using the Query QryXmasCards as the basis, following the steps in the Creating mailing labels section. Save your new report as RptXmasLabels.
Now go to the database window and open the Report RptXmasLabels. Voila!
To print the labels, insert a blank sheet of labels in your printer, and choose File/Print.

A bit of automation makes things easier.

All the parts of the structure are now in place, but it would make things easier and a good deal quicker if we could just press a button or two instead of opening things from the database window. This is done through a Switchboard – a special type of Form, created by a Wizard.
From the Menu Bar, choose Tools/Database Utilities/Switchboard Manager. You may get a box asking whether you want to create a Switchboard – say Yes. Click the Edit button and then New. Replace New Switchboard Command with Input data; in the second line, click the arrow and choose Open Form in Edit Mode from the drop-down list and in the third line choose FormInput from the ‘list’ and click OK
Choose the New button and this time the boxes should be Xmas Labels (typed in), Open Report, and RptXmasCards. The final entry to create is a means of closing the database. Choose New and the lines should be Close database and Exit application. Then close the Switchboard Manager.
A further refinement is related to the process of opening the switchboard when the database opens. This is done by creating a macro.
In the database window, choose Macros/New and scroll down to Open Form. In the small box at the foot of the page click the line opposite Form Name and choose Switchboard from the drop-down box. Save the Macro as Autoexec. (no full stop though).
Close the database and then re-open it. The Switchboard should appear.

Conclusion

This is a very simple database with only one table, but it uses many of the processes in more complex relational databases. I hope it whets your appetite.

John White

The structure of the database we have created is shown diagramatically below.