How to Use MS Access – Simple Guide for Beginners

Looking to learn how to use MS Access? That is indeed a great choice.

The truth is that Access is a powerful tool that offers numerous features to manage your data efficiently. In this guide, I’ll show you how to use MS Access.

Here is a short list of some of the main things that you can perform in Access (not an exhaustive list):

• Create tables, and link multiple tables together
• Build Reports
• Add charts, such as Column, Bar and Pie Charts
• Apply different functions and formulas, similar to those that you can apply in Excel. Examples include: Sum, Multiply, Max, IF, Count, and many more…
• Construct a user-interface to provide users a simple and visual way to manage data
• Build databases, run SQL queries, VBAs and Macros to automate manual procedures

How to Use MS Access – A Beginner’s Guide

So what can you expect in this guide?

In this guide, I’m going to cover the following 5 topics:

1. Creating a table in Access
2. Exporting data
3. Deleting a table
4. Importing data into Access
5. Applying basic formulas

There might be a slight variation in the menus depending on the Access version that you’re currently using. Yet, the concepts are virtually the same for Access 2010, 2013 and 2016.

For illustration purposes, I’ll use Access 2016 to demonstrate how to use MS Access.

So let’s now dive into the steps needed to create a table in Access.

Topic-1: How to Use MS Access – Creating a Table

A table can be used to store your data. It is indeed a fundamental ingredient in Access, and a good starting point to better understand how to use MS Access.

I’ll use an example to illustrate how to create a table in Access to store your data. To start, let’s say that you’d like to store some data about electronic products and their associated prices:

Here are the steps that you’ll need to take in order to create the above table in Access:

(1) The first obvious step that you’ll need to do is to open MS Access

(2) Choose the option ofa Blank database:

(3) Type your desired File Name for your new database (for demonstration purposes, I typed the name of ‘Test’). Then, click on Create:

Under your new database (i.e., ‘Test’) you’ll be able to create and store your newly created tables.

You’ll notice that by default, a new table was created. The default table (which will be assigned a default name of ‘Table1’) currently doesn’t contain any columns (also known as fields) or rows (also known as records):

Adding Columns into your Table

(4) Now you’ll need to add two columns/fields into your table:

• The Product column that will contain short text to describe the products’ names; and
• The Price column that will contain prices with a $ currency symbol

So how do you add columns into your table?

Simply press on the Click to Add for each column (see the images below), and then choose the desired format for your column/field from the drop-down list.

More specifically, for the Product column, choose the format of Short text from the drop-down list:

Then, type the name “Product” (instead of the default name of “Field1”) to represent your Product column:

Similarly, for the Price column, press on the Click to Add on the adjacent column to the right. Then, choose the format of Currency from the drop-down list:

Finally, type the name “Price” to represent your Price column:

Adding Records into your Table

(5) Now that you have your columns, you’ll need to add the actual data into your table.

To add the actual data into your table, simply type the following for each row/record on the grid itself:

And this is how your table would look like in Access:

Notice that Access automatically created a column called ID, containing a unique number for each record. This column represents your Primary Key – A Primary Keycontains a unique key to identify each record under your table. A primary key can be used to link tables in Access.

You can assign your own unique key for each record, or let Access automatically generate those values for you. For the time being, let Access generate these unique IDs (in the form of numbers) for each of your records under your table.

Saving your Table

We are almost done… To complete the last piece of the puzzle, you’ll need to save your table.

(6) To save your table, right-click on the tab that contains the default name of the table (i.e., “Table1”), and then press Save:

(7) Finally, type the name of “Product_List” to represent your new table name. Then, press OK:

(8) You can open and edit your newly created table at any time by double-clicking on the table’s name under the All Access Object menu:

In the next part of this guide, I’m going to show you two methods to export your data from Access into Excel.

Topic-2: How to Use MS Access – Exporting Data from Access into Excel

We will now review two methods to export your Product_List table from Access into Excel:

Method#1 – The “dirty” method

Here is a quick method to export your data from Access to Excel:

