How to Design a Database Using Access

Create Database Using Microsoft Access – Microsoft Access is a nifty software application that comes bundled with the Microsoft Office Professional Suite. With this simple database tool, we sure can learn to organize our work and our life better!

Why use Microsoft Access?

Most people ask us, "Why do I need some fancy database software? Excel works nicely enough for my varied needs."

Here's where we happily tell you that Access is no "fancy" database. It is easy, mighty effective, and can save you a whole lot of time and repetitive effort even as you get the best from your data. In fact, you'll find that you can get a whole lot more out of your data when you combine Excel sheets with a well-organized Access database.

Microsoft Access

• It is a relational database tool. This means that all data is organized into (related) tables. You may use the tool for budgeting your monthly expenses, organize your library catalogue or maintain an elaborate address book; the key lies in structuring your data into simple, non-redundant tables that can be linked together.
• "Remembers" your data. Unlike Excel, it also works as a seamless memory bank that stores and remembers your data so you can easily retrieve it for future use. For instance, you may have stored several Excel sheets for monthly expenses. But if you now want to combine these sheets and study them together, you'll have to use special (and sometimes cumbersome) Excel features like charts, dashboards, etc. In Access, this is seamless.
• It makes complex data operations effortless. Access offers all the basic operations that Excel does, like sorting, calculations, filters, etc. In addition, it also allows you to join or exclude data from tables (like joining Excel sheets) based on special criteria. And the good news is that all of this is effortless in Access!

In this article, we guide you to build your first working database with 6 simple steps.

1. Organize your data into structured tables.

If you intend to use the Access database, the chances are that you already have a fair idea about the table structures you want to create. Please think of this as an important preparatory step as it is the structure of your tables that decides the success of your Access application.

Some questions that will help you get this right include:

• What kind of data do you have? Business records, Personal files, data that support your Cultural society? Ideally, each type will contain its own database.
• What is common among the data you have? Can you group them into separate tables based on this common factor? E.g., Income, Expenses, Address List, etc.
• What is unique across each table? What can you use to identify one row of data from each table? E.g., Name, Id, etc. This will be the primary key and can be one or more columns. (Don't worry if you don't find something unique, as Access can auto-generate a primary key.)
• What are the connecting links across tables? These are potential foreign keys (you will learn more on this below).

We suggest you have a rough draft of your table structure on paper. Don't worry about perfection; your tables will evolve as you work with Access and gain mastery over it. Once you're ready, launch Microsoft Access, choose File -> New -> "Blank Database", and click on the "Create" key to kick-starting your first Microsoft Access database!

Hint: Choose an appropriate name that reflects the type of your database; this sounds way better than simply calling it"My first database"!

2. Create the Tables

Once you successfully launch and create your first database, Access opens up the "Table Tools" View to create tables, because really, Access is all about tables!

Here, we will create a simple budget application with 3 tables: Regular Expenses, Monthly Income and Monthly Expenses. The columns highlighted in blue are unique and can hence be used as the primary key. (Do note that where more than one column is highlighted in blue, the combination together is unique.)

To add the Regular Expenses table, use the right-hand side tab.

• The first column (ID) is auto-generated by Access and serves as the default primary key. (We can change this later.)
• Click on "Click to Add" to enter the next Column name. You will be given a (drop-down) choice for the type of column. Choose Text, and enter "Expense Name".
• Again Click to Add the next column, choose Text and enter "Type".
• Again Click to Add the next column, choose Number and enter "Amount".
• Finally, save your newly created table (using Control Key + "S" keys). When prompted for a name, type "Regular Expenses".

And there, freshly created, is your very own first Microsoft Access table. Hurray!

From the CREATE tab, use Create -> Table and follow the above steps to similarly create tables "Monthly Income" and "Monthly Expenses".

Recommended courses

3. Enter data into tables

In this step, we will manually enter data into the tables. But, note that Access offers several other effortless ways to import data into tables (from an Excel sheet, from a text file, etc.).

Here, we must mention that Access wins over Excel in data validation if the tables are rightly designed. To understand the relevance of table design, we will simply add data to the "Regular Expenses" table and explore the challenges.

Here is the data we intend to add to our tables:

The "Datasheet View" is best used to add/delete/alter table data and opens up any time you double click on a table's name in the left-side tab. You can also access it using DESIGN Tab -> View -> Design View. Once you're in this view, choose the table you will work with ("Regular Expenses") in the left-side tab. On the right-side tab,

• Click on the second column (Expense Name) and type "Rent".
• Click on the third column (Type) and type "Fixed".
• Click on the fourth column (Amount) and type "2000".
• You have completed the first row. Repeat for remaining rows.
• Repeat the same for the "Income" and "Monthly Expenses" table.

Did you notice how Access automatically sorts the data in increasing order of amount? If you want to change this, you can right-click on the Amount field and change the sort order.

Access automatically generates a unique number for the first column (ID), as you are sure to have observed, as this treats this as the default primary key. This is the column that is typically used to retrieve unique rows of data from the table.

But this value may be difficult for us to remember as it has no relevance to our data. Also, we have already identified our unique primary keys and should capture this in the table design. The next step will show you how to re-design the table to take care of this.

4. (Re)Design your Tables

Once you've created your tables, we bet you're curious to see if they turned out okay. In fact, it's important to do this so you can include any additional rules for data validation and accuracy. You can verify this in the "Design View".

