Skip to Main Content
University of York Library
Library Subject Guides

Essential Access: a Practical Guide

Designing tables and databases

Creating new tables

Tables store data. All other Access objects work with this data, so tables are pretty important. You will often have many tables in your database, holding related data. To add a new set of data to your database, you can create a new table or import external data into a new table.

Whether you're designing a database from scratch or creating new tables in an existing database, the process of creating a new table is the same. On the Create tab, the Tables section has options to create a table directly in datasheet view or to use Table Design to make your table using the Table design view. The Table Design option is easiest for first defining your fields correctly and then going to datasheet view to input data if needed.

On the Create tab, you have the Table creation options, including Table Design

You might not end up inputting your data at this point however. You might import in data from another source, or set up a form for more user-friendly data entry.

Configuring table fields

You will need to configure all of the fields in a table so they will contain the right type of data, have suitable names, be validated if needed to ensure correct data entry, and have a suitable Primary Key. We'll explore all of these and they can all be set up from the Design View of the table.

From Table Design you can enter field names, select the correct data type, and configure field properties. Text in the Description column will be on the status bar when the field is in use.

Field names

The Field Name column is the first one in the table design view. New fields are added by entering a name in this column and then setting the data type.

Access is fairly flexible in terms of field names, but you should aim for consistency and clarity. particularly if field names might be something you'd want to have in multiple different tables (e.g. surname).