(1) If your Product_List table is not already opened, then you’ll need to open it by double-clicking on the table’s name under the All Access Object menu (as we saw in the previous section under step 8):

(2) Press Ctrl+A on your keyboard to select/highlight your Product_List table. Then, press Ctrl+C to copy your table:

(3) Open an Excel Spreadsheet, and then press Ctrl+V to paste your table into Excel:

Don’t forget to save your Excel file with the Product_List table.

Method#2 – The “clean” method

Access also provides a clean functionality to export your data. Here are the steps:

(1) Go to the External Data tab in Access:

(2) Click on the Excel icon that is located on the Access ribbon:

(3) The screen Export – Excel Spreadsheet will open up, where you’ll be able to specify the:

• File Name and location where your Excel file will be saved. Here, I chose to save the Excel file on my desktop with the file name of “Product_List”
• File Format. Here, I chose the standard Excel format of xlsx
• Export Options. Those are optional, but you can check the box to export data with formatting and layout

Press OK once you specified your export parameters:

(4) You’ll now have the option to save your export steps in case you’re planing to export your Product_List table multiple times.

It’s not a must option to use, but it can save you some time, especially if you update your table in Access frequently, and need a quick way to export your data on an ongoing basis.

To save your export steps, check the box for the Save export steps and then press Save Export:

(5) Now if you wonder how to execute your saved export steps at anytime, then simply go to the External Data tab, and then press on the icon of Saved Exports:

(6) Click on Run to export your latest Product_List table from Access into Excel (you may be asked whether you want to update/replace the existing file. If this is what you want, then press Yes):

So far, you have seen how to create a table in Access and then export it. But what if you want to delete your table? In the next section, I’m going to review the steps to delete a table that is no longer needed.

Topic-3: How to Use MS Access – Deleting a Table

So you created a table in Access and decided that you no longer need it.

You can easily delete a table by following these steps:

(1) The first thing that you’ll need to do is to close the table you’d like to delete.

Let’s say that you’d like to delete the Product_List table that you just created. Don’t worry, in the next section you’ll see how to import that table back…

To close the Product_List table in Access, right-click on the tab that contains the table’s name Product_List, and then press on Close:

(2) To delete the Product_List table, right-click on the table’s name under the All Access Objects menu. Then, select Delete from the drop-down list:

Click on Yes to delete your table:

Now that we saw how to create and delete a table, we are going to address the following questions:

What if you have a large number of records? Are you going to type them in Access one-by-one?

Well, that’s one way to do that… Another way is to use the Import feature in Access to import your data.

In the next section, I’m going to demonstrate how you can import your data from Excel into Access.

Topic-4: How to Use MS Access – Importing Data From Excel into Access

For simplicity, I’m going to use the same dataset as before, but the following concept will apply to larger datasets as well.

To start, let’s say that you have the following Product_List table in Excel, and you wish to import that table into Access (please note that the sheet name in Excel is “Product_List.” This name will be used under the Save Import Steps to be explained later in this guide).

So how do you import the above table into Access?

Steps to Import your Data

(1) Here are the initial steps:

• Go to the External Data tab
• Press on New Data Source
• Select From File
• Then select Excel

(2) Locate your Excel file which contains the Product_List table. To do that, first press on the Browse button:

Find your Excel file with the Product_List table and then press Open. In my case, I stored the Excel file on my Desktop:

Once you got the correct location of your Excel file, press OK:

(3) Under the Import Spreadsheet Wizard, keep the check box “First Row Contains Column Headings”as checked, since the Product_List table in the Excel file indeed contains headings. Then, press on the Next button:

(4) In the next screen, you can define the columns/fields formats. In our case:

• The first ID column has the Data Type of Double
• The second Product column has the Data Type of Short Text
• While the third Price column has the Data Type of Currency

ID column:

Product column:

Price column:

You can change the format for each of your fields through this screen. To do that, select your desired format from the Data Type drop-down list. Since in our case, all the fields (i.e., ID, Product and Price) already have the desired format, you can then just go ahead and press Next:

