Learn Microsoft Access In About 30 Minutes

In this video I'm going to show you how to create a simple Microsoft Access database. We will build tables, queries, forms, and a mailing label report, all in about half an hour. This video is for people who don't have time to watch my full 4-hour free Access Beginner 1 class, even though if you do have the time, I highly recommend it.

Links

• Access Beginner 1: https://599cd.com/ACB1
• Access Beginner 2: https://599cd.com/ACB2

Summary

In this video, I will show you how to create a basic Microsoft Access database. We will learn how to build tables, queries, forms, and a mailing label report, all in about half an hour. So if you're interested in learning Microsoft Access, sit back, relax, and enjoy.

Now, let's take a look at Microsoft Access. Across the top, you can see different templates you can choose from, such as asset tracking and contacts. However, in this tutorial, I want to build my own database from scratch. To do that, select "Blank Database" below. Here, you'll also see some previous databases that I've used in the past. You can click on them to open those, but for now, let's click on "Blank Database." Simply click on it once to select it, and then double-click to open a blank database.

Next, you'll be asked for a file name. By default, it is set to "Database 1." You can click in the field and change it to something like "Rick's Database" or any name you prefer. The database will be saved in the Users > Richard > Documents folder. You can change this location by clicking on the "Browse" button. For now, let's just click on "Create."

Access will now create your database file, which will be a single file containing all of your objects, such as tables, queries, and reports. They will all be inside this one file, known as your database file or an ACCDB file for those who are interested.

The first thing Access prompts you to do is to create a table. You'll see it named as "Table 1" here in the list of objects. All your tables and other objects will be displayed in this list. I will explain the different objects in a moment, but for now, let's focus on "Table 1," which is the first table in your database.

What exactly is a table? A table is where you store all the data in your database. It can be information about customers, orders, business cards, or even your baseball card collection. Every piece of data or information in your database is stored in a table. You can have one or more tables in your database, and they can work together. In this introductory class, we will focus on having just one table to store information.

Tables are made up of fields and records. If you have experience working with spreadsheets like Microsoft Excel, you can think of fields as columns and records as rows. In a database, fields store different types of information, such as first name, last name, address, and zip code. The records represent different instances or entries, like customer one, customer two, and so on. In Access, rows and columns are referred to as records and fields.

Access automatically starts each table with an ID field, which serves as a unique identifier for each record. For example, in a customer table, you would have a customer ID field. In an orders table, there would be an order ID field. In Access, this ID field is known as an auto number. It starts at 1 and automatically increments as you add more records (1, 2, 3, and so on). It is not easy to change these IDs, but don't worry, you don't need to modify them as they are used internally by Access.

Now, let's proceed to add additional fields to our table to store more information. To do this, click on the "Click to Add" option, and a list of field types will appear. "Short Text" is suitable for text fields that contain numbers and letters. For example, fields like first name, last name, and address would fall under this category. "Numbers" are used for storing numeric values that you work with regularly. "Large Numbers" are meant for extremely high numerical values typically used in scientific data. "Currency" is used for dollar amounts. "Dates and Times" can store both dates and times in a single field. For instance, you can input a value like "January 5th at 6 PM." "Yes/No" fields are used when you need to indicate a true or false value. "Lookups and Relationships" will be discussed in a later class. "Rich Text" allows formatting options like colors, bold, and italics within a field. "Long Text" serves as a memo field where you can input a significant amount of information, up to one gigabyte. "Attachments" enable you to link files, such as resumes, to your table. "Hyperlinks" allow you to link to web pages or email addresses. Finally, "Calculated Fields" offer advanced features, but for this tutorial, we will focus on the basics. Let's select "Short Text" as it's the field type you will use most frequently.

Now, we need to provide a name for this field. In the "Field1" placeholder, type in "First Name." Personally, I prefer not to use spaces in field names. It's a matter of preference, but trust me, if you delve into advanced development and start programming with VBA, you'll find it easier without spaces in field names. Press Enter to move to the next field.

Once again, select "Short Text" for the next field, and this time enter "Last Name" as the field name. Although you can combine first name and last name in a single field, I recommend keeping them separate. It's better to split them into separate fields as it simplifies things. For example, when creating a report, addressing someone as "Dear Henry" becomes easier when first name and last name are in separate fields.

Let's add another field of type "Short Text."

For this field, let's go with "Company Name." Note that if you're entering information for individuals who don't work for companies, you can leave this field blank. Remember, you can always leave fields blank.

Next, let's try a "Number" field. How about "Employees"? This field will store the number of employees associated with the company. Feel free to adjust the field name as per your preference, such as "Num Employees" if you prefer.

Now, let's add a "Currency" field. How about "Credit Limit"? This field will indicate the maximum amount the company is allowed to spend.

