Databases can be small or vast collections of records – depending on the business of your organisation they could include information on anything from staff salaries to buildings, meal ingredients, exam results, stationery suppliers or wallpaper samples.

In the previous chapter you will have seen that spreadsheet packages can be used to create quite adequate databases, but for more sophisticated management of data and the ability to link different sets of records, you are likely to find yourself using a relational database such as Access.

There are three main tasks that you may be asked to perform:

• 1.Input data and keep records up-to-date;
• 2.Analyse the records to find those meeting certain criteria; and
• 3.Present the records attractively in the form of reports.

A BRIEF OVERVIEW

A database file can include any number of records, which can themselves be grouped into different tables. For records to be included in the same table, the data must be stored under the same headings, the Field names.

For example, a training department may have a Training database that contains the following records: staff attending the courses; course details; locations of the training centres and information on all the trainers. In Access, this would mean the database should contain information held in four separate tables: Attendees, Courses, Venues and Trainers.

The advantage of using a relational database is that information does not need to be duplicated. If, for example a member of staff attended a health and safety course that had the code HS14, details of the course would be held in the Courses table and not repeated in the Attendees table. Once the tables were linked, it would be possible to search both tables and discover the names of everyone attending the Health and Safety course, their department, the length of the course and the name of the trainer.

Attendees Table

PRIMARY KEY

To prevent confusion, as well as allow tables to be linked, one field has to contain data that uniquely identifies the records and this field must be included in all linked tables. In the above example, the training courses must have different codes to distinguish between them all. To preserve this uniqueness and warn anyone trying to enter a new course into the table with the same code that another one already exists, the course code field in the Courses table would be designated as the primary key. You would only need to set primary keys if you were creating a table yourself, but understanding why they have been set may help you if problems arise when you try to enter duplicate data or search linked tables.

ENTERING AND AMENDING RECORDS

Opening the database file reveals a window that stays open all the time. The tabs explored in this chapter are labelled Tables, Queries, Forms and Reports and every time you work on an object within the database, you need to first click the correct tab.

To view the records in any table, double-click the name on the Tables tab or select the name and then click the Open button. (If you need to amend the design of a table, you must click the Design button and will see just the fields under which data is stored.)

Tables look similar to spreadsheets, but you can only add a new record at the bottom of the table where you will see a pen symbol in the row header area. Enter details for each record across the row, moving with the tab or arrow key. If a primary key has been set, you must complete entries for a full record before you can make any amendments.

To make changes, click any entry and delete or insert text in the normal way. If you want to delete a complete record, select it by clicking the grey row header box and either click the Delete button on the toolbar or use your keyboard. If a column is too narrow, click and drag the right-hand boundary between field names or double-click the line to adjust to the longest entry.

Field properties

Occasionally, you may want entries e.g. in a Price field to show decimal places but these don’t appear when you type the entry. This is because the table designer has not set the number format correctly. The answer is to click the Design view button. You

will see just the field names and the type of data they can contain e.g. whether the field entries are text, dates or numbers.

For each selected field a lower Properties box opens where you can make changes to the settings. Click your Prices field name and then move down to the properties and change the entry in the Field Size box from Long Integer (the default that will only display whole numbers) to Double. Now you can set the number of decimal places in the Decimal Places box and, when you save the new design and return to your records, future entries will reflect these changes.

REPLACING ENTRIES

When you need to replace entries in databases holding hundreds of records, finding the correct entry can take some time. You therefore need to use the Edit – Find and Replace facility that is available.

To find an entry, type it in the Find What: box, making sure you select the correct match: the whole field or only a part.

One problem with the Access find facility is that only single fields will be searched by default. If you left your cursor marking an entry in the wrong field, click the drop-down arrow in the Look In: box and change the entry so that you search the complete table, or return to the table and click the correct field before using Find.

To replace entries, click the Replace tab and complete the Replace With: box. Either click Find Next to check by eye before replacing entries, or click Replace All. Take care, though, as any changes to your records cannot be reversed easily, and you may make a costly mistake if you replace 1,000 records with the wrong data.

SORTING RECORDS

It is very quick to sort your records alphabetically or by different criteria e.g. from highest to lowest price or earliest to latest dates. Click any entry in the selected field and then click the A-Z (lowest to highest/alphabetical) or Z-A (descending) button on the toolbar. You do not need to select all the data first as records will not be split.

FORMS

Very often you will be asked to add or amend records viewed on screen one at a time, rather than within the complete table of data, although the table will be updated automatically with any changes you make. This is because a different object, a form, has been created for you to use as an alternative way to amend records. Forms can be easier to use and may also protect the full table from being viewed by others or changed by mistake. For example, the form may not display all the fields in the underlying table so that only certain entries can be accessed and changed. If no front-end menu has been created, you will find your form if you click the Form tab in the database window.

SEARCHING

