In this section, we'll explore how to create new tables, configure table fields, data types and field properties, work with key fields and setting up primary keys, create relationships between tables, and import external data into Access. We'll also look at the steps for designing an Access database from scratch and working out your 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.
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.
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.
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.
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 Type||Use for...||Examples|
|Short Text||Text and numerical values that do not require calculation (maximum of 255 characters)||Names, address fields, phone numbers|
|Long Text||Longer text (>255 characters)||Making notes, descriptions or summaries of modules|
|Number||Any 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 Number||A 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/Time||Dates and times. The field also requires setting field properties to define the date/time format to be used.||01/01/1993|
|Currency||Money, with an automatically displayed currency symbol||£199.99, €250|
|AutoNumber||Automatically gives a unique number, suitable for assigning a unique ID to a record||1, 2, 3, 4|
|Yes/No||Data that has only two possibilities||Yes/No, True/False, 0/1|
|OLE Object||Links to files||Photographs, documents|
|Hyperlink||URLs and file path links||www.york.ac.uk, www.subjectguides.york.ac.uk/skills|
|Attachment||Attach documents||Any file that needs to be attached not linked to|
|Calculated||Opens 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 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.
|Field Size||This 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|
|Field Size||For 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.|
|Byte||Positive integers up to 255|
|Integers||Integers between -32,768 and 32,768|
|Long Integer||Integers from -2,147,483,648 to 2,147,483,647|
|Single||Positive and negative decimal (floating point) numbers from -3.4 x 1038 to 3.4 x 1038 and with up to 7 significant digits.|
|Double||Positive and negative decimal (floating point) numbers from -1.797 x 10308 to 1.797 x 10308 and with up to 15 significant digits.|
|Decimal||Positive and negative decimal (floating point) numbers from -9.999... x 1027 to 9.999... x 1027 and with up to 28 decimal places.|
|Format||After 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 Places||For Field Size and Format combinations that display decimal places, use this property to control how many decimal places are displayed.|
|Format||Select the required date/time format for display. Access stores dates and times as decimal numbers, so this does not affect the stored values.|
These properties appear for many data types and are particularly useful for making user-friendly tables for data entry.
|Caption||Use the Caption property to add user-friendly display names to fields with unfriendly names.|
|Default||When 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.|
|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).
|Required||If a field has the Required property set to Yes, it cannot be left blank when a record is created or edited.|
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:
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.
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.
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.
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.
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):
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.
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 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.
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:
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.
Relationships are managed using a diagram that shows tables, including lists of fields and any joins that have been made.
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:
|Enforce Referential Integrity||Only 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 Fields||If 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 Fields||If 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:
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.
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.
To import Access tables:
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.
If you have a Google Sheets spreadsheet, you will need to download an Excel file to import into Access, then follow these steps.
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)
"MOD_CODE","MOD_NAME","MOD_CRDT" "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)
"MOD_CODE" → "MOD_NAME" → "MOD_CRDT" "4210100" → "The Making of the Middle Ages" → 20 "4210103" → "Crusading Europe c.950-1250" → 20
Fixed width text file
MOD_CODE‧‧‧‧MOD_NAME‧‧‧‧‧‧‧‧‧‧‧‧‧‧‧‧‧‧‧‧‧‧‧‧‧MOD_CRDT 4210100‧‧‧‧‧The Making of the Middle Ages‧‧‧‧20‧‧‧‧‧‧ 4210103‧‧‧‧‧Crusading Europe c.950-1250‧‧‧‧‧‧20‧‧‧‧‧‧
The import method for text files is as follows:
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 fields||Check 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 fields||Check 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 Key||If 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.|
|Others||Check that date/time fields have been identified correctly and yes/no fields have been converted correctly.|
Access can incorporate data from ohter souces without importing a copy, but by creating a link. Linking to existing data can help reduce duplicate.
Linked data is ideal in situations where you want to make use of data that is managed using another system, application, or Access database.
The precise method dependso n the type of data source you are linking to, so these are general pointers rather than detailed instructions.
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).
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.
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.
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:
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.
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:
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:
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.
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.
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.
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.
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.
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.
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.