The more flexible Design View of Access is easily accessed using the DESIGN Tab -> View -> Design View. (Alternately, you can click on the table name in the left-side tab and choose "Design View".)

If you explore the "Regular Expenses" table (screenshot above), you're sure to catch some inconsistencies. Let's fix this as follows:

• By default, the auto-generated ID column is set as the primary key, and hence the small key icon to its left. We change this by highlighting (single click) the Expense Name column and choosing the "Primary" Key icon on the top tab. (You can also right-click on the column name and choose "Primary Key"). The key icon will change accordingly.
• We no longer need the ID column. So right-click on it and choose "Delete Rows". This will automatically delete this column (and all previously inserted values for it) from this table.
• The Amount column is set to data type Number. Change this to Currency.

Now our table looks good. You can similarly edit the other 2 tables in Design view to alter their primary key, set currency data type and add additional data into all the tables so we can play with it in successive steps.

5. Define Table Relationships

Any time you want to simply "view" your table data, the Datasheet View can be used. But we reckon you'd like to view the combined results of all your tables. This can be cumbersome to do in Excel but is effortless in Access. The key is in defining table relationships.

Relationships define how our tables are "connected" to each other. These connecting links are called "foreign keys" in Database jargon.

To define table relationships in Access, go to DATABASE TOOLS Tab and click on Relationships. You will see a dialog box that lists all tables. Double click on each table until they are seen in the background relationships view. If a table appears more than once, you can right-click on the extra table and select "Hide Table".

Once all 3 tables are visible, you can move them around the screen as you please.

But right now, there is nothing special about this view. Our tables are simply listed, disconnected. To change this, click on REGULAR EXPENSES. Expense the Name field, and drag it towards MONTHLY EXPENSES. Expense Name field. A dialog box should open as follows:

We are creating a one-to-many relationship between the Expense Name of Regular Expenses table and the Expense Name of Monthly Expenses table.

Regular Expenses.Expense Name -> Monthly Expenses.Expense Name

Primary Key -> Foreign Key

One -> Many

For this,

• "Enforce Referential Integrity" should be checked. This will ensure all added values in the Monthly Expense will be checked against the values of the Regular Expenses table before addition.
• Uncheck the tabs to "Cascade Update/ Delete Related Fields". This will cascade all updates/ deletes in the primary key table (in our case, Regular Expenses). So if the amount is updated for a particular expense, it will be cascaded in all related tables, likewise with delete. We deliberately uncheck this so we can catch any mismatch at a later time. Finally, click on Create.
• Similarly, create a relationship between the Month Name of Monthly Expenses and the Month Name of Monthly Income. Uncheck the "Enforce Referential Integrity" option here.

The relationship view should automatically reflect this as follows:

You have defined your first table relationship and are ready to get more out of your data!

6. Query and View data from your Tables

This is perhaps the most significant and useful part of using Access over Excel. You see, we've created our tables with the right design, added data and defined relationships. The next important step is to put this all together and view what our combined data looks like. And this is done through a QUERY.

Here, we will create a simple query to capture any mismatches in "fixed" expenses.

You can create a Query using the CREATE tab -> Query Wizard, the easiest way to create a basic query. You will be prompted for:

• Type (Simple Query Wizard).
• Table names and fields. Choose Expense Name, Type & Amount from Regular Expenses, and Month & Amount from Monthly Expenses.
• Detail or Summary. Choose Detailed.
• Type "Fixed Expense Mismatch Report" when the wizard prompts you for a name and click on Finish to generate the report query.

The generated query should look like this (without the red highlights):

But as you can see, this query shows all expense records, and not just the mismatched ones (as highlighted in red). We can fix this using the Query Design (right-click on the query name in the left-side tab and select "Design View").

We will use this view to add 2 criteria.

1. Under Regular Expenses.Type, add the value 'Fixed' under Criteria (include single quotes). This restricts the data records retrieved to have type=Fixed.
2. Under any one of the amounts, add the special Criteria to check for a mismatch: "[Regular Expenses].Amount <> [Monthly Expenses].Amount" (without quotes). The <> sign stands for "not equal to"). This will pick up only mismatched records.

Together, these Criteria will pick up mismatched Fixed Expense records.

The final result should look like this:

And this is indeed the final result we desire. Congratulations!!

The Final Note

In this basic tutorial, you've been exposed to a wide variety of Microsoft Access features, from tables to relationships to queries. We recommend that you build on the tables and queries listed here, attempting more complex operations.

Suggestions for further practice include:

• Importing date into tables from Excel sheets or comma-separated text files (also called csv files).
• Adding validation criteria for tables using the Design View. For example, attempt to restrict the values of Regular Expenses—type to either "Fixed" or "Variable" values only.
• Learn about "Normalized" tables and how it impacts your data. Re-design and create the examples shared here as Normalized tables.
• Use Query Designer from scratch to create a summary report containing total income, expense and balance each month for the year.

While your first few trysts with Microsoft Access may include a steep learning curve, we can guarantee the rewards your efforts will bring. Microsoft Access is a lightweight database tool that allows you to tap into greater power on your data with minimal effort. So keeping going and happy Access-ing!!

Recommended Article

Here are some articles that will help you get more detail about creating a database using Microsoft access, so just go through the link.

1. 10 Powerful Features of Microsoft Access
2. Excel Skills
3. How to Improve your Microsoft Excel Skills?
4. Pivot Chart in Excel 2016

How to Design a Database Using Access

Source: https://www.educba.com/creating-database-using-microsoft-access/

Share this post