Access works best if names do not include spaces. You can use camelCase or an underscore instead (e.g. use StudentSurname or Student_Surname instead of Student Surname.

As you will sometimes need to type out field names, choose fairly short ones where possible. You can use the optional Description field to add more detail, as any text in this column will appear in the status bar when the field is in use.

Data types

Once you'ven named a field, the next step is to set what Data Type the field is: what kind of data Access should expect to be in the field. As with most databases, this must be specified for Access to work.


If you're an Excel user, you might not be used to the idea of having to set a single type of data for a column, as Excel allows for multiple data types in a single column. Excel also doesn't require you to declare the data type when the column is created.

Access is more forgiving than some systems (e.g. it will let you calculate with numbers in a Text field), but it is important to define your data types correctly. The types are summarised below:

Data TypeUse for...Examples
Short TextText and numerical values that do not require calculation (maximum of 255 characters)Names, address fields, phone numbers
Long TextLonger text (>255 characters)Making notes, descriptions or summaries of modules
NumberAny numerical data potentially requiring calculation or sorting. The field also requires setting field properties to define the range of values allowed.5, -193, 3.002, -17.42
Large NumberA numerical value larger than the limit for Number (see Microsoft's support page on Large Number for details on the size difference).8,243,543,323,765,343,434,980
Date/TimeDates and times. The field also requires setting field properties to define the date/time format to be used.01/01/1993
CurrencyMoney, with an automatically displayed currency symbol£199.99, €250
AutoNumberAutomatically gives a unique number, suitable for assigning a unique ID to a record1, 2, 3, 4
Yes/NoData that has only two possibilitiesYes/No, True/False, 0/1
OLE ObjectLinks to filesPhotographs, documents
HyperlinkURLs and file path,
AttachmentAttach documentsAny file that needs to be attached not linked to
CalculatedOpens expression builder to calculate a value from other fields (though you'd often do this in a query rather than storing in a table).[mark]/120
Lookup Wizard...Creates a list of values that can be chosen for the field from a list either from another table or query or manually typed when creating the field. Like creating a lookup field in a Form, but in the Table directly.Choices of: Cheese, Ham, Tuna

Field properties

Field Properties configure the individual fields in a table, from the size of the field (how much data it can store) to how it will display in the table.

To configure a field, make sure you're in Design view, then click on a field to select it. The bottom pane of Design view will then display the Field Properties options for that field (these vary depending on data type). Edit these as necessary and then save and close the table design.

Some of the field properties appear for most fields, and others are specific to certain data types. Here is a summary of some of the key properties for common data types and some that appear across many data types.

Text field properties

Field SizeThis determines the maximum number of characters that can be stored in the field. Leaving it at the default 255 characters will "reserve" that much space for every record, making your database file larger than it needs to be. Set a value slightly longer than the maximum value you anticipate.
Format (optional)>Force display of text into uppercase
<Force display of text into lowercase

Numerical field properties

Field SizeFor numbers this determines the range and precision of values in the field. Choosing the most accurate option will save the space as Access will "reserve" enough space for the maximum size of the field, e.g. if you know all values will be integers, choose the integer option.
BytePositive integers up to 255
IntegersIntegers between -32,768 and 32,768
Long IntegerIntegers from -2,147,483,648 to 2,147,483,647
SinglePositive and negative decimal (floating point) numbers from -3.4 x 1038 to 3.4 x 1038 and with up to 7 significant digits.
DoublePositive and negative decimal (floating point) numbers from -1.797 x 10308 to 1.797 x 10308 and with up to 15 significant digits.
DecimalPositive and negative decimal (floating point) numbers from -9.999... x 1027 to 9.999... x 1027 and with up to 28 decimal places.
FormatAfter setting field size, use Format to choose other display options. For example, you can use Fixed to allow you to set a number of decimal places in the Decimal Places.
Decimal PlacesFor Field Size and Format combinations that display decimal places, use this property to control how many decimal places are displayed.

Date/Time field properties

FormatSelect the required date/time format for display. Access stores dates and times as decimal numbers, so this does not affect the stored values.

Other useful properties

These properties appear for many data types and are particularly useful for making user-friendly tables for data entry.

CaptionUse the Caption property to add user-friendly display names to fields with unfriendly names.
DefaultWhen a new record is added to the table, any fields with a value set in the Default property will automatically contain that value (though this can be manually changed in the table). Quotation marks are needed for text, but not for numbers.
Validation Rule
Validation Text
These two properties are used together: data will be checked against the Validation Rule and only allowed if it complies. The Validation Text will be displayed if the data breaks the rule.
Some example rules are <=50 (less than or equal to 50) and >Date() (a date after the current date).
RequiredIf a field has the Required property set to Yes, it cannot be left blank when a record is created or edited.

Date and time values

As you might have noticed above, Access includes a method for inserting the current date and/or time in an expression. You can use these as either a default value or in validation. The options are:

  • Date() - current date
  • Time() - current time
  • Now() - current date and time

When you use these in the Default or the Validation Rule properties, you can use the value itself or you can do calculations, e.g. a certain number of days from the current date.

Key Fields

Relational databases like Access expect that each record in each table has a unique identity. The usual way to achieve this is to include a field that will contain a different value for each and every record. This field is then referred to as the Primary Key.

Access will offer to create a Primary Key for your table if you try to save a table without one. Don't automatically agree to this - first think if it makes sense to define your own, like some kind of ID or reference number, or if a unique field already exists in your data. You can also choose to use the AutoNumber field to automatically create a different number for each record. Occasionally you might even use a Composite Key. We'll explore how these work below.

Setting up a primary key

To define a Primary Key, make sure you're in the table Design View and select the field you want to use as the primary key. On the Table Tools Design tab on the ribbon, select Tools > Primary Key.

The Indexed property from the Field Properties is affected when the Primary Key is set, as it will be set to Yes (No Duplicates) which prevents the same value being entered more than once for records in this field.

The Primary Key control is found on the Design tab and the Primary Key indicator will appear next to the field in question, as well as the Indexed property set to Yes (No Duplicates)

Instead of using a value you've created, you can use the AutoNumber data type to create a new field that numbers each record incrementally. To do this, create a field with a suitable name and set it as the AutoNumber data type, then set this field as the Primary Key as shown above.

AutoNumber versus user-defined Primary Key

Though you can use AutoNumber as the Primary Key, there are advantages to using existing data or creating your own field that is more specific than AutoNumber (e.g. designing a relevant ID number):

  • It may relate to other systems more easily (e.g. an existing user ID).
  • If it is constructed from other data, it enables validation of Primary Key values (e.g. the UK Driver Number is constructed from surname and date of birth) - but bear in mind where there could be duplication in these cases.
  • Additional information might be conveyed through the value (e.g. traditional landline phone numbers indicate country, town and local area).

Foreign Keys

The Primary Key identifies table records uniquely and therefore it can be used to represent a specific record in that table. This means you can enter its value in another table to represent and link to that item (when relationships have been set up between the tables).

The field in the other table that stores the values that are in the Primary Key in antoher table will be called the Foreign Key, meaning a Primary Key stored in another table.

The Primary and Foreign Key fields must be the same data type, with the exception of Primary Keys with the data type AutoNumber, as the corresponding Foreign Key must have the data type set to Number with the Long Integer field size property.

The field properties for both fields must also be the same, except for the Indexed property, which should be Yes (No Duplicates for the Primary Key field and No for the Foreign Key field.

The field names do not need to be the same, though it tends to be easier for constructing queries if they are.

Composite Keys

Sometimes, a table might have two or more fields which if used together would provide a unique value. These can be used together as the Primary Key as an alternative to AutoNumber and would be known as a Composite Key. For example, an optician could only see one patient at a time so an Appointments table could use the fields Date, Time, and OpticianID as a Composite Key.

To create a Composite Key, you need to select both fields in the table Design View by holding down the CTRL key and clicking on the area to the left of the field name column (cursor should become a black arrow) for all the fields you want selected, then clicking on the Primary Key button on the Table Tools Design tab.

Relationships between tables

Relationships between tables can be created when a created, but more features are available if relationships are configured separately. In particular, you can choose to enforce referential integrity and decide how Access will respond to changes in data.

Referential integrity

Relational databases rely on the existence of consistent data in separate tables. If no steps were taken to maintain its integrity, it would be possible to break the database very easily. You could, for example:

  • Enter a non-existent Student ID into a table that uses Student ID as a foreign key (so it is not linked to any student)
  • Change a value in a primary key field when the value also exists in other tables are a foreign key and is not updated
  • Delete a student record, when records for that student exist in other related tables

In order to prevent these from happening, when creating relationships Access allows you to enforce referential integrity, whilst also allowing you to change and delete data as necessary.

Creating relationships

Relationships are managed using a diagram that shows tables, including lists of fields and any joins that have been made.

Relationships are managed using a diagrammatic interface

Before defining a relationship, check that the tables contain the appropriate fields - using a primary key in one table and a foreign key in another. Then follow these steps:

  1. Open the Relationships window by choosing Database Tools > Relationships.
  2. The first time you open this window the Show Table dialogue box will present a list of available tables. To open this dialogue on future occasions select Relationship Tools > Design > Relationships > Show Table. Add the tables you want to define relationships between by selecting and choosing Add. Close the dialogue once added.
  3. Identify the two fields that will make the relationship and then drag one onto the other, ensuring you position the cursor accurately.
  4. The Edit Relationships dialogue opens, with the tables and linked fields pre-entered. Choose how you wish to enforce integrity. The options are:
Enforce Referential IntegrityOnly allow an existing value for the primary key to be entered into a related table where it is used as a foreign key.
Cascade Update Related FieldsIf the value in the primary key is changed, make the same change to corresponding values in the related tabl where it is used as a foreign key.
Cascade Delete Related FieldsIf a record is deleted from the table containing the primary key, also delete all related records from the related table.

Points to bear in mind about referential integrity:

  • Having created these relationships, they will be reflected in any new queries you create. However, you can change the join type if necessary.
  • If you are unlikely to make changes to key field values, or to do so would be problematic (e.g. if a tutor could be deleted, but their module would still exist just without them), enforce referential integrity without any cascade options.
  • Think carefully before choosing Cascade Delete as you cannot undo deleted records.
  • By default, Access warns before deleting reocrds, but this warning can be disabled by a designer.
  • The Relationships dialogue also displays the type of relationship detected. If you expect One-to Many but One-to-One is shown, this usually means the foreign key field is configured incorrectly and does not allow duplicate values.

External data into Access

In many cases, data to be used in an Access database already exists in another form - for example, a spreadsheet or an export from another system. Such data does not need to be re-entered as it can be imported.

Importing data

Data can be imported into Acess if it is in a recognisable format. Common formats include other Access databases, Excel spreadsheets, CSV files, and other text formats.

Importing is carried out using a Wizard and although different choices need to be made for different types of data file, the process is similar. When importing data you can create a new table or append data to an existing one. Appending requires precision as the field order and data types must match exactly.

Access tables

To import Access tables:

  1. Select the External Data tab, then from Import & Link choose Access, then choose Browse and locate the Access database file.
  2. Make sure the Import option is selected rather than the Link option, then choose OK.
  3. A full list of tables will be presented. Choose one or more to import - use CTRL to select more than one - and select OK to import the data.

Excel spreadsheets

When importing from Excel using the Wizard, a view of the data is provided to help ensue you make the correct choices. You have the option to append the data to an existing table, but for this to be successful the field order and data types must match exactly.

  1. Select the External Data tab, then from Import & Link choose Excel, then choose Browse and locate the Excel file.
  2. Make sure the Import (or Append) option is selected rather than the Link option - to append, you must specify a table - then choose OK.
  3. The worksheets available are listed. Select the one you need and check the data, then choose Next.
  4. In the next few steps you must make choices that are appropriate for your data: whether the list has column headings, which field is a primary key, data types, etc. It's usually just OK just to accept the defaults. A table name is then suggested, which you can accept or change.
  5. Finally, you may opt to save the import settings for future use. This is useful if you regularly import data from the same spreadsheet.
The Import Wizard shows a sample of the data to help you make the correct choices


If you have a Google Sheets spreadsheet, you will need to download an Excel file to import into Access, then follow these steps.

Other text formats

Data from other systems may be imported, but you need to be able to identify how data items are separated. The two main options are delimited, where a specific character occurs between fields (e.g. a comma, tab, space), and fixed width, where each field occupies the same number of characters, with extra spaces used for padding.

Here are three examples of data formats:

CSV file (comma separated values - delimited with a comma)

"4210100","The Making of the Middle Ages",20
"4210103","Crusading Europe c.950-1250",20

TSV file (tab separated values - delimited with a tab character which is represented by → in this example)

"4210100" → "The Making of the Middle Ages" → 20
"4210103" → "Crusading Europe c.950-1250" → 20

Fixed width text file

4210100‧‧‧‧‧The Making of the Middle Ages‧‧‧‧20‧‧‧‧‧‧
4210103‧‧‧‧‧Crusading Europe c.950-1250‧‧‧‧‧‧20‧‧‧‧‧‧

The import method for text files is as follows:

  1. Select the External Data tab, then from Import & Link choose Text File, then choose Browse and locate the file and choose OK.
  2. Make sure the Import (or Append) option is selected rather than the Link option - to Append, specify a table - then choose OK.
  3. In the next few steps you must make the choices that are appropriate to your data, checking that it is being correctly separated into fields (columns) in the sample view. One of the steps will allow you to choose a field to be the primary key.
  4. A table name is suggested, which you can accept or change.
  5. Finally, you may opt to save the import settings for future use. This is useful if you regularly import data from files of the same structure.

Post import checks

If errors occur during import (proablems identifying the appropriate data type are common) these will be saved in an error table and you may need to do some manual tweaking.

In the table design, always check the following:

Text fieldsCheck the data type is set to Text and adjust the Field Size property, because an import will often set all text fields to the maximum size of 255 characters. Note that if you change the field size Access will warn you when saving changes, but as long as you have counted the number of characters required for a field correctly you won't use any data.
Number fieldsCheck the data type is Number and configure the Field Size property for the range and precision you require. Any change to number properties will produce warnings when you save the change, but as long as configuration matches your data it should convert correctly.
Primary KeyIf you chose a primary key during import this should be set correctly, but always check the key is set and the Indexed property is Yes (No Duplicates.
OthersCheck that date/time fields have been identified correctly and yes/no fields have been converted correctly.

Linked data (inc. ODBC)

Access can incorporate data from ohter souces without importing a copy, but by creating a link. Linking to existing data can help reduce duplicate.

Characteristics of linked data

  • Any changes made to the linked data by other people will be reflected in your Access database without having to re-import.
  • You may have read-only permissions for data so may not be able to make changes.
  • You will have no control over the design of the linked data (e.g. data types, primary key).

Linked data is ideal in situations where you want to make use of data that is managed using another system, application, or Access database.

Linking external data

The precise method dependso n the type of data source you are linking to, so these are general pointers rather than detailed instructions.

  1. First make sure you have access to the location of the data to be linked. You must have at least read permissions in order to create the link.
  2. On the External Data tab in the Import & Link section, choose the appropriate data source (Excel, Access, ODBC, etc.
  3. Depending on which you choose, you will be presented with an appropriate dialogue box to complete. Make sure you select the Link option when presented.

If you are a member of staff at the University who needs to use Data Warehouse (DWH) through Access, there is separate guidance from IT Services on how to set up the ODBC data source (requires UoY login).

Using linked data

Once a table has been linked, the data it contains can be used in queries just like any other data. In the object list, linked tables are shown with alternative icons to indicate the presence of a link and the type of source.

All Access Objects can contain both Access tables and linked tables like from Oracle databases or Excel

Designing a database

If you're building an Access database from scratch, you will need to think about how you design the database, including the tables needed and the relationships between your data.

Databases are never used simply to store information: they are always created in order to do something. Understanding the purpose of a database and what tasks you want to carry out is essential when considering its design. Generally, you'll want to ask questions of the information in the database (using queries) and that will impact what information you need to enter and store in the database.

Access databases should be designed with users in mind, thinking about input (e.g. data entry and viewing) and output (e.g. reports, exporting data) in the processes people will use.

Below we'll explore the key stages of designing an Access database.

Identify tasks

Start by asking what information you want to get out of the system; this can help what input will be required. Ask yourself what tasks will need to be done, and try to be as specific as you can. For example, if you were making a database for student records and marks, you might have tasks like:

  • I want to be able to enter a surname and year to see modules and results for a particular student.
  • I need to locate all students taking a particular module and enter their results.
  • Each term I will need to export the previous term's marks into a spreadsheet to produce graphs.
  • New student data needs adding at the start of each year - imported from an existing source.
  • Once a month I will produce a list of formative assessment results for each tutor's students to distribute to the tutor.

Once you've identified these tasks, you should be able to start to see what data you would need to store to get these outputs. Bear in mind that you will need to comply with GDPR and not include any information that is not necessary for the purpose of your database.

Identify the data structure

Having identified the tasks you need to do, you can now work out the data structure of your database. To guide you through this, consider these steps:

  1. List all the data items you think you will need.
  2. Group together data items which are attributes of the same subject (e.g. all the information about students, about modules, etc). These groups will become your data tables and the attributes will be the fields in these tables.
  3. Identify a primary key for each group of fields (if there is no obvious choice, you may need to invent an ID number or look for a combination of fields to be a composite key).
  4. Map out the tables and attributes to work out the relationships between them, as in this example:

Mapping out tables for Students, Modules, and Module Choices with the fields each table would have and how they would conntect.

One important aim of a relational database is to avoid duplication or repetition - data should be stored only once. If you see there's likely to be duplication in your database, that typically means you would need at least one other table, with the exception of when a primary key in one table is the foreign key in another table to link them.

Once you've reached this stage, you can identify the precise fields needed in your database, if you haven't already. Here are some tips for choosing fields:

  • Always use separate fields for forename and surname, otherwise it will be very difficult to sort by surname.
  • Always use multiple fields for a postal address, with specific fields for details like Town/City and Postcode rather than generic field names, as this will make writing queries easier.
  • Choose fields that will not get out of date too easily. For example, use date of birth, which is fixed, rather than age, which changes but can be calculated using date of birth.
  • Never store a value that can be calculated from your data (so don't store date of birth and age).

Identify relationships

Most relationships between information in a database are one to many. If, when designing your database, two groups of attributes appear to exhibit a one-to-many relationship in both directions, this is a many-to-many relationship and indicates a linking table is needed.

For example:

  • Can one student take many modules? Yes.
  • Can one module be taken by many students? Yes.

This is a many-to-many relationship and therefore as well as the Students and Modules tables, there would need to be a Module Choices table, which has a one-to-many relationship with both Students and Modules tables.

It is vital to ensure the integrity of your data, so ensuring that any values that link tables together match (so, for example, any student IDs found in a Module Choices table refer to a real student ID in the Students table). You can choose to enforce referential integrity to ensure this.

Check for common mistakes

Once you have come up with your preliminary design for your tables, fields, and relationsips, examine your ideas for common problems. Here we'll explore a few.

Repeating attributes

You don't want to have multiple fields in a table that have the same attributes, as this makes it very difficultt to query that data. For example, if you wanted to record module choice preferences for students, instead of having separate fields for 'Choice 1', 'Choice 2', etc which recorded individual modules, it would be better to have a table that matched up the student with a module and then had a field for 'Preference' that had a numerical value. This makes it easier to understand the order of preference as data, rather than as field order, which is not user friendly or good design.

Multiple values in a field

Storing what are technically multiple values in the same field can cause issues with queries and calculations. For example, you might think of logging appointment information like time, date, and location all in a single field, when this information would be better as three separate fields for maximum flexibility. Even date and time can be better separate, as then you can more easily do a query for date ranges or similar.

Similarly, storing parts of names or addresses in individual fields makes it easier to sort and filter by these parts, for example sorting by surname or filtering by location.

Redundant values

Sometimes you plan to add fields to your tables that are actually redundant, as they could be calculated or displayed using field properties. These often make errors more likely. For example, if you have a date, but also a field to manually enter the day of the week, it's easy for someone to enter the incorrect day of the week for that date. Similarly, having an age field is redundant if you have date of birth, and age changes whereas date of birth doesn't. Both of these values, day and age, could be calculated or displayed in a query or using the properties of the field.

Preliminary design and testing

Once you've worked out what you want to make, it is important to test out your design and make sure your data or expected data will work with your planned database structure. Create preliminary versions of your tables in an Access database and add some test data, then work with that data to see if your database will suit it.

It is important to remember this is a prototype and should not be the final database. Use test data that has been specifically created for this purpose, so you aren't tempted to use the prototype as the real thing and so you can have a good range of values representing both normal and extreme cases in your data. Include true values (e.g. the longest name you might expect) and values that might be entered in error (such as a number with a missing decimal point or a date of birth after the current date). Also try adding duplicate data like people with the same name or making the same choices.

From doing this, you'll start to work out the data types, field sizes, and validation needed in your tables, as well as if any fields often have the same value and therefore should have a default value.

Next, develop some test queries to see if you get the results you'd expect. Try to base these around questions you will want answering.


Make sure you've downloaded the Essential Access exercise files before attempting these exercises.