Building Tables in Microsoft Access

Access can be intimidating, if you're not accustomed to working with databases. The terminology can be confusing and is generally the first topic I teach when starting databases. Generally each table of information represents one entity - such as a person, a book, a place etc. Each table will contain fields that represent categories of information such as a person's name, Date of Birth or suchlike.

microsoft security essentials

Tables can be linked together to avoid duplication, which is the main reason why one would choose Access rather than Excel. The process of breaking your data down in order to avoid duplication is called Normalisation and is a difficult topic to grasp.

MICROSOFT SECURITY

If you're starting from scratch, launch Access and select New > Blank Database. Give your new database a name and click Create. This step creates the database, but has not yet created a table where data can be stored.

You can create a table in Access in three different ways:

Using the Datasheet View

Using The Table Design View'

Importing data from another program already stored in tabular format

To create a blank table, select Create > Table. After doing this, Access will show your new table as a tab and the Add New Field column will be active.

Access also provides table templates you can use when creating a new table. If you prefer to use a table template, select Create > Table Templates. A list of available templates will be displayed. Select the template you would like to use. Access will use the template to create a table. The newly created table will display the default template fields. The Add A New Field column will be active, so you can create additional fields if necessary.

You can add new fields and populate them with data as you go. Once you have finished adding fields and data, you should save your table. Select the Save menu option and type in a name for your table. Remember that your table is a structure within your database. The two are related, but they're different structures. You can repeat this procedure for any number of tables in your database.

You'll want to use the Design view to create tables if you want to specify a field description. A field description is text that appears in a box when you mouse over a database field. This is especially helpful if you're designing a database that will be used by many different people.

You'll also want to use the Design view to create tables if you want to specify custom data formats. You can create rule-based data validation schemes and establish default values for data fields in Design view. These features are not available to you when you create tables using the Datasheet view.

To create a table in Design view, select Create > Table Design. Access will open a new tab with controls that will allow you to define your data fields. For each field you create, you will need to assign a name, a data type, a description and you'll need to define the field's properties.

Field names can be up to 64 characters in length. They cannot contain periods, exclamation points, backquotes (`) or square brackets and they must be unique.

Access recognises the following data types for data fields: text, memo, number, date/time, currency, autonumber, yes/no, OLE object, hyperlink, attachment, and lookup wizard. The text field can contain up to 255 characters. For fields that will include free-form information or notes, the memo data type is more appropriate. It will accommodate up to 64,000 characters. Most of the other data types are generally self-explanatory. OLE Objects allows data from other programmes to be included in the database. Lookup Wizard allows you to create a list of possible values that can be selected by the user. This is a good way to ensure that this field always contains uniform entries.

Field descriptions can be up to 255 characters in length. The description field will appear in the status bar when you're entering data in a field.

Field properties include the field size; the acceptable data format; any default values; whether or not the field is required; and whether or not the table will be indexed on this field.

Building Tables in Microsoft Access

Notes for editors: Claire Blinman is the training manager at Computer Training Solutions in Bristol. For more information visit our website at http://www.computertrainingsolutions.co.uk or call 0800 0196882

MICROSOFT SECURITY