Access is Microsoft's powerful relational database that is bundled with professional versions of Office, or is available separately. The following tutorial covers Access97 onwards and refelcts the main areas of knowledge necessary to create a relational database with confidence.
Access (like Lotus' offering Approach) is a Relational Database Management System (RDBMS). It is used to store and manipulate large amounts of information. It is user-friendly and has a powerful development environment for those wanting bespoke solutions.
A database is an organized list of information that can be queried easily. For example, a telephone directory is a database. Locating a particular telephone number is both easy and quick as names of persons and organizations are listed alphabetically in the directory.
Access is an object-oriented program where even the application is treated as an object. Each object has properties that define how it looks and performs.
In Access, all objects are stored in a single file and the filename has an extension .mdb. The database file is managed through the database window.
At the top of database window are the tabs representing each of the six main objects of Access. Selecting a tab switches the window to reveal a listing of the current object under the selection.
On the right side of the window there are three buttons.
The top button is labeled Open, Run or Preview, depending on which tab is selected. Clicking on the Design button opens the Design view of the selected object so that the user can modify its structure and properties. Clicking on the New button starts the process of creating a new object of the selected type.
All data is stored in tables. Every table in Access usually contains information about some particular subject. A table consists of rows and columns. Each row is called a record, containing information about one instance of that subject. Each column contains a discrete element of information called a field. For example, there is a table client, in which information about a particular customer is stored in rows or records and the varied information about that clientt is stored in the columns or the fields.The field could be the name of the client or personal notes about them.
A query is a question that you ask of the data stored in the tables of your database. For example, you can create a query that asks for only those clients in the Postcode area L14 or members of staff who have birthdays in the current month.
MS-Access also has special queries called action queries that perform bulk update on the data. For example, action queries can find and update records of those clients who have paid.
A user can use action queries to update, append or delete records in tables to construct new tables from the results of the query.
Present the table or query's data in a format that is easy to view or update. Records can be added, edited or deleted with the help of the form.
Create the interface portion of an Access application. The user can, for instance, create forms called switchboards that help navigate to other forms and functions of the application.
Users can also create forms to use as dialogue or message boxes or forms on which information can be entered. The user can then click on a button that dynamically creates a report or query based on the information.
With the help of report tools the user can create and print reports which can incorporate fields from one or more tables. Report wizards exist although they might not provide exactly what you want, adjustments may be made in the Design view.
An Access report can be more than a basic row and column format. It can be a catalogue of products, mailing labels, graphs, or any other form that can take advantage of the what you see is what you get (WYSIWYG) capabilities of the Design view.
Macros provide an easy and effective method for automating many database tasks. Users can use macros for tasks as simple as displaying message boxes to validate data entered into a record before it is saved. Macros are composed of actions. In Access, users can choose from 49 actions. A macro can be created by listing the actions in the order that a user wants them to be performed.
When forms and reports are designed, each object that is placed on them possesses event properties. An event is something that can happen to an object - e.g. a form has a 'Before Update' property that gets triggered just before a record on the form is saved. If the user places a macro in the Before Update property of the form, the macro will run right before the current record of the form is saved.
A macro can be as simple as one which displays a message like "Hello, select a category" or as complicated as that which validates the salary given to a member of staff on the basis of various allowances and deductions.
Modules are the containers for any programming code written in an Access database. The two types of modules are global and form or report.
Global modules are listed in the database window. The code that is stored in these modules is available everywhere in the application, hence, the name global.
Every form or report that is designed has its own module that can store the code. If the form is imported or exported from one database to another, the code travels with it. This code is only available when the form or report is open and, even then, can only be called from the form or report in which it is stored. Access uses the Visual Basic for Applications (VBA) language for these codes.
When working with Access, users can create and use multiple tables to help reduce redundancy in a database. For example, to track client sales, you have to repeat the client information, such as name, address and class details for every record. In Access, you can create a table to store client details and another to store transaction details.
In a clients' table you can create a field, called a primary key, that uniquely identifies each clientt. In a sales table, you need to store only the primary key of the client from the clients' table to access that data.
You can use a database wizard to create the required tables, forms and reports for the type of databases you choose in one operation.
You can create a blank database and then add the tables, forms, reports and other objects later. This is a very flexible method, but it requires you to define each database element separately.
Either way, it is possible to modify and extend your database at any time after it has been created.
Switch to the Database window. Press F11 to switch to the Database window from any other window.
Click on the Tables tab, and then click on New.
Double-click on Design view.
Define each of the fields in your table.
Define a primary key field before saving your table.
When you are ready to save your table, click on the Save button on the toolbar, and then type a name for the table.
In the Field Name column in the top pane of the table Design view, you can type the name of the field you are using in your table.
The field name can have as many as 64 characters and can contain any characters, numbers and spaces except for the period (.), a grave accent (`), square brackets ([ ]) and the exclamation mark (!), since these characters have reserved meanings in conjunction with filenames in Access. Leading spaces are also not allowed.
The datatype of a field determines the kind of data the field can store. You can use the Datatype property to specify the type of data stored in a table field. Each field can store data consisting of only a single datatype. The datatype property uses the following settings:
Setting
Type of data
Size
Memo
Combinations of text& Numbers. Lengthy text or Combination of text & Numbers.
Up to 65,535
Number
Numeric data used in Mathematical calculations.
1,2 ,4 or 8 bytes
Date/Time
values for Time. The year 100 through 9999
8 bytes
Currency
Currency values and numeric data used in calculations involving data with one to four decimal places.
8 bytes
AutoNumber
A unique sequential number or random number assigned by Access whenever a new record is added to the Table.
4-16 bytes
OLE Object
An object such as a MS Word Document
up to 1GB
Yes/No
Contain one of 2 values (0 and 1)
1 bit
Hyperlink
A 3 part address to another document or web page
up to 3 x 2048 characters
Look Up
Creates a field that allows you to choose a value from a list box or combo box.
Each field has a set of properties that you use to customize how a field's data is stored, handled or displayed. For example, you can control the maximum number of characters that can be entered into a Text field by setting its Field Size property. You can set a field's properties by displaying a table in Design view, selecting the field in the upper portion of the window and then selecting the desired property in the lower portion of the window.
The properties that are available for each field are determined by the datatype you select for the field.
Field size
You can use the Field size property to set the maximum size for data stored in a field set to the Text, Number, or AutoNumber datatype.
SETTINGS
If the Datatype property is set to text, enter any number from 0 to 255. The default setting is 50.
If the Datatype property is set to AutoNumber, the Field Size property can be set to Long Integer or Replication ID.
If the Datatype property is set to Number, the Field Size property settings can be byte, integer, long integer, etc and their storage size can vary from 1 byte to 8 bytes.
Format
You can use the format property to specify the data display format for a field or a control. For example, you can choose to have all dates formatted using the month/day/year format or some other format. You can choose from a list of predefined formats for fields with the AutoNumber, Number, Currency, Date/Time, and Yes/No datatypes, or you can create your own custom format. You can create a custom format for any field datatype other than OLE Object.
Input mask
You can use the input mask property to make data entry easier and to control the value users can enter in a text box control. For example, you could create an input mask for a phone number field that shows you exactly how to enter a new number: (___) ___-____.
Caption
All datatypes have a caption property. The caption property provides Access an alternative name to use for your fields when writing labels or references utilized by the user. For example, a field called FNAME on a form could be labeled as first name if this was the caption for the field. Captions help keep field size small while providing users with meaningful names.
Decimal Places
The Number and the Currency datatype have a decimal property, which determines how many decimal places are shown. Remember, only Number datatypes with single or double size have decimal property.
Default value
All datatypes except AutoNumber and OLE objects have a default value property, which automatically specifies the value of a field when a new record is started. Default values are not permanent and can be changed. For example, if most of your suppliers are in Chester, you could set the City field of the Suppliers table to have a default value of "Chester". You can accept the default value or type a new value over it.
Validation rules and validation text
The validation rule validates the data entered into a cell. You can use the validation rule property to specify any requirements for data entered into a field. When data is entered that violates the Validation Rule setting and you can use the validation text property to specify the message to be displayed to the user.
For example, if you had a Scored field that must never accept values more than 100, you could place <=100 in the validation rule. Access would then not allow any numbers more than 100 to be entered into the field. If you have also set up the validation text as for example "Score cannot be more than 100", this text will be displayed whenever the user violates the validation rule. Validation rule and Validation text properties are available for all datatypes except AutoNumber and OLE objects.
Required
The required property specifies whether a field must contain a value before the record is saved. This property does not apply to fields with AutoNumber datatype, because Access always provides values for such datatypes.
Allow Zero Length
Applied to the Text, Memo and Hyperlink fields, the Allow Zero Length property determines whether a zero length string qualifies as a valid value.
Indexed
You can use the indexed property to set a single-field index. An index speeds up queries on the indexed fields as well as performs sorting and grouping operations. For example, in order you search for specific employee names in a LastName field, you can create an index for this field to speed up the search for a specific name.
The primary key in a table is a special field which contains unique values, with the help of which Access uniquely identifies each row. Hence, no two records in a table can have the same values for the primary key. A primary key can be made up of more than one field if you do not have any one field that can uniquely identify a record. Such a primary key is known as composite. A primary key is not required by Access, but having it is highly recommended. When you set a primary key, the index property of the field, which is being used to set up primary key is automatically set to Yes/No duplicates).
To set the primary key, the following steps are taken:
Open a table in Design view.Select the field or fields you want to define as the primary key.
To select one field, click on the row selector for the desired field.
To select multiple fields, hold down the Ctrl key and then click on the row selector for each field.
Design view: In this view the user can customize the table, such as adding fields, setting default values or creating input masks. In other words, users can design the tables in this view.
Datasheet view: The user enters or edits the data in Datasheet view. Here every row is a record and the columns are the fields of the record. Using this view, a user can add, modify, delete, view, sort, find, filter and format the data in the table.
Once you have created the table structure and set all the properties for the fields, save the structure of the table by click oning on the Save button of the toolbar and close the Design view.
To enter the data into the table you need to switch to the Datasheet view. To do this click on the Tables tab of the Database window, select the table in which you want to enter the data and click on Open. The Datasheet opens.
In the Datasheet, you can move from field to field and from record to record using the arrow keys or Tab and Shift+Tab. Pressing the Tab is same as using the right arrow key and pressing Shift+Tab is the same as using the left arrow key. As you move up and down in the Datasheet, a triangular marker moves in the record selector, indicating the current record.
To edit the contents of a field, press the arrow keys until the field you want is highlighted and then press F2. Pressing F2 removes the highlight and allows you to edit the field. If you are using the mouse, simply click on the field you want to edit. If you want to replace the entire contents of the field you need not press F2. While editing if you have made a mistake and want to undo it, you can press the Esc key. This works only if you have not moved out of that record.
ADDING NEW RECORDS
Open the Datasheet of the table in which you want add the records.
Click on New Record on the toolbar.
Type the data you want and then press tab to go to the next field.
At the end of the record, press tab to go to the next record.
DELETING RECORDS
select the record to be deleted by clicking anywhere on the row of the record and:
From menu bar, choose Edit > Delete Record. or
Click on the Delete Record button on the Table datasheet toolbar. or
Select the current record by clicking on the Record Selector and pressing the Delete key. or
Right-click on the Record Selector and choose Cut from the shortcut menu.
CHANGING COLUMN WIDTH AND ROW HEIGHTS
From the Format menu, choose Column Width.
In the Column Width Dialog Box, specify the number of characters you want to see in the column.
Click on OK.
You can also change the width of a column by placing your mouse pointer over the top right border of the column and clicking and dragging the border until it is of the desired width.
PREVIEW AND PRINT THE TABLE
Go to the Datasheet view and click on the Print Preview button on the toolbar. If you are satisfied with the preview, click on the Print button to take the printout of the table.
FIND AND REPLACE DATA
In Datasheet view, select the field (column) you want to search, unless you want to search all fields. (Searching a single field is faster than searching the entire table.)
In the Edit menu, click on Replace.
In the Find What box, type the value you want to find; in the Replace With box, type the value you want to replace it with. If you do not know the exact value you want to find, you can use wildcard (*) characters in the Find What box to specify what you are looking for.
Set any other options you want to use in the Replace dialog box.
To replace all occurrences of the specified value at once, click on Replace All.
To replace each occurrence one at a time, click on Find Next, and then click on Replace.
To skip an occurrence and go on to the next one, click on Find Next.
SORTING AND FILTERING THE DATA
Users usually do not enter the information into the table in the order that they need to view. They may also need to view only certain records at a time. The sorting and filtering functions in the Datasheet view allows this flexibility.
SORTING
Click on the column heading by which datasheet is to be sorted, and then either:
Click on the Sort button on the toolbar. From the Records menu choose Sort and from the Sort menu choose Ascending or Descending. or
Right-click on a field and choose either Sort Ascending or Sort Descending.
You can also sort by multiple columns, but the columns must be adjacent to each other. Select the first column by clicking on the column header, then hold down the Shift key and click on last the column header of the columns by which you want to sort. Now you can perform any of the above mentioned methods.
FILTERING
Filtering by Selection is the quickest and easiest method to filter information in the database.
First, place the insertion point in the field that has the data value you want to extract, and then do one of the following:
From the Records Menu choose Filter by selection. Click on the Filter by Selection toolbar button. or
Or right-click on the desired value and choose Filter by selection from the shortcut menu.
After you have set up different tables for each subject in your database, you need to tell Access how to bring that information back together again. The first step in this process is to define relationships between your tables. Only then can you create queries, forms and reports to display information from several tables at once.
HOW RELATIONSHIPS WORK
A relationship works by matching data in key fields, usually a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table. For example, employees can be associated with orders they are responsible for by creating a relationship between the Employees table and the Orders table using the EmployeeID fields.
A ONE-TO-MANY RELATIONSHIP
A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.
MANY-TO-MANY RELATIONSHIP
In a many-to-many relationship, a record in Table A can have many matching records in Table B and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table, called a junction table, whose primary key consists of two fields --the foreign keys from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table. For example, the Orders table and the Products table have a many-to-many relationship that is defined by creating two one-to-many relationships to the Order Details table.
ONE-TO-ONE RELATIONSHIP
In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table. For example, you might want to create a table to track employees participating in a fund-raising nude rugby game. (Just checking that you're paying attention!)
To define the relationship between tables, follow the steps given below:
Close all open tables. You cannot create or modify relationships between open tables.
Switch to the Database window if you have not already done so. You can press F11 to switch to the Database window from any other window.
Click on the Relationships button on the toolbar.If your database does not have any relationships defined, the Add Tables/Queries box will automatically be displayed.
If you need to add the tables you want to relate and the Add Table dialog box is not displayed, click on Show Table on the toolbar.
If the tables you want to relate are already displayed, go straight to step 6.
Double-click on the names of the tables you want to relate and then close the Add Tables/Queries dialog box.
Drag the field that you want to relate from one table to the related field in the other table. To drag multiple fields, press the Ctrl key and click on each field before dragging them.
In most cases, you drag the primary key field (which is displayed in bold text) from one table to a similar field (often with the same name) called the foreign key in the other table. The related fields are not required to have the same names, but they must have the same datatype (with two exceptions*) and contain the same kind of information. In addition, when the matching fields are Number fields, they must have the same FieldSize property setting.
*The two exceptions to matching datatypes are that you can match an AutoNumber field with a Number field whose FieldSize property is set to Long Integer; and you can match an AutoNumber field with a Number field if both fields have their FieldSize property set to Replication ID.
The Relationships dialog box is displayed. Check the field names displayed in the two columns to ensure they are correct. You can change them if necessary.
Set the Relationships options if necessary. For information about a specific item in the Relationships dialog box, click on the question mark button and then click on the item.
Click on the Create button to create the relationship.
Repeat steps 5 through 9 for each pair of tables you want to relate.
When you close the Relationships window, Access asks if you want to save the layout. Whether you save the layout or not, the relationships you create are automatically saved in the database.
Referential Integrity is a system of rules that Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data. If you want Access to enforce these rules for a relationship, select the Enforce Referential Integrity check box when you create the relationship. If Referential Integrity is enforced and you break one of the rules with related tables, Access displays a message and does not allow the change.
You can override the restrictions against deleting or changing related records and still preserve Referential Integrity by setting the Cascade Update Related Fields and Cascade Delete Related Records check boxes.
When the Cascade Update Related Fields check box is set, changing a primary key value in the primary table automatically updates the matching value in all related records.
When the Cascade Delete Related Records check box is set, deleting a record in the primary table also deletes any related records in the related table.
Instead of importing e.g.Excel tables into Access, you can link the data in them so tey can be changed & viewed through Access but remain in their original location. You can also make ODBC databases visible to Access.
This means that you can link Access to an exisitng Sage database for example. It also means that your Excel table can have a form created for it through Access easily, enabling a quick but friendly user interface for input & viewing to be created.
If you do this often, you may find yourself creating your Excel tables with a first column of incremental numbers, which doubles up as a Primary Key for relational purposes in Access.
Once elements such as Forms and Reports in a database are sorted they can be right-clicked and sent to the Desktop. This makes it easier for the user to initiate tasks.
In Tools-Start up you can specify whether toolbars and menus will be available to the user when the database is opened as well as other tweaks to make the database appear more bespoke.
To restore all menus you must press the SHIFT key as you open the database.