Data management

Data

People use data all the time - people's names, telephone numbers, prices, objects' names, and dates. Anything that is information is data.

Database

A database is a systematic way to hold data.

If you use a word processor, you could say each file you produce is a database, or you could say that together all your files are a database. If you keep a name and address book for your friends, this is a database, as are lists of customers, or purchases.

With a good database it is easy to enter new information and it is easy to extract all the information you require. A good database also has the right physical form (notebook, piece of cardboard you can carry in your pocket, computer) and the right internal organisation (alphabetical list, date order, code).

Database management system

A database is a separate entity from the computer application that processes data and produces reports. A database management system (DBMS) sits between the computer program and the database itself.

data (eg text documents)

ÇÈ

database management system

ÇÈ

computer program (eg Access or Word)

Flat-field and relational databases

There are two kinds of database: flat-field, and relational.

Any two-dimensional table - of rows and columns - is a flat-field database. For example, a piece of paper on which you have drawn a list of names and addresses or a spreadsheet produced by Excel.

Relational databases appeared in the early 1970's and today are common. Relational databases are made up of groups of tables that you may think of as being joined by relationships. Records in two tables are associated together by having the same reference data in each table.

Microsoft Access database

A Microsoft Access database is one computer file. The file name will have the extension "mdb". Thus, we have work.mdb or people.mdb, or ideas.mdb.

Every mdb file has certain standard components.

Microsoft calls these components "objects". Database files always make provision for every component/object, but particular databases may not use each of these provisions. The basic object that you must always use is the table. You will also want to use forms, queries and reports. Macros are useful if you want to make efficient business databases.

Access database file components

Tables

Forms

Queries

Reports

Macros & Modules

An existing database

Look carefully at an existing database. Microsoft Access provides you with a sample database - the Northwind Traders database. It shows what can be achieved. To open the database: file, open, nwind.mdb. It may take you a moment to find the file.

Explore the databases by using the mouse to:

· Open and close tables, forms, queries, reports and macros

· Double click on a few pictures that you find in a table.

Database Design

Approach

There are two distinct actions needed to produce a new database. They require different skills. You must:

1 design, and

2 build.

The design work you do with a pencil and paper. Build at your computer.

Focus first on the basic uses of your proposed database - design for day-by-day use. Later, when the database is working, you will be able to develop it to provide management information and do routine tasks.

Process

To produce a new database you should proceed through these steps:

Step 1 Decide what must be stored in the database

Step 2 Determine the tables and fields

Step 3 Decide the relationships between tables

Step 4 Get the tables working

Step 5 Get data input organised

Step 6 Plan for the extraction of information

Step 7 Get queries and reports working

Step 8 Ongoing developments

Step 1: Decide what must be stored in your database

The goal is to list all the information that you need to store.

There are three ways of thinking about your database. You can either:

· Think about the inputs (information) that you want to key into your database. Think about the present form of the data - how it comes to you. There may be existing order forms or membership application forms.

· Think about what it is you want to extract from your database. What information do you want to get out of the system and how do you want it presented.

· Think about the logical form and structure of the data you want to store. Consider each individual "fact" or "piece of information" - classify it and say how it relates to other pieces of information.

You will probably have thoughts on each of these three perspectives, but experience suggests that the "logical form" is by far the most important perspective.

If you want to design a database for use by others, talk to the user groups before you make final decisions. Pool, and sift, their ideas on the database, what data it should contain, and its use.

Think about inputs:

The form in which information comes to you for data entry is important but it relates only to the input process (that is, later when you design forms). It does not relate to the best structure in which to store your data.

List all the data that you could enter: people's names, addresses, titles, dates, transactions (date, amount, purpose), stock (items, quantities, values), objects (value, colour, size, materials), numbers (of people, houses, items). The possibilities are enormous. You will use this list to consider the logical structure and form of your data.

Decide if every piece of information that is available is captured. Work on the assumption "better safe than sorry". You can review your tables after (say) a year and remove unnecessary data categories. However, if you are to pay people to enter data you may want to be more conservative.

Think about outputs:

Consider the purpose of your database. Some of the outputs you require (statistics and summaries) may be the main justification for your establishing your database. What are the questions your database must answer? List the information you want to receive. Examples include totals, averages, means, counts, high or low values. Again, your list will be useful when you consider the logical form and structure of your database.

Think about logical form and structure:

The nature, and form, of the information that you want to store in your database is very important.

You must identify, and name, each kind (or class) of information that you want to store - and decide its relationship to other kinds of information. This is a skill that you will develop with practice. For those mathematically inclined: you are trying to make discrete sets and subsets.

For example "customers" have “names” and “addresses” but "addresses" do not have "customers". Therefore, the set "customers" will have subsets "names", "addresses", "home phone", and so on.

