What is Microsoft Access?

Lesson 1: Tables, Forms, Queries & Reports Basics

S M L XL FS | Slo Reg Fast 2x | Bookmark

In Lesson 1, we will learn what Microsoft Access is and what it is used for. We will discuss basic database terminology, the benefits of using a database, and how Microsoft Access compares to tools like Excel. We will also talk about the main parts of an Access database, including tables, queries, forms, and reports, and introduce more advanced tools like macros and modules. The lesson covers how Access handles data organization, relationships between data, data validation, and building user-friendly interfaces.

Navigation

Prev: Introduction
Next: Lesson 02

Keywords

Access Beginner, database terminology, relational database, tables, queries, forms, reports, data validation, user interface, data entry, data storage, data organization, action queries, data security, macros, VBA, relationships, data types, data manipula

Subscribe to What is Microsoft Access?
Get notifications when this page is updated

Intro

In lesson one, we will learn what Microsoft Access is and what it is used for. We will discuss basic database terminology, the benefits of using a database, and how Microsoft Access compares to tools like Excel. We will also talk about the main parts of an Access database, including tables, queries, forms, and reports, and introduce more advanced tools like macros and modules. The lesson covers how Access handles data organization, relationships between data, data validation, and building user-friendly interfaces.

Transcript

In lesson one, we are going to learn what Microsoft Access is and what it is used for. You will learn some database terminology. We will discuss the benefits of a database, and we will learn about the parts of a Microsoft Access database, including tables, queries, forms, and reports.

Before we get started working with Access today, let's go over some of the basic database terminology. A computer database is a program that lets you store, organize, and manipulate data. This is great for storing large amounts of information. You can use a database to organize that information by generating different reports and queries. You can use a database to manipulate the data and actually make changes to it.

In the days before computers, data would be stored on paper, usually in ledger books or on index cards. For example, to keep track of your customers, you would make a series of index cards with one customer per card. You would have a separate drawer of cards for the products that you sold or the suppliers that you did business with.

However, as efficient as this may have seemed at the time, it was very time-consuming to sort through the cards or to search through a large drawer of cards for some particular bit of information. When the first computers came along, the earliest databases were really nothing more than glorified text documents. They were great for storing information, and they certainly made searching and sorting easier. However, they lacked many features we would take for granted today, such as the ability to recognize relationships between the different types of data.

For example, you could have a list of customers with some basic details, but if you want to look up information on their purchases, you would have to look in a different file. The earliest databases had no way to relate this information together. This created many problems, including having multiple copies of the same information in different places, such as the customer's name and address. Updating all that information can be a nightmare.

Fortunately, Microsoft Access does recognize relationships, and that is one of its strong points, but much more on that later.

The next progression was for people to store their data in spreadsheets like Microsoft Excel. Excel is a great tool for storing small amounts of information and for analyzing data, but when it comes to large amounts of information, using Excel can be cumbersome. If you have more than a few hundred rows of data, you really should be using an Access database.

Plus, Excel has the same problem that early databases did. It is not relational. There is no way to link your customers to their orders or products to their suppliers, and so on. In addition, Excel can be difficult for novice users to work with. If you do not know how to use Excel, finding the information that you want can be daunting, whereas with Access, you can build a nice user-friendly interface for beginners to easily find their way around.

Plus, it is much easier to secure an Access database than an Excel spreadsheet to keep people from messing with data they should not be playing with. With an Access database, you can control exactly what people can do in your program.

So this brings us finally to the modern database. In my opinion, Microsoft Access is the best desktop database application available. An Access database can store large amounts of data much more efficiently than Microsoft Excel or a simple text document. Access databases are also relatively easy to set up compared to other database platforms.

An Access database can recognize relationships between your data. For example, if you keep track of customers and their orders, you can store all of your customer details in one place and all of their order information in another place. Access can relate those two together so you do not need lots of redundant information copied throughout your database. You do not need to, for example, copy all of the customer's information to each order that he places. The database can track that for you automatically.