To identify everyone in your database who retired in 2004, who works from home, who lives in Birmingham or who owns a car, you will need to carry out a search. The structure of the search, which can be saved and run again in future, is known as a Query and is based on matching your search criteria with the contents of the fields in your table.

There are four decisions to make when designing a query:

• 1.Which fields will you be searching? For example, you could search the TOWN field for Birmingham, the RETIREMENT DATE field for later than 31/12/03 or the CAR OWNERSHIP field for Yes. It is very important that you use accepted wording, operators or numerical expressions if Access is to carry out the search successfully. There are a wide range of accepted expressions you can use when searching a database in Access. Here are a few of the most common:

• Which fields will you be searching? For example, you could search the TOWN field for Birmingham, the RETIREMENT DATE field for later than 31/12/03 or the CAR OWNERSHIP field for Yes. It is very important that you use accepted wording, operators or numerical expressions if Access is to carry out the search successfully. There are a wide range of accepted expressions you can use when searching a database in Access. Here are a few of the most common:

You will notice that, after entering your expression, quote marks appear round text, # symbols round dates but nothing round numbers. These symbols show that the expressions are recognised by Access.

• 2.What information do you want to see when the records are found? You can decide whether to display the first name, surname and full address of everyone living in Birmingham, or just their surname and telephone number.
• 3.Do you want the records displayed in any particular order? If so, you need to sort them when you design the query.
• 4.How do you want the query saved? Always name it carefully, so that it is clear what information will be displayed next time you run the same query to carry out a new search.

(You may not be involved in the design of large databases, but you should be aware that some fields could be indexed to speed up searching. Discuss this with the IT experts if you have problems carrying out lengthy searches.)

Designing a query

Here are the steps to take to search a Villa Details table for villas that sleep 6 or more people:

• 1.On the Queries tab, click Create Query in Design View having first made sure your underlying table of data is closed.
• 2.In the window that opens, select the table you want to search and click Add. Then click the Close button to close the Show Table window. If you click Add twice and two Field list boxes appear, select one and delete it or you will end up with very odd results.

• 3.Decide which fields to view when your records are found. Drag each one onto the grid in the lower window, or double-click to add them automatically. You can also click the next Field box on the grid and then click the arrow to select from the list of fields displayed. If you click the table name above the field list, all the fields will be selected. Drag one if you want to add them all to the lower grid.

• 4.To search for villas that sleep 6 or more people, the expression in the SLEEPS field needs to be entered as >=6. This must be typed at the bottom of the correct field column in the Criteria row.

• 5.To re-order the records, click the field on which to base the sort e.g. alphabetically by NAME and select ascending order.
• 6.To view the matching records that should now be found, you can either click the Run button or Datasheet view button on the toolbar.

• 7.If you don’t want to see the entries in the SLEEPS field, you can hide the display by clicking off the tick in the Show box. However, the field must be included on the Field row as it is your search field.

• 8.If the query doesn’t seem to be correct, you can return to the design by clicking the Design view button . Check that there are no spelling mistakes or other errors before running it again.
• 9.When you are happy with the query design, click the Save button and give your query an appropriate name. You can close the query and it will be saved on the Queries tab.

Regular searches

One common task might be to search your database on a regular basis e.g. to find contacts from various companies, clothes made of different materials or villas in various countries.

For a conventional query, you would need to design a new one every time the criteria i.e. the name of the company, material or country changes. You could then have a range of queries labelled ‘Cyprus Villas1, ‘Portuguese Villas’, ‘French Villas’ etc.

An alternative is to leave the name of the company or country blank, but type an instruction in square brackets into the Criteria row instead e.g. [which country].

Whenever the query is run, a box appears with the instruction to enter the unknown criteria – the parameter value. After typing in a country, the query will run normally and appropriate records will be displayed.

REPORTS

When printing an Excel database, you format the cells and print out the appropriate section of the spreadsheet. In Access, instead of changing the look of the table, you create a new object known as a Report. This can either include all the records in the full table or can display limited records based on a query.

Reports created automatically

For a quick report, you can use the AutoReport feature. On the Reports tab, click the New button and select an AutoReport style from the list. In the bottom window, select the table or query containing the records you want to display and click OK.

Wait a few seconds and a report will appear.

Reports using the Wizard

To have more control over your report, you need to design it step-by-step using the Wizard. This option is available on the Reports tab so double-click to start.

Step 1: Select the table or query on which to base the report and then choose which fields to include. The double arrow will add them all in the original order, but you can select fields one at a time and click the single arrow to add them to the right-hand pane.

Step 2: Select a field if you want your records grouped in any way e.g. by country, company, year etc.

Step 3: As well as a sort option at this stage, you can also introduce calculations. Click the Summary Options button and choose to total, average or display maximum or minimum values in any relevant field.

Steps 4-5: Set the orientation of the page and a preferred layout and appearance.

