The Ultimate Guide to Using Microsoft Access

Microsoft Access is a database management program that allows anyone to easily create, edit, and maintain a database of information. Access, which is a part of Microsoft Office (now Microsoft 365), is suitable for anything from small projects to large business processes, and is a very visual program. This makes Access great for performing data entry, as you don’t need to create complex tables and spreadsheets the way you might in Microsoft Excel. Follow along as this wikiHow article teaches you everything you need to know about Microsoft Access.

Things You Should Know

When you create a database, you'll add your data to tables. Each table has columns (fields) and rows (records).
You can run queries to filter, modify, or delete data in tables. You can also run reports to summarize the data to fit your needs.
Create forms to easily look up and add new data.

Part 1

Part 1 of 8:

Creating a Database

Download Article

The database is what will contain all of your data in its various forms. You can choose to create a blank database from scratch, or choose from a variety of templates.
2
Select a template or choose Blank database. A blank database is a standard Access database and is good for local use. Creating a blank database will create one table as well. You can click any of the templates to see a preview and learn more about its capabilities.^{[1]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}
- Templates are pre-built database structures designed for a wide range of uses, such as task management, inventories, student data, and project management.
Advertisement
3
Name your database and click Create. You can also change your file's save location at this time, or stick with the default folder created for Microsoft Access.
- Depending on which template you selected (if applicable), you may be prompted to create a login for your database. You can add a secure login to any database upon creation.
4
Understanding Access files. An Access database is a file that stores all of your database information. Within the database, you can store and work with data in various ways.
- Tables: This is the main way that data is stored in your database. Tables can be compared to spreadsheets in Excel: the data is organized in rows and columns. Because of this, importing data from Excel and other spreadsheet programs is a relatively straightforward process.
- Forms: You can create and use forms to add, view, and edit any data in a table.
- Reports: These summarize and display the data in your database. Reports are for analyzing data and returning answers to specific questions, such as how much profit was made, or where customers are located. These are usually designed to be printed or shared with others.
- Queries: Queries are used to retrieve and filter your data. You can use queries to display specific entries from multiple tables. You can also use queries to create and update data.

Part 2

Part 2 of 8:

Creating Tables

Download Article

1
Create a table. When adding data to your database, you'll want to create one table for each type of data you're tracking.^{[2]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source} For example, you can list your customer contact information in one table, and products for sale in another. If you are starting with a blank database, you will automatically begin with one blank table. You can begin entering your data into this table, either by hand or by copying and pasting from another source.
- To create a new table, click the Create tab, then click Table. You can also click Table Design on the same tab to create a table in Design View, which gives you more control over your table design.^{[3]
  X
  Trustworthy Source
  
  Microsoft Support
  Technical support and product information from Microsoft.
  
  Go to source}
2
Add fields to the table. Fields are essentially column headers that contain a single type of data, such as name, age, or address.^{[4]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source} Each field has a data type, e.g., "Short Text," "Currency," or "Long Date," which applies to the values in that field (in other words, the values of the column). To name a field, double-click it, then type a name.
- You can set the data type for a selected field by clicking the Table Fields menu at the top, choosing More Fields, and then clicking the preferred data type.^{[5]
  X
  Trustworthy Source
  
  Microsoft Support
  Technical support and product information from Microsoft.
  
  Go to source}
- For example, if you create a field called "Phone" for customer phone numbers, you'll want to set the data type to "Phone" so the proper phone number format applies to the data in the column.
- The ID field, which is the first column, is used to store keys that uniquely identify each row (record) of data in your Access database.^{[6]
  X
  Trustworthy Source
  
  Microsoft Support
  Technical support and product information from Microsoft.
  
  Go to source} To learn more about the keys and the ID field, see Setting Table Relationships.
3
Add field values. Field values are the individual pieces of data added to fields. Each "cell" is a field value that contains information relative to the field (column) it's in. For example, if the field is "Phone," each field value in that column will be a different phone number.
- Each row is called a record. As you generate new records by adding fields, an ID number will appear in the ID column. You can use that ID number to reference that particular record.

Part 3

Part 3 of 8:

Importing Data

Download Article

If you want to import from a supported file or location, you can set Access to grab the information and add it to your database. This is useful for grabbing data off of a web server or some other shared resource:
2
Select the file type that you are importing. In the "Import & Link" section, you will see a few options for data types. If you do not see the type you need, click New Data Source (Microsoft 365) or More to view additional options. ^{[7]
X
Trustworthy Source

Microsoft Support
Technical support and product information from Microsoft.

Go to source}
- You can import data From File (Excel spreadsheets, HTML, XML, & text), From Database (Access, SQL Server, Azure Database, or a dBASE file), From Online Services (Dataverse, Sharepoint, or Data Services), or From Other Sources (ODBC Database and Outlook Folder).
- Once you select a source, the Get External Data wizard will expand.
Click Browse to select a file, or enter the address of the location that contains the data you want to import.
4
Choose how to import the data and click OK.
- To import the data, select Import the source data into a new table in the current database. A new table will be created for the selected data unless you already have a table with the same name and fields.
- Alternatively, you can select Link to the data source by creating a linked table. This option creates a table in the current database for the selected data that stays linked to the original data source.