But notice ‑ names and addresses relate to both "customers" and "friends". Now you have to make a decision - what is the key category (set) of information: customers or people? If your set is "people" then a subset may be customers/suppliers/friends. Sketch your ideas on paper and consider possible relationships between all the words/concepts.

Another example: Say you want to design a database to hold your household accounts - how do you decide what is its logical form and structure?

First, make a list of the inputs and outputs - transaction dates, amounts spent, who you paid, the addresses of firms, income from various sources, children's expenses, cost of clothes and cigarettes, who spent what in your household, what can you claim as a tax rebate, the balances of your cheque book, and the monthly balance of the savings accounts.

Now, identify those items that are subjects (important categories or sets) - these will be the base of your storage system (tables).

Finally, identify the items (facts about subsets) that are constituent of each subject.

A possible guide:

Look at the database wizard in Microsoft Access. (Start Access, file, new, tables, new, wizard.) You will find many examples of tables and their fields. However, be aware that these are only examples, and you may find it better to adopt other structures for your database.

Step 2: Decide the tables and fields

The goal is to turn the lists from step 1 into a structure of tables and fields.

Tables proposed:

The subjects/sets that you decided were your basic units now become your proposed tables. The subsets now become the proposed fields in the tables. Draw up a list then refine it by considering the following:

· tables should be tall and thin, not short and wide - if you propose more than five fields in a table, break the table into two

· there should be very few "gaps" in your tables - every cell should contain data

· no piece of information should be stored twice in your database

· exclude derived data (statistics, calculated numbers)

· data should be recorded in its smallest required unit.

Smallest required unit:

The "smallest required unit" is a piece of information that you may require separately at some time. You can always combine pieces of information later but it is difficult "to separate out" information once it is stored in tables.

For example, which of the following parts of a name do you want to store separately: surname, first name, middle name, middle initial, first name with middle initial and surname, first name with surname?

There is another important rule to consider here: no piece of information should be entered, or stored, more than once. Hence, if you record names in the form "Bill E Jones" you should not also enter and store "Bill" and "Jones". Try to save both keystrokes and disk storage space and give yourself maximum flexibility to extract information.

Should house numbers be separate from street names? Should unit or flat numbers be separated from house numbers?

The New Zealand electoral database keeps unit numbers, street numbers and street addresses separately. It also stores them in combination, unit/street number/street address.

In your projects you might like to record addresses as (say) three separate fields:

ADDRESS1 49 Sea Vista Drive

ADDRESS2 Pukerua Bay

ADDRESS3 PORIRUA CITY

But what about:

ADDRESS1 Flat 3, 21 Churchill Drive

ADDRESS2 PALMERSTON NORTH

ADDRESS3 [blank line/no entry]

If you enter this as two fields, leaving your last field blank, your last field ceases to be a list of cities. What are the implications of this? You might prefer to record:

ADDRESS1 Flat 3, 21 Churchill Drive

ADDRESS2 [blank line/no entry]

CITY PALMERSTON NORTH

The rule is: store data in its smallest discrete parts. Thus, you may prefer:

UNIT/FLAT 3

STREET NUMBER 21

STREET Churchill Drive

SUBURB [blank line/no entry]

CITY PALMERSTON NORTH

Real estate and direct marketing databases usually need the maximum amount of information recorded in separate address fields. I you are going to call on customers you need to order them by street and then by house number. You may need to identify everyone who lives in a unit.

Utility tables:

You may want to have a table just for values needed in calculations. A "Utilities Table" may hold dates, the value of GST (sales tax) and bank interest rates. "Draw off" these figures later when they are needed in calculations. The advantage of this approach is that changes to these values will be automatically carried into your calculations (compared with what will happen if you type them into your queries).

Step 3: Decide the relationships between tables

The goal is to tell the machine how your tables relate to each other.

To enable the program to efficiently use data drawn from different tables, you establish ties between your tables. Thus, each table usually has a field in common with at least one other table (see "Primary Key" page 26).

Records in two tables may be related in three different ways. Thus, there are three kinds of relationship:

· One-to-one (not common)

· One-to-many (common)

· Many-to-many (not allowed in Microsoft Access)

In a one-to-one relationship records in two tables are directly related. This can usefully break one wide table into two narrow tables.

Microsoft Access does not allow you to set up many-to-many relationships. To achieve this effect you need to have a "link" or "mediating" table.

Most of the relationships you establish will be one-to-many. For example, one salesperson will have many clients.

Your task is to select in each table the unique identifer. This is the "key" field and its information will be duplicated in another table (where it is called the "foreign key").

If you cannot identify an obvious unique identifer in a table, add a counter field (see page 26). In business databases use order and invoice numbers key fields. This will help integrate your database with other records.

Step 4: Get the tables working