(5) Now you can choose among 3 options to either:

• Let Access add primary key; or
• Choose my own primary key; or
• No primary key

As mentioned earlier, a Primary Keycontains unique key to identify each record under your table. Since the ID column already contains our unique keys, then you’ll need to choose the second option of “Choose my own primary key” and set it to the ID column. Then, press Next:

(6) We are almost done with the import steps… In this screen, type a name for your table. For consistency, type the name of “Product_List” to represent the name of your imported table. Then, click on Finish:

Save Import Steps

(7) Now imagine that you have to repeat all those import steps every-time you’d like to import your latest table into Access.

That would definitely be a tedious task! Luckily, Access has the capability to save your import steps (in a similar fashion to what you’ve already seen for saving the export steps).

Simply check the box to Save ImportSteps,and then press on Save Import:

(8) Congrats, your new table is now fully imported into Access, and can be opened at anytime via the All Access Objects box:

Load Import Steps

(9) Bonus step – remember, not too long ago, that you saved your import steps under step 7…

Let’s say that you added a new record into the Product_List table in the Excel file. For example, you decided to add a laptop with a price of $1,200 (and an ID of 4).

This is how your new table would look like in Excel (with a sheet name of “Product_List”):

Then, how do you trigger those steps to automatically import the latest table from Excel into Access (without repeating all those tedious import steps)?

Here is how you do it:

In Access, go to the External Data tab, and then press on the Saved Imports icon:

Now simply click on Run,and then select Yes to get your latest Product_List table into Access. For this function to work, you’ll need to make sure that:

• The Excel sheet is named as Product_List (since this sheet name was originally assigned when you initially executed the import steps); and
• The 3 columns (i.e., ID, Product and Price) are present in your Excel sheet; and
• Your Product_List table is closed in Access

Press OK and then Close.

Double-click on the Product_List table under the All Access Objects box. You’ll now see the most up-to-date Product_List table with your new record of Laptop:

You may, or may not believe it, but it is even possible to import all your data into Access using one click.

In the final topic of this guide on how to use MS Access, I’m going to review the steps to apply basic formulas, such as the total sum.

Topic-5: How to Use MS Access – Applying Basic Formulas

You can apply some basic formulas in Access to summarize your data.

Here are few examples of formulas that you may apply (not an exhaustive list):

• Sum, Subtract, Divide and Multiply
• Max / Min
• Count
• IF functions

In this section, we will focus on deriving the total sum in Access.

Let’s say that you want to derive the total price across all of your products. You can achieve this goal by summing the prices under the Product_List table (this table should now also include the imported Laptop record).

So how do you accomplish this goal in Access? Here is what you’ll need to do:

Steps to Sum Values in Access

(1) First, go to the Create tab, and then press on the Query Design icon, where you’ll be able to execute some basic formulas:

(2) Select the Product_List table under the Show Table box, and then press on Add:

(3) Once you added your table in the background, click on Close in orderto close the Show Table box:

(4) To get the total price, you’ll need to type the following statement in the cell that corresponds to the”Field:” label, located at the bottom section of your screen (see the image below):

Total Price:Sum([Price])

For illustration purposes, I used different colors to explain each component in the above statement:

• Total Price: represents the new field name that will contain the total price.
• Sum()represents the basic formula of sum.
• [Price] represents the original field that you’d like to sum

This is how your sum statement would look like in Access:

(5) To see the results of your total price, click on the Run icon:

You’ll now see the total price of $2,950, which is indeed the total sum across your 4 products:

(6) To save your query, right-click on the tab that contains the default name of the query (i.e., “Query1”). Then, select Save from the drop-down list:

(7) Finally, type the name “Total_Price” to represent your new query name, and then press OK:

You can run this query at anytime by double-clicking on the query’s name under the All Access Object menu:

How to Use MS Access – Final Words

Congratulation, you just took the first step to better understand how to use MS Access!

Access is indeed a powerful tool that would make your life easier when it comes to managing your data.

You may also want to check the following guide for more advanced concepts that you can apply in Access.