Part 4

Part 4 of 8:

Setting Table Relationships

Download Article

1
Understand how keys work. Each table will have one primary key that is unique for each entry. By default, Access creates an ID column that increases in number for each entry. This is set as the primary key. Tables can also have foreign keys, which are fields linked with other tables in the database. The linked fields would contain the same data.
- For example, in your Orders table, you may have a Customer ID field to track which customer ordered which product. You can create a relationship with the Orders table and another table by adding the same ID field to the other table.
- Using relationships helps keep your data consistent, efficient, and readable.
It's at the top of Access.
3
Click the Relationships button in the toolbar. This will open a new window with an overview of all of the tables in the database. Each field will be listed underneath its table’s name.
- You will need to have created the field for the foreign key before you create the relationship. For example, if you want to use the Customer ID on the Orders table, create a field in the Orders table called Customer and leave it blank. Make sure it is the same format as the field you are linking (numbers in this case).
4
Drag the field you want to use as a foreign key. Drop it to the field that you created for the foreign key. Click Create in the window that appears to set the relationship for the fields. A line will appear between the two tables, connecting the fields.
- Check the box to “Enforce Referential Integrity” when creating the relationship. This means that if data is changed in one field, the other field is automatically updated. This will help keep your data accurate.

Part 5

Part 5 of 8:

Making Queries

Download Article

1
Understand the role of queries. Queries are actions that let you quickly view, add, and edit the data in your database. There are a wide variety of query types, ranging from simple lookups to the creation of new tables based on existing data. Queries are essential tools for building reports.
- Queries are broken down into two main types: Select and Action. Select queries pull data from tables and can make calculations. Action queries can add, edit, and delete data from tables.^{[8]
  X
  Trustworthy Source
  
  Microsoft Support
  Technical support and product information from Microsoft.
  
  Go to source}
To do so, click the Create tab and select Query Wizard. You can now do basic select queries, including finding duplicates, finding unmatched data, and more.

Creating a Select Query with Criteria

1
Open the Query Design tool to do more advanced queries. To do this, click the Create tab and select Query Design. You can use this tool to narrow down your select query and only display the information you need. Select is selected by default, but you can also select an action to perform an action query, such as Append, Make Table, Delete, and more. [Image:Use Microsoft Access Step 12 Version 5.jpg|center]]
- In this example, we'll create a select query that displays customer names and phones numbers.
Double-click the name of the table in the right "Add Tables" panel" to add it to your query.
In the small pop-up that contains the table name and field names, double-click each field you want to query. The fields will be added to the Design grid at the bottom.
4
Add your criteria. You can use several types of criteria, such as text or functions. For example, if you wanted to only display prices higher than $50 from your “Prices” field, you would enter >=50 into the criteria. If you wanted to only show customers from the UK, you would type UK into the Criteria field.
- You can use multiple criteria per query.
The Run button is located on the Query Design tab at the top of Access. Your Query results will be displayed in the window. Press Ctrl + S to save the query.

Creating a Select Query with Parameters

A parameter query will allow you to specify what you want to retrieve each time the query is run. For example, if you have a database with customers from various cities, you can run a parameter query to ask which city you want to display results for.
Add fields to be retrieved in the query by double-clicking them in the table overview.
3
Add a parameter to the Criteria section. Parameters are denoted by “[]” around the parameter. The text inside the brackets will be shown in the prompt that appears when the query is run. For example, to prompt for the city, click the Criteria cell for the city field, and type [Which city?].
- You can end parameters with “?” or “:”, but not with “!” or “.”
You can use multiple parameters to create a custom range for your query results. For example, if the field is a Date field, you can return a range of dates by typing Between [Enter starting date:] And [Enter ending date:]. You will receive two prompts when you run the query.^{[9]
X
Research source}

Part 6

Part 6 of 8:

Creating a Make Table Query =

Download Article

You can use queries to pull specific data from existing tables and create a new table with this data. This is especially useful if you want to share specific parts of your database, or create specific forms for subsets of your database. You will need to create a regular select query first.
Double-click on the tables that you want to pull your data from. You can pull from multiple tables if necessary.
Double-click each field that you want to add from the table overview. It will be added to your query grid.
If you want to specify specific data from a field, use the criteria section to set the filter. See the “Creating a Select Query with Criteria” section above for more details.
Before you create your table, run the query to ensure that it is pulling all of the correct data. Adjust your criteria and fields until you get all of the data that you want.
Press Ctrl + S to save the query for later use. It will appear in your navigation frame on the left side of the screen. Click on the query to select it again and then click on the Design tab.
A window will appear asking for your new table name. Enter the name for the table and click OK.
Your new table will be created with the query you established. The table will appear in your navigation frame on the left.

Creating an Append Query