The goal is to get the basic tables to receive and display data as you wish and to have appropriate field structures.

Establish the tables (see page 23 onwards). Enter about five lines of data in each table, format them and print them. Now review the contents of each table against the criteria that emerged in steps 1 to 3. It is important that the tables work properly because other things depend on them. Once you enter large amounts of data it is difficult (but, often possible) to alter fields. If you are not satisfied it is often easier to build again from the beginning.

Step 5: Get data input organised

The goal is to provide an efficient means of data entry.

You will be able to collect data when the input forms are working. In a business you will probably base your Microsoft Access input forms on the current paper forms that your staff use. If you are starting from scratch you should design your form on a piece of paper. Set it out in a logical way considering what people do when they fill out the form. Many people naturally put the date first, then the customers name and then the item and finally the amount. Do not be too creative. Keep it simple.

The chapter on forms describes the mechanics of establishing a form.

Finally, the rule is: no piece of data should ever be entered more than once. Design forms to minimise the number of key strokes.

Step 6: Plan for the extraction of information

The goal is to provide for the extraction of information and ease of use.

Once you have some tables and forms working you can plan for the extraction of information. Decide the questions that you want to ask: How many customers? How many goats? How long between order and dispatch? How busy is each month? What is the sales tax to be paid? What does electricity cost us every month? Electricity and gas together? What labels do we need to print? Who lives in Wellington? Set your questions in order of priority. What is needed straight away?

Step 7: Get the queries and reports working

The goal is to provide for the extraction of information.

Most of the questions you want answered will require that you write a query or design a report, or both. See the chapters on queries and reports for the mechanics.

Get each query and report to work before you proceed to the next function. You will find, however, that perfection is not a sensible goal. Do not be too ambitious. Set yourself limited practical goals and get that part of your database operating before you try anything else.

Step 8: Ongoing developments

The goal is to improve your database.

The task of improving your database may never be complete. Your skills will improve after you have constructed two or three databases, and you will be able to use your new skills to improve databases that you first developed. However, always remember it is more difficult to alter tables when they hold vast amounts of data.

Key specific checks to carry out include:

· Does every field have plenty of data?

· Is there any duplication of data entry or storage?

· Is there any important information not being captured?

Drive Microsoft Access

Understand relational databases, and how Microsoft Access constructs databases, and you will quickly learn to drive the program.

Versions of Microsoft Access

This guide gives instructions for version 2, 7 and above. The improvements in versions are significant for beginners. Between versions 2.0 and 7.0/8.0 improvements to note are:

· a further way to create a table - produce am Excel spreadsheet and have the program construct the table

· a report wizard that often makes unnecessary to create a query

· a useful query wizard

· an improved form wizard

· auto-format for reports

Help built into Microsoft Access

Your first task should be to master the various forms of assistance built into the program.

At any time F1 gives you "help". It brings you directly to the information that relates to the screen before you. Habitually, use F1.

Sequential instructions to achieve tasks are given in the cue cards but people find them cumbersome. Their font is too small.

Often there are several ways to achieve the same task and this can confuse beginners. You might decide to focus on drop‑down menus initially.

The basic components

The basic components of a Microsoft Access database are tables, forms, queries, reports, macros and modules. Let us begin by considering the basic structural parts first and briefly.

Tables

Tables are composed of data that is presented in two dimensions. Each line in a table is called a record. Headings across the top of the table are called field names and the columns of data are called fields.

A good table will be about only one topic. Topic (and thus table) definition is a major challenge when you design a database. Tables are the essence of any database. Try to understand table structure and build good tables for much depends on your tables.

Tables comprise the fundamental building blocks of any database. If you're familiar with spreadsheets, you'll find database tables extremely similar.

Take a look at this example of a table from the Northwind database that is provided by Microsoft as an example.

The table above contains the employee information for our organization -- characteristics like name, date of birth and title. Examine the construction of the table and you'll find that each column of the table corresponds to a specific employee characteristic. Each row corresponds to one particular employee and contains his or her information. That's all there is to it! If it helps, think of each one of these tables as a spreadsheet-style listing of information.

Forms

Forms provide a convenient way to enter data into your database. They set out on the computer screen the data that is to be entered. One form can accept data that will go to different tables.

Queries

If you want to see data that is stored in more than one table, you use a query. Each time you run your query the machine will take current data from your tables and produces a special table for your use.

Reports

Reports are printouts of data. They summarise the information that you want. Reports can include company logos and pictures. Mailing labels are printed as reports. Every time you run a report the machine will take the current information in the tables and construct your report.

The database window is your home base. It lists the parts of a particular database.

Tables

Tables are the basic building blocks of your database. In this chapter you learn about tables themselves. You need to learn about:

1 Navigation in a table

2 Changing data within fields

3 Manipulating fields

4 Making tables with the wizard