One of the problems with spreadsheets and older database applications is that you have little or no control over what kinds of information get put into your database fields. With Access, you can specify exactly what types of data the user can type into each field. This will prevent, for example, a number where the customer's last name should be, or a four-digit phone number being entered, or a missing zip code. Access gives you strict controls over the structure of your data and that is a great thing.

Yes, it is possible to set up some simple data validation in Excel, but Access is much more powerful. Access is a great tool for you to build databases for other people to work with. You can design a very user-friendly interface so they do not get lost. All of the data entry forms and reports that they need to work with can be presented for them in a nice simple menu. Plus, since you, the developer, control the interface, you can easily secure your database and lock them out of sections they should not see.

Sure, there is a little bit of a learning curve to initially get your database set up, but once it is built, you will definitely save time in the long run and increase productivity.

Now, Access is a great tool by itself, and I have personally built Access databases that have run very small companies with two to five employees and fairly large businesses with hundreds of employees. Access can certainly handle a lot of traffic.

However, if you do eventually outgrow your Access database, you do not have to lose all of your work. You can upscale your database to a more powerful back-end database server like Microsoft's SQL Server. You simply send all the tables and the data up to the server, and you can keep the interface you have built, the forms, reports, queries, and so on. You get the rapid application development of Microsoft Access and the power of SQL Server behind it.

Let's talk about the parts of a Microsoft Access database. An Access database consists of data and the tools to work with that data. What are these tools? An Access database consists of tables, queries, forms, reports, and optionally macros and modules.

Tables are used to store data. All of the data in your Access database will be stored in one or more tables. Queries are generally used to organize data. Forms are used to display data on the screen and to edit that data. Reports are for printing out data or formatting information for the printed page.

Optionally, for more advanced users, macros are used to automate tasks and modules to give you the full Visual Basic programming language inside your Access database. Now, I have macros and modules grayed out because you can build a fantastic database in Microsoft Access without ever using a macro or writing a line of code. I cover macros and VBA module programming in my developer-level classes. But all you really need are tables, queries, forms, and reports.

All of the data in your Access database is stored in one or more tables. You can think of a table like a single Microsoft Excel spreadsheet. However, tables give you much more control over the types of data that can be input into them. For example, here you see part of a customer table.

Tables are made up of a collection of fields. Each field holds a specific type of data. For example, here I have highlighted the last name field in red. This field should only store the customer's last name and nothing else. In fact, you can specify rules in the database to force fields to contain only certain types of information, like text, numbers, dates, currency values, and so on.

Fields are sometimes referred to as columns, just like in an Excel spreadsheet. All of the data concerning one item is stored in a record. Each record consists of the collection of all of the fields of data for that item. In this customer table, for example, each record represents one customer. Here I have highlighted one customer, James Kirk, in red. You can think of a record like a row in an Excel spreadsheet.

You might not always be storing customers. A product table, for example, holds information on products, and each record would represent one product. An order table, for example, holds information on each order that is placed, where one record would represent one order. A contact history table, for example, like the one shown here, could store information about each time you talk to your customer. Every phone call, email, etc., will be stored as a separate record.

In a timesheet table, for example, each record might represent one instance of an employee clocking in or out. Your tables can store many different types of data, people, places, events, and so on. You should store one type of data per table. Your customer table should hold information on your customers. You would not store product information in your customer table. One of the mistakes that beginners make is they try to store too much information in one table.

For example, you would not try to store all of a customer's orders in the customer table. You would use a second table for that. The data in your tables might not be stored in any particular order. You may have hundreds of thousands of records in your table, and the boss says to you, I want to see a list of only customers from Florida sorted by last name. That is what a query is used for. A query can be used to display data in different ways. You can sort your data or apply criteria to only a few specific fields.

Queries can be saved and used later, so you do not have to keep redesigning them, and someone with little Access knowledge can run your query simply by double-clicking on it. Queries can also be used to modify data, add records, delete records, or edit records. We will learn more about these types of queries, called action queries, in our expert classes. For today, just keep in mind that queries let you view the data in your tables in different ways.

Forms are used for viewing and editing data on your screen. Forms allow you to build a nice user-friendly interface to work with data. Whether you are building a database for just yourself or for other people to work with, forms are a major time saver.

