Microsoft Access is an easy-to-use relational database program that enables businesses to address and manage an unprecedented amount of data. With Microsoft Access, you can track orders, assets, and do plenty of other business-related activities. In this blog, we will discuss in-depth on Microsoft Access and how to start using it as a beginner.
What makes Access Database better from Excel Spreadsheet?
Excel spreadsheet is a simple way of handling your business information, but it limits you in a number of ways. Following are 3 factors in this perspective:
- Let's assume you are selling some products and you have made an Excel sheet mentioning customer details, order ID, and the number of ordered items. Now if one of the customers makes another order, then you have to enter another row for the same customer mentioning the same customer details, new order ID, and the number of newly ordered items. So, you have two rows of the same customer in Excel that are not linked to each other, so it is not going to be business efficient. But if you have a database, then you can have separate tables for customer details and orders, and just update the relevant sections when the customer places a new order.
- In an Excel sheet, you get the limit of just over 1 million entries, while your database is capable to store and process unlimited entries.
- With Excel, you can extract some insights from the data using PivotTables and other features, but if you want to write a query and get a report, then this isn't quite easy. With a database, you can not only see useful in-sights but also write a query and get a report.
Understanding the Fundamentals of Designing Database using Access
In this guide, we will make a database in Access and use its different functionalities to see how it can help your business manage data and extract data analytics. Following are the steps you have to follow:
Step 1. Launch Microsoft Access and Create Blank Database
Launch Access and you will see the start page on it. From there, you have access to a number of different templates that you can use to design a specific database. As a beginner, let's start with a blank database. So, click on "Blank database". Enter the file name and then click "Create". This will take you inside your first Access database sheet.
Step 2. Understanding Basic Terminologies in Microsoft Access
Once you are in the database sheet, you will notice that it is showing "Table1". This means that you can create a number of tables and then link them out. Also, the interface is a bit similar to Excel, then instead of calling cells as rows and columns, here they are called records and fields. For this tutorial, we will assume that you want to make a database where you will keep a record of customers' details and the corresponding orders by them. For that, you will need two tables, one for customers and another for orders.
Step 3. Setting up Customer Table
The first field is showing "ID" which you can rename to "Customer ID" by double-clicking and then rewriting it. Now click the "Tab" key and it will take you to the next field where you first have to mention the data type. As customer ID usually involves first name, last name, email, address, and similar other details, so you will set the data type as "Short Text" here. Now it will ask you to enter the name of the field, you can type it as "First Name". Now press the "Tab" key and it will take you to the next field and ask again about the data entry. So, continue this process until you have set up fields naming "First Name", "Last Name", "Address", "Email Address", "Phone", and similar others as per your needs. You can also add the "Notes" field to remember some particular information about the customer and can make the data type as "Long Text" in it.
If you want to change the data type of a field, you can click on the field, and from the top ribbon, you can change the type from the "Data Type" drop-down options. By right-clicking any field, you also get different options, such as hide field, insert field, delete field, etc.
Step 4. Viewing Database from "Design View" and Database View"
In the customer table you are currently working on, you are in the "Database View". If you go to the extreme bottom left corner, you will see the icon of "Design View". Click on it and it will first ask you to save and rename the table. Afterward, it will show you the design view of the customer table with the fields you created and data types. From there, you can also easily edit data types or add any other description to the fields. To go back to the "Database View", click on the "Database View" icon located next to the "Design View" icon.
Step 5. Filling Data in the Customer Table
Once you are done setting up your customer table, it's time to start filling in the data. So, click below the field "First Name" and start filling out details. You will also notice "1" below the "Customer ID", which is basically the ID number that Access automatically assigns. Add around 4-5 customer details to the table for a better understanding of the remaining tutorial.
Step 6. Setting up Order Table
Now that you have completed the customer table, you have to create another table for orders. So, go to the "Create" tab from the top ribbon and click "Table". The new table will appear. Here, you can rename "ID" as "Order ID". As you want to link this table with the customer table, so add one field as "Customer ID" with the data type as "Number". You can add another field of "Order Date" with the data type as "Data & Time" and a field for "No. of Products Ordered" with the data type as "Number". You can also add another field for "Revenue" with the data type as "Currency" and another as "Order Fulfilled" with the data type as "Yes/No".
Step 7. Adding Calculation Field and using Expression Builder
The order table is incomplete if you don't know how much revenue you made per single product. You have the "Revenue" and "No. of Products Ordered" fields and you can easily use them to calculate revenue per single product. To do that, click on the new field and select the data type as "Calculation Field" > "Number". A pop-up window will appear which is the "Expression Builder", where you can enter the expression for calculating the value. As you want to calculate revenue per single product, double-click on "Revenue" and then type "/". Now, double click on "No. of Products Ordered" and then click "OK". Afterward, rename the field as "Revenue per single Product". This way, you have created a field that calculates the revenue per single product.
Step 8. Fill out your Order Table
Once you have set up all the basic fields in your order table, fill out some records to have some entries to proceed further. Make sure you are mentioning the same customer ID that you have on your customer table. Once done, you can go to the "Design View" to save the table and rename it, or you can cancel the table and it will ask you to rename it and save it before closing.
Step 9. Setting up Relationships between Tables
Your customer table and order table both have the "Customer ID" field, but your database does not know that they are the same. So, you have to set up a relationship between them. To do that, click on the "Database Tools" tab from the top ribbon and click "Relationships". Now here you can define the relationship among the tables. On the right-hand side, you will see all of your tables. Currently, you will have two tables. So, click on the customer table and pull it to the main blank screen. Now also pull the order table. Afterward, click on "Customer ID" from the customer table and drag it to the "Customer ID" in the order table. A pop-up window will appear which shows you are linking the two fields and then click "Create". This way, you have set up the relationship. Now go to the top menu ribbon, click on "Close", and then click "Yes" to save the relationship layout.
Step 10. Import Data from other Sources
Other than manually adding the data, you can also add data from other sources. To do that, click on the "External Data" and then click "New Data Source". From there, you can choose the data source from where you want to import the data.
Step 11. Create Data Entering Form
Currently, only you are able to enter data in the database. But if you want other users to also take part in data entry, you can create data entering form with a better user interface. Select the customer table from the left-hand side. Go to the "Create" tab from the top ribbon and click "Form". It will show you a sample form with your customer table field already integrated into the form. From the top ribbon, you can customize your form as you want. You can add themes, color, font, layout, etc. You can also go to the "Design View" to further modify your form. But currently, you and our fellow mates will be seeing existing entries in the form. To add a new entry in the form, you can simply click the "New (blank) record" icon from the bottom and get a blank form to fill. Once you are done customizing the form, you can close it and it will ask you to save the form. So, click "Yes", give the name to the form, and click "OK".
Step 12. Write and Run Queries
One of the advantages of databases is that you can write and run queries. To do that, go to the "Create" tab and click "Query Design". It will open the query designer in front of you. Let's assume you want to write a query about all the orders that have not been fulfilled till now so that you can send them to the warehouse. From the left-hand side, drag customer and order table one-by-one to the main blank screen. As you want to know all orders that are not currently fulfilled and you want to send that information to the warehouse, then the staff would be needing some basic information about order and customer, like customer name, address, order ID, etc. To add all such information in the query, double-click on "First Name", "Last Name", "Address", "Order Date", "Order ID", "Order Fulfillment", and other fields you want your staff should know about. For all the fields you double-clicked, you will see them added in the below sheet. The below sheet is where you will give the command how you want the query to proceed and also check/uncheck the checkboxes. From there, go to the "Order Fulfillment", and below its check box, write "=No". Now if you click on the "Database View", you will see the first query created, which displays only those orders where the "Order Fulfillment" status is "No", along with all those fields which you shortlisted from the customer and order table.
The above-based query is written based on a graphical interface, but you can also type a SQL query. To do that, click on the "SQL" icon showing the "SQL View" of the recently written query. You will see the actual SQL query you just created. You can learn the syntax from there and use it to write more advanced queries. You can also use the graphical query designer to write more advanced queries. Once you are done with query designing, close the window and save the query. Now from the left side, you can see the "Queries" category, and inside there will be the first query you just created. Now your warehouse staff can easily open this query to see which orders are not fulfilled till now.
Step 13. Create Reports
Maybe your management wants to see the data, or instead of letting your warehouse staff access the database to see the query, you want to print out the list of orders to be fulfilled every day. All this is possible by creating reports of your database. So, let's assume you want to make a report showing all the orders that are needed to be fulfilled. So, open the query you recently created, go to the "Create" tab and click on "Report". It will generate a report of all the orders that are pending fulfillment. You can format the report as you want, like themes, fonts, colors, etc. You can also go to the "Report Designer" to further modify the report. Once you are all done with the report editing, you can close it and save the report with a specific name. From the left-side, you can now see the new "Reports" category and inside there will be the first report you just created.
Step 14. Save Database
Once you are all done with the database designing, you just have to save the database. Click "File" and then "Save As". From there, choose the database file type and then save the database in the location of your choice.
Alternatives of Access
Microsoft Access is ideal for individuals and small businesses, but if you belong to a mid-size or large corporation, then you might face limitations with the Access database. So, there are few alternatives to Access you might opt for, such as MySQL, Oracle, MongoDB, Microsoft SQL Server, and similar others that are more scalable. But as seen from the above beginner tutorial of Access, it presents as a handy tool for those who want to learn, practice, and develop basic database designs.