5 Altering field properties

6 Importing and exporting data

7 Links between tables

8 Keeping your data consistent between tables

Navigation in Tables

Open a database (for example, nwind.mdb). From the database window select the object "Table" and investigate several tables. Double click to get them open.

The following are some of the more useful shortcuts:

Key ... Will take you to ...

Tab or right arrow

or enter the next field

End the last field in a record

Home the first field in a record

Ctrl down arrow the current field in last record

Ctrl end the last field in last record

Use key ... To ...

F2 toggle navigation and edit modes

F5 go to a particular record

F6 move between screen sections

F11 see the database window

Ctrl ; insert today's date

Ctrl : insert current time

Ctrl ' repeat record that is above

Ctrl - delete a record

Ctrl + insert new record

(move to blank record)

Shift spacebar select current record

You must know how to navigate using the mouse and the bar at the bottom of the records.

Find data

Ctrl F will bring up the "Find" dialog box. Make certain you check your preference in the "Search in all fields" option. If you are likely to wish to find the same information more than once you should set up a query.

Enter, edit and delete data

You may enter data directly into tables, just as you can with a spreadsheet. There is always a blank record at the end of every table. Enter data at any time by moving to this blank record. Use Ctrl + to move to the blank record.

Practice adding data to a table, remove lines (records), alter data, and delete tables. Practice navigation using both the mouse and the keyboard. Drop-down menus give you information on the keystrokes.

Counter fields cannot be edited. They are useful to set as unique identifiers.

You cannot enter information in some fields if the data is not in the form required. This relates to the presence of a "mask" that controls what may be entered in a field. For more information, see under the heading "Field properties".

Add, delete or move a field

Using the icons on the toolbar (look to the extreme left) learn to move between "design view" and "datasheet view".

In design view, go to the field above where you want an add a field and key "Insert". "Delete" will remove a field.

To move a field - datasheet view, select the field (mouse click onto the top plane next to the field name), click the selected field and drag it to the new position. In design view the field will still appear in the old place.

Format fields

Double click on the lines between field names and the size of the fields will be set on the basis of the data that they currently contain. Practice other approaches to formatting using the drop down-menu.

Construct tables – for beginners

Open a new database - Tables, New. Give a name when prompted (up to 64 characters long). The .mdb extension will be added automatically.

Construct tables using the table wizard. It is possible to construct tables in other ways, but use the wizard until you become proficient with Access.

Note that in the table wizard you can change the names of fields. Give fields your preferred names drawing upon your design decisions.

When you are more proficient construct tables without the wizard. In design view, enter the names of the fields, data types, and other properties. Use F1 help as a guide.

Primary key

To speed up the functioning of Microsoft Access, each record (line) in a table must have a unique identifier. The primary key is the unique identifier. Set the primary key when you create a table. .

In any table you can set a counter as your primary key - each line in the table will then be numbered. If you remove a line, the number goes with it: hence, the counter may not tell you the number of records in a table.

When a primary key has been set for a field, Microsoft Access will not allow you to enter data in that field unless it is unique. Nor will you be allowed to leave the field blank. It is possible to have more than one field set per table, but do not do this. A counter is a good primary key when you are learning Access, but invoice numbers, receipt numbers, date codes, and computer logon ids, can all be useful.

To set the primary key in the wizard, allow the wizard to do the job. Otherwise, in design view, place the cursor on the field you want and hit the golden key button on the toolbar.

Field properties

You should customise the properties of fields in any table you produce, including those produced by the wizard. Using the wizard has the advantage that your new table will work, but you can often improve on the wizard's work. To improve the table you need to know about the properties of fields. Each of the fields in a table has specific characteristics that are called the "properties".

Data types

Of prime importance is the definition of the type of data that will be entered in the field. There are eight different types of data and the types of data have specific sets of field properties:

Counter Starts at 1 and counts each new record

Text Up to 255 characters ("letter to the editor" length)

Memo Up to 64,000 characters (a short book)

Number Virtually any number

Currency $ sign in front, and up to 4 decimal places

Date/Time Stored as numbers

Yes/No Stores yes/no, on/off or true/false

OLE Object Sound, picture or animation

Each type of data is associated with particular properties. You will become familiar with the properties as you use them. You do not need to remember the properties because F1 opens a very useful help screen. (It is worth noting how "help" is organised because you will be making use of it from time-to-time.)

Instruction: Open your new table in Design View. Practice the toggle F6. Now work through the fields looking at the field properties for each. As you work through the field properties read the useful blue description that comes up beside each.

You can change the size of each field. The database file you construct will reserve space for each field according to its size. The space is always reserved even when it does not contain data. Therefore, you should minimise the size of the database file by reducing each field to the maximum size that you think you will need. If you get this wrong, you may later increase the field size.

Number fields