For the next field, let's go with "Date/Time." How about "Customer Since"? This field will capture the duration of the customer's relationship with your company. By the way, you can resize these fields if you can't see the entire field name. Simply click and drag the little divider between the fields to adjust their width.

Now, let's add one more field. Let's change the drop-down option to "Yes/No." If you accidentally click elsewhere and the field name is stuck as "Field1," you can right-click and select "Rename Field" to change the name. In this case, let's name it "Is Active." This field will indicate whether the customer is currently active or not. Avoid using question marks or any punctuation in field names. "Is Active" simply signifies whether the customer is included in mailing lists or if they should be excluded, for example, in the case of deceased customers.

While you can directly enter data into blank fields, I recommend giving these fields meaningful names. Unlike Excel, Access works best when fields have descriptive names, as we will be working with them later. Finally, let's add one more field of type "Short Text." Let's go with "State." We can use this field for queries later on. If you want, you can include the full address in this field, including the city, state, zip code, and country. However, for the purpose of this tutorial, we will focus only on the state field.

Now that we have defined the table with all the necessary fields, let's proceed to enter some actual data. Click back on the "First Name" field to begin entering data.

I'm going to put myself in as an example. Notice that as soon as I start typing, the record is being entered into the table, and I'm assigned ID number one. Alright, I'll hit Tab to move to the next field, just like in Excel. Last name: Ross. Company name: Amacron. Employees: 2. Credit limit: $10,000. Customer since: 11/94. Is active: Yes. You can toggle it on or off with the space bar. Personally, I prefer using the keyboard for data entry, so you can either click it with the mouse or use the keyboard. Tab to move to the next field: State - New York.

You can create dropdown boxes to select from a list of states, but that's a more advanced topic covered in the advanced classes. Tab again, and now I'm on record number two, the next customer. Let's add a couple more customers. Your school, Joe, will leave the company field blank. He has no employees, a $2,000 credit limit, customer since 1/20/10, and he is active. He is from Pennsylvania. How about Sue Jones from XYZ Corp? She has 15 employees, a $15,000 credit limit, customer since 1/1/2018, and she is not active anymore. She is from Texas.

That's basic data entry, quite simple. The data is automatically saved in your table as soon as you move from one record to another. For example, if I'm editing Sue Jones and change her name to Susan, the record becomes "dirty," meaning the changes have not been saved to the table yet. However, as soon as you move to another record or to a new record, the information is saved in the table automatically. You don't have to manually save it like you do in Excel.

If you make design changes to the table, such as adding or deleting fields, you will need to manually save the changes. Click on the Save button or use the keyboard shortcut Control-S to save the table. You will be prompted to provide a name for the table. In this case, let's name it "Customer List." I personally follow a convention where I end all of my tables with the letter "T." For example, queries end with "Q," forms with "F," and reports with "R." This convention helps differentiate between different types of objects, such as tables and queries, and makes it easier to work with them. Additionally, it can help avoid confusion when you have similar names for tables and queries.

Now that we have the table saved as "Customer T" in our object list, let's go ahead and close it. To do this, click on the little "X" on the tab, and it will close. If you want to open it again, simply double-click on "Customer T" in the pane. 

If you don't see the object pane, click on the button that shrinks it up to save space. If you want more room to work with the table, you can click on the little button to open and close it. Let's close the "Customer T" table for now.

Next, let's create another table using a different method. Click on "Create" and then select "Table Design." This will open up the table designer, which looks slightly different. 

One thing to note is that I have my menus collapsed. If you prefer to keep the menus open all the time, you can double-click on one of the entries, such as "Create," and the menu will stay open. This menu is called the ribbon. However, I find it takes up a lot of space, so I prefer to keep it collapsed. To collapse it again, double-click on it.

Now we are inside the table designer, which is where I prefer to build my tables. Here, we can specify the field names first and then go into data entry mode to input the data. Let's say we want to store a list of our products in this new table.

The first field you want to add to any table is the ID field for that table. Click on the field cell and enter "Product ID." Then, press Tab to move to the next column, and select "Auto Number" as the data type. You can either choose it from the dropdown or simply type "A" to select it. 

The "Description" field is optional, mainly for developers to provide additional information about the field. I rarely use it. 

Next, enter "Product Name" as the field name. I prefer not to use just "Name" as a field name because "Name" is a reserved word in Access, which can cause issues when programming. Similarly, we used "First Name" and "Last Name" in the previous table. Select "Short Text" as the data type.

Let's add "Unit Cost" as the next field, using the "Currency" data type. Then, add "Quantity in Stock" as a number field. There are different types of number fields available, such as long integers, bytes, integers, doubles, and decimals. In this case, we will use the "Long Integer" type, as it is commonly used for counting numbers. If you need fractional values, you can use "Double" or "Decimal," but "Long Integer" works for most scenarios. 