Finally, give your report a name and click Finish for a preview.

Customising a report

You can see fairly quickly that there is a great deal wrong with the above report. Some columns overlap, unnecessary wording has been added, some data has been lost and the prices have not been formatted to currency.

Fortunately, all these things can be corrected by clicking the Design button and going into the design of the report.

Controls

There are two main types of object in a report which are known as controls: the labels e.g. making up the title or headings, and the text boxes that contain data drawn from the database. Controls can be resized, moved and formatted so that, where required, you can give the entries more space and re-position headings and columns of data in the report. To drag controls, you need to move the mouse around until the pointer displays a black hand .

Click the button on the toolbar to draw a new box if you need extra labels anywhere on the report. You may need to de-select the control and then click it again to access the formatting buttons on the toolbar.

Delete unwanted labels by clicking and then pressing the Delete button. For example, you can remove the date and page number codes added by default as well as the text starting ‘Summary for’ & ‘COUNTRY’ . .. You can also amend the label Avg to something more meaningful such as Average Price. However, it is important to leave the actual formula =Avg[(MID)] intact.

Click the Preview button to check the changes you are making.

Format data

If you want to format e.g. numbers to currency or change the number of decimals showing, you need to right-click a text box control in the Design view and select Properties. Click in the Format box and select an alternative such as Currency from the list. You can then set the decimal places to a new number before closing the properties box.

LINKING TABLES

To search two or more tables, these must first be linked. For a link to be possible, both tables must contain the same field containing the same type of data. In one table it is the primary key field but in the second table it is referred to as the foreign key field as duplicates are quite acceptable. The second table would also have its own primary key field.

Imagine you have the following two tables of data: one contains details of your contacts in various companies and the other details of all your meetings. The structure of the tables would appear as follows:

Contacts table	Meetings table
Contact ID (Primary key)	Booking code (Primary key)
First Name	Date
Surname	Contact ID (Foreign key)
Position	Location
Company	Time

Adding a primary key

If you are creating a table, you can add the primary key to any field in Design view. Select the field in the Field Name list and click on the key symbol. Save this change and then return to your records by clicking the Datasheet view button.

To link the tables, you must click the Relationships button on the database window after clicking the Tables tab.

Add all the tables you want to link from the Show Table window and then locate the primary key field in one table (where it will appear in bold). Click its name and then drag it to drop over the foreign key field in the second table. A window will open showing the type of relationship and you can leave the default settings. Click OK and you will see that a dark line has appeared showing that the link has been made.

Save the changes and now, when you want to search across the tables, you can add both to the Query box and continue setting up a query exactly as you have done for a single table search. The Table row automatically shows which table each field comes from.

COPYING DATA

Access offers three quick ways to copy your data into other applications: you can create a text file in Word, a spreadsheet in Excel or initiate a mail merge again in Word.

Word processing

Select the table name on the database window, click the arrow next to the Office Links button and select Publish It with MS Word.

Wait a few moments and Word will open showing your table of data on the page. As the file is in a rich text file format (simpler than full Word formatting), you may prefer to save it with a new name as a Word document. Treat the data just like any Word table. (Sometimes the table is too big to fit on the page: if so, you may prefer to open the Access table, select and copy the data and paste it into Word in one or more blocks.)

Spreadsheets

Select the Analyse It option and your data will appear in a new Excel Workbook where you can treat it exactly as normal Excel data.

Mail Merge

Selecting the Merge It option will either open a blank Word document that displays the Mail Merge toolbar along the top of the page or will link the data to a file you can find on your computer. All the fields from your table will be available from the Merge Field button so that you can create a mail merge document directly. (Details in Chapter 3.)

IMPORTING DATA FROM ELSEWHERE

One very important task may be to combine or use data that is stored in non-Access applications such as Excel. You therefore need to know how to import data into Access. There is a wizard to help you, and you move through the steps by clicking Next.

• 1.Create a new database or open the destination file and click the Tables tab.
• 2.On the File menu, select Get External Data – Import.

• 3.When the Import window opens, make sure you search for appropriate files e.g. Excel spreadsheets or CSV (text) files. When the file is visible, click it and click Import.

• 4.When the relevant data appears, check that the column headings (new field names) are shown against a shaded background. (Before importing, it is a good idea to check that the column headings form the first row of the spreadsheet or other data file. If necessary, remove any titles and empty rows as you will otherwise end up with the generic field names Field 1, Field 2 etc. created by Access and will have to rename them yourself.)

In the next window, select the appropriate option – to add the records to an existing table selected from the drop-down list or to create a new one. If adding records, you must be sure the field names in the table and spreadsheet are the same or the process will not work.

You will be offered the option to delete or rename some of the fields and remove or change the primary key field. Finally, name any new tables before finishing the importing process.

Your new table will now appear in the database window.