You have a selection of five formats:

Format

Range

Notes

Bytes

Byte

0 to 255

No negatives

Decimals rounded

Integer

-32,000 to + 32,000

Decimals rounded

Long integer

-2,000,000,000

to +2,000,000,000

Decimals rounded

Single

-3.4 to +3.5 E38

Max 15 decimal places

Double

-1.7 to 1.7 E308

Max 15 decimal places

& 1.7x10³⁰⁸

Telephone numbers

Set these as text, not numbers. Select the number of characters you want.

Add and delete fields

In design view, click on the left margin of the field below where you want the new field, press insert, fill in the blanks (F1 helps).

Import data

Data in many file formats can be imported into Microsoft Access. Select the menu option File, Import. Follow the prompts. In version 7.0, use the table analyser wizard to split a large table into two, or more, smaller tables. You should always do this if you import large Excel spreadsheets.

Export data

There are several ways to export data:

· To Excel or Word, use the logos on the toolbar, follow the prompts

· Into a new file format (that is, not a .mdb file), select file, export

· To move data between applications by using copy and paste

· Version 7.0 and above users can drag-and-drop data from datasheet view into Word or Excel

· Version 7.0 users and above can save highlighted data in Rich Text Format.

Relationships between tables

To this point we have considered tables as separate entities. Now we look at how your database enables all the tables to function together.

Recall that the primary key provides a unique identifier for each record in a table. Now you may use these fields to make links between different tables. These links are called "relationships".

Relationships are important for two main reasons. First, they speed up the functioning of Microsoft Access when you run queries and construct reports. Second because they promote the integrity of your data.

There are logically three types of relationships: one-to-many, one-to-one, many-to-many.

One-to-many

Here one record in a field relates to many different records in another field that is in another table. For example, each customer in the "Customers Table" may relate to many different "purchases" in the "Purchases Table".

One-to-one

This breaks large tables into two. You may want to do this to avoid blank cells or simply to make your tables more manageable on the screen.

Many-to-many

To set up many-to-many relationships you need a "link table". This establishes two sets of one-to-many relationships.

Data integrity

You do not want to delete a customer from the "Customer Table" when there is still a bill to be paid in the "Invoice Table" and no receipt in the "Receipts Table". Nor do you want to refer to a salesperson in your "Orders Table" when that person does not appear in your "Employees Table".

What happens when Miss Jones becomes Mrs Smith? You will want to change her name in the "Customer Table" but what about her dozens of purchases in the "Sales Table"?

Fortunately, Microsoft Access will take care of these problems for you by making certain that entries (data) in different tables are consistent - that is, it can maintain referential integrity for you as you add or delete data.

Create relationships

There are two types of link that you can create between tables: relationships, and relationships with referential integrity.

It is best to let the table wizard create relationships between tables as you establish each table.

However, to establish relationships manually, in the database window: edit, relationships, use the toolbar icon to show the Add Tables facility, add the tables you want, close the add table window, drag and drop the fields bringing together those that are common to two tables. Keep going until all tables are linked.

A simple line will be shown in the relationships window to indicate each link. You can delete the link by clicking on the line and hitting the delete key.

But note you:

· Must select the primary key field for the "one" table, and

· Must drag and drop from the "one" table to the "many" table.

Finally, click the "create" button and the relationships window will show you that referential integrity is set on a one-to-many relationship by adding the number 1 and the infinity sign to the join line.

Keeping data consistent in different tables

To ensure consistency in different tables, you can arrange for the machine to adjust data in several tables when the data in one table is altered. You may have this feature work when data is updated, or deleted, or both.

If Miss Jones ceases to be a customer you may delete her from the "Customer Table" and Access will simultaneously remove all references to her from the "Sales Table". If Miss Jones marries and becomes Mrs Smith you only have to change her name once - say, in the “Customers Table” - to have her name changed many times in the “Sales Table”.

To use this facility check the appropriate boxes in the relationships window after you select "Enforce Referential Integrity".

Construct tables versions 7 and 8

Use the same approach - using the wizard - as outlined for version 2.

However, you also have then ability to enter data into a table and let the program decide the properties of your data. To use this approach, open a blank datasheet and enter a sample of your data. When you close the datasheet Microsoft Access will analyse your data and assign a data type and format to each of your fields.

Forms

A useful feature of Microsoft Access is the tight control it gives over data input. If you have staff who input data for you this may be very important.

With good design, forms will speed up data entry, improve the accuracy of the data entered and appear attractive. You can also show on a form, data drawn from tables. For example, say you type "shoes" on a form that records orders, and then the price of "shoes" can appear (this data being drawn from a table).

Produce forms

There are three ways to produce forms. You should use the form wizard - select the table that the new form will be based on, click the Form Wizards button, single column, and follow the steps.

Modify forms