You can display information however you want. You can include just the types of data that you want your users to work with. You can combine information from multiple tables, such as displaying a summary of a customer's orders on the customer form. You can secure your fields, so users can only modify specific fields and can only see other types of data. You may not want all of your employees pulling up credit card numbers, for example.

You can also display calculations on your forms, such as the total number of days an employee missed work. Your forms can also contain drop-down lists called combo boxes, so users can select data. Command buttons allow us to perform tasks such as opening other forms or finding records. In fact, you can turn a form into a main menu for other forms.

The benefits of working with forms go on and on, but essentially you will build the interface with which users will work with your data out of forms. You never want your end users to work directly with your tables. You will build them nice, pretty forms they can use to work with the database.

Reports are specifically designed to present data to people who are not using your database. You can print a report out, you can send it to someone as an email attachment or as a PDF file, you can use reports to generate all kinds of data, customer information, invoices, product catalogs, mailing labels, charts, and lots more.

Anything you want to present to someone else can be designed as a report. A lot of times people interchange the words form and report. In Microsoft Access, they are very specific terms. A form is worked with on the screen, whereas a report is generated to be printed out or at least saved as a printable document. We do not print out forms and we generally do not work with reports on the screen. You generate them, then you print them out or you send them to someone.

An Access database can optionally contain macros and or modules. These are more advanced topics that I cover in my developer classes. In a nutshell, macros are generally used to automate repetitive tasks or to carry out simple actions like opening a form. Modules contain the full Visual Basic programming language and can really take your database to a professional level.

The good news is you can build a really great database without ever touching a macro or writing a line of code. So if you do not consider yourself a programmer, do not worry about it. You do not have to know these things to build great Access databases. But on the other hand, they are very easy to learn in my developer classes.

Quiz

Q1. What is a main advantage of using Microsoft Access over storing data in Excel?
A. Access is relational and can link different types of data
B. Excel is faster at searching large amounts of information
C. Access lacks data validation features
D. Excel allows more data per field than Access

Q2. In Microsoft Access, what is a table primarily used for?
A. Storing data
B. Printing data
C. Generating reports
D. Creating user interfaces

Q3. Which of the following is NOT a component of a basic Access database?
A. Tables
B. Queries
C. Spreadsheets
D. Forms

Q4. What is the purpose of a form in Microsoft Access?
A. Displaying and editing data on the screen in a user-friendly way
B. Printing mailing labels
C. Sorting data for export
D. Importing data from other databases only

Q5. Why should each type of data be stored in its own table in Access?
A. It reduces redundancy and keeps the database organized
B. It makes the database harder to use
C. It prevents using queries on the data
D. It increases the speed of printing reports

Q6. Which statement best describes a record in an Access table?
A. A group of related fields representing one item
B. A single column in the table
C. An entire Access database file
D. The instructions for generating a report

Q7. Why are queries important in Access databases?
A. They allow you to view, sort, and filter data in different ways
B. They replace the need for tables
C. They are printed directly as reports
D. They can only be used by programmers

Q8. What is the main difference between a report and a form in Access?
A. Reports are intended for printing or sharing while forms are for on-screen data entry and editing
B. Forms have more data than reports
C. Reports are always interactive
D. Forms are only used for administrators

Q9. What is a potential benefit of setting data type rules for fields in an Access table?
A. Preventing incorrect data, such as numbers in name fields
B. Making the database more visually appealing
C. Slowing down data entry
D. Hiding data from all users

Q10. What are macros and modules used for in Access?
A. Automating tasks and writing advanced code
B. Printing reports quickly
C. Managing tables only
D. Copying data between databases

Q11. If a business grows too large for Access alone, what can be done?
A. Upgrade the database to use SQL Server as a back-end
B. Delete old records to free up space
C. Only use Excel from then on
D. Build separate databases for each department

Q12. Which of the following would NOT typically be stored in a customer table?
A. Product information
B. Last name
C. Address
D. Phone number

Q13. What is one reason NOT to let end users work directly with tables in Access?
A. Forms provide a user-friendly and secure way to handle data
B. Tables are faster to use for beginners
C. Users can only print from tables
D. Reports are needed to enter data

