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:

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

Numerical field properties

Date/Time field properties

Other useful properties

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

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.

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.

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).

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 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:

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:

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.

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.

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)

"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:

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.

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:

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.

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.

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:

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).

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.

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.

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.