Modify your wizard-produced form to make it more attractive and more functional. Modify it in design view and see the changes in form view.

Combo and list boxes

A combo box is a drop-down list that assists data entry. A list box is similar, but the list is permanently displayed.

In design view -

1 mouse click to select the field that you want to turn into a box

2 delete the field using the Delete key

3 click the field list icon, to show the fields that you may select

4 click on the toolbox icon, to show the toolbox window

5 click the wizard button on the toolbox to "on"

6 click the combo box button on the toolbox (or the list box), drag the field required from the field list onto the form and select its position

7 follow the wizard's instructions.

Fonts and colours

Make forms easy to use and attractive. Set them out in a logical way. In design view you can move the fields around, separate names from data entry spaces, change fonts, and add colour to all parts of your form. Add colour by bringing forward the colour palette.

To move fields around you use drag-and-drop techniques with the mouse. The hand that appears will move both the field and its label simultaneously. You will need to make the spaces large enough to hold the larger font sizes you are likely to select.

Create forms, versions 7.0 and above

Use AutoFormat to enhance your form. Also, use the formatting toolbar in design view and the drop-down menus.

Lists taken from other tables, versions 7.0 and above

Your form can include a list imported from any table. In design view, specify the lookup wizard as the data type for a field. The field will appear in your form with whatever data you have in the table. You may show the data as either a combo or a list box.

Queries

Use

Queries enable you to ask questions of your data. In business databases you will seek information through queries rather than by looking in tables.

Queries can:

· Select fields and records

· Sort fields and records

· Calculate.

When you construct a query you select the fields that hold the data that interests you, and you set the criteria that will select particular records from those fields.

The information brought together by a query is called a dynaset. "Dynaset" means a dynamic (changeable) set of data. The dynaset is not stored in your database, instead the program constructs it each time you run the query. The query is totally dependent on the data in your tables. Change the data in a table and you change all the queries that call upon that table.

Shift F9 will rerun a query at any time.

Kinds of queries

There are two groups of queries: view queries and action queries.

1 View queries select data, analyse it and report on it. They do not modify the data in tables. There are several kinds of view queries to consider:

· Select query - the usual query that you will use

· Parameter query - a select query that allows you to specify new criteria each time the query is run

· Select query used to find unmatched records from two tables

· crosstab query - summary analysis of data

· Summary query - summary analysis of data

A view query runs when you select either the datasheet button or the run button on the toolbar (compare with action queries).

2 Action queries modify the data in tables. There are several kinds:

· make table query - creates a new table from data in other tables

· append query - copies data from one table to another

· delete query - deletes data from a table

· archive query - stores data (in version 7.0 use the make-table query)

· update query - a find and replace function

The database button will give you a preview of an action query. An action query is run when you select the run button on the toolbar (the exclamation mark). Be warned.

The query design view icons on the toolbar provide for all the different kinds of queries, except the parameter and unmatched record queries. These are kinds of select query, and are reached via the select query (version 2).

To construct a select query

The query-by-example (QBE) grid is a box where you place the information that relates to your query. It consists of the following:

· Field - table field name, or an expression

· Sort - up or down, use the drop-down menu. To sort on several fields, put the columns in the order of your subsort (eg surname, first-name).

· Show - hide fields you do not really need to see in the dynaset

· Criteria - either rules for selecting data or type in the text (in square brackets) which will be the prompt for a parameter query

· Or - another set of criteria that will be also used along with the first.

F6 moves you between the QBE grid and the field lists (upper screen).

Do not to use the wizard in version 2.0 to create ordinary (select) queries. However, in versions 7.0 onward there is a useful query wizard.

To build a new query: from the database window, query, new. Then, use the Add Table dialog box to reveal the tables that hold the fields you want. Use the mouse to drag-and-drop field names into the field part of the QBE grid.

Note: drag the * down and all the fields in the table will appear. Should you later change the table's fields the changes will appear in your query. However, you cannot sort, or specify criteria, for fields added in this way.

Having added the fields you need, decide which you should hide (by clicking on their boxes) and decide the order in which you want the fields to appear in your dynaset. Highlight and drag fields into your preferred order. Change column widths as required, just as if you were adjusting a table.

You can rename the fields in your query to make their names more intelligible. In design view, click to the left of the name. Type the new name and a colon (:). Leave the proper fieldname in place.

To define the records you want to select you will use the criteria cell. Just set the rule you want used when Access selects from all the available data. Use symbols to set your instructions, as follows:

Symbol ... Means ... Examples ...

= Equals =Auckland

=156

< Less than <24

> Greater than >12

<= Less than or

equal to <=13

>= Greater than or

equal to >=44

<> Not equal to <>"Smith"

Between ... And Between the values Between 66

And 98

Like Pattern match Like "34*"