Lastly, let's add a field for notes. Previously called "Memo Fields," they are now referred to as "Long Text." If you are using an older version of Access (before 2007), it may still be called a "Memo Field."

Once you're done adding the fields, click on "Save" or use the keyboard shortcut Control-S to save the table. Name it "My Product T." 

When you save it, a message may appear stating that no primary key is defined. A primary key is the field that Access uses to uniquely identify each record. In this case, our primary key is the "Product ID" field. Confirm that you want to use it as the primary key, and Access will recognize it with a little key icon next to the field.

Save the table again using Control-S. You can switch to "Data Sheet View" (also known as Data Entry Mode) in a couple of ways. You can click on the

 "Design" tab and then select "Data Sheet View," or simply hit Escape. Another option is to right-click on the tab header and choose "Datasheet View."

Now we have two tables in our database: the "Customer T" table and the "My Product T" table. You can open both tables side by side if you'd like, by clicking on their respective names. 

It's important to note that these tables are independent and have no inherent relationship to each other. Customers and products are separate entities. In our expert-level classes, we will discuss table relationships and how to connect information. For example, we might want to know which customers have purchased which products. To achieve this, we would need a third table, such as an "Order" table, which would indicate which customers ordered which products. This involves setting up relationships between tables, but it's beyond the scope of this beginner tutorial. If you're interested in learning more about table relationships, I will provide a link to my expert-level classes in the video description below.

Returning to table design, I want to mention that personally, I prefer using the window mode instead of the tabbed interface in Access. To switch to window mode, go to "File," then "Options," and select "Current Database." Under "Document Window Options," change the default setting from "Tab Documents" to "Overlapping Windows." Click "OK." You will need to close and reopen the current database for the changes to take effect. When you reopen Access, you can find your database in the "Recent" section or click on "Open" and browse to locate it. 

It's worth noting that malicious programmers can potentially insert harmful code, such as Visual Basic scripts, into an Access database. Therefore, exercise caution when accepting database files, spreadsheets, or Word documents from others. In this case, since we created the database ourselves, it's safe to enable the content. Access is simply being cautious. 

Once you open the tables, you'll notice that they appear as separate windows within Access. This windowed view allows you to resize and arrange them as needed. This feature becomes particularly useful when working with forms. I prefer this windowed view over the tabbed interface, but it ultimately comes down to personal preference.

Okay, let's open up the customer table. Sometimes, you may not want to see all the information in the table, especially if you have thousands of customer records. In such cases, you might want to apply filters to only display specific subsets of data, like customers from a particular state or customers with a certain credit limit. 

To apply a filter, go to the state field, click on the drop-down box, and select "Text Filters." From there, you can uncheck "Select All" and check the desired option, such as "New York." Click "OK" to apply the filter, and the list will be filtered accordingly. You will notice the word "Filtered" at the bottom. To remove the filter and see all the records again, simply click on the "Filter Off" button.

Let's change the state of the Texas record to New York to demonstrate how the filter works with multiple records. After making the change, apply the filter to New York once again. Now, only customers from New York will be displayed. You can remove the filter to see all records.

Let's try another filter example. Suppose you only want to see customers with a credit limit of $10,000 or more. Click on the credit limit field, choose "Number Filters," and select "Greater Than." Enter 10,000 as the value. The filter will be applied, and only customers with a credit limit greater than or equal to $10,000 will be displayed. You can remove the filter to see all records again.

Yes, you can apply multiple filters simultaneously. Select New York as the state filter, click "OK," and then apply the credit limit filter for values greater than 10,000. This will show customers from New York with credit limits of $10,000 or more.

However, using filters in tables can become cumbersome, especially with large datasets. It's not an ideal solution if you want to quickly and easily view specific sets of information without going through the steps of applying filters. This is where queries come in. Queries allow you to create simple and efficient ways to view specific information based on selected criteria.

Let's turn off the filters. I rarely use filters directly in tables, unless it's for a one-time need. Most of the time, I prefer to build a query for such purposes because it's easier to manage. Click "Save Design Changes" if prompted.

So how do we create a query? It can be done under the "Create" menu, by selecting "Query Design." This will open the "Show Table" window. Here, you can choose the tables or queries you want to include in the query. For now, we'll focus on tables, specifically the "Customer T" table. Click "Add," and the table will appear in the background. Close the "Show Table" window.

You can rearrange the elements in the query design to improve visibility. For example, you can grab the bottom of a box and drag it down to see all the fields clearly. The table and its fields are displayed in the upper section, while the lower section is where we will add the fields we want to see in our query. For example, you may or may not want to include the customer ID field.

To add fields to your query, you can click and drag them from the table into the columns of the query design. Alternatively, you can double-click on the field name to add it to the query design. You can also select the field from the drop-down box. Access provides multiple ways to accomplish the same task, so choose the method that suits you best. In this case, we'll add the fields "First Name," "Last Name," "Credit Limit," and "State" to the query design.