Answers: 1-A; 2-A; 3-C; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is all about introducing you to Microsoft Access, exploring what it is, and explaining what it is typically used for. We will go over some important database concepts, discuss why databases are so valuable, and cover the main components that make up an Access database, such as tables, queries, forms, and reports.

First, let's start with some basic terminology. A computer database is a type of software that allows you to store, organize, and manage data efficiently. Databases are ideal for handling large volumes of information, allowing you to keep things organized and quickly generate reports and queries to view or modify your data.

Think back to how information was managed before computers. Data would be stored manually, often in ledger books or on index cards. For example, you might have used a separate card for each customer, kept in a big file drawer. Products or suppliers would each have their own sets of cards. While this worked, it was very time-consuming when you needed to sort through the cards or find specific information.

Early computers improved this process somewhat by letting you store data electronically, but the first databases were really just simple text files. They made searching and sorting data easier, but they didn't support relationships between different types of data. For instance, customer details and purchase information might be kept in separate files, and there was no easy way to relate those pieces of information without entering details in multiple places. This led to duplicated data and could make updating customer information a real hassle.

Microsoft Access stands out because it does understand and manage these relationships. We will look into this ability in more detail later.

Before Access became popular, many people turned to spreadsheets, like Microsoft Excel, to store and manage data. While Excel works well for small amounts of data and quick analysis, it becomes difficult to use effectively with larger datasets. If you have more than a few hundred records, it really makes sense to use a proper database system such as Access.

Excel, like the early databases, isn't relational. You can't easily cross-reference customers to orders or products to suppliers. Plus, Excel can be challenging for beginners, especially when it comes to finding specific data. With Access, you can build a much more user-friendly system, where people can find the information they need through menus and forms.

Another advantage is security. It's much easier to control what people can see or edit in an Access database compared to an Excel sheet. You get much more control over who can do what in your database.

All of this leads us to what we consider modern databases. In my experience, Microsoft Access is the best desktop database tool out there. It handles large amounts of data much more effectively than Excel or text files, and it's relatively easy to set up, especially compared to big enterprise databases.

Access is built to handle relationships between different types of data. For example, if you are tracking customers and their orders, you store customer data in one place, order data in another, and Access links them together for you. This means you don't need to duplicate customer information for each order; the system keeps track of the relationships automatically.

One challenge with spreadsheets and older database tools is that you can't really control what users enter into your data fields. Access allows you to set exact data types for each field, so you avoid problems like a phone number in a name field or incomplete addresses. Access gives you the ability to enforce data consistency and structure.

While Excel lets you set up basic data validation, Access is much more robust. It's especially useful if you're building databases for others to use. You can create simple, intuitive forms and menus, so even users without much technical knowledge can interact with the database comfortably. Plus, you can lock down areas they shouldn't access and make the database secure.

It does take a bit of effort to build a good database up front, but you will save a lot of time and work in the long run, and you will boost your productivity.

Microsoft Access is powerful enough to support anything from very small businesses to organizations with hundreds of employees. If you eventually find you need even more horsepower, you can move your data to a more robust system like Microsoft SQL Server, and keep using the same Access interface you built. That way, you get both the speedy development capabilities of Access and the muscle of a back-end database server, without starting from scratch.

Now let's go over the main components of an Access database. The data itself is at the heart, but Access also provides tools to manage it. These include tables, queries, forms, reports, and optionally macros and modules.

Tables are where all your data is stored. Queries help you organize and work with that data. Forms provide a way to display and edit records onscreen. Reports are used to prepare your data for printing or to share in a formatted way.

For more advanced needs, macros can automate tasks, and modules allow you to use the full power of the Visual Basic programming language within Access. However, you can build an excellent database in Access without ever touching macros or writing code. I cover those advanced topics in developer-level courses, but at the beginner level, just focus on tables, queries, forms, and reports.

Every piece of data in Access is stored in one or more tables. You can think of an Access table as being similar to an Excel worksheet, but with a lot more control over the data types in each column. Each table is made up of fields, where each field stores a particular piece of information, such as a customer's last name. You can enforce rules for each field, specifying exactly what type of data is allowed, whether it's text, numbers, dates, or currency.