Not like Pattern match Not like "B*"

Not like "876*"

In() In a list of values In ("iwi", ...)

To include calculated data in a query, add a new field called a "calculated field". In an empty field put the field name in brackets () and show what you want done to the values in that field.

Use ... To ... Examples ...

+ Add (Price)+0.20

- Subtract (Value)-(Price)

* Multiply (Price)*.05

/ Divide (Cost)/2

Some operators need only be followed by the field name in brackets, eg Avg(Cost). These are called "aggregate functions", and they include:

Avg Average

Count Number of selected records

Max Highest selected record

Sum Total of selected records

To use the aggregate functions, click on the toolbar totals icon. A total's row will appear in your QBE grid and you can use the drop-down box to select the particular function required.

Build your query piece-by-piece, and view each part as it is added to see if it works. Hence, shuffle backwards and forwards between design view and datasheet view. When the query fails, delete the new part and rebuild.

Query wizard, versions 7.0 and above

The query wizard works well in versions 7 and 8. Use it for select queries.

Parameter query

If you often run the same select query but with different criteria, you will save time by building a parameter query. Every time you run this query you can easily change the criteria (parameters/scope) that will apply to a field.

For example, a select query may select data between two dates, or, all the customers who live in Auckland. A parameter query will enable you to change the dates, or the city, every time you run your query.

Practice constructing a simple parameter query: in the QBE grid, clear the criteria box for a field that contains a list of names (eg cities). Type there, in square brackets, the prompt that you want to see each time you run the query. Eg [Enter the city name]. (To make this easier Shift+F2 will give you zoom.) Now, run your query by clicking datasheet view. At the prompt, type in a criteria (eg Christchurch) using the correct format. Click OK. Practice using the wild cards * and ?. Also, use parameters to select a range of records. Try this criteria on a date field "Between [Enter the start date] And [Enter the end date]".

In your applications you will want to ensure that the data you enter in parameter boxes is always of the right kind. Define the data type for the parameter. From the query menu choose parameters and adjust the query parameter dialog box.

Find unmatched query

Sometimes you end up with data in a table that does not have the corresponding entry in another table. For example, you may have customers in your Customers Table but no orders for them in your Orders Table. You may wish to identify such situations.

There will normally be a relationship established between the two tables. When such a link is established, Microsoft Access will accept data into the "one" table, but not into the "many" table. For example, if there is a link between the Customers Table and the Orders Table, you may enter customers who do not have orders, but you cannot take orders for customers who do not exist.

Use the query wizard and select the Find Unmatched Query. You are prompted to select first the table that you see as complete (that is, it has all the records that you want to have displayed). Then you select the table that has the inaccurate or incomplete data. Note: if the tables are already linked Microsoft Access will select the linked tables for you.

Make-table query

You can copy selected records from tables and place them in a new table either within the same database or in another database.

Create a normal select query with the records you want in the new table. Then turn your select query dynaset into a table by clicking onto the make-table button on the toolbar. Naturally, you are asked to name your new table and say what database will hold your new table.

Crosstab query

This is a query that summarises data in a spreadsheet form. To produce this statistical information, you must specify at least three fields: row heading, column heading and data. For example row heading Customers, column heading Sales people, data Totals.

Use the wizard to create crosstab queries. Experiment.

To archive, delete, or change data

Use the query wizard each of these actions. But remember, some actions cannot be undone so you must preview before you run your query. In version 7.0, use the make-table wizard to archive.

Reports

Use

Reports can group data, present groups separately and perform calculations. You can print data from tables, forms or queries. Reports produce attractive pages that you may use as invoices, purchase orders, and mailing labels. A further advantage of reports is that they can summarise, subtotal or compare data.

Reports are generally based on queries in version 2.0. However, in version 7.0 the report wizard has a field picker that makes it easy to select the fields that you want in your report. The wizard will ask you what summaries you require and organise these for you.

Types of reports

The wizard gives you three options:

· Single-column reports- a simple header and footer, with the data listed in a column

· Group reports - data from the fields you select appears in a single row across the report. Subtotals are available for all numeric fields

· Mailing labels - sized based on some standard options.

Create reports

You should use the report wizard to create reports. So you might go F11, reports, new, and then select Report Wizards from the new report dialog box.

The report wizard will offer you choices and show you what is the effect generated by the choices available. When you have completed your selections, see the report in design view and then in print preview.

You may alter an existing report: use the sorting and grouping button on the toolbar. You can alter fields, expressions, and sort order and group properties.

Pictures in forms & reports

Embedded objects

You will want to include your company logo on invoices and statements you send to clients. You may also want to put graphs and pictures on forms. Databases with employees or products sometimes include pictures. Student records in schools often include their photographs. In Microsoft Access speak these are called embedded objects and there are two kinds of embedded objects.