Once the query is set up, you can run it to see the results. One way to run the query is by clicking on the "Run" button under the "Query Tools" section in the "Design" tab. The query results will appear, currently named "Query 1."

To go back to the design view, you can right-click and select "Design View." You can also use the buttons located in the bottom right corner of the window or use the menu options. Design view allows you to make changes to the query design.

If you want to filter the results to show only customers from New York, you can enter "New York" in the criteria field under the "State" column. Press Tab to confirm. Access automatically adds quotes around the text. Now, when you run the query, you will see only the records of customers from New York.

To add additional criteria, such as customers with a credit limit of at least $10,000, you can enter ">=10000" in the criteria field under the "Credit Limit" column. This represents an "AND" condition. When you run the query again, you will see customers who meet both criteria.

Remember that the query view shows live data. Any changes you make in the query view will be saved in the table. However, the changes may not be immediately visible in the query results until you close and rerun the query.

To return to the design view, you can click on the appropriate button or use the right-click menu option.

To filter records in a query based on multiple criteria, you can use the "OR" operator. Move the existing criteria to a new row and adjust the "OR" condition accordingly. For example, if you want to see customers who have a $10,000 credit limit or are from New York, you can cut the criteria for each condition and paste them in separate rows. This indicates that the criteria are connected by an "OR" condition. Running the query will display customers who meet either of the criteria or both.

After setting up the query, you can save it for future use. Press Ctrl + S to save the query. Choose a name for the query, such as "Customers from New York, 10,000 Q." The naming convention is a personal preference, but adding a "Q" at the end helps differentiate queries from other objects.

To run the query, simply double-click on it. The results will be displayed. This user-friendly approach eliminates the need for end users to work directly with tables and provides a more visually appealing interface. If further changes are needed, you can right-click on the query and select "Design View."

While forms offer a more aesthetically pleasing and user-friendly experience, designing them can be a complex topic. In the full-length classes, proper form design and aesthetics are covered extensively. However, for this tutorial, we will provide a crash course on form design.

To proceed, close the table and move on to discussing forms.

When designing a form in Access, it's helpful to keep the ribbon open for easy access to controls and options. To create a form, follow these steps:

1. Click on the table that contains the data you want to base the form on.
2. Go to the Create tab and click on Form.
3. Access will generate a user form with all the fields from the selected table.
4. Resize the form and the fields as desired. You can adjust the height and width of the fields and columns by dragging the edges.
5. Save the form by pressing Ctrl + S. Choose a name for the form, such as "customer F." Adding an "F" at the end is a personal convention, but you can use any naming convention you prefer.
6. To open the form, double-click on its name in the object list. You can navigate through the records using the navigation buttons at the bottom of the form.
7. To add a new record, click on the button for a blank new record. Use the Tab key to move through the fields and enter the desired data.
8. Forms can be customized further, such as adding buttons, colors, and other controls. Advanced form design topics are covered in more detail in comprehensive Access classes.

To make design changes, right-click on the form and select Design View. In this view, you can rearrange fields by clicking and dragging them. Remember to save any changes made to the form.

Forms provide a user-friendly interface for working with data, allowing users to easily navigate and enter information. They can be customized to suit specific needs and include interactive elements like menus and buttons.

Closing the form without saving changes will preserve the original design when opening it again.

Lastly, reports in Access are used for printing or creating PDF files to share information. As this tutorial focuses on the basics, further details on report design and functionality are covered in more advanced Access classes.

You've covered the four basic objects in an Access database: tables, queries, forms, and reports. Tables store the actual data, while queries allow you to display and manipulate the data in different ways. Forms provide a user-friendly interface for working with the data on the screen, and reports are used for printing or sharing data in a formatted manner.

The Label Wizard in Access is a useful tool for creating mailing labels. You can choose the label format, select fields from the table to include on the label, specify sorting options, and generate the labels. The resulting labels can be printed or exported in different formats, such as PDF.

Access provides various templates for creating tables, queries, forms, and reports. Additionally, you can use macros and modules to add advanced functionality to your database, including utilizing Visual Basic for Applications (VBA) programming language. Access offers a range of capabilities, and with proper knowledge and utilization, it can be a powerful database development tool.

If you have concerns about security or size limitations, you can combine Access with other databases like SQL Server or web servers to store the data while using Access as the front end. This allows you to leverage the strengths of different platforms and achieve your desired functionality.

Overall, Microsoft Access is considered a robust database solution, and with its versatility and capabilities, it can be an excellent choice for desktop database development.

Notes

I used Cockatoo to generate a transcript of my original video, and then ChatGPT for summarize it into text. What do you think of the results? Now they just need to come out with an AI tool that can take screen shots of my video at important moments and I will be able to automatically generate handbook PDFs. LOL.