Fields are like columns in a spreadsheet, and all the information for a particular entry is called a record. In the customer table example, each row or record stands for one customer. Similarly, a product table would have each product as its own record, and an order table would have each order as a separate record. If you have a table for contact history, each interaction is a record.

You might have a timesheet table, where every clock-in or clock-out event for an employee is a record. You can store all sorts of data: people, products, places, or events. Just remember to keep one type of information per table. For instance, keep customer info in a customer table, product info in a product table, and so on. One common beginner mistake is trying to store too much information in one table.

Don't try to store every customer order within the customer table, for example. Create a separate orders table. The data in your tables may not appear in any particular order. With hundreds or thousands of records, if your boss asks to see only customers from Florida sorted by last name, that's where queries come in. Queries let you display data in different ways, filter and sort it according to your needs.

You can save queries for later, so even inexperienced users can run them by double-clicking. Queries can also be used to modify, add, or delete data; these are called action queries and are covered in advanced classes. For now, just remember that queries let you view your table data from multiple perspectives.

Forms make it easier to view and edit records onscreen. You get to design a user-friendly interface so anyone - whether it's just you or a whole team - can work efficiently with the database. You can decide what fields to show, combine information from multiple tables, and summarize related data, such as a customer's order history right on the customer form. You can also secure certain fields or hide sensitive data so only authorized users can see things like credit card numbers.

Forms can display calculated values, such as the total days an employee has been absent. You can include combo boxes for easy selection and add command buttons to perform tasks, such as opening other forms or searching for records. You can even design your main menu out of a form, making the system even easier to use for non-technical users.

The real benefit of forms is that your end users never need to work directly in the tables. Instead, you set up attractive and easy-to-navigate forms for data entry.

Reports are for sharing your information with people who might not use Access themselves. You can print out reports, send them as PDFs or email attachments, and create all sorts of documents like invoices, catalogs, mailing labels, charts, and more. If you want to present professional, formatted information to others, you use a report.

It's important to distinguish between forms and reports in Access. Forms are for interacting with data on the screen, while reports are for generating print-ready documents. You generally don't print out forms, nor do you usually use reports interactively on screen.

Optionally, an Access database can also include macros and modules. Macros are for automating common tasks, and modules allow full-featured programming. You do not have to become a programmer to build fantastic databases, though. These tools are available if you want to extend your database further, and they are covered in my developer courses.

You can create impressive, fully functional Access databases without needing to use macros or write any code. If you do want to learn those more advanced skills they are available and pretty straightforward to pick up.

If you would like to see a full video tutorial with step-by-step instructions covering everything we talked about here, you can find it on my website at the link below.

Live long and prosper, my friends.

Topic List

What is Microsoft Access
Uses and benefits of databases
Basic database terminology
Historical evolution of data storage
Problems with non-relational databases
Advantages of Access over Excel
Relational databases explained
Data validation and field types in Access
Tables in Microsoft Access
Records and fields explained
Designing efficient tables
Introduction to queries
Using queries to view and organize data
Introduction to forms
Building user-friendly interfaces with forms
Securing data through forms
Introduction to reports
Difference between forms and reports
Macros and modules overview (brief)

Article

Microsoft Access is a powerful tool designed to help you store, organize, and manage large amounts of information efficiently. At its core, Access is a database program that lets you manage data in a structured format, far beyond what a simple text document or spreadsheet can provide. Understanding what Access does and how it functions is the foundation for building effective and user-friendly databases.

Before computers, people organized information using paper methods like ledger books or index cards, keeping one card for each customer, product, or supplier. Although these methods worked for small amounts of data, they became time-consuming as the volume grew. Searching for information meant going through stacks of cards by hand. Early computer databases advanced the process by storing the data electronically, making searching and sorting easier, but they were still quite primitive. One major problem was that these early databases did not understand relationships between different types of data. For instance, if you had customer information in one file and their orders in another, there was no efficient way to connect the two. This caused issues like duplicated information and difficulty updating details in multiple places.

