<<< back to Access Tips
- American Standard Code for Information Interchange
- A collection of information concerning a topic or business
- Databases help you organise this related information in a
logical way for easy and fast retrieval.
- In Access, a database is the
overall container for the actual data and other objects (tables, forms,
queries, reports etc).
- Data Type
- A characteristic of a field that defines what type of information can
be stored in that field - Text, Number, Date etc.
- A category of information within a table - often
thought of as columns of data items within a table.
- Each field has a name that
identifies it; [FirstName], [City],
- Fields are also identified as being of a certain
- Foreign Key
- A field (usually not the primary key) in a
table that is linked (through a relationship) to a field (typically the
primary key) in another table.
- The foreign key will hold the same data type as the primary field in
the link table
- Often they will have the same name but this is not a requirement
- Indexes maintain a presorted order of fields that speed up queries,
- In Access, you can assign any field as indexed - typically you pick a
field that will be used a lot for searches, links etc.
- Indexes may also be unique - preventing duplicate values in that field
- Like the primary key, an index may be more than one field - using the
combined values of several fields
- In Access, objects are the tools you need to manage your database:
- Allows you to present or enter your data using your layout design
- Built-in commands to help you automate tasks without programming
- Programs you can create using Visual Basic for Applications (VBA)
- Helps you search, sort, retreive, update and delete sets of data
- Display, print, organise, group and sum your data using your
- Primary Key
- One or more columns (fields) in a table that will uniquely identify a
- Each table should have a primary key because:
- A primary key is an index
- When inputting data, Access prevents duplicate entries in the
primary key field
- Unless you tell it otherwise, Access will display your data in the
order of the primary key
- A set of data that can be accessed or sequenced as desired.
- A record
typically consists of fields (or just one field) of data pertaining to a
single entity within a table.
- Referential Integrity
- Refers to the requirement that primary and foreign keys remain
synchronised between tables
- When tables are linked, one table is usally called the parent
table, and the other (the table it is linked to) is usually called the child.
Referential Integrity ensures that there will never be an orphan - a
child record without a parent record.
- You can prevent the deletion of a record in one table (i.e.Customers)
if there are records in a linked table (i.e. InvoicesDue)
- A logical link between two tables that describes how the tables are
associated with each other
- A relationship in which one record in a table can related to any
number of records in another table. (A teacher may have many
students, an order may have many items, a department may have many
- A relationship in which one record in a table relates to only one
record in another table. (A patient table, detailing address and
billing information may relate to one record in the Patients'
Personal table, detailing health information, treatments etc.)
- Many -To-Many:
- A relationship where any number of records in a table can relate
to any number of records in another table. An example might be the
relationships between people and addresses, in which many people can
have the same address AND any one person may have a number of
addresses (home, work, townhouse etc.). This type of relationship is
implemented by creating a new 'join table' and creating two
one-to-many relationships from the original tables to the third
table. The 'join table' would include two foreign keys - one to each
of the other two tables' primary keys.
- A container for raw data - typically, a logical grouping of similar
- In Access, the tables' design organises the data into rows
(records) and columns (fields)
- A location in which to store a single value