Microsoft Access is a database management software that is different from Excel. It is a simple way to find, sort, store, and organize data. Microsoft Excel is best for formulas and calculations, but Microsoft Access allows you to create relationships between the information stored on the tables so you can do more with it.
You may see that you’ve been using Microsoft Excel for databases that you could have been better suited using in Microsoft Access.
How to Create a Contact List Database in Microsoft Access for Forms, Queries, and Reporting
When opening Microsoft Access, you have the option to create a blank database, or you can create a database using one of the many templates included in the program.
Today, we are going to create a blank database.
When you create a blank database, it will bring up the screen below. This requires you to save the database before getting started.
This program is slightly different from Microsoft Excel, where you can simply open it and get to work. When saving, it will show you where you’re saving the file. You can choose to save this file in any location on your computer.
Once you have created a file name, click create, and it will bring you to this screen.
On this screen, you have many of the standard Microsoft Office options that you do in Microsoft Word, Excel, and so forth. For example, in the File tab, you can open a new database, save the existing database, or print the database.
Home Tab
The Home tab allows you to look at the database using different views. You can choose between Database View and Design View.
When you click Design View, you must save the table you are designing. We have saved ours as Demo Contact, which you can see below.
From here, you can look through the different options that you have in the Home tab section.
The Home tab holds many quick tools you will need when editing. You can format the text, save the database, or sort and filter through the information.
Create Tab
The next tab is the Create tab. This tab allows you to create different elements in the database.
You can create tables, run queries, and create forms and reports using the Create tab, and it is where we will spend today’s lesson, so more on that will follow a little bit later.
External Data Tab
The next tab is the External Data tab. Using the External Data tab, you can import different information like an Excel worksheet or a Word document.
Database Tools Tab
The Database Tools tab is where you can build relationships, object dependencies, and run analysis on the Access database you have created.
How to Create a Table in Microsoft Access to Build Our Contact List
To build our contact list in Microsoft Access, we first need to create a table. To do this, go to the Create tab and click on Table. When you create a new table, you need to save it by right-clicking on the table tab, as shown below.
When you save your table, it will automatically create the first line item with an ID and Auto Number row. In our example today, we will show you how to delete this row because we don’t need this row for our example.
To delete this row, right-click on it, which will bring up a window that allows you to delete the row, as seen below.
At this point, you can fill in the field name for the first row. For this example, “first name” is our field name. In the second column, for data type, you can choose from many different data types, as shown below.
Because we’re creating a simple database for this example, we will stick with the short text data type, but you can choose many different data types depending on the type of database you are creating.
Next, we will need to create our second row, our last name field. To do this, you will simply type “last name” into the field name, as seen below.
From here, you will fill out the remainder of the field names with the information you need to build your contact list. We’ll show you what we used in the example below.
At this point, you will need to switch from the design view to the datasheet view. This is what it looks like in the datasheet view.
Just as in Microsoft Excel, you can simply type in the cells you want to fill with information.
Once you finish with the first record, you can press enter, and it will take you to the second line to create the second record.
You also have the option of clicking “new blank record” at the bottom of the screen, which will create a new blank record the same way hitting the return key does.
If you want to manually type in each record, you can, especially if this is the first time you are creating a contact list. However, let’s say you have a contact list already created in an Excel worksheet. You can import the Excel worksheet directly into your Microsoft Access table, and we’ll walk you through that now.
To import an Excel worksheet into Microsoft Access, you will need to close the table you’re working on. In this example, we need to close the “contact demo” table. Once closed, you will right-click on the table, go to import, and choose the type of file you want to import.
In this example, we are going to import from Excel. When you choose Excel, it will bring up the screen below. Make sure you know where the Excel datasheet is saved that you want to import to your Access table.
Once you have located the Excel sheet you want to import, click OK. It will bring up the Excel sheet in the format shown below.
Important: The fields must match for the Excel sheet to import correctly into your Access table. In our example, the fields must be first name, last name, and so on, for each field in the table.
Click “next” on the Import Spreadsheet Wizard and then click “finish”. Then close out the wizard.
When you click on the table you have created, it will show up, and all of the information will be populated.
And that’s how you create a table in Microsoft Access!
How to Create a Form in Microsoft Access
Now it’s time to show you how to create a form in Microsoft Access.
To do this, you will go to the Create tab and then click on Form. It will bring up a window that looks like this:
Each form populates with each record’s information.
So, when you scroll through each record, the information will change to reflect the record you’re on.
Let’s say there’s something you don’t like about how the form looks once you create it. You can easily fix this by going into Design View under the View tab, as shown below.
When you go into Design View, it will bring up a screen that looks somewhat intimidating but don’t let it scare you. This is where you can easily change the formatting or design of the fields in your form.
All you do is select the field that you want to edit and then make your changes accordingly. In this example, we have removed the “first name” field and recreated it by capitalizing it with “First Name”. You can make any changes you want. You can make the font bold, italicized, or underlined, just as you can in Microsoft Word.
You can do more than just adjust the way the font looks in Design View. You can also change the text box sizes and location to create the exact form you want.
To make more changes to the design of the form, you can go to the Form Design tab at the top of the screen. This gives you many more options to make changes to how the form looks.
Once you are done making the changes to the design of the form, you need to save it. You can do this by right-clicking on the table tab and clicking save or by clicking the X on the tab and bringing up the save box as seen below.
Once saved, the left side of your screen will have the table and the form you have created. If you don’t see both on the left side of your screen, make sure that you have selected “All Access Objects” on the drop-down arrow as seen below.
If you want to make changes to the data in the form, you can go to Home > View > Form View. This will take you to the screen below, where you can change the information in the different fields.
Let’s say you want to change the phone number on this record. Simply click on the phone number field and make the changes directly in this field. Keep in mind that any change you make in the form will be updated on the table.
You can also add a new record to the table by clicking the “New Record” button at the bottom of the screen to create a blank form for you to add information to create a new record on the table.
How to Create a Query in Microsoft Access
A query asks a question in Microsoft Access by searching through all of the information in the database and giving you an answer to the question you asked.
To create a query in Access, you will go to Create > Query Wizard. This will open up the new query box as shown below.
Highlight the “simple query wizard” and hit OK.
On the screen below, select the information to be included in the query.
Simply highlight the field you want to include and click the > arrow to ensure that the selected information appears in the query results.
Once you have included all of the fields in the query, you will click next and then click finish.
This will produce a query result in Access. Now on the left side of the screen, you will have a tables, queries, and forms section.
You can also modify your query search using Design View. To do this, you can right-click on the query tab and click “Design View” to bring up the design view of the query you have run, as shown below.
In this example, let’s say we want to look at just the results with Phoenix as the city. All you do is type Pheonix in the box as shown above. Then, click the “Run” button in the top-left corner, and you will see the query results showing the results of the modified query you created.
You can also drag additional fields into the query to include additional fields for this modified query. Just drag the field from the table box and place it in the next empty column.
If there’s a field you don’t want in the results, simply uncheck the box to remove it from the results.
How to Create a Report in Microsoft Access
The final function we want to show you today is how to create a report in Microsoft Access.
To create a report, you will need to highlight the query and then click the Create tab at the top of the page. From there, click “Report”, and it will bring up the screen below.
You can change how these fields look by going into Design View. You can change the font style and size as well as drag the fields where you want them to be on the report.
When you’re ready to print the report, just go to Print Preview to make sure it looks how you want it to look. Then you can click the print button to print the report, as shown below.
Conclusion
We hope this Microsoft Access tutorial helps you get a better understanding of how to build a contact list for tables, forms, and queries. We know that Microsoft Access can be an overwhelming program, so we wanted to make it as simple as possible for you today.