You can use an append query to add data to a table that already exists from another table. This is useful if you need to add more data to a table you created with a make table query.
This will open the Append dialog box. Select the table you want to append.
For example, if you created a table with the criteria “2010” for the Year field, change it to the year you want to add, such as “2011”.
Make sure to set the correct fields for each column that you are appending. For example, when using the above changes, data should be appending to the Year field on the Append To row.
Click the Run button on the Design tab. The query will be run and the data will be added to the table. You can open the table to verify that the data was added correctly.

Part 7

Part 7 of 8:

Creating Forms

Download Article

Forms allow you to easily see the data for each field, as well as quickly switch between records or create new ones. Forms are essential for extended periods of data entry, as most people find them much easier to work with than tables.
2
Click the Form button in the Create tab. This will create a form based on the fields contained in the table automatically. Access does a pretty good job of automatically creating fields that are the correct size, but you can resize and move around any elements on the form that you want.
- If you don’t want a specific field to be displayed on the form, you can right-click on it and select Delete.
- If your tables have relationships, a datasheet will appear beneath each record, showing the connected data. You can edit your connected data this way much easier. For example, each sales rep in your database may have a customer database attached to their record.
The arrow buttons at the bottom move from record to record. The fields will be populated by your record data as you switch between them. You can use the buttons on the edges to move to the first or last record.
This is located in the upper-left corner, and will allow you to start changing the values of your table by using the form.
You can edit the text in any field of each record to change the data in the table. The changes will reflect automatically in the table, as well as in any connected tables.
Click the “Add Record” button near the navigation buttons to create a new record at the end of the list. You can then use the fields to input data into the blank record in the table. This is a much easier way to add new information than through the table view.
Make sure to save your form by pressing Ctrl + S so that you can easily access it again later. It will appear in your navigation frame on the left side of the screen.

Part 8

Part 8 of 8:

Creating Reports

Download Article

Reports allow you to quickly display summaries of your data. They are often used for income and shipping reports, and can be tailored to just about any use. Reports draw data from either tables or queries that you have created.
2
Click the Create tab. Select the type of report you want to create. There are a few different ways you can go about creating a report. Access can create your report for you automatically, or you can create a custom one.
- Report – This will create an auto-report with all of the data from your source. Nothing will be grouped, but for small databases this is probably sufficient for showing what you need.
- Blank Report – This will create an empty report that you can fill with your data as you see fit. You will be able to choose from any available field to create a custom report.
- Report Wizard – The report wizard will guide you through the report creation process, allowing to to choose and group your data, and then format it accordingly.
3
Set a source for a blank report. If you’ve selected to create a blank report, you’ll need to select a source for it. First, click the Arrange tab and then select Property Sheet. Alternatively, you can also press Alt + Enter.
- Click the down arrow next to the Record Source field. A list of your available tables and queries will appear. Select one and it will be assigned to the report.
4
Add fields to your report. Once you have a source, you can start adding fields from it to your report. Click the Format tab, and then click Add Existing Fields. The Field List will appear in the right frame.
- Click and drag the fields you want to add into the Design frame. The record will appear in the report. As you add additional fields, they will be lined up automatically with existing fields.
- You can resize fields by clicking on the edges and dragging the mouse.
- Delete fields from the report by clicking on the heading and pressing the Delete key.
5
Add groups to your report. Groups allow you to quickly parse information in a report, as they allow you to organize related information. For example, you may want to group sales by region or by salesperson. Groups allow you to do this.
- Click the Design tab, click the Group & Sort button.
- Right-click on any part of the field you want to add to a group. Select Group On from the menu.
- A header will be created for the group. You can adjust the header to whatever you want to label the group.
Once your report is finalized, you can save it and then share it or print it like any document. Use this to share company performance with investors, contact information with employees, and much more.

Community Q&A

Search

Add New Question

Question

How do I put a password on a Microsoft Access database?

Community Answer

In the File tab, you can click 'encrypt with password'. Type in the password and encrypt it.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 9 Helpful 18
Question

How do I make the Access Database I have open to a specific form?

Community Answer

Go to where your form is listed in your computer window. Right-click on the form, then choose "Open With". Choose Microsoft Access. Alternatively, open Access, and got to "File>Open", (CTRL + O), and browse to the file you want to open.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 10 Helpful 9
Question

What kind of data can be used only with the Access database?

Community Answer

For any form of valid CSV (Comma Separated Value), Access, Word, Excel may be used. Integers, words, symbols, yes/no are all logical inputs for Access.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 13 Helpful 11

See more answers

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Video

Tips

Microsoft Access opens in "Backstage View," which provides menu options that allow you to open an existing database, create a new database or access commands to edit any of your databases.

Thanks
Helpful 0 Not Helpful 0

Submit a Tip

All tip submissions are carefully reviewed before being published

Submit

Thanks for submitting a tip for review!

Warnings

Some features in Access are not always available, depending on the type of database you created. For instance, you cannot share a desktop-only database on the Web, and some desktop features, such as query totals, will not work on a Web database.

Thanks
Helpful 9 Not Helpful 5