Modern databases, like Microsoft Access, have solved these problems by allowing you to create relationships between different sets of data. For example, you can store your customer details in one location and their orders in another, then easily link the two. This eliminates data duplication and makes updating information much simpler.

Some people use spreadsheet programs like Microsoft Excel to store information. Excel works well for small amounts of data and simple calculations but becomes unwieldy when dealing with hundreds or thousands of entries. More importantly, Excel is not relational, meaning you cannot directly link customers to their orders or products to their suppliers. Additionally, securing and restricting data in Excel can be challenging for beginners. In contrast, Access offers more robust security features, enabling you to set permissions and control what each user can see or do.

Microsoft Access is designed to handle large datasets efficiently and introduces important features that spreadsheets lack. For example, you can put in place strict rules about what kind of data is allowed in each field. This means you can ensure that only text goes into a last name field, dates go into date fields, numbers are entered where required, and so on. Data validation in Access is much more comprehensive, reducing errors and keeping your information accurate.

Another advantage of Access is that it lets you build user-friendly interfaces called forms. Instead of typing directly into a table or spreadsheet, users can enter and update information using customized forms that display only the necessary fields and guide them through the data entry process. This makes it much easier for people who are not comfortable with databases to interact with your application.

Access is flexible and scalable. It is well-suited for both small teams and larger organizations. If you eventually find that your business has outgrown a single Access database, you can migrate your data to a more powerful server like Microsoft SQL Server, keeping the interfaces and reports you have built in Access while taking advantage of increased performance.

Every Access database is made up of two main components: the data and the tools used to work with that data. These tools include tables, queries, forms, and reports. More advanced users also have access to macros and modules for automation and programming.

Tables are where all your data is stored. You can think of a table like a single sheet in Excel, but with better control over what data each column (called a field) can hold. Each field is meant for a specific type of data. For example, a Last Name field stores only last names, and you can set rules to prevent users from entering anything else. Tables are built from records, which are like rows in Excel. Each record contains all the information about a single item, such as one customer. Different tables are used to store different kinds of data: customers go in a customer table, products in a product table, and orders in an order table. It is important to keep each type of data in its own table to avoid confusion and redundancy.

As your table grows, it may no longer be practical to scroll through thousands of records looking for a specific subset of data. That is where queries come in. A query is a tool that allows you to view and organize data from your tables in different ways. For instance, you can make a query to list only customers from Florida, sorted alphabetically by last name. Queries can be saved for future use and run easily, even by users who are not database experts. In addition to viewing data, more advanced queries can be set up to add, edit, or delete records, but the most common use is for filtering and displaying data.

Forms are the interface you and other users work with when entering or viewing data. Forms take information from your tables and present it in a customized, user-friendly way. You can design forms so that only certain fields are shown, or group related information together, such as displaying a customer's orders right on their customer form. Forms also let you secure data by setting permissions on who can view or edit particular fields. They can include dropdown lists (combo boxes) to make selection easier, buttons for navigation, and calculated fields to show totals and summaries. You can even set up a main menu form to help users find their way through your database.

Unlike forms, reports are intended for presenting data in a format you can print or share, such as invoices, mailing labels, or summary charts. Reports gather data from your tables and queries and arrange it for printing or saving as a PDF. Whereas forms are interactive and used on screen, reports are static and designed for distribution. The distinction is important: work with data using forms, present data using reports.

Advanced features in Access include macros and modules. Macros automate repetitive tasks like opening a form or running a report, while modules provide access to the full Visual Basic for Applications (VBA) programming language. With VBA, you can add sophisticated logic to your database and create professional-level applications. If you are just getting started, there is no need to worry about these right away, as you can build very effective databases using only tables, queries, forms, and reports.

To recap, Microsoft Access is a desktop database application that allows you to efficiently store, relate, retrieve, and present data. It is more advanced and secure than using spreadsheets for data management and can be scaled up for larger organizations if needed. Access gives you control over your data, lets you design easy-to-use interfaces, and helps you produce professional reports. While there is a learning curve at the beginning, the time you invest in setting up a good Access database pays for itself many times over by making your work faster and more organized. With its combination of powerful features and ease of use, Access is an excellent choice for anyone looking to manage information more effectively.