Bound and unbound objects

There are two kinds of objects, and they are handled slightly differently:

· bound objects - the information is stored in a field in a table

· unbound objects - the information has no link to a table (eg picture files brought in from different applications)

You will make use of your toolbox when you insert objects. Tools in the toolbox include:

· Text box - a bound box for words or a calculated value

· Label - an unbound box for words, for example headings

· Combo box - see under heading "Combo and list boxes"

· List box - see under heading "Combo and list boxes"

· Bound object frame - to insert bound objects

· Object frame - to insert unbound objects

· Command button - to set up a button to run a macro. See "Customising for users", "Buttons"

· Graph - inserts a graph.

Add pictures: bound objects

To create a bound box, click on the toolbar's field list button. Select the fields you want and drag-and-drop these to the place that you want them to appear in your form or report. Often you will need to make space for the boxes before you drag them in.

Add pictures: unbound objects

To construct the objects (pictures) to be added you will need a Microsoft application that supports Object Linking and Embedding (OLE). This includes Microsoft Paintbrush and PowerPoint. Most graphics programs can produce and edit bitmap picture files that are compatible with Microsoft Access.

Apart from pictures, you will use unbound objects to bring in text, such as headings or instructions.

Establish an unbound object in design view by clicking on the item in the toolbox - then, drag‑and‑drop it into place on your form or report. Add what is necessary to complete the job.

Add a graph

A graph, based on data in your tables, is just a form of bound object. Microsoft Access has a wizard to help you when you want to design a graph.

Make room for your graph on the report or form. Then click the graph tool in the toolbox. Place the graph control in the blank space you created for your graph. The GraphWizard should pop up.

Use simple forms of graph - they look better and convey information more clearly. Ordinary bar or line graphs are best in business applications and you should look for opportunities to use graphs.

Image control in version 7.0 and above

Click the image control button in the toolbox and the location on your form or report where you want an unbound object to appear.

Calculations

Expressions

Often we need to know averages, percentages or totals. Calculated information may appear in reports and forms. Tables differ from spreadsheets in that they do not have calculations. Queries can also produce calculated figures but the technique is different from that described here.

Microsoft Access uses expressions to make calculations. Expressions are calculated each time you preview or print a report or view a form.

Expressions usually appear in unbound text boxes. An unbound text box is an object that is not linked to a field. You embed the object in your report or form.

With a form in design view, make a space where you want the information to appear. Click the text box button and then click in the place where you want the calculated information. View the properties of the text box and enter your requirements: name the control (the source of the data), define the calculation (begin with =), set the format (usually currency), and give the caption heading of the associated label. Now, save the form.

To calculate the... Use the expression ...

Sum =Sum[(fieldname)]

Total cost =[quantity]*[price]

Average =Avg[(fieldname)]

(Use F1 to gain a list of possibilities.)

Expression builder

One way to produce an expression is to insert it into the Control Source property using an expression builder. The expression builder has many common expressions ready for use.

Macros

Macros automate processes - they store keystrokes that you can use again-and-again. Each macro lists one or more actions that you want performed. To construct macros in Microsoft Access you select the actions you want from a list provided. Usually you will use by drag-and-drop methods with your mouse.

You may want to construct macros that will run from buttons that you construct in forms.

To create a macro, from the database window: macro, new. Instructions such as "open this form" can be achieved by simply dragging the forms name into the macro design view.

Customise for users

If your staff uses your database you may wish to simplify its presentation, and keep them away from some areas of the program. A control panel will help you with these tasks, but before you can establish a control panel you need to know how to create simple macros, so master macros first.

Control panel

A control panel may be opened when you open a database, instead of the display of database objects. The control panel is a form with buttons that lead the operator to specific database objects (forms, reports or tables). Behind the buttons are macros.

The macro that plays automatically when the database is opened is called the autoexec macro. Construct it like any other macro. To avoid it, hold down the shift key when you open the database.

Buttons will run macros when you click on them with your mouse. Each button you establish will be likely to have a text box next to it that states the purpose of the button. On the button itself will appear the name of the macro (unless you change it in the properties box).

Build the macro before the button. In forms, design view, click to the button-making tool in the toolbox. Place the cross where you want the button, click and a wizard will appear. Answer the wizard's simple questions.

Retreating from control panels

When you construct a control panel, make certain you build a button that takes you out of the control panel. Call it "Stop" or "Close".

Store obsolete data

You may want to adjust your database to more accurately reflect current business circumstances: For example, you may wish to:

· Separate out the information on orders cleared

· Archive information on earlier tax years

Use a query to construct a new table that will hold data selected from existing tables.

The query wizard enables you to take data from tables and delete it, or export it to an archive table. This archive table might be stored on floppy disk or zip drive. In versions 7.0 and above you can build archive tables without using queries.