Databases, tables, and relationships
What is Access?
Do you have an idea for a database or web app to help your team work more efficiently? With Access, you don’t have to be a developer to implement it.
Take advantage of your data with the power of Access—without writing code or becoming a database expert:
- Use templates to build databases and web apps quickly.
- Easily find and report on data stored in Access.
- Streamline data entry with autocomplete.
- Create web apps that work in the browser or databases on your desktop, and share them with others.
- Build professional-level web apps with just Access—no coding required.
- Get greater reliability, scalability, and security, because Access web app data is stored in a Microsoft Azure SQL database or in SQL Server.
- Manage and monitor your Access web apps through Access Services, SharePoint Online, on-premises SharePoint Server 2013, or SharePoint Server 2016. Take advantage of SharePoint permissions and app usage data.
Get started with databases
Databases and web apps can yield big business advantages. Database design is critical to achieving your goals, whether you want to manage employee information, provide weekly reports against data, or track customer orders. Investing the time to understand database design will help you build databases that work right the first time and that accommodate changing needs.
IMPORTANT: Access web apps are different from desktop databases. This article doesn’t discuss web app design.
Concepts and terms
Let’s start by learning some basic terms and concepts. To design a useful database, you create tables that focus on one subject. In your tables, you capture all the data needed for that subject in fields, which hold the smallest possible unit of data.
|Relational databases||A database in which data is divided into tables, which are kind of like spreadsheets. Each table has just one subject, such as customers (one table) or products (another table).|
|Records and fields||Storage for the discrete data in a table. Rows (or records) store each unique data point, such as the name of a customer. Columns (or fields) isolate the information being captured about each data point into the smallest possible unit—first name might be one column and last name might be another.|
|Primary key||A value that ensures each record is unique. For example, there might be two customers with the same name, Elizabeth Andersen. But one of the Elizabeth Andersen records has the number 12 as its primary key and the other has a primary key of 58.|
|Parent-child relationships||Common relationships between tables. For example, a single customer may have multiple orders. Parent tables have primary keys. Child tables have foreign keys, which are values from the primary key that show how the child table records are linked to the parent table. These keys are linked by a relationship.|
What is good database design?
Two principles are fundamental to good database design:
- Avoid duplicate information (also called redundant data). It wastes space and increases the likelihood of errors.
- Ensure that data is correct and complete. Incomplete or erroneous information flows through in queries and reports and may ultimately lead to misinformed decisions.
To help with these issues:
- Divide database information into subject-based tables with a narrow focus. Avoid duplicating information in multiple tables. (For example, customer names should go in only one table.)
- Join the tables together using keys instead of duplicating data.
- Include processes that support and ensure the accuracy and integrity of database information.
- Design your database with your data processing and reporting needs in mind.
To improve the long-term usefulness of your databases, follow these five design steps:
Step 1: Determine the purpose of your database
Before you start, have a goal for your database.
Step 2: Find and organize required information
Gather all of the types of information you want to record, such as your product names and order numbers.
Step 3: Divide information into tables
Divide your information items into major entities or subjects, such as products, customers, and orders. Each subject becomes a table.
Step 4: Turn information items into columns
Decide what information you need to store in each table. These discrete pieces of data become fields in the table. For example, an Employees table might include fields such as Last Name, First Name, and Hire Date.
Step 5: Specify primary keys
Choose each table’s primary key. The primary key, such as Product ID or Order ID, uniquely identifies each record. If you don’t have an obvious, unique identifier, use Access to create one for you.
Get to know database objects
Access provides six database objects that, when combined, help you take full advantage of your data. Learn the role that each object plays.
Access organizes your information into tables: lists of rows and columns reminiscent of an accountant’s pad or a spreadsheet. Each table stores information about a specific subject, so most databases include more than one table.
Each row in the table is called a record, and each column is called a field. A record contains all the specific information for a particular entity, such as a customer or an order. A field is a single item of information about that entity. In the Products table, for instance, each row or record would hold information about one product. Each column or field holds some type of information about that product, such as its name or price. If you aren’t already familiar with them, learn the basics of databases.
In a well-designed database, the data that you want to present through a form or report is usually located in multiple tables. You use a query to pull that information from various tables and to assemble it for display in a form or report.
A query can be a request for data results from your database, or it can be used to perform an action on the data, or it might be both. Use queries to get answers to simple questions, perform calculations using data, combine data from different tables, and add, change, or delete data from a database.
There are many types of queries, but the two basic types are:
|Major query types||Use|
|Select||To retrieve data from a table or make calculations.|
|Action||Add, change, or delete data. Each task has a specific type of action query. Action queries are not available in Access web apps.|
Forms are like display cases in stores—they make it easier to view or get to the database items you want. Similar to paper forms, Access forms are objects through which you or other users can add, edit, or display the data stored in your Access desktop database. It’s important to design your form with use in mind. For example, if it’s for multiple users, a well-designed form helps to make data entry accurate, fast, and efficient.
Reports offer a way to view, format, and summarize information from your Access database. For example, create a simple report of phone numbers for all your contacts or a summary report on the total sales across different regions and time periods.
Reports are handy when you want to present the information in your database to:
- Display or distribute a summary of data.
- Archive snapshots of the data.
- Provide details about individual records.
- Create labels.
A macro is a tool that allows you to automate tasks and add functionality to your forms, reports, and controls. For example, if you add a command button to a form and associate the button’s OnClick event to a macro, it then performs a command each time the button is clicked.
Access provides a design environment to help you create macros. Essentially, you use a simplified programming language to build a list of actions to perform. Some actions require additional information, like which field to display fill in. The design environment makes it easier to create macros, as you select from lists of actions and fill in information.
Macros are used to automate a series of actions, make changes to data in a database, and more. Through the Design view, macros provide a subset of the commands that are available in Visual Basic for Applications (VBA). Most people find it easier to build a macro to add functionality to forms, reports, and controls than to write VBA code, but you can always convert the macros to VBA from within the Design view.
Suppose that you want to open a report directly from one of your data entry forms. Add a button to your form and then create a macro that opens the report. The macro can either be a standalone macro (a separate object in the database), which is then bound to the OnClick event of the button, or the macro can be embedded directly into the OnClick event of the button itself. Either way, when you click the button, the macro runs and opens the report.
Modules are VBA code that you write to automate tasks in your application and to perform higher end functions. You write modules in the VBA programming language. A module is a collection of declarations, statements, and procedures that are stored together as a unit.
Create an Access desktop database
To create a desktop database quickly, use one of the many templates provided with Access. Or, if you don’t find a template that meets your needs, create a database from scratch.
NOTE: If you want to create an Access web app, see Create an Access web app.
Create a database from a template
The easiest and fastest way to create your database is to use an Access template, which creates a ready-to-use database, complete with the tables, forms, reports, queries, macros, and relationships that you need to start working right away. If it doesn’t meet your needs exactly, customize it.
When you open Access, you see a set of templates. More are available online.
TIP: If a database is open, select File > Close to close it.
- 1. Open Access (or select File > New), and select a desktop database template. (Desktop database templates have a picture of a datasheet in the background.)
TIP: If you don’t see the template you want, look for a template on Office.com. Beneath the Search for online templates box, select Databases. Then, in the Search box, enter one or more keywords.
- Enter a name for your new database, and select its location. Then, select Create.
Messages you might see
If you see any of the following messages, here’s what to do:
|If you see this||Do this|
|Login dialog box with an empty list of users||
|Getting Started page||To learn more about the database, use the links on the page, or, to explore the database, select other buttons and tabs.|
Create a database from scratch
If you don’t find the template you need, create your own.
- Open Access (or select File > New), and select Blank desktop database.
- Enter a file name, and then click Create. To save to file in a specific location, select Browse. Access creates the database with an empty table named Table1 and then opens Table1 in Datasheet view. It automatically adds a column, called ID, for the primary key.
- Table1 isn’t saved automatically, so be sure to save it, even if you haven’t changed it.
You’re almost ready to enter data. But first, you need to define fields for the table, use Access to add other tables with fields, and then create relationships between them. For instructions, start with the Build tables and set data types module in this course.
Create an Access web app
View and sort data online, using any device, in an Access web app—even if you don’t have Access. The fastest way to get started is to use a template.
Create an Access web app from a template
When you select a template, Access assembles an app that contains the tables you probably would have added yourself if you had started from scratch.
NOTE: To create an Access web app, you need Office 365 with SharePoint Online, on-premises SharePoint Server 2013, or SharePoint Server 2016 with Access Services and SQL Server 2012 (or higher).
TIP: If you need to close a database, select File > Close.
- Open Access, and select a web app template. (Web app templates have a picture of a globe in the background.)
TIP: If you don’t see the template you want, under the Search for online templates box, select Databases. Then, to find database templates on Office.com, enter one or more keywords in the search box.
- Enter a name for your new app.
- From the list, select its location. (Or enter a SharePoint URL or an Office 365 URL.)
- Then, select Create.
Create an Access web app from scratch
Creating an Access web app from scratch is very similar to creating one from templates. The biggest difference is that you create the tables in your web app.
- Open Access, and select Custom web app.
- Enter a name for your new web app, and select its location. Then, select Create.
- Add structured or blank tables to the web app, or create tables from existing data sources. Search for a table template based on the type of information you want to track, create a table from the existing source data, or add a blank table to customize yourself.
Build tables and set data types
Because they store your data, tables are the backbone of your database. Each table contains information about a specific subject. For example, a supplier table might store supplier names, e-mail addresses, and telephone numbers.
NOTE: Before you build tables, it’s helpful to understand Access database objects.
When you open a new, blank database, Access automatically creates an empty table. To customize that table, start defining your fields and add data.
Rename a table in a desktop database
Table1 is the default name of the first table in a new desktop database. It’s a good idea to name the table something more meaningful.
- On the Quick Access Toolbar, select Save
- In the Table name box, enter a descriptive name.
Add a table to a desktop database
Add more tables to a database, if you need them, even if you started with a template.
- On the Create tab, select Table.
Access adds a new table with the name Table<#>, where <#> is the next sequential, unused number.
- Rename the table using the procedure in Rename a table in a desktop database, earlier in this module.
Save a table
Before you close your database, to avoid losing the work you’ve done and the data you’ve entered, be sure to save your table. When you try to close the database, if you haven’t saved your work on a table, Access prompts you to save it. Or, at any time, select .
Add a field by entering data
- In Datasheet view, enter data in the Click to Add column of the datasheet.
Access creates a new field.
- In the column heading, type a new name for the field.
Change the data type of a field
When you add a field by typing data into it, Access sets the field’s data type based on its contents. View the data type on the Fields tab, under Data Type.
To change the data type:
- Select the field.
- On the Fields tab, open the Data Type list and select a data type.
Add a field for a specific data type
Access validates data as it’s entered to ensure that it matches the field’s data type. If you need a specific data format in a field, specify its data type when you create it.
- With the table open in Datasheet view, select Click to Add and then select a data type
- Type a descriptive name for the field, such as Last Name.
Because you specified a data type, Access validates the data you enter in the new field. For example, text isn’t accepted in a Date field. Specifying the data type also helps to minimize your database size.
Combine fields using the Calculated data type
Use calculated fields to concatenate data from separate fields, like first and last name; or to calculate dates, like order date plus five days.
Use calculated fields to:
- Calculate values that don’t exist in your data.
- Specify criteria for queries.
For example, combine first and last name data to display in a full name field, like this:
[First Name] + ” ” + [Last Name]
The plus signs (+) combine the value in the First Name field, a space character (a space enclosed in quotation marks), and the value in the Last Name field.
Or calculate the quantity, price, and tax rate for each item in an order to display the total price, like this:
([Quantity]*[Unit Price])+([Quantity]*[Unit Price]*[Tax Rate])
NOTE: The calculation can’t include fields from other tables or queries. The results of the calculation are read-only, but they are usable as values in other calculated fields.
Calculated fields display results based on the data type you chose.
|Text||Displays calculated data as a text string, exactly as typed in the referenced fields.||To display full names, concatenate values from first and last name fields with a space between them. To ensure sortability, remove “A” or “An” from the beginning of a book title.|
|Number||Displays calculated data as a number, which can be used in other mathematical calculations.||To display minimum inventory level, subtract average quarterly breakage from average quarterly sales.|
|Currency||Displays calculated data in the selected currency format.||To display order total in the specified currency, multiply order quantity by price per unit.|
|Yes/No||Displays calculated data as Yes or No. Stores data as a Boolean value.||To display Yes if the discounted total exceeds the minimum needed for free shipping and No if it doesn’t, multiply order total by discount and validate against the minimum purchase amount that qualifies for free shipping.|
|Date/Time||Displays calculated data as a date or a time stamp.||To display estimated delivery date, add estimated shipping time to order date.|
- Select a table.
- Select Click to Add > Calculated Field, and then select a data type.
- Enter a calculation for the field, and then click OK.Type the expression yourself, or select expression elements, fields, and values to put them into the expression edit field.
NOTE: In a calculated field, don’t start the expression with an equal sign (=).
- In the field heading, type a name for the calculated field and then press Enter.
NOTE: To change or edit the output of a calculated field, select the column. Then, select Fields > Modify Expression.
After you add a calculated field to a table, the calculation is done each time you add or change data.
build tables with Table Designer
When you create a table with Table Design, you can be more deliberate about designing your database, set field types, create a lookup list, and create a foreign key to pair with your primary key.
Create a table with Table Design
- Select Create > Table Design.
- In the new table, for the first field, enter a field name and a data type.
- To set the primary key for the table, select the Field Name cell for the appropriate field and then select Primary Key.The Primary Key indicator appears to the left of the field name. You can change the primary key at any time, but each table can have only one primary key.
- Select File > Save, and name the table.
There are many more options when you build and modify tables with Table Design.
- Switch between Design View and Datasheet View for any saved table.
- Designate any field as the table’s primary key.
- Use Builder to create an expression or input mask.
- Test validation rules.
- Insert and delete rows.
- Create lookup lists.
- Create, rename, and delete data macros.
- Define relationships and object dependencies.