Microsoft Access 101 – The Basics of Access

Database Software and Applications | Microsoft Access

Microsoft Access is the original “no code” platform created for people who have no specialized database knowledge, but want to manage their information or create simple applications.

However, many might still find MS Access daunting, which is why there is a thriving market of MS Access alternatives, which introduce yet another level of simplicity.

So what is Microsoft Access?

Microsoft Access is a database management system which allows people to organize information. This could be students, teachers, guardians and classes in a school; or customers and orders in a business, or policies and insured in an insurance firm. Since this information is usually interconnected – students have guardians, students attended specific classes – this information is stored in related tables. So MS Access can also be seen as a relational database.

To understand Access, you need to be familiar with the following components of Access.

Tables

Microsoft Access 2010 - Lesson 25: Grouping Records on a Report

A table is quite simply a collection of rows and columns. Each row represents an information object, and each column of that row lists the details of that information object. For example, a row might relate to a Student, and the columns could contain First Name, Last Name, Email, Phone Number, Address etc.

Unlike Excel, which is a free form table, where you can add any piece of information in any cell, you can enforce data types in Access columns. For example, a Student First Name can only have text, while the phone number field can only have a number.

Table Relationships

A key capability of MS Access is the ability to relate information between tables. Just imagine the relationship between students and guardians. You could quite simply add all information about students and guardians in a single table, but it would have a lot of columns to capture the information of both students and guardians, especially if a student had more than one guardian.

Access lets you store the information about students and guardians in separate tables, and just relate each student with their guardian. This is done by creating a column in the Guardian’s table for Student ID.

Since this column refers to the Students table, it is called a “Foreign Key“. The Student ID column in the Student table, which uniquely idenitfies a student is called a “Primary Key“.

Forms

VBA example - Microsoft Access : search data form

Although information can be entered and viewed in access directly in the data table, it is usually done through a richer window-like interface. This is called a “Form”. A Form can be customized, and provides a pleasing interface to enter or view/edit a single row of data in a table.

Queries

MS Access 2003: Complex set of queries that involve calculating ...

Queries are a powerful feature in Access that let you pull out any subset of data from across tables. A few examples:

  • A list of students who suffer from allergies
  • A list of students whose guardians live out of state
  • A list of Names and Emails of guardians to invite them to the annual day

Reports

Report Builder for Microsoft Access- The Better Access Report ...

Reports are similar to queries, in that they let you pull out specific data across tables based on specific conditions. However, Reports may be designed in a presentable layout where they might be printed, or provide a quick visual overview.

Conclusion

Our article merely scratches the surface of Access. The deeper you dive, the more possibilities emerge, in the spirit of Microsoft software. The possibilities of what you might achieve are endless, but it does assume some basic understanding of database concepts from its users.

For companies looking for a simpler, cloud based alternative to MS Access, we encourage you to take a look at our WorkMap.ai.