IPT home IPT A Virtual Approach by Peter Whitehouse
Quick Links:
Access home
Information and Intelligent Systems Social and Ethical Implications Human Computer Interaction Software and Systems Engineering eXercise Files Course Outline and Assessment A-Z of Geeky Acronyms Terrace Work Program 2004 Sillybus FAQ = Frequently Asked Questions Help

Data Definition

Tables and Relationships

Database Definition

A Database in Access is a container for database objects. Access maintains all data and access permissions in 2 files:

  • MDB = the database file and
  • LDB = Multi-User Administration File.

To Create a NEW DATABASE choose FILE then NEW DATABASE from the top menu. You will be prompted for a name and location, then will be presented with a blank database window.

Table Definition

To Create a NEW TABLE choose New from the Table Database Window then the New Table button. You will be presented with a blank field builder grid.

Table Definition Dialog

Each field(column) of your proposed table is defined with:

  • Field Name - this can be any descriptive word or phrase. Access allows you to use spaces in the name of your tables. Ensure that within each table, field names are unique.
  • Datatype - a rich variety of datatypes are available in Access including Text (alphanumeric, up to 255 bytes long - 1 byte per char), Memo (alphanumeric up to 64000 chars/bytes), Number (integers or fractional - 1, 2, 4 or 8 bytes each), Date/Time (8 bytes each), Currency (8 bytes each), Counter (an Access autonumber value - 4 bytes), Yes/No (boolean value - 1 bit each) and OLE Object (binary data from another Windows application - up to 1Gb limited by hard disk space). In addition to type specification, you can specify Field Size (# significant chars or precision of numeric value), Format (appearance when displayed), Input Mask (standard input format), Caption (Message or title for field), Default Value (if no value is supplied, this will insert predefined value into field), Validation rule (check conditions), Validation Text (Error Message if invalid data is supplied), Required (Mandatory/Optional) and Indexed (unique with or without duplicates to speed up search and retrieval based on this field)
  • Description - this is text that will be displayed in the status bar when this field is selected on the table or in a form that uses this field.

Defining Primary Keys for your tables involve highlighting the fields involved in the key and pressing the Set Primary Key button on the Button Bar. You can highlight multiple fields by click-dragging multiple field selector buttons if the are sequential, or control-clicking individual field selector buttons if they are not immediately adjacent to one another.

Secondary Keys are created by specifying Unique Indexes on those columns as part of their format specification. It is possible to view and edit indexes created by you by pressing the INDEX button on the button bar in table design view.

As you can imagine, the datatype of each field profoundly effects storage space required by your database. Suppose you wanted to store height measurements, 180.5cm as an example. As a number it would occupy 8 bytes, as text data it would occupy 5 bytes - the saving of 3 bytes (for 1,000,000 records and this is a saving of 3Mb). As text, mathematical operations like averages, sums and the like would be very clumsy so choose column types prudently.

Indexes should be created on all fields that are likely to be the basis of search or ordering as a general rule. These indices will greatly improve Access' performance when using these fields. Indexes cannot be placed on OLE or Memo Fields, neither can these be sorted.

It should be noted here that Access performs JOIN queries faster than SUB-QUERIES. This would appear to be a 'side-effect' of MS-SQL. Query performance can be sluggish on large tables, yet can be improved with prudent use of indexes

Referential Integrity

Relationships Window

Foreign keys are defined by specifying relationships between defined tables.

To Define Relationships between tables choose Edit from the Top Menu then the Relationships from the Edit menu (or press the Relationships button from the Button Bar). You will be presented with a blank relationships window. Add the tables in your database, then pointing to the field that provides the Superset (the '1' pictured opposite) click-drag it onto a field that provides the subset (or foreign key - indicated with an '' in the diagram opposite).

You will then be provided with a relationship builder grid where you can specify Cascade effects (delete and update), Referential Integrity Enforcement, nature of relationship (1:1 or 1:many) and Join Type. For foreign keys within the same table, multiple copies of that table can be added to the window and relationships dragged and dropped as before.

Relationships provide powerful control over you tables populations, and coupled with field descriptions can automate much of the validity checking that is in other products normally externally applied.


©Copyright t 1992..2017+. Edition 25.150117
Creative Commons License
This work is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 2.1 Australia License