00. Introduction (15:17)

Welcome to Microsoft Access Beginner Level 1, a beginner's guide to getting started with Microsoft Access Database Design, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

This class is for the beginner who has little or no experience building Microsoft Access databases. This is the introductory course in my Microsoft Access Series and is designed to teach you the absolute basics of how to build a database. If you've never used Access before, then you're in the right place - start with this course. However, even if you've been working with Access for a little while, you will still benefit from taking this course. Even expert or advanced users have emailed me and told me they picked up a few tips or tricks from watching my beginner lessons. Consider this a solid foundation for your Microsoft Access education.

The goal for today is to get you up and running building your first Microsoft Access database. We'll begin by learning some terminology and the benefits of using a database. We'll cover the various components of Microsoft Access. You will learn how to properly plan out your database and determine the different tables, queries, forms, and reports that you'll need.

We'll go over the Microsoft Access Design Interface, see how to create new database files, and I'll show you some of the settings that I like to change for my databases. You will learn how to build your very first table to track customers. You'll learn about the different types of data that you can store in a table, and you'll learn about primary keys.

You will learn how to enter data into your table, edit that data, and work with records. You'll learn about data sorting and filtering. We'll see how to use different queries to display information from the tables in different ways. You'll construct a form to provide a user-friendly interface for working with data on the screen. You'll learn how to generate a couple of different printable reports, including a customer list, and you'll see how to print mailing labels for all of your active customers only.

This class is recorded with Microsoft Access as part of a Microsoft 365 subscription. It is roughly equivalent to Access 2019. However, the lessons in this class are also valid for Access 2016 as well. This is the fourth edition of my Microsoft Access course. If you are using an older version of Access, I have versions available for 2013, 2010 (which also covers 2007), and the older 2003. Feel free to contact me for a free copy if you need one of these older versions.

Now, this is an introductory course for Microsoft Access, so you don't need any prior knowledge of Access or even databases in general to benefit from this course. However, you should definitely be familiar with Microsoft Windows basics. You should know how to use the keyboard and mouse, start programs, minimize and maximize windows, use scrollbars, and so on. If not, please go to my website and take my free Windows Beginner Level 1 course before continuing.

It would also be beneficial if you know a little Microsoft Excel. While this is not required, I've often found that people who know how to work with spreadsheets have an easier time learning how to build databases with Microsoft Access. If you don't know Excel and you're interested in learning about it, I recommend, again, go to my website and find my free Excel Beginner 1 course. You will benefit more if you know how to use Excel before learning Access.

My courses are broken up into four groups: Beginner, Expert, Advanced, and Developer. My beginner courses are for novice users who have little or no experience with Microsoft Access. They're designed to give you an overview of the basic features of Access and to cover just what you need to get up and running.

The Expert courses are designed for more experienced users who are already comfortable with Access and have finished the beginner courses. The Expert classes go into a lot more depth about each topic than the beginner classes and cover more functions, features, tips, tricks, and techniques for power users.

Once you've mastered the Expert classes, move up to the Advanced series. You'll learn how to work with events, macros, automation, and many more advanced features that really add enhanced functionality to your databases.

Finally, my Developer level courses are designed to teach you how to program in Visual Basic for Applications. This will allow you to create true professional-quality databases and unlock the full potential of Microsoft Access.

Each group is divided into different numbered levels, starting with level 1, which is the class you're watching now: Beginner, Level 1. Each subsequent level teaches you new and different topics in Microsoft Access, building on the lessons you learned in previous classes. I strongly recommend you don't skip around - watch the classes in order. When you've finished all the beginner classes, move up to the Expert series, then Advanced, and then finally, Developer.

Beginner Level 1 is around 4 hours long to give you a good, well-rounded introduction to Access. Levels 2 and beyond are usually between an hour and 90 minutes long each.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building a calendar and scheduling database, securing your database from multiple users, working with Access and SQL Server online, working with images, work orders, the SQL language, accounts payable, loan amortization, and lots more. I also have a complete list of all of my seminars and templates. Visit my website at AccessLearningZone.com.

If you have any questions regarding the material covered in today's class, just scroll down to the bottom of the page that you're on and post your questions there. Also, make sure to take a minute to read through any other questions that have been posted as your question may have already been answered. Make sure to click on the subscribe button to get notified by email if any other questions or comments are posted for this class.

Also, make sure to visit my Access forum where you can post questions and join conversations with me and my other Access students. Also, be sure to visit my TechHelp page. This is my kind of, sort of, almost daily video podcast about Microsoft Access where I answer questions sent to me by my students.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then replay the lesson from the beginning and follow along with my examples. Actually create the same database that I build in the video, step by step.

Don't try to apply what you're learning right now to other projects until you've mastered the sample database from class. If you get stuck or don't understand something, watch the video again from the beginning and start over, or check the student forum to see if your issue is listed there. I know sometimes I have to read or see something two or three times myself before that little light bulb turns on over my head.

Most importantly, keep an open mind. Access may seem intimidating when you're first learning it, but once you get the hang of it, you'll see that it's really easy to use. Also, don't try to take on any other monster projects until you've completed the beginner series and at least the first two classes from the Expert series.

I get emails all the time from people who are halfway through the beginner classes and they've already made plans to completely redesign their company database. That is great. I love the enthusiasm. However, I recommend you wait.

The beginner classes teach you all of the fundamentals of Access, and the first two classes in the Expert series teach you relational database concepts, which is relating multiple tables together. You'll need to know that before building any serious, real-world databases like customer contact managers, accounting systems, or things of that nature. So take my advice. I've been teaching Access for over 25 years. Just be patient. You'll get there.

For now, if you have a hobby like collecting baseball cards, managing a sports team, tracking your vehicle maintenance, build a database for that. You'll learn more if it's for fun and not for work. Trust me. You'll thank me later.

Now, I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website right below the list of video lessons. Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database.

I know because one of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes with Microsoft Access. It's a great sample database. So while I encourage you to try building the database along with me in the videos, feel free to look over the one that I've built as well.

Also, be sure to take a look at my blank database template. That's something that I built for people to use as a starter template for building their own databases. It comes complete with a video explaining how I built it. It's all part of my TechHelp series, which is absolutely free.

Below the database files, you'll find the links section for other videos and resources. These are optional topics so you can learn more about anything that I happen to mention in class. Since this is the fourth time I'm recording Access Beginner 1, I've already got tons of more advanced videos I can point you to if you want to learn about something more specific right now.

Now, let's take a closer look at exactly what's covered in Access Beginner Level 1.

In Lesson 1, we're going to learn what Microsoft Access is and what it's used for. You will learn some database terminology. We'll discuss the benefits of a database and we'll learn about the parts of a Microsoft Access database, including tables, queries, forms, and reports.

In Lesson 2, we'll discuss planning your database. What tables do you need? What fields should go in each table? What do you want your forms and reports to look like?

In Lesson 3, we're going to get started creating a new blank database. We'll talk about the Access Interface. I'll show you how to turn on overlapping windows, which I prefer over the tabbed document interface. I'll show you what to do if you get that security warning.

In Lesson 4, we're going to begin building our customer table. We'll see how to create a new table. We'll learn about field names and naming conventions. We'll learn about the different data types that are available and we'll start building the customer table.

In Lesson 5, we are continuing designing the customer table. We're going to decide whether or not certain fields need to be text or number, like phone number for example. We're going to start with text or as number. We'll see the difference between long integers and doubles, why we want to use an ID field, an autonumber field, how to move fields around inside the table, saving your table, of course, table naming conventions, and we'll talk about primary keys and indexing.

In Lesson 6, we're going to learn about entering data into our tables. We'll see how to type new records in. We'll talk about resizing columns. We'll look at different date/time values. I'll show you the Zoom window, and we'll discuss what dirty records mean.

In Lesson 7, we're continuing with entering data. We're going to enter some more records into our table. I'll show you how to download a sample database from my website so you don't have to type in all those records if you don't want to.We'll talk about the record selectors, how to turn off the delete confirmation warning, saving layout changes, resizing our columns, and moving columns around.

In lesson 8, we're going to learn how to sort and filter our data. We'll learn how to sort a field. We'll learn about text, numeric, and date sorts and the difference between numeric and alphanumeric sorts.

We'll see how to remove a sort. We'll see how to filter our records, both filter by selection and filter with the check boxes. We'll see how to show blank records and null values. We'll also see how to print, use print preview, and send our table as an email.

In lesson 9, we're going to learn how to build customer queries. We'll see how to add tables to your query and add fields from the table to the query.

We'll see how to run the query. Then we'll learn how to sort by multiple fields and add query criteria.

In lesson 10, we're going to build a customer form to present our user with a nice, friendly interface for adding and editing records.

We'll see how the quick form builder works. We'll learn about navigation, layout view, deleting records, and the different types of forms in Microsoft Access.

In lesson 11, we're going to learn the right way to build a form using form design view. This is a preview of a lesson that I normally used to cover in Level 2.

We'll learn about creating a new blank form, setting the record source, adding existing fields from the table to the form, resizing objects, labels, and text boxes, and changing colors.

In lesson 12, we're going to learn how to make a couple of different customer reports. We're going to build a simple customer report.

With one customer at a time, then we'll make a customer list report where we can see all of our customers listed one per row. Then we'll make some mailing labels and we'll only send mailing labels to active customers.

We'll learn about different things like paper size, margins, printing, and so on.

Now it's time to begin with lesson 1: What is Microsoft Access? Go ahead and click on the link for lesson 1 right now.

01. What is Microsoft Access? (14:10)

In lesson one, we're going to learn what Microsoft Access is and what it's used for. You will learn some database terminology. We'll discuss the benefits of a database, and we'll 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'd make a series of index cards with one customer per card. You'd 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'd 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 wanted 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 creates 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's 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've got 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's not relational. There's 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 don't 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's much easier to secure an Access database than an Excel spreadsheet to keep people from messing with data they shouldn't 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 don't need lots of redundant information copied throughout your database. You don't need to, for example, copy all of the customers' 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's a great thing. Yes, it's 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 don't 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 shouldn't see.

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

Now, Access is a great tool by itself, and I've 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 don't 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've 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've 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's 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 wouldn't 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 wouldn't try to store all of a customer's orders in the customer table. You'd 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's 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 don't 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'll 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're 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'll 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'll 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.

Thanks for watching.

02. Planning Your Database (21:54)

In lesson 2, we'll discuss planning your database: what tables you need, what fields should go in each table, and what you want your forms and reports to look like.

The first thing you should do when planning a new database is to sit down with paper or a whiteboard and try to figure out exactly what you want your database to do. Plan this out in advance. Make a list of all the features that you want included in your database.

What kinds of data do you want to store in the database? What do you want the different forms and reports to look like? What other features do you need? Do you want to store customer information, such as name and address, for example? Do you want to track correspondence with those customers so every time you talk to them, that's stored in the database? Do you want to generate mailing labels on a regular basis, perhaps for a certain set of customers? Do you want to create quotes and invoices and print those out? Do you want to store order history and keep track of everything your customers have purchased?

Do you want to track employee timesheets, clocking them in, clocking them out, calculating the number of hours they've worked in a week, and so on? Do you want to maintain a product inventory so you know how much of each unit you have on the shelf? Do you want basic accounting features such as accounts payable, accounts receivable, and all that stuff? These are all things you should write down before you even start building your database in Access. You should know exactly what you want the database to do and have a roadmap.

Once you've figured out what you want your database to do, write down the types of tables you think you'll need. Remember, all of the data in your database will be stored in one or more tables. I like to use index cards, one for each table. If you want to use sheets of paper, that's fine too. You want all of one kind of information in the same table.

For example, all the information about your customers goes in the customers table. All of the information about the orders they place goes in an orders table. You wouldn't put order information in the customer table, and generally, you don't put customer information in the orders table, although there are some exceptions. I'm going to track employees and products in their own separate tables. Employees and products are two completely different things, so each gets its own table.

Later on, we're going to learn how to relate these tables together. For example, customers can place orders, so they can be related to each other, but they are completely different things. Customers and employees, although they represent people and you could certainly store them that way if you wanted to, for the purposes of a beginner database, we'll put them in separate tables. You generally get different kinds of information about employees than you would for customers. For my beginner lessons, I like to separate them. Later on in the more advanced lessons, we can put them together because they are both types of people. We'll just have a category on the person table so we can track which ones are employees and which ones are customers, but that's more advanced. We'll get to that later on.

Customers can actually place multiple orders, so you would not want to store order information in the customer table because you wouldn't know how many orders each customer would place. The customer could have one order or could have 500 orders, so that information goes in separate tables.

We're going to talk a lot more later on about relating tables together. For now, just remember, each type of information goes in its own separate table.

People ask me all the time: How do I know what tables to make? How do I know which fields go in which tables? It's literally just a matter of experience. You've got to see some examples to know the right way to do it, and that's what I'm going to show you.

Now that you've got an index card for each table that's going in your database, take each index card, for example, customers, and write down a list of all the fields or types of data that you want to store in that table. Remember, each specific bit of information is considered a field. If we were making an Excel spreadsheet, which more people are familiar with, each field would be a column of data.

So, for example, my customers table has basic information like first name and last name. You can put middle name and all that stuff in there too if you want. I've got address, city, state, postal code (or zip code if you prefer), phone, and fax (if you still use fax numbers). I've got a mailing list field, which indicates whether or not this customer is on my mailing list. You might have people that you don't want to send your newsletter to, for example. Customers since will be a date field, so I know how long this person has been a customer. I've got their credit limit to store currency value. What's their maximum credit limit? And then finally, I've got their email address and some notes. I put a notes field in just about every table.

Now, make sure you're as specific as you think you're going to need when setting up your fields. It's easier to put two fields together later (something called concatenation) than it is to try and split them apart. For example, if I just had one field called customer name and later on I want to send out a mailing that says "Dear John" and the customer name field says "John Smith," now I've got to figure out how to split that apart into two fields. So do that up front. It's easier to type in John and then in the last name field put in Smith than to try and pull that apart later.

Address, for example, is another one. Address, city, state, and zip code are usually enough for most of my clients. But I have built databases for companies before that do heavy mailing lists and they want to split the address field up. You've got the number of the building on the street and you've got the street name like Main. Then you've got the type: is it a street, a drive, or an avenue? Mailing list companies like to split all that up. Some other companies like address one and address two. Address two could be like a suite number. This is completely up to you. Some companies want a zip and a zip plus four. Again, your decision. Decide that now. Be consistent. Just remember, if you ever think there's a possibility in the future that you're going to want it separated, separate it now. Be as specific as possible but don't go overboard, of course.

One thing you'll notice is that I didn't use any spaces in my field names or my table names. First name, for example, is capital F first, no space, capital N name. Mailing list is capital M capital L, no space. In my 20 plus years of building databases, I've discovered that it's much, much easier later on if your field names, your table names, and your query names don't have spaces in them. When we start getting into Visual Basic programming and macros and all that stuff, spaces can cause problems. Trust me, don't use spaces. I will explain a lot more about this in future lessons.

One thing I want to address right now at the very beginning of the course is something that people comment about all the time in my forums and when they email me. They say that I use the phrase "in a future lesson" or "in future lessons, we'll do this" or "I'll teach you about that." It's almost a complaint, like people think I'm intentionally withholding information so I can sell more classes. No, that's not the case at all. Just like the analogy, you have to learn how to walk before you can learn how to run; it's the same with learning how to use computers or any education for that matter.

You can't just jump into calculus without understanding basic arithmetic and algebra. So, when I tell you I'll teach you more about proper naming conventions for tables and for fields in future lessons, we'll cover it when we get to it. I can't teach you everything all at once up front. I wish I could. I wish I could just plug your brain into my computer and download all this information right into your head, but it doesn't work that way. So, when I say we'll cover it in a future lesson, trust me. That usually means I'm going to give you a little bit of information now just to satisfy your curiosity, but we'll go over the details when it's appropriate.

If you really want to learn something when I say we'll learn about it in a future lesson, I've probably already covered it because I've done this course before in earlier versions of Access. So, if you really want to learn it, like if I mentioned something about queries, go to my website, search for the topic, and you'll find it. I'm only holding back because I know the proper way to teach this. I've been teaching Access for almost 30 years. Just trust me when I say we'll get to it in a future lesson.

Now, back to designing our tables. Only put customer information in the customer table. You'll notice I don't have any information about this customer's orders in the customer table. I've seen people try to build databases like this where, in the customer table, they've got order information: order amount one, order amount two, order amount three, and so on. Where does it end? How many order fields are you going to put in the table? Hopefully, you're going to want your customer to have hundreds of orders, so it doesn't make sense to store that information in the customer table. Right now, this customer is limited to only three orders, and that's bad database design. It's a subject called database normalization, which I have whole lessons on, and again, we'll get to that in a future lesson. For now, just think to yourself: customer table - customer information; order table - order information.

Now, I've eased up my stance on this somewhat. The last time that I did this course was back in 2013, and it's now 2021. So, I've eased up on this a little bit, for only some things, for example. I used to say if you've got multiple phone numbers or multiple email addresses or multiple physical addresses, put them in a separate table. Now, I'll allow you to have one, two, max three, because I know a lot of people nowadays have multiple email addresses, multiple physical addresses, or a ship-to address, a billing address, and so on. For beginners, if you want to have two phone number fields or two address fields, that's okay. If it's ever going to be more than three, you're going to put it in a separate table. Again, we'll cover that when we get to making multiple tables.

If you want to have a bill-to address and a ship-to address in the customer table, that's fine. If you want to give the customer the ability to have three email addresses, that's fine. Three is my limit. If you go over that, you really should consider making multiple tables for it.

Set up a separate order table to put the order information in. Here I've got an order table: order date, sales rep (that could be an employee), order total, sales tax collected, whether it was delivered or not, and so on. All the information about each order will go in the order table. This, of course, is just a simple example for class, but you get the idea.

Later on, I will teach you how to link these two things together with IDs. Each customer will be assigned a customer ID. Each order will have an order ID, and we'll link them together using that customer ID. Each order will have a customer ID associated with it, so we know which customer placed the order.

I will spend lots and lots of time on relationships, a very, very important concept in Access.I devote whole lessons to it, and we will get to that eventually.

So with this setup, you can have an unlimited number of customers within reason. You can have an unlimited number of orders for each customer. You are not storing order information in the customer table, that is bad. You generally do not store customer information, aside from the ID, in the order table, with the exception of maybe a ship-to address. You might want to know where the order was shipped, so if the customer moves, you can still tell if the order was shipped to the old address.

We will talk about all of that when we get to relationships. If this seems at all confusing, do not worry about it. We are not going to work with multiple tables and relationships until we get into the expert series. We have got a lot of learning to do first before we get to that point.

For now, I just want you to recognize that the different types of information in your database will go into their own tables. For today's class, we are going to focus solely on the customer table, but later on, we will add other tables like Contacts, Employees, and eventually Orders and Products, Vendors, and lots more.

The next step, after you figure out all the tables that you need, is to get out some paper or a whiteboard and draw out the way you want your forms to look on the screen. Remember, forms are what the user interacts with the database with. You do all your data entry and lookups using on-screen forms.

For those who are curious, these are the original images I drew from my Access 2010 class over 11 years ago. Back then, I still shipped most of my classes on CD-ROM. I kept these images as an homage, and also because I did not feel like making new ones. No, I am just kidding. Honestly, these images still do the trick. They show you what you need to see, and your sketches do not have to be works of art. They just have to be something simple to guide you as you are building your database. It is easier to build forms if you have a roadmap, a template, that you can see exactly what you want your finished product to look like. Even if you do not know how to do something now, or do not know how to build what you want in Access, just write down what you want it to look like on paper and eventually you will figure out how to translate that into Access. I will show you.

It is much easier to build a building if you have an architect put together some blueprints. As you can see, I am no artist, but I do sit down and sketch out what I want each of my forms to look like when the database is finished. Whenever I build a database for a customer, I tell them the same thing. Sit down, sketch it out on paper. Take a picture of it and send me that so I can see what you want the database to look like and what you want the workflow to be.

For example, I have got a main menu on the left with a couple of different buttons on it - customers, orders, employees. I want to click on that first button, the customer button, and have it open up the customer form with all the basic customer information on there - name, address, phone number, and so on. I want to see the customers' contact history below that so I can see the last couple of phone calls and what we talked about. I also want to see the customer's picture over on the right.

At this point, you might not have any clue how to set this up, but that is what you want it to look like for your business and your workflow. We will get there as far as translating all that into Access. It is not that tough.

I have two tips. Tip number one, if you are going to use a whiteboard, make sure you take a picture of it with your cell phone camera and save it. I have had people put stuff on whiteboards that was amazing and then they forgot to save it. Then the cleaning staff came in and it was gone the next day. So trust me, take a picture of your whiteboard.

The second tip is no matter what you see on other websites or read in books, we do not store files inside our database like pictures. We do not store attachments. There is something called an attachment field. If you see things like books or websites talk about these kinds of things, do not do it. Trust me. You will thank me later. I will handle this later on. If you are curious and you want to see now, post a comment down below in the comments section and I will point you to a couple of videos I have that cover this kind of stuff.

Part of the beauty of having done this course three or four times over the past 20 years is that by now I have got a lot of these videos already made. I do not have to say "in a future lesson." I can actually tell you in this lesson, for example, in expert 14, we will cover this topic. I will start doing that and I will put helpful links down below the video and in the comment section down there. So if you are curious about something right now, just click on that link and go right to that lesson if you want to.

I have thousands of hours of Access training videos on my website. There is very little that I have not covered before. I just redo the beginner lessons every now and then for new users who have never used Access before. I do not want you to be confused by minor changes in the interface. Access has been very similar since 2007, with some minor differences that I will cover over the course of the next couple of hours.

The next step is to gather together all of your printed reports or, if you do not have them printed right now, sketch something just like you did with your forms. You have probably got paperwork that you are using right now, things that you are printing out from Microsoft Word or Excel, or forms that have been given to you by an insurance company that you work with or a vendor or that kind of stuff. Gather all those things together so you can see what kinds of reports you are going to need to build later on.

For example, I have got an accounts receivable printout that is important at the end of each month. I have got an invoice that is important. I am going to print that out or email it to my customers, and I have got a set of mailing labels that I need for sending out these invoices.

Now, an important word about terminology. A lot of people call paperwork "forms." Here is a form that I want you to fill out for me. They hand you a clipboard with a piece of paper on it and tell you to fill out the form. Remember, in database terminology, a form is something we are going to use to work with on the screen, whereas a report is something that is generally designed to be printed out or saved as a PDF file, for example, or emailed. They are generally formatted for printout.

Think of it like this - forms are for the screen, reports are for print. You are going to do all of your work on the screen - looking up data, entering invoices, typing in customer stuff, that kind of stuff - in forms. When you want to print out an invoice or print out mailing labels, that is going to be reports.

Can you go the other way? Can you print out a form? Sure. Can you view a report on the screen? Of course, you can do a print preview and see what it is going to look like. But generally, forms are for the screen, reports for printing or emailing, or if you still happen to be living in 1992, faxing.

So get all your current printed paperwork together, put it all in a folder. This will make it easy when it comes time to build reports later, so you have got all your stuff in one place. You do not have to go hunting around for all the different reports.

Speaking as a contract developer when I used to build databases full time for other clients, one of the tough things was that they would say, OK, I want the database to do this and this and this and this and this. I would build them a database that had maybe 10 forms and some menus. They would say, yeah, I need invoicing and I need accounts receivable. OK, great. I put those reports together. Then when the database was all done, they would be like, well, I need this report and this report and that report, and give me like 20 different reports that they wanted to generate. That can sometimes take longer than building the database itself.

Definitely take that into consideration when you are building your database, whether it is for you or for your company, or if you are building it for someone as a client. Reports can be time-consuming, trust me.

The bottom line here is to plan ahead. Do not just get crazy and start jumping into Access and think you are going to build your final company database right now.

In fact, one thing I am going to tell you is while you are learning Access, at least for the next couple of classes, I do not want you to work on your actual company database or the project that you have planned at all. I want you to build the sample examples that I have for you in class. You will learn better. Follow along with me and build my database. Do not try to apply it to what you are doing because this is a teaching exercise and I guarantee you between now and the time you finish the class, you are going to learn all kinds of new stuff and you are going to scratch what you have built and start over three times. I have done it myself.

A complex database takes a lot of planning and you do not want to be almost finished with your database and then realize, oh, I forgot to put this in. Then you have to go back and put it in your table, which means you have got to update your queries and add it to all your forms and add it to the reports.

Try to get this stuff on paper as much as you can up front. I know it is impossible to think of everything up front and yes, you can certainly add things on the fly as you are going, but the more you plan ahead, the more time you will save later. At least figure out all the tables you are going to need, what you want all your forms to look like, and all the reports that you need.

Do not worry too much about queries. We usually build queries to support a form or to support a report. We will talk about queries a little bit later, but tables, the fields in each table, the forms and the reports that you need - write all that stuff down now before you continue to the next lesson.

Once again, plan ahead now to save a lot of time later.

03. Getting Started (16:40)

In lesson three, we are going to get started creating a new blank database. We will talk about the Access interface. I will show you how to turn on overlapping windows, which I prefer over the tabbed document interface. I will also show you what to do if you get that security warning there.

Let's begin by starting up Microsoft Access. I am using Windows 10, so I am going to click on my Start button and then find Access. If you cannot find Access on your menu, just come down here in the search bar and type in Access. There it is right there.

If you are not familiar with basic Windows usage, you should go take my Windows Beginner 1 class. I am not going to take the time to cover basic Windows stuff in this class. For example, certain things like minimizing and maximizing, closing a window, using the title bar to drag a window around, I am not going to cover all that, so go take the Windows Beginner 1 class if you are new to using a computer.

Now, if you are planning on using Access a lot while you are learning it, and I hope you are, come down here on the Windows Taskbar, right-click on the Access logo, and go Pin to Taskbar. That way, even when Access is closed, you will still be able to open it by clicking right there.

Now, when you start Microsoft Access, you are going to see this screen. Unlike other programs in the Microsoft Office suite, like Word and Excel, Access does not automatically start you in a blank document. In Excel, you get a blank spreadsheet, and in Word you get a blank Word document. Access is different. You have to create your own blank database shell to put all your stuff in. All of your tables and queries and forms and reports, and later on your macros and your modules, all of that stuff goes in one Access database file.

Right here, you will see where it says New Blank Database. That is what we are going to use. Over to the right here, you see some templates. There is asset tracking, contact students, event management. If you click here, you will see a ton more. Templates are basically databases that someone else has already built for you.

If you are looking to get up and running fast and you need a contact database, go ahead, start up the contact template. Play with it. See if you like it. That is actually how I learned initially when I was learning Access. Way back in 1994, there was a template, just one, that came with Microsoft Access called the Northwind Traders Database. It was your typical company database with contacts, orders, customers, and all that. I tore that thing apart. Between that and a few different books, that is how I taught myself Access.

Feel free to play with these templates, experiment with them, have fun with them. You are not going to break anything. But we are here today to learn how to build a database from scratch, so we are going to skip the templates. I do have some other videos where I cover a few of these templates. Go to my website and click on the search bar, and type in templates if you are interested in learning more about those. I do not cover them in my normal classes because we are here to learn how to build our own database.

Now, I have a Microsoft 365 subscription, which as of right now is February 2021. This is roughly equivalent to Access 2019 or 2016. It is about the exact same with the subscription. You constantly get new updates and new features, so Microsoft is always tweaking little things here and there. The core of Microsoft Access really has not changed much since 2007, but there have been minor adjustments, tweaks, and some tiny little changes here and there. That stuff can be confusing for beginners. That is why I like to re-record Access Beginner 1, sometimes 2, every few years.

As you get into my more advanced lessons, you will see Access really is the same thing. The reason why I mention that is do not hate me if your screen looks a little different from mine because sometimes Microsoft likes to make little changes.

So let's go ahead and click on Blank Database.

Now Access wants us to give the database a file name and to pick the location where we want to save it. By default, it will be saved in your Windows User Documents folder. There is mine. You can change it if you want by browsing here. I am going to leave mine where it is. The file name is database1.accdb.

Now, ACCDB is the Windows file extension. Microsoft actually used to hide that, and I think it is important for you to have them on. So if you have Windows 10 now, the default is you will see that file extension again. If you do not see that there and you want to know how to turn Windows file extensions on, go to my website and search for Windows File Extensions. You will find a video that explains how to do it. I will put a link down in the description below the video.

Now mine says database1. That is because this is actually the first database that I will be building on this brand new machine. I just got a brand new laptop just for you guys. No, I am just kidding. It is for me for recording classes. But I have not made any databases yet, so it says database1. We are going to change this though. We are going to change database1 right here to something else. So delete that.

In this course, we are going to be building a fictional database for a fictitious company called PCResil. Basically, they take old computers and they refurbish them and they resell them. When I first did my very first Access class, now we are going back to the 90s, I actually was in the PC business, and you can actually make money reselling computers. Flash forward down to 2021 and you can buy a brand-new, state-of-the-art laptop for less than $500.

So PCResil is a fictitious company that has not been around since the 90s, but we are still going to build a database for it. So right here for the file name, type in PCResil. Like I said, build the database with me. Do not try to do your own thing just yet. There will be time for that. Press enter.

So Access has now created a blank database file and it is trying to get us to build table1, our first table. Now, I particularly do not care for this method of building a table. They start you off with an ID field, which we will talk about in a second, and they want you to add more fields by just clicking here and typing in data. Not the way I like to build tables. I like to define the table first, the structure of the table, then we will add data to it. Building a table is not like just typing in data into an Excel spreadsheet, so we are going to do it differently. They have switched things around like this to make it easy for new Access users, but I am going to show you the right way to do it.

So let's just close table1 right here. It will go away and now we are just left sitting at our blank database shell.

Real quick tour of the interface. Across the top here is your title bar, where you can see the path and file name of your database. If you have a Microsoft account, you will see the account that you are logged on to here. You should have done this when you installed Office. If not, do not worry too much about it. And no, that is not a real email address. That is my username on Microsoft's website. Do not email me there because I will not get it. If you have to email me, go to the contact page on my website. You will find my email address there.

You have your minimize, maximize, and close buttons over here. Over on the left-hand side, this is the quick launch toolbar, where you can put things like save and undo and other things. I will show you how to add things. I like to add form design and a few other things to the quick launch toolbar. We will talk about that in a future lesson.

Now this thing here is called the ribbon. The ribbon is their big menu system. Back in the old days before 2007, we just had the simple File, Edit, View menu, and that was what I was used to. Then Microsoft came around, introduced this ribbon in 2007, and threw everybody for a loop. But I will be honest, after getting used to it and working with it for the past, what, 13 years now, I really do like it. It is a vast improvement over older versions of the menu system.

The ribbon is dynamic and will change based on what you are doing. If you click on a picture, you will see options for pictures, for example. If you are editing a query, the ribbon changes and shows you query design stuff, so it is really cool.

Now over to the left here, this is the thing called the navigation pane. This is where all of your objects are going to show up. All of your tables, your queries, your forms, your reports.

Here is another database that I built previously. This is my TechHelp free template. You can actually download a copy of this on my website if you want to. It is a free template. I will put a link to it down in the description below the video. But here you can see all my tables, my queries, my forms, a couple of reports. There is a main menu. You can open that up. Go to the customer list. These are all forms that we work with on our screen. Open a customer, open up his orders, print an invoice report. All kinds of stuff you can do off them inside your database. All of these objects are stored here in your navigation pane.

Now if you need more space on the screen and you are not designing your database, you are just working with it, you can actually minimize the navigation pane and the ribbon to save some space. This is handy if you have a small monitor or, like me, when I am recording videos. I am only recording a small window on my screen, so I want to use more screen space.

You can resize the navigation pane right here by just grabbing that border and dragging it. See that? Or you can close it, minimize it basically. Open it back up again with that chevron. You can also minimize the ribbon by simply clicking on any one of these tab headers up here, like double click on the Create and it minimizes. So now you have plenty more space down here. This is handy when you are working with the database and you want to fit a bunch of forms on the screen, but you are not doing any design work so you do not need to have these. You can click on these to bring up the menus, and then when you click on something else it goes away, or you can double click again to leave it open. I am going to leave it open for class, just so everything is nice and within our reach right here.

The forms and reports we are building in class are going to be nice and small. But I have seen some pretty big databases with some large forms. So if you need more space, that is how you can get it.

Now, depending on the size of your Access window, your ribbon might look different. For example, if I come over here and if I resize Access, notice how the ribbon kind of scrunches up, and you want to click on these buttons to open those up. So if my menus look a little bit different from yours, do not panic.

As with everything in Microsoft Office, there are five, six, ten different ways to do everything. So in addition to double clicking on the ribbon tabs, for example, you can right-click and go to Collapse the Ribbon, and that will collapse the ribbon. I like to find one way that works for me, and that is double clicking, and then I just stick with that. But yes, people always say, oh, you could do this too. Yes, of course you can. There are a million different ways to do everything in Microsoft Office.

Down here on the bottom, you get the status bar. Usually it sits there and says Ready, but you might also see messages there. If Access is in the middle of processing a large query, for example, it is crunching numbers. You might see Calculating. Depending on what you are doing, if you are designing something, you might see Design View, F6 to switch panes, that kind of stuff. You can actually program in prompts for your user.So if you're building a database for other people to work with and they don't know what a particular field is, for the first name field, for example, you could put a caption in there that says, this is the customer's first name.

Obviously, that's a simple one. But if you have something like customer sense, they might not know what that means. You can explain it to them down in the status bar.

And of course, Access will also show you error messages. I just did a video this morning for my TechHelp series. The error message is: the record set is not updateable. Lots of people ask me what that means, so I explained it in a video this morning.

Of course, down here in the bottom right corner, you'll see different indicators for things like the num lock key and the caps lock key.

Now, before we actually get into building our first database, there are some interface changes that I want to make. This is totally my preference, the way that I like to build Access databases. You don't have to make these changes if you don't want to, but if you're going to be learning from me, I strongly recommend that you do.

The biggest change that I want to make is that by default, Access puts you in what's called the tabbed interface where every table, every query, every form, every report shows up in different tabs like this. Each tab takes up the full screen. I don't like this. I think it's difficult to work with databases like this. You've got this report, you've got this form, you've got this table. It's just hard to work with it like this. I don't like this at all. I don't like the way it looks. I don't like the way it operates.

I like my databases to look like this. This is called overlapping forms. When you open multiple objects, they're windows inside of the Access window. Here's a form, that's its own window. Here's an order form, that's its own window. If I want to open up a table directly, it's got its own window. They're not big tabs taking up the whole screen. You can have stuff side by side if you want to. I prefer this. This is the original layout that Microsoft Access came with, and I want to return Access to this. I don't like the tabs.

I used to wait until Beginner Level 2 to show people how to do this. I'd go through Beginner Level 1 using the tabs. Over the last couple of years, I've said to myself, why do I do that? Let's just get it over with right up front. Let me show you how to turn on the overlapping windows.

So we're going to click on File and then come down to Options. That brings up the Access Options window. Go to Current Database. Click on Overlapping Windows. Turn off the Tab Documents. I hate that. Then click OK. Access says you have to close it and reopen the database for this specified option to take effect. Go ahead and hit OK.

Shut down Access. Come down here in the task bar. Click on the Access logo. Access starts back up again. Notice right here there's our PC Resale database that we created. Click on that.

Now it opens back up. We haven't added any objects yet so you won't see any changes. But when we create our first table you'll see a difference.

Now let's talk about this security warning. It says some active content has been disabled. Click for more details. You can click this yellow bar right here for more details if you want to.

But I'm going to tell you what you need to know right now. Basically, it's possible, although unlikely, but it's possible that a Microsoft Access database could contain a virus or some other malicious code. If you get a database from someone else, don't open it in a trusted folder. I'm going to teach you what trusted folders are very soon.

And don't click on this button here that says Enable Content if you're not 100 percent absolutely sure that you trust the database. Now, we're building this from scratch as a blank database, so you can trust this database. There's nothing in it. Go ahead and click on Enable Content. From now on, this database file is marked safe.

I'm going to show you a little later on how to set up your own safe folder called a trusted folder. We'll get to that. If someone sends you an Access database file or an Excel spreadsheet or any of that stuff and that window comes up, that little bar comes up and says this is not trusted, don't enable it if you're not sure.

Because they could do things like delete files off your hard drive and all kinds of crazy things.

If you're curious about some of the other customizations that I like to make to my blank databases, I do cover them in my blank database template design video. It's part of my TechHelp series. I'll put a link to it in the description down below if you want to jump ahead and watch some of that stuff.

It's essentially how I build this guy. It's a little more advanced, but if you want to get a jump start, go ahead and watch that. I will cover all of this in the series that you're watching right now, my regular Access series of courses.

But this template was like a quickie for people who want to get up and running and build fast. I use the TechHelp free template in all the TechHelp videos that I do so I don't have to keep starting from scratch.

Don't worry. We're going to start slow and we're going to get to all of that eventually. In the next lesson, we're going to start by building our first table, the customer table.

Thanks for watching.

04. Customer Table, Part 1 (25:14)

In lesson 4, we're going to begin building our customer table. We'll see how to create a new table. We'll learn about field names and naming conventions. We'll learn about the different data types that are available. And we'll start building the customer table.

Now that we know our way around the Access interface, let's build our first table. One thing I'm going to mention: I just switched computers. My other computer was a brand new one that I wanted to use for starting up a blank brand new database to show you what it looks like on a new system. I just switched over to my other computer because this is the one I normally record my classes on. So I just don't want anybody worried if you see that the file name up here is different. Everything else is exactly the same. Don't worry about it.

Now let's go back to our index cards. Find the card for your customer table. These are all the fields we're going to add to the customer table in our database: first name, last name, address, and so on.

Access gives you multiple ways to do pretty much everything. Just like in Word and Excel, there are five ways to do pretty much anything you can think of. There are multiple ways to create a new table. Click on the Create tab and you'll see Table, Table Design, and SharePoint lists. Don't worry about SharePoint lists. That's for working with online databases. I've got a whole seminar, as in classes, for SharePoint stuff. We'll worry about that later. For now, there's Table and Table Design.

The first option here, Table, puts you into Datasheet View. This is where Access started us when we first started to create the database. Remember that? Datasheet View lets you type in the data first and then define the tables afterwards, kind of like a spreadsheet. You can type in Joe, Tab, Smith, Tab, New York, and so on. Just like you're working with Excel.

Personally, I find this backwards when it comes to developing databases. I think one of the strong points of Access is you define the table first. You specify exactly what type of data each field is going to contain. Then you add data to it afterwards. So I don't like this method. I'm going to close this table. When Access asks if you want to save changes, I'm going to say no. We don't want that.

We're going to create a table the right way. Click on Create and then Table Design. This puts us right into Design Mode.

Design View lets you specify the field names, like first name, last name, and so on. The data types: text, number, currency, that kind of stuff. Once you're done defining the table structure, you can go back and add your data to it. It's better to build the structure first and then add the data.

One of the problems with Excel, for example, is anybody can come in here and just type in anything they want, wherever they want. Put Joe there, put Smith over here. You can set up some kind of basic validation and some structure in here, but an Access database is what you want to use to make your users conform to the style and the types of data that you want them to be typing into each field.

Excel has its purposes, but Access is what you want to use for structured data entry. With Access, you force your users to type in the data types that you want. They can't type in text, for example, in a currency field. If you want a date, Access makes sure they give you a date.

When constructing a table, there are two types of information that you have to have, and an optional third one. You have to have the field name, the data type, and optionally a description. The name describes the kind of information that you're storing: first name, last name, address, city, state, all that. Those are field names.

The data type describes what kind of information that field is going to hold: text, number, currency, date, and lots more. If you make the data type a number field, then the user can't type something like blue in there. They have to put a number in. Later on, we'll get to something called validation rules, where you can say it's got to be a number between 10 and 100.

Finally, you can type in an optional description if you want to. This is usually to explain what an ambiguous field might be to a user who doesn't know your database. For example, you might have a field called is active. You know that means it's an active customer. They buy from you regularly. They're on your mailing list, that kind of stuff. Someone else, a new employee, might look at that field and go, what does that mean? It could sound like it means the person likes to exercise, they're active. The description field can be used to clarify what that actually means.

If you type in something in the description field, it will show up down in the status bar when they're entering data.

Let's start by putting in our first field. Come right over here, and in the field name column, type in first name. No spaces, capital F, capital N, and then press Tab. Remember, this is just my personal naming convention, but if you're going to be learning from me, I strongly suggest you follow it too. It's good. I've been doing this for almost 30 years now, and it works. I don't like to put spaces in my field names, my table names, my query names, my form names. Trust me, when you get into more advanced stuff, macros, modules, and VBA programming, you're going to thank me.

This is what I usually do: capital F, no space, capital N, name. Nice and easy, very popular with Visual Basic programmers. If you already have been building Access databases for a little while and you've got some spaces in your field names, that's okay. You can still work with them. Access will allow it, but now you have to remember to put square brackets around your field names if you use them in queries, Visual Basic, or SQL. Once you start getting into more advanced stuff, you're going to thank me and wish you didn't have spaces in your field names or your table names.

In fact, if you are just getting started and you've got some other databases that you've been working on that have spaces in them, you might want to go back now and get rid of those spaces. Trust me.

Access is not case sensitive. So all lower case first name, all upper case first name, Access doesn't care. All lower case or all upper case is okay. It's a little more difficult to read, especially when you get into programming. If you do decide to get into the more advanced stuff like VB programming, if you have mixed cases in your field names and your variable names, if you type them in all lower case, Access will capitalize them for you, and that's a quick way that you can see the types of things you entered wrong. That's a trick that I'll get into in my developer classes. So trust me, type in your variable names like this, your field names. You'll thank me later.

You might see some programmers, some databases, use the underscore. That's perfectly fine too. I used to be a C programmer years ago, and in C, that's how you do all your variable declarations: first underscore name. That's okay. Again, I like my method better. You can use a hyphen in your field name if you really want to, but again, I don't recommend it. I don't recommend using any non-letter or number characters in your table names or field names. That can be confused with subtraction sometimes. So again, stick to that. Trust me. Just try to keep it to letters and numbers. Don't use question marks. Don't use dashes.

You may see some other developers or book authors use this kind of notation. This is called Hungarian notation. It's basically the first name field with a prefix on the front to tell you what kind of information first name is, if it's text, for example. I personally don't bother with this because most of my field names are self-explanatory. I usually know that first name, last name, those are text values. Credit limits, currency, amount, number of children, that's a numeric value.

If you're working in large teams of developers, you might want to do that, but generally, for sole developers, I don't ever bother. Like I said, I've been building Access databases for almost 30 years and my naming convention works pretty well. The important thing is to maintain consistency. Stick with one style of naming your fields and your tables.

There are some reserved words that Access has that you should avoid using for your field names. For example, name, date, left, picture, those are all either property names or function names. Don't make a field called name, for example, use first name or last name. Don't have order date be just date. Date has a special meaning in Access.

I've got a page on my website with all of the reserved words on it. I'll put a link in the description down below the video if you want to check that out. There are a lot of them, so I'm not going to put them here in the video, but go check that page out. You'll see the big long list.

Usually, if you try to keep your field names compound, like more than one word, like first name, order date, you're safe. Use profile picture or left junction or something like that. Try to be as descriptive as you can without going overboard. Usually two words work perfectly for a field name or a table name.

So I put in my field name, first name, now I'm over in the data type column. The default option for the data type is short text. If you drop down this combo box, you'll see the complete list of all of the data types available in Access.

The first data type is short text. It's the most popular one; you're going to use it most of the time. Short text includes pretty much all of the printable characters on the keyboard: A-Z, upper and lower case, 0 through 9, exclamation point, ampersand, all that stuff. These can be anywhere from 1 to 255 characters long.

So you can use a short text field to store most types of data: names, addresses, all that stuff. If you happen to be using an older version of Access from 2010 or earlier, this used to be called just text. They used to have text and memo and they changed it in 2013 to short text and long text. So if you hear me refer to something as a text field, I mean short text. If I say memo, I mean long text.

What is long text, you ask? Short text fields can only be up to 255 characters long. Long text fields can be 65,000 plus characters long. You can store a lot of information in a long text field. Long text fields also support formatting. So you can have rich text with colors, bold, italics, all that stuff.

In some of our later lessons, we're going to see how to make a letter writer where we can actually type up a letter just like you would in Microsoft Word, but it's all stored inside the database. Then you can hit print, print it out, and it looks just like a Word document. That will be using formatted long text. Long text fields used to be called memo fields in the older versions of Access. So if you hear me say a memo field, I'm talking about long text.

At this point, a lot of people ask me why not just use long text for everything if it's so much better. Short text has some benefits. In fact, I made a 15 minute video that I put on my website and on YouTube that just goes over the differences between short text and long text and why you want to use each one.

If you're really curious, I'll put a link to that video down below in the description field.See, this is why it's really nice that I've been doing this for so many years, because I've got videos to explain all kinds of other stuff and I can point in that direction if you want to know right now. Instead of saying, we'll cover it in a future class, I've already covered it. Go watch that video if you really want to know.

The bottom line is, if it's a normal bit of information like a name and address, a social security number, something like that - a small bit of information - use a short text field. If you don't know how much information you're going to be typing in and you could be typing in lots and lots of notes, use long text. For why specifically, go see that video.

If you're going to be typing in directions to get to a customer's house, or information about a service call, or like I said before, the letter writer, use long text. Everything else, short text.

Short text fields are much, much more efficient when it comes to searching and sorting. If you have a big long list of customers, for example, and you want to sort them by last name, short text is much more efficient.

Next, you'll see there are number type fields. You have your text - short text, long text. Next, we have numbers. There's a whole bunch of different kinds of numbers, but there are two that we're going to focus on for beginners: long integers and doubles.

We're going to use long integers for any kind of counting number: 1, 2, 3, 0, negative 16, integer type numbers.

Doubles are what we're going to use to store floating point numbers, anything with a decimal. How many gallons of gas did you put in your car? 10 and a half, that's 10.5. We're going to put it in a double.

How many children do you have? 5, that's a long integer. There are 2 types of numbers I want you to focus on. Yes, there's a whole bunch of other ones. There's byte, there's integer, there's single, there's decimal. Don't worry about all those for now.

In future classes, I'll go over all the little nuances between them, but for right now, long integers for counting numbers - integers basically - and doubles for floating points.

Now, one thing to take into consideration when you're planning your database is: should the field be a number or text? My rule of thumb is if you're ever going to perform calculations on it, use a number. If not, store it as text. Text is much easier to work with.

For example, social security number for us in the United States. Store it as text. Yes, it says number and yes, it's always numeric, but it's easier to work with as text. You're never going to be adding up a column of social security numbers. You're never going to be finding the average of a bunch of social security numbers.

And sometimes they start with zero, so we don't have to want to deal with that zero falling off. Store it as text. Zip codes. Yes, they're all numbers. Store them as text. They're easier to work with. You're not going to be figuring out the largest zip code or the average of a bunch of zip codes. Store it as text.

Keep in mind that if you're working with old school systems, old databases, or people using paper systems, they might have something called a part number, but it's not a number. The part number might be PT463G. That's not a number. That's text. So keep that in mind.

Access has something called a date/time field. A date/time field can store a date or a time or both. So you can store just a date, January 1st, 1980, or just a time, 4:55 pm, or both at the same time, January 1st, 1980 at 4:55 pm.

You can be as specific as you want. Access has a special kind of numeric value called currency. Use currency fields for dealing with money.

Currency is a special type of floating point number that's optimized for dealing with currency. So use currency values for money. I'll explain the specifics a little bit later.

One nice thing about currency is it will observe the regional settings on your computer. So if you're in a different country, for example, I'm here in the United States, if you're in Europe, it'll use euros or whatever your country's currency is.

Next up, we have the Yes/No data type, also True/False, On/Off. These are called Boolean values - Yes or No.

Next, we have a very important data type called AutoNumber. I make sure there's an AutoNumber in pretty much every table that I build.

AutoNumbers are going to start at one, and then every time you add another record, it's going to increment that number. So each record has a unique identifier: customer one, customer two, customer three. No one's got the same AutoNumber.

Later on, when we create relationships between our multiple tables, this is how we will track which customer belongs to an order, for example, because we'll store the customer ID in the order table.

Access maintains these numbers for you. You don't have to worry about them. You don't even have to see them if you don't want to.

They're stored in the table and they're used internally for Microsoft Access to make relationships.

Now, I sometimes get into arguments with other developers who don't like AutoNumbers. I put a whole huge video together on why I like AutoNumbers.

If you care, I'll put a link down in the description below the video. Go watch it if you want to. It's good reasons why I like to use AutoNumbers in almost every one of my tables.

Next up are two data types that I try to recommend people don't use.

OLE Object stands for Object Linking and Embedding. These are pictures, documents, videos, anything you can copy and paste, you can store in an OLE Object.

You can copy a whole Word document or an Excel spreadsheet or a PDF file and you can paste it into a table inside your Access database.

Just like the Attachment data type, you can take whole files and save them inside your database.

The problem with these two methods, with these two data types, is that they make your database big and bloated and slow.

And it doesn't work right if you've got lots of attachments inside of it.

So yes, I am going to show you how to use both of these because they do have their place. I'm not saying they're 100 percent evil.

But I'm also going to show you better ways to manage working with files and pictures than using these two data types.

People always ask me, that's why I'm mentioning them. But we'll get to these eventually.

Next up, you'll see the Hyperlink data type. This is good for storing web pages and email addresses.

Because if you click on it, it will load up your web browser or your email program.

Personally, I like hyperlinks for storing web addresses. I don't like hyperlinks for storing email addresses.

For web pages, it's nice because you can click on the web link and it loads up your web browser. For email addresses, it gets hard working with email addresses.

I'd rather store them in short text and I will show you a way in the more advanced classes on how we can send an email from Access.

Don't use the Hyperlink data type for email addresses and we'll talk about this when we get to email.

Next up is the Calculated field type. I don't like storing calculations in my tables.

As a general rule of thumb, if you have to do a calculation, do it in a query. There are some exceptions and I will talk about this in a future class.

Remember we had this conversation earlier. But if you see Calculated field types, don't use them.

Microsoft added the Calculated field type to tables to make it easier for beginner users to do calculations like sale price minus unit cost equals profit.

But that's more properly and better done in a query.

Calculated fields can cause trouble later on.

Next up is the Lookup Wizard. Lookup Wizard is an evil wizard. Are you a good witch or a bad witch?

Well, the Lookup Wizard is a bad witch. Don't use them. What is the Lookup Wizard? Well, again, Microsoft wanted to give beginner users the ability to look up a value from a list.

You want to pick a state from a list of states: New York, Pennsylvania, Texas, whatever. Put it in the Lookup Wizard and that goes right in your table.

But it violates the normal database rules of normalization. You don't want to use them. Trust me when you get into anything but the most basic of databases.

Lookup Wizards are a nightmare to work with. Please don't use them. And if you have them in your existing databases, get rid of them. I'll show you how to get rid of them when we get into the expert classes, when we start working with relational combo boxes.

If you're a new Access user, this is your first time using Access. Trust me. Don't use the Lookup Wizards.

So now that we've gotten a basic overview of all the different types of data types - the types of data types, say that 10 times fast - which one do you think works best for first name? That's going to be text. Short text or long text? I don't know too many first names that are longer than 255 characters, so short text. All right, and then we'll hit tab.

Now, if you want to type in a description, you can. Personally, I almost never use descriptions, very, very rarely.

If I know I'm building a database for someone who has no computer skill and the field name is confusing, then maybe I'll put a description in there.

But I almost never do unless I'm building a really complicated table for myself later on and I want to remind myself what this field is for. Then sometimes I'll use that, but generally no. So just hit tab.

If you decide you want them later, you can always come back and put them in later.

If you're working in a team with other developers or multiple people in your office that are designing the database, you may want to rely on the description fields a little more to explain to your teammates what some of these fields are.

One of the most difficult things as a database consultant myself to work with is when I get a database from a client and they want me to fix it or add something to it, and I have no idea what some of their field names are.

So the description can help me sometimes to understand what they've done. But I find that most people don't use them anyway, so I have to call them up and say, what does this field mean?

Next field: last name. Remember, no space, capital, and short text. Tab, tab, done. Nice and quick, once you get the hang of it.

Now, do you want middle name, middle initial, prefix, suffix, title, all that stuff? That's completely up to you. That's one of the benefits of building your own database with Access, you can decide what's important and what's not important for your database.

I've built databases for customers where they really only wanted one field for the customer's name because it wasn't important.

And I've built databases for mailing list customers where they wanted everything: prefix, suffix, title, middle name, all that stuff. You decide. One of the nice things about the short text field is that you can specify a limit on the size down here, where you see field size 255 by default. They start off at 255 characters. That's the biggest possible.

If you want to limit that to only one character or five characters for a zip code, for example, you can change that and make it small. We'll talk more about field sizes a little bit later on.

You can't do that with a long text field. People can type in as much as they want.

So for the purposes of class, I'm going to go with just first name and last name, which honestly is all I use in my database.

In my company database, I just have first name and last name. I don't care about all the rest of it. Honestly, I really only care about first name and email address. It's an internet business. I call people by their first name when I'm addressing them in an email.

Last name, I need it for credit card validation, but that's about it. How about the customer's address? And that's the short text field as well.Now, I personally have always been happy with just one address field. A lot of my clients like two address fields, one address, two. They like to put the suite number or apartment number in the address two field. That's fine. If that's what you want, build it that way.

You can put line breaks inside of a short text field. So you can type in part of the address, hit the line break (Shift+Enter or Ctrl+Enter), and it will give you a second line in the address field if you want that.

I have had customers who have wanted to break the address field up into the number on the street, the street name, and the type, whether it's a drive or an avenue. Again, the benefit of building it in Access is that you can make it as specific as you want to. For me, just address.

Let's finish this up with city, state, and zip. If you're in a different country, you might want to use postal code or whatever you have for your region.

I'm in the United States, so I'm going to use US addresses here. If you do business internationally, you might want to add country. That's fine.

If you're in Canada, change state to province, or whatever you want to call it. What I do for country is most of my customers are here in the United States, but I have customers in pretty much every country of the world.

I leave country blank for US customers. That's just how I do it. You would probably want to make your country blank for your home country.

So far, we've only used text data types. In the next lesson, we're going to add some additional fields to this table, and we're going to start talking about some of the other data types.

05. Customer Table, Part 2 (21:29)

In lesson five, we are continuing designing the customer table. We are going to decide whether or not certain fields need to be text or number, like phone number for example, or if we are going to store that as a text or as a number. We will see the difference between long integers and doubles, why we want to use an ID field, an auto number field, how to move fields around inside the table, saving your table of course, table naming conventions, and we will talk about primary keys and indexing.

Continuing on with our customer table, everything we have added so far has been a text field, specifically a short text field. Let's add some other stuff that is not text. I am going to scroll down a little bit here. You can add a whole bunch of stuff to one table. You can have lots and lots of fields. I have seen people send me databases with 100 fields in them. It should not have to go that much. If you start getting over like 50 or 60 fields, then you might want to start thinking about breaking that down into multiple tables. We will cover that more when we get into future classes, but do not try to put too much stuff in one table.

Speaking from experience, most tables that I have built have somewhere between 10 and 20 fields in them. If you start getting up over 100 fields, you really want to start considering a second table. We will talk a lot more about multiple tables working together with relationships in Access Expert Level 1. I will put a link to that down below if you want to learn how to do it now. The upward limit, the maximum limit, is 255 fields in a table, but you should never get anywhere near that close.

Let's put in this customer's website. That will be a hyperlink field. Like I said before, if you want to capture their email, make that short text. Trust me on that one.

How about the person's phone number? Now you think you want to use a number for this, but I am going to go with short text. Remember the way you tell the difference. Are you ever going to be adding up a column of phone numbers or calculating the average phone number? No, so store it in text.

If you want to do home phone, work phone, cell phone, I will allow it. Generally, I say if you have got multiple bits of information, store them in a second table, but if it is just two, three at the most, you could do home phone, work phone, cell phone. Billing address, shipping address, that is fine. For class, we are just going to store the one phone number.

Since I actually made a slide for this when I did the Access 2013 version, I will put it here for you. For calculations, if you need to find the sum or an average, use a number. Otherwise, use text. Drive that point home. Are you ever going to add two phone numbers together? No. Store it as text.

Do you ever think you are going to want to find the customer with the greatest number of children? Yes, you might. Or the average number of children for all of your customers, if you sell, let's say, kids' clothes. Those are fields that you might want to store as numbers.

Another thing to take into consideration is the sort order. Numbers are sorted numerically: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, and so on. That is numerically, that is a numeric sort.

Text values are sorted alphanumerically, and they are sorted by the character placement. For example, all the text fields that start with 1 will come before all of the text fields that start with 2. So it is going to go 1, 10, 11, 12, 13. Then after all those, you are going to get 2, 20, 25. It is a totally different sort whether you have it based on numbers or text. That is why if you are doing a lot of mailings, for example, you might want to split out the house number from the street name. That way you can sort all the stuff on the same street together and then put them in order based on the street number, which should roughly put them in the order for delivery. If you store them together, one Main Street will come right next to one Park Avenue. That is something to take into consideration when you are figuring out what fields to put in your database.

Here is another one, leading zeros. Some things that are called numbers like social security number, even though it is a number, we are going to store it as text. That leading zero is important. Yes, you could store it as a number and still display it with a leading zero, like the zip code for example, it is a five-digit number. We want to always have that leading zero there. It is just easier to deal with it as text. Plus, if you are dealing with Canada or some other countries that have a postal code that accepts letters as well, then you could still use your zip code field for those countries.

As a preview, we are going to store only the characters, not those dashes, because you can format the number however you want to.

Another thing to consider is whether it is always a number. If you have got a phone number field stored as a number, and once in a while you might get something thrown at you that has got letters in it, like 1-800-Drew-IDD or 1-800-Flowers. I remember back in the day, my grandma used to have a phone number where they still used letters in the phone numbers. You do not really see that often anymore unless it is a business number, like an 800 number, but it could still happen.

Next, how about the company name? That will also be short text. Let's say PC Resale does a lot of business with companies, a lot of business to business. Now, I would like to keep that company name up with the person's name.

How do I move these fields around? It does not really matter internally as far as Access is concerned, but you can move these fields up and down. Watch this. Click right there. That will select that row. Let your mouse go, take your finger off the button. Now, click on that same spot and then drag it up. See how that line is moving right there? Drag it wherever you want it and drop it, like right there. Like zip code, for example. Click on it. Click, drag, drop. See? I do not want to do that though, so let's put it back where it was. It is easy to move these things around. First name, last name, company name.

Let's come back down here. Let's say at each company, I want to know how many employees they have for marketing purposes. How many people have you got there? That way I know that if it is a bigger company, they are more likely to buy more computers.

I like to start my number fields off with the word num, with the prefix num, so numEmployee. I like to keep everything singular. I know it seems kind of weird, but that is just my own personal preference. In fact, if you watched the last version of this class that I did, I was calling this numEmployees, but since then, and that was 2013, in the past eight years I have really come to make sure that I keep all of my field names singular, because it bites me. It hurts me a lot. Look on my website; I have got a table called CommentsT, plural, and every time I am coding for it, I am like, look up this from the CommentT, comment table, and I am wrong because it is plural in the table. So I will try to keep everything singular.

Now, numEmployee is going to be a number field. If you put in just employee, well, what is that? The employee ID, the employee's something, I do not know. NumEmployee tells me, oh, okay, yes, number of employees this company has. It makes a little more sense. I do not want to type out number of every time, so for me, again, this is just one of my naming conventions, numEmployee. If you are making a database based on family information, you might have numChildren, for example.

This is going to be a number field because I might want to run a query later that says, show me a list of all my customers with more than 50 employees or sort this list of customers, everybody from New York with more than 20 employees, sorted by the employee number. I might say add up all of the employees that my customers from Pennsylvania had. What is the total number?

On the number field down below here, it says field size long integer. Click in there and then drop this box down. You will see all the different types of numbers that are available. Remember what I said earlier? For now, we are only going to worry about long integers and doubles, that is it. Long integer for counting numbers, 0, 1, 2, 3, 4, and the negatives. Doubles for anything that has a floating point, a decimal.

In my more advanced classes, I will talk about the differences between these number types. For example, integer and long integer, what is the difference? Integers are smaller, they only go up to 32,000 some odd, as far as their value goes. Long integers go up to the billions. However, integers are also smaller as far as the amount of room they take up in the computer's memory. So if you have got lots and lots and lots of numbers, you might want to use an integer. If it is something like, for example, number of employees, you might not have a company that has a billion employees. Long integer works, but it takes up more space. These are considerations to worry about later on when you become a database expert.

For now, just think integers for counting numbers, doubles for decimals. That is all I want you to worry about at this point.

What is something you use a double for? How about discount rate? That will be a number of type double because you can put 5.5 in there. In fact, if you look under format, you will find percent. We will talk about format in a future lesson coming up pretty soon.

Next, I want to keep track of how long each customer has been a customer and give rewards to people who have been with me for a year, two years, five years, whatever. I just recently started doing this on my website. I put badges up there. You get a five-year badge, a ten-year badge.

How long have you been a customer? I am going to use customerSince, and that is going to be the date you first became a customer. So let's make that a date/time field. Remember, a date/time field stores date or time or both together.

Let's keep track of each customer's credit limit, how much money they can spend with us. It is a money value. What does that mean? Currency. Currency is a special type of number field that is optimized for storing currency values and it will use whatever regional settings you have got going on. So if you are in the United States, you get dollars.

Earlier, I mentioned a field called isActive. Do not put a question mark on the end of it. Remember, only letters and numbers go in here. This is going to be a yes/no field. Yes or no value. What does that mean? Will it show up in lists? I am mailing list. You can explain what a possibly ambiguous field means by putting something over here in the description field if you want; you do not have to.

Now, pretty much every table, I put a notes field in, almost every one. That is going to be long text, formerly known as memo. If you hear me talk about something being a memo field, it is a long text field. Pretty much everything I can think of, like customers, orders, products, vendors, all that stuff, you might want to throw some notes in there to explain something about this particular customer.All right. So I put a notes field in just about every table. They will not waste space. A notes field will not use any space unless you put data in there. So it does not hurt at all to have a notes field in every table.

Now, we are going to skip some of these data types for now. Like Date/Time Extended, that is new. We are going to skip Large Number. We are going to skip OLE Objects and Attachments and Calculations and Lookup Wizards and all these things. We will get to these later on. Some of these I do not use at all, but I am going to show you how to use them in case you want to.

For now, I think we are just about good with the number of fields we have got in here. Now, there is still one bit of information, and it is one of the most important bits of information that we are missing from our table. Can anyone think of what that is? We are missing a way to uniquely identify each customer.

Remember, every customer should have their own ID so that we know that this John Smith is different from that John Smith. So we are going to add a CustomerID field to this table. And what are we going to make it? We are going to make it an AutoNumber. Right there, AutoNumber. Very important.

Now, personally, I like to keep my ID fields up at the top of the table. So I am going to select that row, let it go, then click and drag and put it at the top. That is just a personal preference, a style, if you will. I keep all of my IDs at the tops of the tables.

AutoNumbers will start at one and Access will keep counting up for you. So the next customer is two and three and four and so on. If you delete someone, like customer three, that number is gone. But you do not have to worry about those numbers. Do not use those numbers for anything in real life.

I have recorded whole videos on why you should not rely on AutoNumbers for real world things. For example, if you are a new company starting out and you make someone an invoice and they see on the invoice that they are customer number seven, now they know you only have six other customers. So those are the numbers that we do not want our customers to see. AutoNumbers are purely for Access to use internally. You do not use them for things in the real world.

If you want to have a separate customer code, like P3642, that is up to you. And again, I have got whole videos on how to set that kind of stuff up. I will put a link down below in the link section for my customer code video. It is a little more advanced, but it shows you how to set up a second field that you can use if you want to give the customer a customer ID, but do not show them the actual customer ID.

Pretty much every table that I build is going to have an AutoNumber in it. Again, I have gotten into some arguments with some other developers. I recorded a whole video on why you should use AutoNumbers. Products will have a ProductID, vendors will have a VendorID, employees will have an EmployeeID. These AutoNumbers are used by Access internally to make sure that you have unique records and to create relationships.

For our customers to orders, for example, which customer does this order belong to? Well, you look up the CustomerID. I am going to keep repeating this information because I want your brain to start thinking about AutoNumbers to make relationships.

It is going to be a little while before we get to relationships, but just be thinking about it. The other fields in this table really are not good for uniquely identifying people. You might have 100 Johns, you might have 6 John Smiths, you could have 3 John Smiths from New York.

I have seen some companies use phone number. You could have 2 people with the same phone number. It happens. Not only do you have people that share the same phone number at home, but you get phone numbers that get reassigned. Someone moves out of the area and someone else gets their phone number. So an ID field makes it so that internally in the database, you know that this customer is this customer. It cannot possibly be someone else.

If you have got an old paper system, and I know a lot of you do, you are upgrading from either an old database, or you are using Excel spreadsheets, or even you are on index cards still, you might have customer codes, or some kind of a product ID that you are currently using. That is fine. You can just make another field for that, but still add the AutoNumber for Access. Trust me.

AutoNumbers will never get reused in the system. So if you get 100 customers and you delete customer 62, that 62 will never be reused again. And that is a good thing, because you would not want customer 62 to have related records in the database, and then they get reassigned to the new 62.

One of the things that I like to teach is we do not delete records. We just mark them inactive. That is one of the reasons why we have this IsActive field. If someone, let us say, dies or moves out of the area, do not delete their record. Just mark them inactive, and they will not show up on your lists anymore. We will talk about this as we go along.

Again, if you want to learn more about that, I have got a whole video that I recorded about not deleting records. I will put a link to that down below too.

Now, nothing that we have done so far has actually been saved to our database. So let us save this table so we do not lose all our work. Right up here in the Quick Toolbar, you will see the little floppy disk icon. Remember floppy disks? Who is old enough to remember those? So you can click on that floppy disk, or you can see the little tooltip pop up, Control S. That is what I use. Control S for save. It is the same as in Excel, and in Word, and pretty much every other program you can think of, Control S to save.

It is going to ask you for a table name. What are we going to call this table? Well, what kind of stuff have we got in here? Customers. So, customer, singular, customer, T. I like to end all of my tables in the letter T. All my queries will be Q. All my forms will be F. All my reports will be R.

Do not worry about macros and modules. I have good reasons, and a lot of my reasons for things that I do in the beginner classes have to do with when you get to the advanced classes, when we start doing things like SQL, and building advanced queries, and some Visual Basic. If you know upfront that it is a table, you can do things differently, versus if it is a query. So just trust me. Benefit from my 27 plus years of experience teaching Access. All your tables end in a T.

You might see some people do this. They might do TBL Customer. That is fine too. I just like my way better. So customerT is how I am going to be doing it. If you are going to be taking my classes, do it the way I do it. Things will go easier for you.

Now, you get this thing up. There is no primary key defined. Although a primary key is not required, it is highly recommended. A table must have a primary key for you to define a relationship between this table and other tables in the database. Do you want to create a primary key now? Yes, No, or Cancel.

Primary keys are important. A primary key is that one field that uniquely identifies each record. Now, we did add an AutoNumber. So we created the field already that we need to use for our primary key. We just forgot to tell Access, hey, this is my primary key. There is a button right there that you can click to make that your primary key.

The reason why I did not like this is because personally, I have been using Access for almost 30 years. I always forget to do that. I never remember to click on that primary key button. So I wanted to show you the way that I do it because you are going to do it the same way. Then you get this thing popping up in your face and you are like, what is primary key? Oh, I forgot to click the button. It will happen.

So I just saved the table and then when this pops up, I go, yeah, okay. Now, if Access sees an AutoNumber, it will use the AutoNumber as the primary key because you should make all of your tables have a primary key that is an AutoNumber. That is just my rule for good database design. If Access does not see an AutoNumber in your table, it will add one for you and call it ID.

Try to get in the habit of remembering to add an ID field for each of your tables. What exactly happens is when you make something a primary key, Access will index it, no duplicates. We will talk about what indexing is later on. It is very important. It speeds up searches and sorts a lot in your database.

No duplicates means you cannot have two of the same thing. You can index other fields. You can index something like phone number. If you want to make sure that no two customers have the same phone number, that is certainly possible. You would not want to index something like last name because then you could only have one person with the last name Smith. That is bad. That is where you can go Indexed, Yes Duplicates. We will talk about indexing. I have a whole separate lesson on indexing. For now, just keep in mind that the primary key is unique.

Now that we have got everything saved, let us go ahead and close the customer table and notice it is sitting right over here in our navigation pane. There is the customerT right there. If you want to make more design changes to it, editing the fields and the data types and stuff, right click on it and go to Design View. That will open it back up in Design View where we just were.

Let us go ahead and close it again. Now, when you are ready to actually add some records to it, put some data in it, you just double click on it. That opens it up in Datasheet View and that is what we are going to do in the next lesson.

06. Entering Data, Part 1 (21:08)

In lesson 6, we are going to learn about entering data into our tables. We will see how to type new records in. We will talk about resizing columns. We will look at different date and time values. I will show you the Zoom window, and we will discuss what dirty records mean.

In the last class, we built this brand new customer table. We have no records in it, so let's go ahead and put some records in it. Double-click on it right here, and then we'll open it up in Datasheet View.

If you want to get the most from your screen space, you can maximize this guy inside of Access. Now the table will take up the full area here in the object pane. Just like other Windows applications, you can restore it to normal size right there. So I am going to maximize it.

If you want to go back to Design View to make design changes, the button is right there. You can click on Design View. See that? That is where we were before. There is Datasheet View. It is like Spreadsheet View. In Access, they call it Datasheet View. You can drop this guy down here and you can see the two of them right there: Datasheet View and Design View. Design View is where you build the table. Datasheet View is where you add data or edit data in the table.

Remember, I said before I prefer building the table first, setting up the structure. Then we can go back and add data to it. Now, the first field in your table is the customer ID. Remember, that is your autonumber. It has not been assigned yet. That is why it says new. That number gets assigned as soon as you start typing the first character into the first field in your record. You cannot edit that; Access assigns it. You do not have to worry about it.

I am going to press the Tab key to move over to the first name field. Personally, when I am doing data entry, I like to use the keyboard as much as possible. In fact, I have a whole series of keyboard shortcuts to enhance your data entry pleasure. I hate having to stop and grab the mouse when I am doing data entry, especially if I am typing in a bunch of stuff. So, Tab to move to the next field, Shift+Tab to move backwards. I will be teaching you little bits here and there.

Do not try to learn too many shortcut tricks at once. I recommend in my videos you just do one a week. I will put a link down below to my shortcut tips. I will teach you a few here and there as we go along.

I will type in myself, Richard. Notice as soon as I do, customer ID number one gets assigned to me. See the pencil? The pencil means this record is what is called dirty. Dirty means that the record is being edited and has not been saved to the table yet. While you are editing this record, those changes do not get committed or saved to the actual disk until you leave the record or close the table.

Press Tab to move to the last name field. Press Tab again. The next column says Company Now. That means the column is too narrow to see the whole thing. I can click right there and widen it, just like in Excel. You can double-click there and resize it as wide as it needs to be to fit all the data and the title in there. You will find there are a lot of similarities between Access and Excel as far as the user interface goes.

I will type in my company name: Access Learning Zone. Tab. If you want, you can double-click there to widen that out.

Next is the address field. I will type in my address now. If you want to see a bigger field, and you do not want to have to widen all of these out, I am going to leave that about there because I know addresses can be big. Shift+F2 on your keyboard opens up this Zoom window. We do not need to have it that big. Let's make it about yay big. Zoom just means that you are zoomed into that field. Anything you type into here, when you hit OK, is going to go back in there. You can control the font and change those things. I will talk about that later.

For now, I will just put in my address: 6,900 Daniels Parkway, Suite 29-122. Some people like to put the suite on a second line; some people put it in an address field together. I am perfectly happy with that right there. So I will hit OK and notice it goes right back there into the address field. I will hit Tab.

I am in Fort Myers, Florida, FL. It is funny because I am watching the 2013 version as I am recording this so I know what to cover. The last time I did this, I was still in New York. I am from Buffalo, New York originally. I moved down to Fort Myers, Florida at the end of 2013.

Now, talking about the state field: I personally prefer just to type in two-digit state codes for your United States customers, or if you are in Canada, you have two-digit province codes. Depending on your country, your rules might be different.

Yes, it is certainly possible to make this a drop-down list, also called a combo box in Access. I will talk about that in a future class. If you want to learn about combo boxes now, I will put a link down in the link section. You can go watch that now. This is nice - I have already re-recorded all these classes. I can just give you links to stuff to go watch.

But I recommend at this point, do not get ahead of yourself. Just go with me. Go with the flow. If you are a beginner, if this is your first time using Access, stick with me. If you have a little Access under your belt and you want to skip ahead, I will put a link down below to my combo box lessons.

There are two kinds of combo boxes you can use. There is something called a value list, which is just a list of values that you give it, like a list of the 50 states, for example. There is also a relational combo box, which gets its data from another table. You would set up a second table that has a list of states in it. Both of those I will cover in future classes. If you really want to learn about it now, there are links down below.

Let's move on to zip code. My zip code is 33912. If you want to put a zip plus 4 in here, you can. We did not limit this to just 5 digits. That is completely up to you. Some people like to use a separate field altogether. Some people just put it together.

Personally, I do not do a lot of mailing. The whole point of the zip plus 4 system is to give discounts to people who do huge volumes of mail. I do not. The 5-digit zip code is just fine for me. I do not even bother putting the 4 digits in. They actually are 4 digits.

If you are the kind of company where you do business only in the United States, you can limit that to just 5 digits. I will teach you in Access Beginner 3 how to set up something called an input mask, which forces them to type in data in the way that you want, in the format that you want. You could say just give me 5 digits. If you are in Canada, you have 6. You could change your format accordingly. We will cover that eventually. That is called an input mask or a format property. Again, I will put links to that stuff down below.

Part of my job as your instructor is to give you information in the order in which I think it is beneficial. I have been teaching this stuff almost 30 years now. This is how I want you to learn things. I am going to give you links so you can jump around if you are curious. But just stick with me. Right now, that is all you have to worry about - just type in the zip code. We will get to all that crazy formatting stuff later.

Now for country, I leave people in my home country, the United States for me, as blank. I do not bother putting a country in there. There is no need to put it in there for printing addresses or labels or anything. So I will leave that blank. If you are in Canada, leave Canadians blank. Whatever you want to do.

Alright, Tab. Let's slide over a little bit so you can see these fields here. You can just use the scroll bar at the bottom there. If you do not know how to use a scroll bar, go take my Windows beginner class.

Now, website. For website, I am going to type in 599CD.com. Notice how it comes in as a hyperlink. If I move my mouse over it, you can see I get the little finger with the HTTP 599CD.com. Why is it 599CD? It goes back to the beginning of my company. There is a whole story on the website about why it is 599CD. I will actually put a link to that down below too if you are curious. You can go read it.

But if you click on it, there it is. It pops up. There is today's website. That is what it looks like today. There is me. Hi. I will just close that.

Now, email address. We are storing it just as short text right now. You can store it as a hyperlink. In fact, in my older versions of this class, last time I did this in 2013, I did have my students save email addresses as a hyperlink. But I do not like doing that anymore.

In fact, here is the old video, where I show how to do it as a hyperlink. I am not going to play this. But it causes problems. The way that Access stores hyperlinks internally, it is just a mess. If you want to do anything with that hyperlink, like pull out that email address and display it some other way, you have to jump through hoops. So I find it easier just to store email addresses as text. I will show you a trick later on where if you really want to launch your email program using that email address, you can. We will cover that in a future lesson. But I do not like storing email addresses as hyperlinks. It is just - no, please do not.

So I will type in my email, amacronatgmail.com. There is my email address. Let me widen that a little bit, shrink that one down a little bit. Let's get the phone numbers.

Here in the United States, we have a seven-digit phone number. That is a three-digit area code, a three-digit prefix, and then a four-digit suffix. So 716-555-222. And yes, I am still stuck on 716. That is my Buffalo, New York area code, even though I live in Florida now, which is 239. But I always use 716 in my examples for some reason.

Again, once we get to that format property, I will show you how to format that with parentheses or hyphens or dashes or whatever you want. That is Access Beginner 3. We will get to that. For now, just store the digits.

I try to avoid letting people type in phone numbers in all kinds of different formats. Right now, the text field is wide open, so you can type in anything you want in there. But for now, just get in the habit of typing in just the digits.

I will show you tricks later on to clean up messy phone lists like this. You can remove the parentheses and the dashes and the dots and whatever other things people type in. Eventually, like I said, in Access Beginner Level 3, we will type in an input mask, which only allows them to enter in digits.

If you do a lot of phone calling and you want to generate a query that says "show me everybody in the 716 area code," if you have a messy list like this, it is very hard to generate that query. So you want to keep all your data consistent.

I do not make a lot of phone calls. In fact, I do not even collect phone numbers anymore from my customers, so I never use the phone. I hate using the phone. I cannot stand talking on the phone.

As far as database queries go, I am going to show you string functions later on where I can say "show me the left three characters of the phone number field," and if it is 716, then do this, or give me the middle three characters.You can't do stuff like that if your formats aren't consistent.

What's up next? Number of employees. One, just me. All right. This count rate. I'm going to type in 50, 50.

Now, we formatted that number as a percentage when we developed the table, when we designed the table. Just so you know, internally, all that does is it multiplies the number by 100.

So, internally, that is actually stored as 0.5. And that's significant. We'll talk about that later when we start getting into math calculations and stuff.

Next up is the Customer Since field. And remember, this is a date/time field. See the little calendar there and the calendar pop up. You can click on that if you want to and pick today's date. You can see today's date has kind of got a little bar.

Click. See, 2/24/21. Or you can type a date in, 1/1/20. Or you can put a time in there, 6 p.m. Or you can put a date and time in there, 1/20 at 3 p.m., just like that.

And when you do that, that happens. All these little pound signs, octothorpes as they're called, hashtag marks. That symbol has got a million names. That just means that the data that's in there is too wide to be displayed.

So we just have to double click there to fit all that information in there. But you could put a date or a time or both.

Here's a little keyboard trick for you too: Control + Semicolon pops today's date in there.

If you type in a date without a year, you get the current year. So if I type in 5/5, I get 5/5/21. It's currently in 2021.

Now, I'm in the United States, so I get month/day/year. That's based on your Windows regional settings. So if you're in a country where it goes day/month/year, which I think is more logical to be honest with you, then that will be based on Windows.

Access stores that internally as a number. It just formats it based on your regional settings.

There are lots of different ways you can actually type in dates in here. You could type in Feb space 1 and it gets February first. You could type in the whole month name like May 6 and it'll get it. It's pretty smart.

You can type in 24-hour time, like military time, like 1500, and it'll get 3 p.m.

Once again, in Level 3, I will show you how you can change that format to display whatever you want, in whatever format you want. I know I'm talking up Level 3, but remember, I can't cover everything in one lesson.

Level 3 is where we do all the formatting stuff. So in here, I'll just put today's date. Control + Semicolon. There we go.

Everybody in my class always asks me at this point if there is a way to put a default date in there, so if you're entering orders, for example, you don't have to keep typing in the order date. Yes, that's called a default value. Again, I've got videos on that. I'll put a link down below.

Default values are covered in, guess which class, Access Beginner 3. That's a popular class, Beginner 3.

Now, when it comes to two-digit years, the cutoff date is actually set in Windows. For example, I'm still using Windows 8 on this computer. This computer is a little bit older. So if I type in 1/1/29, Windows assumes it's 2029. If I type in 1/1/30, Windows assumes it's 1930.

Now, in Windows 10, they've changed that. I believe the cutoff here now is, let's see, 2049. So if I type in 1/1/49, I get 2049. If I type in 1/1/50, I get 1950. You can change that. I've got a separate video. That's actually a Windows setting in your Control Panel.

I've got a separate video that shows you how to do that. I'll put a link to that down below as well. That's not an Access thing. That's a Windows Control Panel thing.

You might want that number to be different based on whether you are a pediatrician or a geriatric doctor. Your two-digit year might be based on the birth date of your patient. So, depending on what you get more of, you might want to change it.

The best way to handle that problem if you're not sure is to just stick to typing in four-digit years, honestly.

Next up is Credit Limit. I'll type in 2000. Notice I've got dollars because I'm in the US. Again, that's a regional setting.

Next up is Is Active. That's our yes/no value. You can use your mouse and click on it. Like I said, I hate having to stop and grab the mouse when I'm doing data entry. I just want to fly with my fingers on the keyboard. Space bar will toggle that on and off as well. So you just use the space bar and tab.

Next up is the Notes field. You could type pretty much as much information as you want in a notes field, 65,000 or so characters. That's a lot. 64K worth of data, kilobytes.

Again, Shift+F2 zooms in and you could type in whatever you want. This guy is really cool. He's a Star Trek nerd and a Rush fan. You can go for days. And it goes right back in there.

While you're in the zoom window, Shift+F2, if you come in here, if you press Enter, it gives you a new line.

Now, older versions of Access didn't do that. Enter was the same as pressing. You'd have to do Control+Enter to get that new line.

In fact, if you're in the field itself, let's slide over here. Let's say we widen this out. If you come in here and you press Enter, it doesn't give you that new line.

You can actually make this taller like that. You can make these rows taller, just like in Excel. If you want to type in more stuff in here and press Enter, you see it doesn't do what the zoom box does.

But you can come in here and type Control+Enter and get the next line if you want to do that. There is a way you can change that. It's called the Enter key behavior.

I talk about that in a future class. That's something that you set in the form, because you're not going to have your end users working directly in your tables. We haven't talked a lot about that yet. This is just for us. We're the developer, we're setting up this database, putting some sample records in it. Your end user, even if that end user is you.

You want to work with the data using forms. We'll get to that in a couple of classes, in a couple of lessons.

We're going to work on tables first, then some queries, then we'll get to building forms.

Let's resize this table back where it belongs, right about there. That's fine. I can Shift+F2 to go back in here and make design changes if I want to for the zoom, for the text inside of here.

Tab. That'll bring me down to the next row, the next record. Remember, let's try to use Access terminology.

Columns are actually called what? Do you remember? These are fields. Columns are fields: the Address field, the State field. And the rows are called what? Those are records.

Try to use your Access terminology, not Excel. I know probably a lot of you have been using Excel for many years. When I get your questions in my email and on the forums, you're all like, I got this column. No, it's a field. Let's use our Access words.

Another thing to notice now that I'm down here, notice the pencil is gone. If I come back up here and start editing this, the pencil is back. That means this row is being edited. It's dirty.

As soon as you leave that row, that record, the pencil goes away. That means Access has saved your changes to the database file. It's been committed to disk.

It's not like Excel where you have to constantly worry about saving your sheet. Access will save your data every time you leave the record or close the table.

You only have to worry about hitting Save, that Control+S that I showed you earlier, when you're making design changes, when you're making changes to the structure of the table, the query, the form, the report, or whatever you're working on.

The data gets saved every time you move from record to record.

One thing you might have noticed way over here to the right is this "Click to Add." They added this, I think, in Access 2007 or 10, where you can drop this down and you can add new fields right here. I don't like this. I don't like adding new fields while I'm in datasheet view.

If you want to add or make changes to the structure of your database, of your tables, go into design view and do it in here. Just trust me. Just listen to what I'm telling you to do. Don't come out here and hit the add button out here. That's the lazy way to do it.

There are some real problems. I'll talk about them later.

So we've got one customer added to our table. In the next lesson, we're going to add some more.

07. Entering Data, Part 2 (15:09)

In lesson seven, we're continuing with entering data. We're going to enter some more records into our table. I'll show you how to download a sample database from my website, so you don't have to type in all those records if you don't want to.

We'll talk about the record selectors, how to turn off the delete confirmation warning, saving layout changes, resizing our columns, and moving columns around.

I'm now ready to type in my second customer. Let's go with Joe, Tab, Smith, Tab. Notice the customer ID has already been set by Access. It's set to customer two. Remember those will automatically count up and you don't have to worry about what they are. That number is completely meaningless to you. That's just for Access to use internally.

Joe is at XYZ Corp, 101 Main Street, Buffalo, New York, 14220. Tab past the country since he's in the US. I don't have his website or his email address, and that brings up a good point.

I've always been of the mindset that no data is better than bad data. Don't force your users to have to type something in, for example, zip code or a phone number, or even a last name if they don't have it. Unless that's absolutely critical for your business, don't force the user to type it in and I'll show you how to do that very soon.

There's something called the required property we can use to force the user to have to put a value in. We'll get to that in Access Beginner 4. But I very seldom force users to have to type in information. Sometimes it's necessary. Sometimes you want to make sure you've got a specific bit of information. But if you don't have that information as a user, you're likely to just type in any old thing, like a phone number like 555222. It's better to have that field blank than to have something in it that's wrong.

It's easier to go through later and say, which customers do I not have phone numbers for? I then try to contact them by email or mail and get the phone number. But if it's got a junk phone number in there because you required them to type that in, that's much harder to find those. So just keep that in mind as we're building our database.

Moving on, phone number 7165553333. Number of employees, let's say 500 tab. Discount rate. Again, remember, you can type it in as 10 like that or you can type it in as .1. Either one works. Customer since, let's say 1190.

And while I'm at it, let me fix my customer since, as I know I put in 1130 in the last lesson just to show you the cut-off dates. But the company wasn't even around in 1930. That brings up another point. I'm going to teach you about something called a validation rule. That's coming up in Access Beginner 3.

That's where you can say the value has to be greater than this and less than that. For example, greater than 112000 if the company was created in 2000, but less than today's date. You can do that with a validation rule. I'll put links to these things down below in the links section. If you want to learn about them now, you can go ahead and click on it because these lessons are already made. You just have to get there.

So I'll type in 112002 for me. Now, I'm going to use the down arrow to go back down to that record. Then the right arrow, just like a spreadsheet, you can use the arrow keys to move around if you want to, just like Excel. Credit limit of $500 tab is active. I can press with the space bar tab. And then any notes you want to put in there. Bill him net 30 and then tab. That'll bring us down to row 3 for the next customer.

Notice the pencil is gone from the left-hand side over here. That means this record has been saved in the table. The Joe Smith record has been saved. Now, as you can see, once you've got the table properly defined, you've got it built, you've got the fields all set up and defined, it's easy to enter data into the table.

Like I've said before, we don't want our end users, the other people that are going to be using our database, to interface directly with the tables. But me as a developer, I like to put some sample records in a table when I'm done building it. This lets me see what the table is going to look like, how the data is going to work, and make sure everything's working OK. Even if it's just bogus data, it gives me an opportunity to see the table in action.

Remember, your end users will be working only with forms. You don't want them poking around inside your tables. We'll get to building forms a little bit later today. But it's easier for us as the developers to be able to build those queries and forms and reports and see some sample data in there. It's easier, for example, with some of the wizards; it will show you the sample data in the field. You can say, this column has to be that wide. This field needs to be this wide and so on.

For the purposes of class, I'd like to have a bunch of different customer records in here for us to play with. We're going to be building some queries in a few minutes. The whole point of queries is to display the data in different ways. We have to have a bunch of different data, a bunch of different records in there to see how the queries work.

If you just want to type in your own sample data, that's fine. Put a couple of people in there from different states. Mix up the data a little bit. Now, if you don't like typing and who really does, you can go to my website right there. There's the link and you can grab a copy of the database as it is with all the data in it.

I'm going to type in a bunch of data right now. I'm not going to make you sit here and watch me type it all in. That would be boring. Feel free to go to my website and download my database.

Here's the database page. This is where the link's going to take you. This is what the page looks like right now. It still says "in progress" right now because it's not finished yet. I'm still recording it. That's what I'm doing right now.

But come down here where it says "full course lessons page." Click on that. Scroll down. You'll see I'm still in the middle of lesson seven. Right there, it says "databases built in class." Go ahead and click on that. That will save the file to your Downloads folder.

Depending on the browser that you have, this may be a little bit different. This browser here is Microsoft Edge. You can open the file directly if you want to by clicking on Open File. But you can't run the database out of here because it's still inside of a zip file.

If you don't understand what zip files are, I've got a whole lesson on just working with zip files. It's on my website. I'll put a link down below. But basically all you have to do is copy this file to your desktop or somewhere else that you save your databases so you can open it up.

I'll just take this file, click and drag and drop it on my desktop. There it is. Now I've got my PC resale, middle of lesson seven, dot ACCDB. Now I can close the zip file. And let's open this guy up.

Now you'll get the security warning. It says some active content has been disabled. This is to keep you safe and to prevent you from getting a file that might have some malicious code in it. Yes, it's possible for viruses to be transmitted through Office documents, Word, Excel, Access, all those things. So Microsoft disables it for unknown databases.

I'm going to click on Enable Content. That marks this database as safe. You can tell it's safe too because there's only a table in it. You can't hide any kind of viruses or whatever in tables. There has to be a form in here or a module or something like that with some code in it.

But if you're going to be learning from me, you're probably going to want to download the databases that I build and trust me. If you get it off my site, if you download it directly from 599cd.com, I guarantee it's safe. I've been doing this since 2002, zero viruses. 40,000 plus customers.

And you'll find the customer tables right there and there's all of our records. We've got everybody from Ellen Watson to James Kirk and John Luke Picard and we're all set. So we got some sample records to work with.

Mini story: When I did this class the first time back in 2002, I just put in some random email addresses like [email protected]. A couple of people who actually had those addresses said to me, hey, you used my real email address in your class. So from now on, I will only use email addresses at domains that I own, like emichron.com and 599cd.com.

And there's my actual email address right there, emichron@gmail. Although I prefer you contact me using the website because I get so much email, I wouldn't want to miss your email. So if you want to contact me, use the website.

I hope all of the phone numbers that have been here are bogus. Although I haven't gotten any complaints about those. People are more likely to fire off an email than they are to make a phone call.

Now let's talk about deleting a record. If you come over here and you start typing something in, let's say, Kenny, and then you realize you really don't want that record there. So I'm still editing the record. You can see the pencil is there. If you decide while you're mid-edit that you don't want it anymore, press escape. It'll go away. That's if you haven't saved the record yet.

Now let's say you did save that record. Let's say I type in Kenny. The first thing I want you to notice right there: look what happened. It was 17 before. Now it's 18. Where did 17 go? This is how an auto number is assigned.

The second you start typing in that first character in that first name field, that auto number gets assigned and it's gone forever. You cannot get it back. There is a more advanced way to get it back if you really want to know how; I'll put a link down below. But the bottom line is you don't have to worry about that. That customer ID is meaningless to you.

I get emails all the time from people like, oh, I missed some auto numbers. Don't worry about it. Don't worry about those auto numbers. Those are not for you. Those are for Access.

When we start making relationships between our tables, Access will use those.

Now, let's say for example, I've already saved Kenny's records. He's saved. Pressing escape now will do nothing.

To delete that record, click over here. That's called the record selector. This little gray box over here. If I click there, I've got Peter Smith selected. There I've got Anna P. Corse selected. I want to select Kenny's record.

Yes, you can select multiple records by clicking and dragging, just like in Excel, but be careful. It's very dangerous if you're going to delete them. So click there, select Kenny, press delete on the keyboard, and it's gone.

You might get a delete confirmation up. You might see this dialog. It says you're about to delete one record. If you click yes, you won't be able to undo this delete operation. Are you sure you want to delete?

Once you delete a record, there is no undo operation. Make sure you have a good backup before you do any major deleting in your database. Be very careful. I'm going to say yes, and then it's gone.

In case you're curious, you can find that setting about the delete confirmation under File and then Options. Then under Client Settings right here, where it says Confirm Record Changes. If you turn that off, it won't prompt you every time you delete a record. I personally like it off, so I'm going to leave that off.OK, that's all I had initially when I deleted that first record. Then I went back in, turned it on so I could show you the delete confirmation.

Just be careful and make sure you have good backups. But if you don't trust yourself, leave that checked on.

So now notice the next record that I add is going to be record 19 now. See that? Those auto numbers get used. I'm going to press Escape a couple of times, and I'll come back to a blank new record. Now the next one should be 20 and so on. See how it works? Don't worry. Don't stress about those auto numbers. They're meaningless to you.

This is about the time I usually throw this slide up: back up your data every night, regularly. I use Google Drive. I basically copy my files over to Google Drive, and they get copied up to Google's cloud server. So it's off site in case something happens to my office. My database files are all safe. I've got a whole separate lesson on backing up your data. I'll put a link down below.

It's important. Make sure you back up your data. If you're running your business out of your Access database, back it up every night.

So back in the database, I'm going to resize a couple of things. See, that custom writing doesn't have to be that big. First name can be a little bit smaller. You can double-click here - watch this, double-click - and it will resize it to be as wide as it needs to be. Maybe the company name can be a little bit bigger. Maybe that can be a little bit bigger. State can be smaller, and so on. Just size these the way you want to see them.

And again, remember, we're editing data in the table. So this is just for you. Your end users are not going to interface with your tables.

Let's say you want to move country out in front of the address. You can move these columns around in here. Watch, click to highlight the column, let the mouse go, then click and drag it wherever you want it to go. See that dark thick line right there? Let it go, and then we just moved the field. A little bit easier than moving columns around in Excel.

The important thing to know about Access is that it's impossible to mix up data. Like in Excel, you can easily move someone's address, for example, off of the same row. You can't do that in Access. In Access, each one of these rows is bound as a record. You can't sort a column and scramble the data like you can in Excel.

You can move multiple columns, too. For example, let's say I want to move all of this stuff here - Address, City, State, and Zip - in front of Country. Watch this. I've got the down arrow there, click and drag to select multiple columns, let the mouse go, then click a second time and drag to the left. And now it's kind of back to the way it started. I can move those fields around. And again, this is just a layout change. We really haven't changed the design of the table, just its layout, and this is just for you.

Now let's say I want to close this table. So I'm going to click on the Close button. Access says, do you want to save changes to the layout of the table CustomerT? The layout changes are the moving around of stuff, the resizing of the columns, and the moving of things around. Yeah, sure, go ahead. It'll save that right in the table. So the next time you open it up, it will look the same way. For the most part, some things don't save, but we'll talk about that later.

The only time you have to worry about saving changes is if you make layout changes or design changes. Remember, as you're entering data, every time you move from record to record or close the table or form, all that data that you type in is saved each time you move from record to record. So you don't have to worry like a Word document or an Excel spreadsheet. You don't have to worry about constantly saving your work. Access saves it for you.

Now that we've got some data in our table, in the next lesson we are going to talk about sorting and filtering.

08. Sorting & Filtering Data (18:30)

In lesson 8, we're going to learn how to sort and filter our data. We'll learn how to sort a field. We'll learn about text, numeric, and date sorts, and the difference between numeric and alphanumeric sorts. We'll see how to remove a sort. We'll see how to filter our records, both filtered by selection and filtered with the checkboxes. We'll see how to show blank records and null values. And we'll see how to print, print preview, and send our table as an email.

As of right now, we only have 16 records in our database. What happens when we have 1,600 or 16,000? These records can quickly become difficult to manage, so we have to learn some tools to work with them to sort and to filter our data.

Right now, for example, if I want to find all the customers from New York, I can just come over here to the state column and I can see, okay, there's one, there's another one, there's another one. That's easy to do when you only have one screen full of data. But if you have 16,000 records in here, my database right now for my business has over 50,000 records in it. I've been doing this since 2002, so I've got a lot of customers in my database. To find the customers from New York would take me a while.

So what do you do when the boss comes over and says, "Hey, I need a list of customers from New York sorted by last name, and I want it on my desk in five minutes?" What do I do? I'm the boss. So I would never do that. I wouldn't do it to you either. But what do you do if your boss does it to you? Well, then you have to learn how to sort and filter.

In table datasheet view, which is where we are right now, and again, remember, this is just for you, the developer. Your end users will never work with the tables directly, but this is a trick that you can use. You can sort and filter this data to quickly come in here and find stuff that you need.

Now sorting is pretty easy. See, there are little arrows here. All you have to do is find the field you want to sort on, drop that down, click on that arrow, and there's sort A to Z, sort Z to A. Pretty straightforward. I just sorted those in reverse alphabetical order. There's sort A to Z. Not that hard to do. Kind of like Excel, except here you don't have to worry about selecting everything. Each row will stay together. If I sort based on city, I don't have to worry about scrambling all the rest of the data.

So we've got sort ascending and sort descending. Z to A is descending. Now, very important to remember, and I teach this in all my classes: ascending has nothing to do with the back of a donkey. Okay, bad joke. I've been teaching that one since I used to teach in the classroom. I used to have a classroom training center before I started teaching online, and I started doing that in 1996. I have been teaching that joke for a long time now.

If you sort a numeric field, let's find a numeric field here. Number of employees, sort that. You'll see sort smallest to largest, largest to smallest. It's the same thing. Discount rate, smallest to largest.

Now remember, some of our fields that look like numbers are actually text fields, and that's on purpose. So if I sort by phone number, let's say for example I change this guy to just 555. Let me sort it again. Let me sort by something else, and then I'll come back in here and sort phone number. Ready? Go. Look, 555 stays with the fives, because alphanumerically, that's how it would be sorted. The 555 wouldn't float to the top, because that's not a number. That's an alphanumeric sort. Versus over here, this is a numeric sort. Big difference.

In a numeric sort, the numbers are sorted in order, smallest to largest. So 569, 555, because 555 is greater than 9. In an alphanumeric sort, they're sorted by character. So all the ones would stay together, the twos, the threes, the fives. 555 would come right after 5, because this 5 is less than 6. See the difference? An alphanumeric sort obviously works with characters A through Z too.

So that's the difference between those two sorts. Remember, some of our fields that look like numbers, like zip code, are actually text fields. You'll get a better appreciation for this as we go on. Again, remember the rule is, are you ever going to need to put them in order numerically? Are you ever going to be doing math on them? Taking the sum of a column of zip codes, no. You may want to sort them, that's fine, but it'll be an alphanumeric sort. If they're all five digits long, they'll sort just like numbers would.

So let's put this back to a normal number, a phone number that is. There we go.

The data type in the field in your table design view and your properties determines how this data is sorted, how it's stored. Remember, these are stored as numbers, this is stored as text.

Moving on, date time fields like "customer since," you'll see sort oldest to newest, newest to oldest, again, same thing. A to Z, smallest to largest. Internally, the way that Access stores dates and times, they're stored as a number internally, and smaller numbers are earlier dates. Keep that in mind. So numerically, 1966 is less than 1999, that's the way they sort, that's the way they're stored. This will become important when we really get into sorting and using dates as criteria later on.

Currencies work just like numbers. Yes/no values, selected to clear it and cleared to select it, there are basically two sorts. Internally, this is stored as negative one. I know it's weird. Selected values, yes values, are stored as negative one inside of Access. Do you have to remember that? No. Don't remember that. Even later when we get into programming, you don't have to remember that. Just remember zero. Yes/no values are either zero or not zero. That's what's important. You can sort them either way, clear to selected or selected to clear, it doesn't matter.

Let's go up to our ribbon. Now again, my ribbon is minimized. I showed you this before. Just double-click on one of these guys, it'll pop it open. When I teach my developer and my advanced classes, I usually minimize this because I don't want to waste all this space on the screen. Sometimes I come back to my beginner class and I forget to open it back up again. Now you know how to open and close the ribbon. To minimize it, double-click and open it back up again. I showed you that back in lesson three, but I'm going to keep showing it to you. Because repetition is good.

Sometimes people get mad at me when I repeat things. Well, yeah, I'm going to repeat things a couple times. You are beginners and repetition is good. You don't learn the alphabet when you're a kid the first time you go through it. You've got to repeat it a few times. Yes, you can replay the video, but it's not the same thing.

One of the analogies that I like to give in my later classes is that working with Access is like playing with Legos. All of the pieces are the same, but you can put them together in an infinite combination. Infinite diversity in infinite combinations. Any Trek nerds in there like me?

So, you know, the forms, the controls, the text boxes, all that stuff. It's all the same piece parts, but you have to learn different ways of putting them together. That's my job. I'm going to teach you the piece parts first. That's the beginner series. Then as we get into the expert classes, I'm going to teach you how to put them together differently. Here's how you manage a customer table. Here's how you deal with contacts. Here's how you put an order together. It's all the same stuff. I could make the course very short if I just showed you one way to use everything, but I'm going to show you lots of different stuff.

Now, right now I've got a bunch of different weird sort things going on in here. I'll talk more about what these exactly do. You can do all kinds of weird crazy things like multi-column sorts and all that stuff. For now, let's just click Remove Sort. That'll take all those sorts off. There is advanced sorting and all kinds of crazy things you can do. We'll talk about this stuff later.

In fact, I've got a different video you can go watch right now if you want to. One of my free TechHelp videos on sorting and filtering. I'll put a link to that down in the link section. If you want to learn more about sorting and filtering right now, go watch that. If not, take your time and relax. We'll get to it.

Now, up top here in the ribbon you'll also see the ascending and descending buttons. See, I should have waited. I usually wait until I mention the ascending button before I go over that joke. I've got to remember that for the next time I rerecord this class 10 years from now. But whatever field you happen to be on, like company name, if you hit the sort ascending button, it will sort that field. Or this one here: sort descending. See how that works? Same thing. Same as using these little buttons here.

Let's remove the sort and it puts it back into the... This is the order that they were entered in. If you turn off the sort.

Now, the boss wants to see all the customers from New York. Let me go ahead. I'm going to maximize this table. If you're only working with one table and you want to see it fully, just double click on the header or click on the maximize button over there. That will maximize that table. So now I can work with it with more space here. Again, to restore it, click right there. But that's Windows 101 stuff. You guys know that stuff already.

So I want to see just the customers from New York. There are a million ways to do everything in pretty much all the Microsoft Office applications, Access especially. Access and Excel. There are like six different ways to sort and filter. I'm going to show you a couple different ways. You pick one that you like and stick with your favorite one. My favorite way is just to find the one that I like, right click, and then right down here you'll see equals New York, does not equal New York, contains New York, does not contain New York. There are more advanced options over here, but for now, I'll just pick equals New York. There you go. I have now filtered the records to show just New York. You can see this little guy here has a little filter symbol on it, and the toggle filter is now on and down here it says filtered. There are a lot of different ways you can tell that you've filtered the records.

Click here and that'll remove the filter. Or you can toggle it there. The filter actually stays set in the table, you just turn it on and off. Right now the filter is customers where the state is New York. I can turn that on and off at will.

Another way you could filter, drop this little box down here, and you'll see all these little checkboxes. You'll see a checkbox for each item that exists in that table, in that field. You'll see "Select All," which allows you to select all of them or deselect all of them. There's "Blanks" if you want to see just the blank records.Turn off all the records, then click on blanks, and you'll see just the blanks   the null values   people who don't have states. Drop it down, select all. Let's say you want to see just New Yorkers. Turn that off. Select all. Turn them off. If you just turn them all off and hit OK, it goes back to everybody. But turn that off. That allows you to deselect these boxes and pick New York. There you go. This is just New Yorkers.

If you want to see New York and Florida, check those both on. Now, you can pick as many or as few as you want. There you go.

Again, I've got lots of lessons on advanced filtering. Honestly, I don't really spend a ton of time on filtering and sorting at the table level because in the next lesson, we're going to learn about queries. Queries are a lot more powerful. Plus, queries are permanent; you can build a query, set the criteria, the parameters that you want. You could say, "I want customers from New York with credit limits of $1,000, sorted alphabetically by last name, first name." Save that as a query, and now you never have to repeat those steps again. It's saved for you. You'll see the data just the way you want. We're going to learn that in the next lesson.

I really only use these sorting and filtering tools as a quick way for me, the developer, to just come in here and quickly see who's from the zip code. Come over here, right click on the zip code, equals 90802. There you go. All done. Turn the filter off and get out of dodge.

Now, keep in mind when you filter something, all the records are still on the table. They haven't gone anywhere. The filter just displays the records that you want to see. You said, "I want to see just Smiths." There you go. It's saved. Don't worry. They haven't gone anywhere. Access doesn't eat them.

I mentioned a minute ago how you can see that you're filtered. Filter those. Notice down here you've got filtered. That says filtered. It'll say filtered over here in the status bar. Plus, this also now says one of five, right down here with these navigation buttons. These are the navigation buttons, by the way. We'll talk more about these when we get into forms. This moves to the next record. See that? So that's moving. Previous record. Last record. New record.

You don't usually use these when you're in datasheet view. You use these when you're in single form view. So each record, like Barbara Peters, will show up on the screen by herself and all of her fields. We'll learn about that when we get to forms in a couple of lessons.

Now you can apply multiple filters and sorts at the same time if you want to. Again, queries are better for this, but if you, the developer, are just coming in here and poking around, let's say I want to sort this by last name. So I'll put a sort on, right click, sort A to Z. Then I want to filter based on New York. Right click, equals New York. Further, I only want to see customers from Buffalo, New York. So I'll find city, right click, Buffalo. Now I see Buffalo, New York, last name, filtered by state, filtered by city, sorted by last name. So there's multiple filters and a sort.

Now how are we going to send this to the boss? The boss wants to see this list. You can print it if you want to go old school. You can send it as an email attachment. Or you can export it to a Word document if you want to make some changes to it.

Let me show you real quick how to do these. We'll have more lessons on all of this stuff very soon. To print it, just go up to File and then Print, just like the old days. Remember File, Print? Quick Print just sends it to whatever your default printer is. Mine's a little laser printer I've got sitting right here on my desk. If I just want to print it without wasting time with options, just hit Quick Print.

The Print will bring up the print banner, which lets you pick your printer, number of pages, number of copies, all this good stuff. I'll talk a lot more about printing in a future class.

File, Print. Print Preview brings up a preview of what it's going to look like when you print it. Now, on this screen, this is where you can also set things like margins and the page size and all that. But right over here is what I want you to see: the Email button. Click on that and it will open up your default email program. Usually, for most of you, if you're working with Microsoft's Office, it's Outlook. Again, this is pretty simple. I cover this in a future lesson also.

Here's the More button right here. Drop that down and you can export it to a Word document. You can send it to Excel. There's all kinds of stuff you can do with it. Right now, I'm just showing you quickly where this stuff is. We'll cover it in detail in future lessons. Here's the Print button here also when you're ready.

Take a peek at it. This is a little magnifying glass. You can zoom in and out so you can see what it's going to look like. If you have multiple pages, you can scroll up and down or use the navigation buttons.

But I don't actually want to print this, so I'm going to close the Print Preview and come back here to Table, Datasheet View.

Let's turn the filter off, toggle filter, and let's remove the sort. Everybody's back to normal now.

So one more time, and I can't emphasize this enough: if you're building a database that you plan on having other people work with that don't know Access, you want to keep them out of your tables. Sorting and filtering here, these are tools for you, the developer, to use. Your end users are going to stick with forms and reports and we'll make menus for them that they can use to navigate your database. They'll never be in your tables.

Now, one of the problems with sorting and filtering is that they're not permanent. They'll stay when you make a sort and you close the table and come back into it. For example, let's say I sort by last name. If I close the table and then come back into it, I'm still sorted by last name. But if someone else comes in here now and sorts by first name, close the table. Now, it might ask if you want to save changes to the design. I changed the order, I changed the sort, so say yes. If I come back in here, now it's sorted by first name. So the last thing that you do is what gets saved.

Now filters also get saved, but they don't reapply when you open. So if I sort this, or excuse me, if I filter this by New York and close it, even if I say save changes, yes, if I open it back up again, the filter is still saved and if I toggle the filter, it keeps the last one that you used. But it doesn't reapply it.

I just covered this in a TechHelp video called Filter On. It's the Filter On property. If you're curious about how this works, go watch that video. I'll put a link down below. But it's a more advanced technique.

The point I'm trying to make is you might come in here and do something like see just the customers from New York, from Buffalo, sorted by last name, and you want to save that so you don't have to keep coming in here and reapplying sorting and filtering. That's where queries come in. You may actually have multiple queries based on your customer table   another one for customers from California, another one where you have to type in the state each time you run it. Those are all possible with queries. You don't have to keep reinventing the wheel, putting in parameters, putting in filtering, putting in sorting. Queries allow you to save all of that information   the filters, the sorts. You can save that as a custom query. Then you or anybody else, really, who doesn't even know Access can open it up by just double-clicking on the query.

09. Customer Queries (24:56)

In lesson 9, we are going to learn how to build customer queries. We will see how to add tables to your query and add fields from the table to the query. We will see how to run the query. Then we will learn how to sort by multiple fields and add query criteria. In this lesson, we are going to learn how to work with queries.

Now we have the same mission that we had in the last lesson. The boss wants to see a list of all the customers from New York sorted by last name. More importantly, I want to create a query to do this so I can pull it up at a moment's notice at any time in the future without having to redo all the steps.

So, come up top and click on Create, and right here you will see a Queries section. There is a Query Wizard and Query Design. Now, the Query Wizard is okay for building some of the more advanced queries. But right now, we are just going to build a simple query, and I want to teach you how to do it from scratch. So click on Query Design.

You will see a blank query window opens up right here. This is Query 1. Over on the right-hand side, you will see Add Tables. This pane appears. If you do not see Add Tables over here, click on this little Add Tables button.

Now, a query can get its data from a table. Here we only have the CustomerT in our database. Other queries, which we do not have any. Or you can get your data from links. Now, links are linked tables. That is basically tables in other databases. We do not cover this for a while, so do not worry about links right now.

So let's go over here and click on Tables. The only table we have in our database is our CustomerT, so make sure that is selected and come down here and click on Add Selected Tables. You will see that brings the table over into the query right over here.

Later on, when we have more tables in our database and we build some other queries, we can actually make queries based on multiple tables and/or other queries. We will talk about that in a more advanced class when we get into the Expert series.

But now we are done adding tables, so I can close the Add Tables pane right there with that X. That gets rid of that.

Now notice how the ribbon has changed since I am building a query. It says Query Tools, Design, and there are all kinds of new buttons on here: Make Table, Append, Update, Crosstab, all this stuff. We will talk about this stuff in the future. But for today we are going to make a simple select query. This is where you select data from a table.

So the table or tables, in our case just one, that are part of the query will show up here in this big area. Down here in these columns will be the fields that we want to see.

Now I will tell you that this is not intuitive. This is one of the stranger things you are going to see in Access. This interface can be confusing, so it will take a little while to get to know how it works. But it is not that bad once you get used to it.

Up here in this table, you will see the different fields: First Name, Last Name, Company Name, and so on. We can make this bigger by simply grabbing and dragging the edge.

We add fields to the query by clicking on them and dragging them down to these columns down here. So let us say I want to see First Name and Last Name in this query. So click on First Name and then click and drag it and drop it right down here in that first column. See that? Now do the same thing with Last Name. Click and drag and drop it right there in that second column.

So now your query consists of the Customer table showing the First Name and the Last Name. It is that easy. That is all you have to do to see a list of first names and last names.

Now to see the results of your query, to see the actual data, come over here on the ribbon and click on the Run button. There you go. There are all the customers in our Customer table displayed in this query. You have a list exactly as you asked for it. There is First Name and Last Name, and that is it.

Now it is not sorted yet, and we are seeing all of the customers from all of the states. But that is exactly what we told Access to give us. We have not put a filter on, and we have not sorted it yet.

Now this list is going to be for phone numbers. So let us say I want to add Phone Number to this list. Let's go back to Design View. Click on the little Design icon over here. That will put us back in Design View. Let us find the Phone Number field right there. Click and drag and drop it in column three. Now run the query again. There we go. There is every First Name, Last Name, and Phone Number in our Customer table.

Back to Design View. Let us add Credit Limit to the query. Now, here is a trick. Instead of clicking and dragging, you can just double click. Watch this: click click, and there it goes. It will automatically add to the next open column in your query. It is a little faster than clicking and dragging.

Run the query again. There we go. There are all the credit limits. Now the boss sees this and says wait a minute, I do not want that. I do not want credit limits showing up in the query. Get rid of that. How do we delete something out of the query? Go back to Design View.

This is a little tricky. Take your mouse and move it right there over the top of that column. See, I have got that downward pointing black arrow. Click right there. That selects that column. Now press Delete on the keyboard. That is how you delete something out of the query. That is how you delete a field from the query.

So if I accidentally added Num and Employee, come over here. Find that spot. Not this spot. This allows you to drag that little barrier window up there. See that little side? That is not the spot you want to be on. You have to be right there. That little black arrow. If you click on it, you actually lose the black arrow. Come over here, you click, that selects that column.

You can actually select multiple columns by clicking and dragging like that. But we just want the one. So click right there. Now let it go. Now press Delete on the keyboard. That is how you select and delete a column out of your query.

Now, how do I sort this information? If you are in Datasheet View, you can apply sorts. If you go back to Datasheet View or if you run the query, it is the same thing, by the way. Going to Datasheet View or running the query, if it is a select query. We will talk about these different types of queries in future classes. But this is the same thing. It just switches over to Datasheet View.

You can perform these sorts up here like we learned about with tables. But these are not permanent, and they are easily changed by other users. What we want to do is apply a permanent sort, and in a few minutes, a permanent filter to the query itself in Design mode. So go back to Design mode.

Notice down here there is a row that says Sort. Right there, Sort. So click inside the Sort box under First Name, and you will see a little drop-down box appears. Click on that. We have got Ascending, Descending, Not Sorted. Pick Ascending and now run the query. Look at that. It is sorted now by First Name.

Go back to Design View. Now let us add a second sort by Last Name. Drop this down and pick Ascending and now run it. That is not quite what I want. Notice I have got two Joes in here and they are now sorted First Name, then Last Name. But usually when you do a sort, you want it Last Name, then First Name.

So how do we do that? Well, in order to do that, we have to rearrange these columns. Access queries will sort left to right, so whatever you want sorted first has to be on the left side.

How do we do that? We just learned how to select the column. Move right there. Get that black arrow. Click. Let it go. Take your hand off the mouse. Now notice I have got a white pointer arrow right there. Now click and drag that line and put it right there. That is how you move a column.

Let us practice. Let us move Phone out in front. Click. Let it go. Now I can move my mouse freely. Now grab that same spot. Now click and drag to the left. See that. But I do not want Phone out there. I am going to put Phone back where it was. This is practice. Click right there. Let it go. Click and drag to the right. See the line moving around? That is where it is going to drop. There is where you want to drop it.

So that is how you move these things around. Now if I run it, now it is sorted by Last Name and then First Name. We have two Smiths there: Joe and Peter, and they are now sorted Last Name, First Name. I am surprised the first one I got in here is a J. That is like half the alphabet missing.

Back to Design View. Now it is time to save this query. So I am going to hit Control S on my keyboard for Save. Control S as in Save. Query name: let us call this CustomerQ, my customer query, and then hit OK or press Enter. Now notice it is over here in my navigation pane. There is CustomerQ. Now I can close this query.

Go about my business. Do what I want to do. Go have lunch. Go watch some more of my videos. Then when you come back later and you open this guy up by double clicking on it, there is your query exactly as you saved it. You have got the fields you want. You have got the sort order that was in there. That is the benefit of queries.

You can make different queries to display data in different ways. This way, you do not have to keep reinventing the wheel. Unlike in Excel, where you have to resort this and change that and move this around, in Access you can have multiple different queries to view your data in different ways.

More importantly, you can set these up for people that do not know Access. If they want to see a specific set of data, like we are going to see in a second how to do a list of customers from New York with, let us say, a credit limit under $1,000, you can set up a special query for people. All they have to do is open up the query, and there is the data that they need. They do not have to know how to use Access. They do not have to know how to build queries. You build it for them and just give them a link to it.

Later on in future classes, we will build full menu systems where you just have them click on a button and there is their query.

But we are not done yet. The boss said that he only wants to see customers from New York. So let us go back to Design View.

Now, to filter based on a field, you have to have that field in the query. So find State, double click on it. Now it is over here. Run the query now and you will see there are all the states.

Back to Design View, which, by the way, you can also get to by right clicking here on the title bar of the query. You can switch between Design View, SQL View, and Datasheet View. There is Datasheet View. SQL View is a little more advanced. I cover this in my more advanced classes. That is basically a programming language to write queries. It is very, very powerful, and we will cover that in future lessons.

So now we have the State in our query. There it is. We know about the Sort row. We will talk about Show in a minute. Find this guy here. It says Criteria.

Come into the Criteria row underneath State, and here is where you are going to put your filter or the criteria for this particular field. So I am going to type in New York. NY, and then press Enter or Tab. Now notice that Access put quotes around it. I will zoom in there so you can see it.Notice it put quotes around it. In Access, whenever we are dealing with text values, that's called a text string. A string of characters. We have to make sure we put them inside of quotes.

Now usually Access realizes that's a text string and puts the quote there for us. But keep that in mind. You should not always rely on Access to do that. I just showed you as an example, but you should type in quote New York quote every time you put in your criteria there, if it's a text string. Sometimes you will have numbers. Sometimes you will have currency values or dates. Those do not get text strings.

We will see that as we go along.

So now that we have our criteria there under state, let's run the query again and look at that. Now I am only seeing customers from New York.

So I have got it sorted and I have got it properly filtered with criteria, and that is now a permanent part of this query.

Notice there is no filter on here. It says no filter. That is in the design of the query and the SQL underneath it.

Now I want to save this query so I can pull it up again at any time in the future. But do not just hit Control S or click on the little floppy disk icon over there. Otherwise, you will overwrite the original customer query. I do not know how many times I have done that. I have done it a million times, especially with Word or Excel.

I have got a Word template, like a document that I use, and I do not have it set as an actual template, so it is read-only. I will open it up and start making changes, and I will hit Control S, save. I just overwrote my old document.

It happens all the time. So what I am going to do in this case is go to File and Save As.

Now you can save the whole database; that is not what I want. I want to save this object. Save Object As. Hit the Save As button.

This will allow you to save Customer Query 2, right, copy of Customer Query, or whatever you want to call it. I am going to call it Customer New York Query.

Remember, singular. I try to keep all my objects, table names, query names, field names, singular if possible. Someone actually called me out that notes is technically plural. I know. But I have been doing notes forever, so notes is my exception.

Hit OK.

Now this query has been saved as Customer New York Query. Now if I close this and open up the other one, there is the original query. And I can open up the Customer New York Query now too. There are both of them. They overlap each other like that. Move them around if you want to. You know how to use windows. If not, go take my Windows class. We are not going to cover Windows stuff here. You can even open up the table if you want to get them all open up here.

Close that, close that, close that.

Let's say, in addition to New York, we also do business in Pennsylvania. So I want a similar query, but I want customers from Pennsylvania. Now I know ahead of time I am going to be making changes to this query, and I do not want to have to open it up and then go File, Save As, Save Object. Let's just copy this ahead of time. Right click on it. Go Control C, Control V. Copy, paste, my keyboard shortcuts, Control C, Control V.

Change the name. We will call this one Customer PA like that. Then hit OK.

Now let's open Customer PA up. It still says New York. Why is that? I have not actually changed the query yet, have I? Go to design view. Right click, design view. Change our criteria to PA. Save it, Control S.

Save on the keyboard.

Now let's run it. And we have it. Nobody in there. I do not have any customers from Pennsylvania. Let's open up the customer table and make sure. There are my states. No, no, no, no, no, no, Pennsylvania.

Now let's go in and cheat. Let's change a couple customers and make them from Pennsylvania, just for class.

So go back into customer table. Let's change this one here. Make them from Pennsylvania. And let's change this one here. Pennsylvania.

Close the customer table.

What is going on? Why did my query not update? It is important to note that a query will not update its records unless you either close it and reopen it or click the Refresh All button. If you hit the Refresh All button, it will reload its results, or you can close it and then reopen it again.

It will not update itself if it is sitting there open.

This usually is not a problem if you are the only person using the database. But sometimes if you have a multi-user database, and we are going to get into that in future classes, you have multiple people working on the same set of records at the same time. Someone might change something, and it does not appear to change right away on your screen because you have to refresh the data, either close it and reopen it or hit the refresh button.

If someone says they just changed a record a few minutes ago and you are not seeing the data, just close it and reopen it or hit the refresh button.

We will talk a lot more about this when we get into multi-user databases, and that is coming up a lot later in the Expert classes.

Another thing to remember is that queries themselves do not have any data in them. This is just a way to look at data stored in the tables.

Let me repeat that. All of the data in your database is stored in your tables.

This is just three different ways to look at this set of data. The queries themselves have no data in them. The query is just showing you what is stored in the table; it is not a copy of the data. It is just a live view of the data in the table.

On the other hand, keep in mind that this is a live view of data in the table, and if you change it in the query, it will also change in the table.

So, for example, I come in here and I change these people to Florida. Notice I am able to edit them here. Now if I hit the Refresh All button or close this and reopen it, look what happens. They go away. Why? Because that is live data. I just changed those people to Florida. There is one of them. I remember that one from Jamestown. Jamestown. This one up here too. These were the Pennsylvania ones.

Now they are from Florida. I will put this one back to Pennsylvania, and Jamestown should be Texas.

Close that. Now open up Pennsylvania again. There is your one person from Pennsylvania.

So keep those two things in mind.

One, queries themselves have no data in them. They are just showing you data that is stored in the tables.

Two, any changes you make while you are viewing a query get saved in the table.

Now, can you make queries read only so that people do not have the ability to change the data? Yes, you can. Personally, I would prefer giving your co-workers forms or reports to work with instead of having them work directly in your queries. We will see how forms and reports work in just a few minutes.

Now we have two queries from two different states. What if you wanted to have a query for each state? Would you have to make 50 separate queries? The answer is no. There is something called a parameter query, where you can make it so the user types in the state when the query is run.

That is a little more advanced. I cover parameter queries in Access Beginner Level 5. I do also have a tip video on that. I will put a link down below in the links section if you want to learn how to do that.

It is a little more advanced, but it is not too bad.

Today, we are just learning the basics of query design. We have to walk before we run.

My students ask this every time I teach this class, so I just want to let you know that, yes, it is possible. If you really want to learn how to do it now, go check out the links down below. You want to find the parameter query video.

Another popular question I get asked all the time is, what is this little asterisk up here, this little star? The star is so that you can add all of the fields to the query without having to bring them all in individually.

If you double click on that star, for example, you will see it says CustomerT.*. That means all of the fields. Now we have to have first name and last name in here individually because we want to put sorting on them, or if you have the state field in here today, you have to add that in. But if you just want to see all the rest of the fields, just bring down the star.

When you run it, you will see all the rest of the fields. That is really handy if you want to have them all on the query and you have lots and lots of fields like we have up here.

Another thing I get asked is what are these Show boxes for. If you do not want to see a particular field, even though you have to have it in the query, you can hide it.

For example, let's get rid of the CustomerT.*. Earlier, I accidentally brought in the credit limit. If I run this, you can see everybody and their credit limits.

Let's say the boss wants to see a phone list of everybody who has a credit limit of less than $1,000. Maybe you are going to be calling them to see if they want to upgrade their credit limit or whatever. In the Criteria row down here, I can put a criteria in of less than 1,000.

We are going to do a lot more with all these different types of criteria in future classes, but I am just giving you a sneak preview.

If I run this now, you will see everybody in this list now is under 1,000.

You might not want your employees seeing what their actual credit limit is, so you can come in here and hide that column. Now when you run this, you know this is a list of everybody that has less than $1,000 credit limit. But you do not know what the actual credit limit is.

It's a little more discreet.

That is what the Show box is for.

There are tons of things you can do with queries. I literally just scratched the surface today. You can make queries with multiple tables. You can make queries that prompt the user for information, like I mentioned before with parameters. You can make queries that edit records in a table. Make Table queries will create whole new tables based on sets of data. Append queries can add records onto a table, taking them from one table to another. Update queries change information. Crosstab queries look like little spreadsheets. Delete queries delete information.

There are all kinds of things you can do with queries.

If I put together all of my query lessons from all my different courses, I probably have hundreds of hours of stuff on just queries. So this is literally just to give you a taste of what queries can do. This is the basics of a Select Query.

A lot of books are written in more of a reference format, where they teach you everything there is to know about tables first and then they teach you everything there is to know about queries next.

I do not do that. I do not go depth-first. I go breadth-first with my teaching. I will teach you a little bit of this, then a little bit of that.Like a little bit of tables, a little bit of queries, a little bit of forms. Then in the next class, we'll go over a little bit more with tables and a little bit more with queries. I think you learn better that way.

Tutorials aren't really designed to be reference material. I want you to watch this and apply a little bit of this lesson, then a little bit of that lesson. It will stick in your brain a little better.

Some people learn better with books. If you like book learning, then great. Go buy a book. There are lots of books I recommend. The Access Bible series is really good. I like those.

But lots of people always ask me, why didn't you cover this and this and this about queries? Because I'm only giving you a little bit. We're already over 20 minutes for this lesson. So now we're going to move on to something else.

We're going to do forms in the next lesson. I'm going to teach you a little bit about forms. If you want to learn more about queries, I have tons of query lessons on my website. Go to my site, search for queries, and you'll find all kinds of stuff.

I give you a little bit at a time so you can digest it. Now I'm going to give you some form stuff, then some report stuff.

So in the next lesson, we're going to learn how to build a form to make a nice user-friendly interface for our end users.

10. Customer Form 1 (18:49)

In lesson 10, we're going to build a customer form to present our user with a nice, friendly interface for adding and editing records. We'll see how the Quick Form Builder works, we'll learn about navigation, layout view, deleting records, and the different types of forms in Microsoft Access.

So far today, we've seen how to build a table, enter and edit data in a table, and we've created a couple of queries. Now, tables and queries are, of course, functional and they're necessary for most databases, but they're not very friendly to work with, especially if you don't know Access. In this lesson, we're going to see how to set up a couple of different types of forms.

Forms are used to work with data on the screen. Forms look a lot better than working directly with tables or queries. That makes it easier for you to enter, edit, and find information. A database that's easier to use is more efficient.

Here's an example of a simple customer form. You can see all the information laid out, one customer per screen. This is a lot easier on the eyes, a lot more user-friendly than simply dropping the user right into a table and saying, "Here, go to town," just like if you give them an Excel spreadsheet. This is a nice, user-friendly interface.

Forms also provide you with additional control and security over your database. You can control which fields your end users can see and what data they can edit. If you don't want them seeing the credit limit, for example, don't put it on that form. You can keep it in the table and make a separate form for you or your managers, but your average end user doesn't have to see all of the information for every customer.

You can control whether users can add data, edit data, delete records, that kind of stuff, all using forms. In later classes, we'll learn how to make buttons so, from the customer form, you can open up their contacts form or their orders form. You can do all kinds of neat stuff with forms.

You can set up forms to look like existing paper forms that you might have in your current system. If your users are used to entering data in a specific way on a specific paper form, you can make your on-screen form look just like it. This way they know what data goes in what fields. It makes it easier for your users to transition from an old paper system or even old software into a new Access database.

You can design the forms to look however you want. You can have data from multiple tables displayed together on one form. For example, you could show a customer and all of his contact history in something called a subform. It's a form that displays inside of another form. Or you could have all of their order information show up in a subform. Or, like I mentioned before, you can have different buttons to open up forms related to the form that you're on. For example, here's a customer record. I can click on the order button and that opens up the orders for that customer.

We get to subforms in the expert classes, Access Expert 3, and they're really powerful. In today's class, I'm going to show you how to set up a simple customer form just to get you comfortable with form design. Over the next couple of classes, we'll be spending a lot of time on form development.

Keep in mind that building forms is really more of an art than a science. You can spend many, many hours making your forms look good, and I know I'm pretty guilty of that. I've got whole lessons where I spend time just showing you how to make custom form elements, custom backgrounds. You can make your database look like Windows 95, you can make it look futuristic. All kinds of crazy stuff. I'll put some links down below in the link section you can click on.

Good form design is time-consuming, but it can really make your database look professional. It just takes practice, and it's something that the more you do it, the better you'll get.

Now, there are two primary ways to build a form. There's a couple of different side ways too, but the two big ways are layout view and design view. I use design view. Design view is a little more advanced, but once you get the hang of it, you get a lot more control over your forms. Layout view is a simple way to build fast forms, but you get fewer options up front.

I like design view myself, and I'm going to stick with design view throughout most of this course. However, I know a lot of you who are watching this video aren't necessarily looking to be Access developers. You just want to get in there and start working with Access and build some quick forms.

So, I'm going to show you layout view first, then I'm going to show you design view in the next lesson. So if you're just curious about how to get in there and build a couple of quick forms, you're not looking to be an Access developer, then this lesson is for you. The rest of you, if you really want to learn Access and get your hands dirty and become a developer, you're going to want to learn design view, and that's going to be in the next video.

So here we are back in our database that we started earlier, and you can see my navigation pane over here. Click on the customer table. Now on the Create tab, right over here, you'll see a forms section. Now, these are all the different ways you can build a form.

There's Basic Form, which we're going to start with. That's for layout view. Then here's Form Design. That's the more advanced one that I'm going to show you in the next lesson. There are some other options that I almost never use: Blank Form, Form Wizard, all this different stuff in here. I will talk about these things in future lessons, but these are the big two right here.

Click on Customer T, then come up here and click on Form, the first one. Now, Access just goes ahead and builds a form for you. It doesn't give you any questions, any options, just boom, there's your form. It bases it on whatever table or query you had selected over here.

I personally don't like layout view, I think this is messy. It's designed for novice users to get in here and just quickly build a form. I understand why Microsoft put this together. A lot of people are familiar with Excel, and they're trying to treat this almost like a spreadsheet. You can resize these objects as rows here, like click on that, and then resize it. You can resize these as columns. See that? Click here, resize that column, resize that row. But I think this overall look is just a little messy. That's just me. It takes a lot of time to make this look good.

Now here in your form, you'll see there are two components to each field. There is a label and a text box. The label is the name of that field: first name, last name, company name, and so on. Those are labels. The data goes in something called a text box.

Some of these fields, you'll see, have check boxes. Yes or no fields will have check boxes associated with them. You just click them on and off.

Now I'm in layout view. I can see the data in here, but I can't actually edit it yet. I'll show you how to do that in just a second. If you want to change any of these labels, you can. Just click on it a second time, and you can make that say like "first name." Because these are labels, this is what the user is actually going to see on the screen. You're not changing the name of it. Remember, the actual name in the table, the name of the field, I want to be "first name" with no spaces there. But you can come in here and make these say whatever you want. These are just labels. These are just for display purposes. You can just put "company" in here, for example, if you want to. That's not going to change anything really.

You can resize these rows like that. Again, I'm not very impressed with layout view. I don't use it myself, but it's a quick option to come in here and build a form.

Up top here in the form header, you've got a bit of a description. You can change that. That's also just a label, for example, "Customers." A little picture over here, if you want that, you can delete it if you don't want it. Click on it and hit delete. Just a little image.

When you're happy with the way this looks, you can save it. Ctrl+S on your keyboard, or you can hit the little floppy disk in your toolbar. Save. I'm going to call this "customer F," my customer form. Hit OK.

Now you'll see it appears over here in the navigation pane. If you're using an older version of Access, I know in Access 2013 there was a bug when you saved an object that didn't appear right over here automatically in the navigation pane. You had to refresh it with F5. So hit the F5 key on your keyboard if you don't see your objects appear. That's an old bug. I just remembered it now because the last time I recorded this class in 2013, I mentioned it in that video. So if you're using 2013, by the way, upgrade. Get Microsoft 365, get the latest version of Access. Trust me, lots and lots of bug fixes that they've since gotten rid of.

Now that I've saved my form, I'm going to close it and then I'm going to reopen it again. Now when you open it this time, you're in form view. We're no longer in layout view, and you can tell because you got a blinking cursor right here. Customer 4, I can hit tab. I can start typing in data. I can make edits, "Forestry Services Inc.," for example.

Down here, remember, these are navigation buttons. Just like in tables and queries, we can move to the next record, previous record, first record, last record, and new record if you want to add a new record. Start putting in the next person here, and they get assigned the next auto number. You don't need to worry about what that number is.

This is what your end user is going to work with, this form. They're not going to play with your tables. They're not going to play with your queries.

If you want to make changes to the layout of the form, right up here under Views, drop this down. There's form view, layout view, and design view. Now, we're going to talk about design view in the next lesson. That's how I prefer to work with forms. For now, if you want to change the layout, just go back to layout view. Remember that? You can see you got these yellow bars around these guys right here. You can move things around. You can move credit limit down here, for example. There's all kinds of stuff you can do. Click and drag things. But again, this is not my preferred way to work with Access forms.

Done changing the layout? Come back over here. View, form view. You go back to form view.

I personally like to close a form, save the changes, and then reopen it. That's a good habit to get into, especially if you're planning on becoming a developer. Take it from me. Once you start putting Visual Basic programming code behind these forms, you want to make sure you close them and reopen them so any changes take effect. You don't want to flip between views.

Now as a reminder, just like queries, the data that you see here is live. This information is linked to the table, so if you delete records out of here or make changes to records, it affects the data in the table. It's not a copy. It's a live view of that table's information. It's just displaying it in a nice, pretty way.

When you are adding a record or editing an existing record, remember you'll see the pencil over here that indicates the record is dirty.That means it has not been saved to the table yet. How do you save it? You simply move off the record or close the form. That will save the record to the table underneath.

Remember, if you come in here and start editing it, now it is dirty. As soon as you close the form or move off that record, it saves the information to the table.

If you do not want this record, how do you delete it? Come over here and click on this big gray bar on the left hand side. That is called the record selector. Click on that and then hit delete on the keyboard. Access will say you are about to delete one record. You cannot undo this operation. Once you say OK, you are done. That record is gone. Be careful.

If you want to delete that, confirm your choice and move on. If you want to delete multiple records, I recommend going right to the table. But be careful. That is dangerous. Make sure you have good backups. Back up your data. Back up your data regularly, especially if it is important.

I have whole lessons on backing up your data. I have some links down in the link section for you. Go watch those.

When you are working with your forms, you will also find a familiar set of buttons up top here. Sort ascending, descending, the filter buttons. We learned about a lot of these working with tables. You can come over here, for example, and find a state, right click on it. Equals Ohio, for example. Now you will see just the Ohio records. We learned about sorting and filtering earlier. Let me unfilter that now.

If you want to sort these records by last name, click on the last name field and then hit sort ascending or descending or however you want them to sort.

Now, like I said, I do not particularly like building forms this way. I want to show you the quick way for you non-developers who want to get in here and just quickly work with Access. But for the rest of us, I am going to delete the customer form. Click on customer F, hit delete on the keyboard. Are you sure you want to permanently delete customer F? Confirm your choice, and now it is gone.

In the next lesson, we are going to make a customer form with design view. I want to really quickly show you the other form options that are here and just explain what they do. So click on customer T.

Blank form just gives you a blank form. No controls. That is it. That is all you got. Blank form. It is not that useful as it is. Get rid of that. Close that.

Click. What is the next thing over here? There is the form wizard. The form wizard is not too bad. It will ask you what fields you want on your form. Customer ID. Click on that one, bring it over there. First name, last name. Let's say I do not care about the address. I just want their email and their discount rate. Because sometimes you can make specialized forms.

Like I mentioned earlier, you might have a form for your front end people to use and a different one for the managers with all of the fields on it. Hit next. How do you want it to look? There are different options in here for how you want it to appear. Let's go with column. Now that is the default right there. Column, now how we pronounce it. Next.

Open the form to view or enter information or modify the form's design. We have not gotten into form design yet, so let's pick the first one and then hit finish. Then it builds the form for you, very similar to what we did before. It puts you in layout mode like this. Quick and dirty way to build a form. But again, I do not want this. Let's just close it and delete it. Are you sure? Confirm your choice.

Again, just a quick tour of the different types of forms. Customer T. Create. What else have we got here? Navigation forms. Now, there are navigation forms that let you make forms to open other forms. I am going to show you how to use these in Access Beginner 7. I spend about 10 to 15 minutes showing you how they work.

Personally, I do not like them. I like to build my own menu forms using my own buttons. Here is an example of my TechHelp free template. For example, I like to build my own menus like this. Customer list. See this? Customer form.

I do not like to build in navigation forms. I am going to spend a little time showing them to you, but I do not personally use them.

What else have we got in here? More forms. There is this thing called a multiple items form. That looks like this. Let me close this field list. We will talk about the field list in the next lesson. This is the multiple items form. Now it comes in pretty big. I am going to just resize one of the rows like that.

Another name for this is a continuous form. Here, this is called a continuous form in Access speak. Now, I use continuous forms all the time, but I do not build them this way. Again, I do not really use the wizards that come in Access. I like to build things by hand. Here is what my continuous form looks like. I think you will agree this looks a lot nicer.

So again, I do not want that. Close it. Save changes? No.

What else have we got? Create, more forms. We have a split form. Split forms are okay. A split form gives you the layout of one on top and then a spreadsheet view down here in the bottom that looks like a table. You can move through the records this way. Again, I do not particularly care for this. I do not use it myself.

I like taking my continuous form and making it so you can double-click on a customer and it opens up the customer form. That is how I like to build them. I cover how to do all of this in the videos that come with the TechHelp free template. I will put links down below if you want to go watch that now. But I will cover this throughout the entire course, throughout the series of these classes. You will learn it. I just go over it faster in the template video.

So again, split forms, not my favorite. I do not usually use those either.

The last option in here, modal dialog, is for making little pop-up windows, usually to give some kind of warning or some kind of information or to get a few bits of data from the user and do something else with it. Again, I do not use these. I will show you how to make modal dialog boxes, but we are not going to use this more forms option.

So that is a quick tour of all the different kinds of form building buttons that are in here, all the different ways you can build a form. Except the one way that I personally prefer, and that is by using form design. That is what we are going to cover in the next video.

Form design lets you get in here and actually position all of these elements exactly where you want them, resize them, move things around, and so on. We will talk about that next.

11. Customer Form 2 (10:06)

In lesson 11, we are going to learn the right way to build a form using Form Design View. This is a preview of a lesson that I normally used to cover in level 2.

We will learn about creating a blank new form, setting the record source, adding existing fields from the table to the form, resizing objects, your labels and your text boxes, and changing colors.

This lesson is new for beginner level 1. I used to cover this material in beginner level 2, but I said to myself, I do not often use layout view. I almost never use it. So I am going to teach these people from the beginning the right way to build a form.

I want to show some people who are not planning on doing much with Access just how to get in there and build a quick form. That is what layout view is for. That is what we covered in the last lesson. But today, in this lesson, I am going to show you how to use design view to build forms properly.

I have been working with Microsoft Access since version 2.0 came out in 1994. This was the only method available at the time, and this, I think, is the best method for building forms. It is a little more complicated, but stick with it. I am going to repeat this a couple more times.

I am still going to spend a lot of time in level 2 going over form design, because designing forms is very important for your databases. So consider this a sneak preview, a short version of what I am going to cover more of in level 2.

Let's build a form the right way, our customer form. Create form design. When you do this, you get a blank form. The first thing we have to do is tell the form where you are getting your data from, because a form can get its data from a table, and we may have multiple tables in here, or a query, and again, we can have multiple queries in here.

Open up the form's properties. Now, where these two ruler bars meet, right in this corner, there is a little block. See that little block? Double-click on that. That will open up this property sheet. I am going to resize it just a little bit. It does not have to be that big.

Now on the Alt-AB, the top property is called Record Source. Select the Record Source property to the customer T. That is saying this form is going to get its data from the customer table. That is all you have to do in the property sheet. Close the property sheet now.

Now we are going to add fields to our form. On the Design tab, over in the Tools section, find Add Existing Fields. Click on that and it opens up this thing called a field list.

Since the form knows it is getting its data from the customer table, you see the fields that exist in that customer table. All you have to do is click and drag these fields off the field list onto your form. Watch this. Click, drag, and drop, and that is it.

When you drop the field, you get a text box and a label. Remember that from the last lesson. For the next field, first name, click, drag, line it up with the text box underneath, and drop it there.

If these do not drop exactly where you want them, you can click and drag to move them around. Make sure you leave room for your label. If you go too far, it will squish your label. You do not want to put it right up against the edge.

You can drag either one, the label or the text box. Get them right up next to each other just like that. You resize these by clicking on them. Then you see the little border around them. If you move to the corner or the edges, you see the little double arrows. You can click and drag and resize the labels, or you can click and drag and resize the text boxes.

The customer ID does not have to be that big so I can make that smaller. First name can be a little bit bigger. Ready to get last name? Click, drag, drop. Usually I like to drop it, then move it over here, get it in position, and resize it like that.

You can change your labels like we did in the last class. Click and just hit the space bar - last name, customer, space, ID - that kind of stuff. These are just labels, they're just for display.

You can move them all individually if you want. But if you want to move a block of them, let's say you want address, city, state, zip, and country altogether, click on the first one. Hold down the shift key and click on the last one. That selects that whole block.

Now click and drag that whole block and drop it there. See that? I realize I forgot company name, so click, drag, and drop company name. You may decide you do not want company name. Press delete on the keyboard to remove it, just like that.

Slide address over here. Address can be bigger. You may decide you want city, state, and zip on the same line. Slide city over there like that. City can be about yay big. Now I want state next to it, so I am going to delete the label for state. Click on the label, hit delete. If you delete a text box, the label is also deleted. But you can delete just the label and it leaves the text box behind. Now I can move state up here like that, resize it, and then zip next to it. You can make these exactly as big or as small as you want. Country can go underneath that like so.

Resize country. Now these are just slightly off. These go out to about there. These do not. I am going to select all of these. Click and drag a box that just touches those. Now I have address, zip, and country selected. I can now resize all of them together. Watch that right there.

There are all kinds of layout tricks I am going to show you over the next couple of classes. For city, come in here, click, then go comma, state, comma, zip. Just like that. City, state, zip in the label.

Let's save this form right now. Control S, save. Let's call this customer F. There it is. I am going to close it and then reopen it.

I have all this empty space in here. Let's get rid of that and make it just this big. How do I do that? Right-click on the title bar. The toolbar is the guy up top here - the quick access toolbar. Go to design view. Now come out here where the edge is. You see that edge, click and drag. That is the actual width of the form. That is what this ruler dictates up top. I am going to scroll down, find the bottom of it, and slide that up. Slide it up just like that. Make it fit exactly so it fits these fields.

Now save it, close it, and then reopen it. You can have just the information on here that you want. You do not have to have all the information. In level two, we will build a nice big, pretty customer form, just like the one I showed you before. This guy here, very similar. Again, this is just a quick preview.

Back to design view. Right-click, design view. Let's add a little bit of color. Click on the background of the form. Come up top to format. Here you can change the background color of the form. Let's go light purple like that. There you go. Or maybe light blue. Light blue looks better like that.

I like to stick to the standard colors. We are going to talk about theme colors and standard colors in a future class, but I like to stick to the standard colors because the theme colors can change if the user changes the theme. That is a pro. It can also be a con. It can make your database look different if someone changes your theme.

You can also change the label colors. Now I am going to select all of them. I am going to come right up here in the rule bar, click and drag. That is going to select all of them. Now I am going to change the foreground color to black so they are easier to read.

One more thing I like to do - since the customer ID is an auto number and it cannot be changed, I like to make that have a gray background like that. That just visually signals the user that they cannot change that.

Save it. Close it. Open it back up again. There we go. It is already starting to look really cool.

This is just a sneak preview. We are going to spend a lot of time in level two going over form design. Most of level two is form design. I am going to show you lots more tricks to make this look professional.

Over the past, I do not know how many years, I have been saying to myself that in level one I really should show them at least the basics of how to use form design, because I do not really use layout view myself at all. There are a couple of tricks that I do with layout view, but that is it. Ninety-nine percent of the time when I am working with forms, I am in design view. So I figured I have to show the beginners that.

We are going to do more with this in level two and we are going to be building lots of different kinds of forms - contact form, order form, order details form, products forms, all kinds of different forms over the course of the series.

I hope you learned something there.

12. Customer Reports (25:42)

In lesson 12, we're going to learn how to make a couple of different customer reports. We're going to build a simple customer report with one customer at a time. Then we'll make a customer list report where we can see all of our customers listed one per row. Next, we'll make some mailing labels, and we'll only send mailing labels to active customers. We'll also learn about different things like paper size, margins, printing, and so on.

So far, we've learned how to build tables, queries, and forms. Now, reports are very similar in design to forms. However, reports are designed to be presented to someone who's not using your database. You can print them out, you can send them as email attachments, you can do lots of stuff with reports. Forms are used for entering and editing data in the database. Reports are used for taking that information and displaying it for someone else.

Creating a report is very similar to creating a form. First, we pick the table or query that has the data in it. Then we click Create. Over here, you'll see the Reports section. Table, Queries, Forms, Reports. Now, just like forms have lots of different ways you can build forms, so do reports. There are lots of different things you can do to build reports.

Here we have the simple report designer. This is where Access pretty much just takes the table or query that you're pointing at and just makes a report for you. You get some options, but basically Access does it for you. Then you have Report Design, which gives you a blank report and lets you start from scratch. This is actually my preferred way to build reports. We'll cover that in future classes. We're just going to do some simple reports today.

Blank Report sets up the report for you and then lets you go ahead and move the fields around in layout mode. There's the Report Wizard. I actually like the Report Wizard. I like a lot of wizards. Some of them are good wizards, some of them are bad wizards. The Report Wizard is a good wizard. I like the Report Wizard. I'm going to show you the Report Wizard in a few minutes. Then we have labels for making mailing labels and such. We're going to run through this too.

Let's start with just a simple report. Again, click on Customer T and then click on this first one here that's just Report. Now, Access throws together a basic report for you. I'm going to maximize this guy so it takes up the full Access window. Now, this looks very similar to that multiple items form that we looked at earlier, the Continuous Form, it's called.

If you scroll down, you'll see all your different customers down here. You can scroll to the right. This is a multi-page report. You'll see there are a couple of pages here. Up top, you'll see the title. You can double-click there and change it if you want. PC, Re-sale, Customer, List. Press Enter. Over here, you'll see the date and time. Normally for forms, I don't bother putting date and time on there. But for reports, you might want to know when this was generated. This is printed on the date. If you don't want the time there, click there and press Delete. It goes away.

Now, just like forms, Reports also have different views. You'll see the views are over here. If you drop this down, right now we're in Report View. Print Preview is the one you're going to want to pick the most. Print Preview will actually show you what this is going to look like when you print it. That will give you the best view of that report. Down here, you can use the navigation buttons to move between the different pages.

Now, this isn't a very good report. It's not very well-formatted. Like I said, Access just throws together a real simple report for you. But yeah, that's not the best. I'll show you how to build a better one in a minute. When you're done with Print Preview, click the Close Print Preview button over here. That'll put you back into Report View.

There's also Layout View and Design View. We'll talk more about Design View in future classes. This is where you can get in here and actually edit the fields and the labels and all that stuff. But remember, this is beginner level one. We still have our training wheels on. I can't show you everything in one class. It's coming, don't worry.

Come back up top. Click on Design. For some reason, when you're going to Design View, it puts you into Page Setup mode. Don't worry about that just yet. Come back over here and go to Report View. Report View is the best way to view the data in the report on the screen. Layout View lets you come in here and resize these columns, or resize the rows.

Now, I don't particularly care for this simple report that Access put together. So I'm going to hit the Close button up here. That'll close this report. Do I want to save changes to this? No, that's okay. I didn't like it anyway. Let's make the same kind of report, but I don't need all of those fields. I just want a simple customer list.

This time, let's use the Report Wizard. The Report Wizard lets you pick what fields you want, what layout you want, and all that stuff. So, based on the customer T, now if you drop this down, you can see you can pick any table or query. This is one of the reasons why, back when I started using Microsoft Access back in like 1994, it didn't say table or query next to it over here on the left. So you didn't know if this was just customers. You might have a table called customers and a query called customers. You didn't know which one was which. That's why I personally started using that TQF naming system. It just stuck with me. It works. Plus, later on when you get into macros and modules and you do some VBA programming, it's handy to know if you're working with a table or a query based on the name of it.

So I'm going to pick the customer T here. Now, what do I actually want to see on the report? Let's do the customer ID, first name, last name, company name, and phone number. Click on the field over here, then click on this little button that brings it over there. If you don't want it, you click on the button to send it back. That's for all of the fields, and that's to bring back all of the fields. Or you can double click on something on this side. But that's all I want to see on this particular report.

Hit Next. Now, it's going to ask if you want any grouping levels. You can do something like group the customer list based on state, so all your New Yorkers show up first and all your Pennsylvania people show up. We're going to talk about grouping levels and sorting levels and all that stuff in future classes. Training wheels, training wheels. We'll get there.

Next. You can add a sorting level if you want to. What do you want to sort by? Let's sort by last name and then by first name ascending. Now, if you have a sort in a query, if you base this report on a query, this sort will override it. But if you're based just on a table, this is your only sort.

Next. What kind of report do you want? Tabular is the one we just had a minute ago that Access threw together for us. That's like a continuous form, a multiple items form. You get every record one per row. Columnar, or columnar, however you pronounce it, looks like our single form. We have the labels and the text boxes, and you'd have one customer per page, for example. There's Justify, which kind of looks like an invoice. For this, I'm going to pick Tabular, and I'm going to go with Landscape so it goes this way instead of that way.

Adjust the field width so all the fields fit on a page. Yeah, that's kind of nice. Remember, the other one was kind of spilling over into three pages. There's a limit. Obviously, you can't put 700 fields on one report and expect it all to fit.

Next. What title do you want? Let's call this the customer list R. It's my list of customers. Do you want to preview the report or modify the report's design? Let's just preview it. I'll hit Finish. There it goes. See? Now that's a good wizard. I like that wizard. It put together a pretty nice looking report.

Notice my mouse is now a magnifying glass. I can click to zoom in. There's your first name and last name. Customer ID kind of came in there weird. I don't know why it's in that order. Company name, phone number. Down here on the bottom left, you'll see the date. Over on the right, you've got the page. You can also use this guy here to zoom in and out if you want to. You have multiple pages or whatever.

Let's get this customer ID out of here and move it over here to the left in front of the last name. Now, I'm going to cheat again. Normally, I wait for level 2 and 3 to go into Design View. But, like I did earlier with the customer form, let's go ahead and sneak ahead a little bit.

Close the print preview. Let's go over to Design View.

I'm going to take this label and this text box and slide it over here before last name. In Design View, there are different banded sections. Forms have them too, but in reports they're a lot more prevalent. Details is where all your details go. Page Header appears at the top of each page. Report Header appears once at the top of the report. Then you have the Page Footer at the bottom of each page and a Report Footer, which right now has nothing in there. But you can put stuff at the bottom of each report. Those are the different sections of a report.

This is just a little sneak preview. I'll cover all of this in detail in future classes. Right now, I'm going to take this text box. Watch this. Grab the edge. Click, drag, and drop it right there on top of first name. Now I'm going to take last name. Sorry. Now I'm going to take first name and slide it over to the right, just like that. Then take last name or first name, whichever one this is, and slide it over to the right. That's how easy it is to move things around in here.

Now we have to do the labels too. The labels are on top. Take this, click, drag it over there. Now I'm going to click and then shift-click on that one to select them both. Then click and drag, and we'll go to the right. See?

Save it. Let's close it. I'm going to open it back up again. If you double click on it, it opens up in Report View. I don't like Report View. Report View, to me, I like Print Preview. So I always right click and then Print Preview. You can see what it's actually going to look like when you print it.

In a couple classes, I'm going to show you how to make a main menu form with different buttons on it to open up your forms and reports. We'll have stuff open right up into Print Preview.

One more change I want to make. Look at that. Customer ID is kind of chopped off there. Numbers tend to line up on the right side, whereas text lines up on the left. Let's change the ID. Let's rename that just to ID. We'll make this all aligned to the left.

One more time. Close the Print Preview. Right click. Design View.

I want to change this label to just say ID, just like that. Let's resize this guy like that. Then I'll go to Format and Align Left.And we will do the same thing with the text box. Watch this. We will do Align Left. All of this stuff should line up to the left.

Save it. Close it. Right click. Print Preview. There we go.

I apologize. My recording software is a little box, so I have to right click. There, you can see it is down there below the Access window. Print Preview. There you go. That looks a lot better now, doesn't it? ID, then you have that stuff right there.

I am going to show you all kinds of cool tricks in future lessons. How to turn off these alternating bands. If you do not like that gray band. Grouping levels. All kinds of neat stuff.

Let's close that. That was kind of fun. Let's make another one. Let's make a single report where we have a bunch of different types of things, where we have a bunch of data for each customer, one customer at a time.

Let's click on Create, and then Report Wizard. I like the Report Wizard. It is a good wizard. We will pick the customer T again. You could pick a query, remember. If you want just customers from New York or whatever, whatever you want to appear in this report, you can set up a query for it and then base it on that.

But I want the customer T. It has all my fields in it.

Let's do customer ID, first name, last name, company name, state, zip, country, and let's add phone number and how about notes. Just those fields.

Next. Do you want to add a grouping level? No, that is OK. Next.

What do you want it to look like? Sort by, let's go last name and then first name. Next.

Let's do column number this time, so we get the labels and the fields next to them. Next.

What name do you want? This will be my customer R, my customer report. Then preview. And hit finish. There we go.

Now you can see I have one customer. There is Joe Jones. Scroll down. There is the next customer. Same Susan Jones. Page one of eight. Go to the next page. Scroll back up here. There is the next customer.

Again, I do not like that customer ID aligns to the right and everything else aligns to the left. So let's close the print preview.

Click on customer ID. Format, and then align left. Again, I do not know why it keeps putting it down here. Let's do that.

I am going to draw a box that touches these. See that? Then click and drag it up to the top. Then click and drag a box that touches those and drag these down. You can move these things around in design view. We will be spending a lot more time on this design view in the next level and level two.

While we are in here, let's highlight these like that and bold them. See that? Save it. Close it. Right click. Print preview. Look at that.

There we go. There is a single customer report. Scroll through. There is page two. Page three. Notice my notes. It takes a lot of space. Page four.

People always ask me at this point, can you make it so you get one customer per page? Yes, you absolutely can. You can force a new page after the section. I will cover that in a future class. That involves getting into some more advanced stuff, but it is possible.

Some other things you can also modify in here once you are in print preview. Go to size right here under page size. Here is where you can change the type of paper that you are dealing with. Letter is the most popular, but if you are dealing with legal paper, there you go. That changes it to legal, or A5 or an envelope or whatever else you want to pick. I am going to go back to letter.

You can change the margins if you want. There is normal, wide, narrow. If you go to wide, you can see what that looks like. You get more room around the page.

You can set custom margins if you want to under page setup here. I will talk about all of this in upcoming classes. Here is where you can change from portrait to landscape.

You can export reports in different formats. You can set them to Excel. You can make a text file. PDF is what most people do. Just click on the PDF or XPS. XPS is Microsoft's type of open document format, but everybody pretty much has PDF. Just pick PDF. Give it a file name. Pick where you want it to go.

There is standard or minimum size. If you are sending it to someone by email, usually minimum size is fine. Standard if you are actually printing it and you want high quality. I am just going to hit cancel.

You can also email it as an attachment. You can send it as a Word document. You can do all kinds of stuff up here.

Let's close this one. Let's make some mailing labels. The boss says I want to print out mailing labels for all of our active customers, not everybody.

If you remember from our customer table, we have a field in here called is active. Some of these people are no longer active customers. Maybe they are deceased or they moved out of our area that we service. I do not want to waste money mailing to those people.

What do I do? How do I make a report to print labels for just the active customers? Whenever you want to do any kind of criteria like that, what do we use? We use a query.

Just like this customer from New York. Remember this guy? We have a query down here, a criteria that says New York. Let's make a query that has only active customers in it.

Let's go to Create Query Design. We are going to go to tables, bring in our customer T. I can close this now.

Bring in the fields that you want for your mailing labels. I do not care about customer ID. I want first name, last name, company name, address, city, state, zip, country. Then I need is active.

Now for is active, I am going to come down here in the criteria and I am going to say true. That means only customers that are active will show up in this query.

If I run this query now, look at that. They are all the active customers. I am only seeing 13 of them, whereas if I open up the table, there are 16 total. I have three inactive customers that will not show up in the mailing labels.

Do you have to sort this? No. We will handle the sort again inside the report.

Let's save this. I am going to call this customer active Q, my active customer list. I like to keep all the customer stuff starting off with customer. That way it is all grouped together.

Again, I try to keep my tables and queries in some singular. Instead of saying customers active, it is just later on it just becomes less confusing. You do not have to say to yourself, well, was that customer active or customer is active?

Our query is all set. We have a query with just active customers. Now we can use this to make our mailing label report.

Let's go to Create Labels. The wizard creates standard labels or custom sizes. First thing is to find the manufacturer of your favorite labels, whatever types of mailing labels you have. I personally use Avery labels. Avery is one of the most popular manufacturers. Even the third party, the knockoffs and the other brands usually have an Avery compatibility number.

I like 5160s. That is where you get 30 on a page. It is three columns, 10 rows, 30 per page. Of course, it might change English versus metric.

Sheet feeder or continuous. Sheet feeder is inkjet and laser printers. Continuous is dot matrix printers. It just keeps feeding them in. Or you might have a continuous label printer, like a Dymo label printer.

Yes, you can create custom labels. I do have lessons on that. That is a lot more advanced though. But find your label type that you want.

Hit next. Font, font size, font weight, text color. I am going to stick with all the defaults. If you make your font size too big, you might get fewer lines on the labels. I do not think you can go 20 point in here and get five lines on there. Eight point is usually good enough. You do not want to go too small either because sometimes the post office might not be able to read your label.

Next.

Now we are going to set up our prototype label. Basically, we pick the fields over here and we arrange them on this prototype label how we want them to appear on the label itself.

First, bring over first name. Double click, or you can click that button there. Now, between first name and last name, what do I want? A space. So press the space key on your keyboard. Now bring over last name.

Time for the next line. Enter. Press Enter on your keyboard. People always miss that step.

Company name. Now if company name is blank, it will not put a blank line there. It will squeeze out that empty space. It is kind of neat.

Enter. Address. Enter. Now we have city, space. Sometimes people put a comma there. I do not. State, space, zip, space, and then country.

If you have a larger label or you go with a small font, you could fit country on the next line. But this usually works just fine. Remember, I leave United States. I am in the United States, so I leave US customers blank so it does not print USA there. That is one of the reasons I do that.

Do I want is active on the label? No. Should leave it over here.

Ready? Next.

What fields would you like to sort by? If you are doing bulk mailing and you are printing thousands of these, you have a bulk mail permit and you get a discount, you might want to sort by zip code and then address so all your stuff is properly sorted as it is printed. I am not going to bother with that, so I am going to sort it by last name and then first name.

But this is where you control the sort. You do not have to do it in the query.

Next.

What name do you want? We do not want that. We want customer mailing label R. All singular. Again, that is my preference. You can name it whatever you want. No spaces. Again, I emphasize no spaces in your object names.

Trust me, if you do decide to get into development and you get into programming, you will thank me then for not having spaces. They cause so many problems.

Finish. Let's see the labels as they were looking when they were printed. Finish.

Now, this is a popular error message that comes up almost all the time. It says some data may not be displayed. There is not enough horizontal space. The wizard sometimes makes the width of the label just a little bit too wide. This happens all the time. I get tons of emails from people about this.

Just hit OK. It still looks fine, does it not? It looks just fine. But that error message still pops up.

Here is what we are going to do. Close this. If you go back into print preview, right click Print Preview, you still get that. How do we get rid of that?

Go to Design View. Take this edge right here, this little tiny edge, and just click it and drag it in just a teeny tiny bit. Even that sometimes is enough. Save it.

Let's go back into Print Preview again. Let's see. If not, just eat off just a little bit more. I think this is a long-time Access bug. Let's cut it just a tiny bit more. Maybe slide these over a little bit like that. They will still fit just fine on the labels.

Right click. Print Preview. There we go.But it gets rid of that stupid error message. And these will still print just fine.

So there are your mailing labels. Now, when you are ready to print, you just come over here and click the Print button. I am not going to do it because I do not want to waste a sheet of paper. But that is that.

Now, I have to give you my laser printer talk at this point. Notice how I have only got half a sheet of labels that are going to print. I have a bunch down here; well, I have the four times three, twelve, thirteen. So I am going to have, what, seventeen blank labels on the bottom down here.

If you have an inkjet printer or a dot matrix printer, you can ignore this. If you are using a laser printer, you do not want to feed a sheet of labels through a laser printer more than once. Why? Because laser printers use heat when they print. They basically sprinkle on some toner and then the heater bakes the toner onto the page.

That heat tends to cause the glue behind those labels to soften. If you run the same sheet of labels through that laser printer and it keeps getting repeatedly heated, those labels have a tendency to peel off inside the printer and get stuck to the rollers. I have personally had this happen to me and a couple of my clients, and it is expensive to have your printer taken apart and have those labels peeled off. You are better off just buying a new printer.

So if you have a laser printer, do not feed your sheets of labels through more than once. If you print a lot of one-off, single labels, I do highly recommend the DIMO label printers. I have got lessons on how to use those with Access. In fact, I have a free one on my website. I will put a link down in the link section below on how to print to a DIMO label printer. They are pretty cool.

These guys, these little printers, are right here. In this video, I will show you how to use them.

So let's go ahead and close that. Now we have got a couple of different reports in our system. We can also print or mail to people.

We covered a lot of stuff today. We learned how to build a table and how to build queries based on that table with different criteria. We built a customer form to edit and enter data on the screen, and we have got a couple of different reports that we can print out. So let's go ahead and close that.

Thank you.

13. Q&A From Students (16:57)

Welcome to the question and answers segment of the class. As I mentioned in the earlier videos, I have been teaching this course for over 20 years now, and during that time, I have seen a lot of the same questions come up over and over again.

I cannot take the time to answer every question during class. It is already over four hours long. So, I put together this Q&A list of the top questions that I have seen come up over the last 20 years.

One thing that I have learned is that people hate when I say, we will get to that in a future class, which I used to do before I had all these videos recorded. But I have all these videos recorded now. So, all of these questions are answered in other videos, some are free and some are future classes. This will be a list of all of the popular questions and where you can find the answers. If you need an answer to something now, here it is.

This question is, I want to make a form that has a list of all of my customers and I would like to be able to click on one of those customers to open up their record in the customer form. How can I do this?

Well, this is something that we cover in Access Beginner Level 7. I also have two free TechHelp videos, the Continuous Forms video and my Blank Template video, that will explain how to do it. There are the links. I will put links in the links section down below so you can just go click on them.

You can see an example of the customer list form from Access Beginner 7. You just click on one of these customers and click on the Open Selected Customer button and it opens up their form. Here it is in the TechHelp free template. Just click on the customer list button there. There is the customer list, and you can either click on the button, like click on John Luke and then hit the Open Customer button, or I show you how to make a double-click event. You go double-click and it opens up that way too. There are a lot of different ways to do it.

When I press the Shift F2 key to open up the zoom window, it does not come up. This happens a lot with laptop users, me included. I use laptops for all my work. Generally, when you get a laptop, the function keys have been remapped so you have to actually use the function key too. So, instead of just going Shift F2, you have to go Shift Function Key F2. I know that is annoying to me too, but everybody always asks this question so I figured I would share it.

There is a way that you can remap that so that the function keys are not necessary to hit the actual function keys. On a lot of laptops, for example, those function keys will also be for the screen brightness up and down, the volume up and down. You can switch that and each laptop is different, so you have to look in your laptop documentation.

Is there a way to zoom in with forms and tables and queries and stuff? Unfortunately, the answer is no. In Access, the only object that lets you zoom in and out is a report. Here, for example, is an invoice report that I built. You can click on it to zoom in or you can use the little zoom bar down here for reports, but unfortunately for forms and tables and queries, you cannot do that.

I know in Excel and Word and PowerPoint, you can zoom in and out, but you cannot in Access. Unfortunately, Microsoft does not give the same love to Access that it gives to those other apps because, let's face it, Access is not as popular.

A lot of people really want that feature to be able to zoom in and out on a form, especially for people that are hard of sight, including myself. You can design a bigger form with bigger fonts and bigger text boxes, but you cannot do it just on this one standard form or in tables. You cannot zoom in and out of tables either. So yes, I know a lot of people ask for that. You cannot do it.

Now you can change the datasheet font size. In tables, for example, you can go into File and then Options. Under Datasheet, you can change right here the size of your font. You jack it up to 20 points, for example. Now when you open up any datasheet, you can see everything is much, much bigger. That is an option.

However, one of the things that I am going to teach you is that you generally do not want to poke around in your tables and your queries. You want to work with forms, and that setting does not change the font size in forms.

I have a lot of people ask this and I am pretty sure I mentioned it somewhere in the course. I am pretty sure, not positive.

How do I delete a table? I just right-click on it in the Navigation Pane and hit Delete. Here is your Navigation Pane. So, you want to get rid of version history, T, just right-click on it and pick Delete, right there. Are you sure? Yes. Once it is gone, it is gone. By the way, there is no undo. When you delete something, it is gone. Back up your database. We will talk about backing up in a few minutes.

Here is a popular question. I am building a database where I already have a student number assigned to each student by the university. Should I still create an AutoNumber as the primary key?

I get this question a lot. I have already got customer IDs assigned in my old paper system or with Excel. I have already got product IDs that I am given from the suppliers. Should I keep those numbers? Yes. Definitely keep those numbers.

Make a field in your table called, in this case, student number, and store that number. But you should make an AutoNumber too and have that as your ID field, student ID, for example. There is nothing wrong with having two different fields like that, a student number and a student ID, a product ID and a product code, whatever.

Now, we will talk about relationships. When we get to the Expert Series, I am going to talk a little bit about relationships in the next class, Access Beginner 2. Access uses those ID fields for making relationships between tables for relating customers to orders, students to classes, that kind of stuff. That is relationships. We are going to learn a lot more about relationships as we move forward.

Those AutoNumbers are not for you. They are for Access. You do not even need to worry about what they are. Watch that video there. My AutoNumbers video explains all of this in a lot more detail if you want to learn more about AutoNumbers.

I deleted a couple of customers that are no longer with me. Now there is a gap in my AutoNumbers. How do I get them back?

Again, just like the answer to the last question, do not worry about them. Those AutoNumbers are not for you. They are for Access. So, if you delete a customer and he is gone for good, do not worry about that AutoNumber. It does not matter if they go 1, 2, 3, 4, 8, 12, 26. This is for making relationships.

There is an exception. If you already have relationships set up in your database, let's say you have got customers 1, 2, and 3 and you have got orders in the system for customer 2 and you accidentally delete customer 2. Now you have got a whole bunch of orders in there missing their customer. Yes, there is a way you can restore that AutoNumber. There is a link right there. Go watch that video if you absolutely need to get that AutoNumber back. It is possible.

While I am thinking about it, sometimes people just want a counter variable. They want a number that goes 1, 2, 3, 4, 5 and counts up. Orders, for example, you can make unique order numbers for a customer and each customer has order 1, order 2, order 3, and so on. That is possible. Again, I have another video on that. It is a little more advanced and requires a tiny bit of programming, but go watch my counter video if you want to learn how to do that.

Can I use my Access database to send out letters to all of my customers? Absolutely you can. You can actually write all of your letters inside of Access as a report. I cover that in Access Expert Level 5.

You can also write the letter in Microsoft Word and then use Access as your data source for a mail merge. I cover that in my Microsoft Word 201 class and in my Access Expert 19 classes. If you want a quick version, watch my Letter Writer video. That is a TechHelp video that does the same thing in a quick format. It is possible. It is one of my more popular classes on how to make Access into a letter writer. You can do it with or without Microsoft Word.

Here is another very popular question. I want to build an Access database for everyone in my company to use, but the boss does not want to fork over that much cash to buy copies of Microsoft Office for everyone, especially the warehouse guys who could use the database, but do not need Excel, Word, and all that. Is there a cheap version of Access available?

The answer is no, there is not a cheap version available, but there is a free version available. It is called the runtime edition. You use the runtime edition for people who only need to use the database and do not need to make design changes. You have one copy of Access for the developer, the person who is building the database, and everybody else in the organization can get the runtime edition and it is absolutely free. Go watch that video. It will explain how to get it, how to install it, and how to use it.

If you have got 20 or 30 people in your office and 10 people in the warehouse, they all have their own workstations, they need to be able to use the database to enter in products or whatever, all their copies are free. You just need to buy one copy of the full Microsoft Office with Access for your designer, for your developer. That is you.

Here is a good question. What if a customer has multiple phone numbers, emails, or addresses?

I am pretty sure I talked about this in class, but generally, as my rule of thumb, if they have two, maybe three of something, then it is okay to make multiple fields in the same table. Home phone, work phone, cell phone, not a problem. Shipping address, billing address, not a problem.

If it goes over three, then you really should consider making a second related table. Again, we are going to focus on relationships in Access Expert Level 1. I talk about it a little bit more in Beginner 2, but we are going to give the full treatment in Access Expert Level 1.

I have seen people build databases where they were doing orders, for example, and instead of having an order table and then an order details table for all of the line items, the individual products on the order, they had 20 order item fields. Product 1, product 2, product 3, product 4. Do not do that. That is bad database design. You will learn why in those two videos, Access Expert 1 and my relationships video.

This is a very, very important part of Access. It is also a little more advanced, though. I cover it a little bit later on. Right now, we are just focusing on basic database design, but keep that in mind. If you have got six of something or like children, they might have zero children, they might have 10 children. You do not know how many children they are going to have. You do not want to reserve 10 fields. That goes in a separate related table.

I have been using Excel for years. How can I import all of my Excel sheets into Access?

It is certainly easy to do, but it can be difficult to do right, especially if your data is not in your Excel sheets the right way.If you do not have all of your columns with the same kind of data, I have seen people do some really weird stuff with Excel sheets. I give importing data from Excel the proper treatment in Access Expert Level 20. It is not hard to do. If everything works great the first time and all your data is nice and orderly, then it is easy. But when you have problems, you can be pulling your hair out all night trying to figure out why it is not working. We will get to that.

I have three different classifications of customers: sales, service, and warranty only. How can I differentiate between them? Right now, I have them in three separate Excel sheets.

One thing you do not want to do in Access is have three separate tables with the same type of stuff. Customers are customers. They all have the same fields pretty much for the most part - first name, last name, address, phone number, all that stuff. You do not want to have three different tables with the same kind of stuff in them. What you will do is you will just put a field in that table to indicate what kind of customer this is. Is it a sales customer? Is it a service customer?

Now, the tricky part is if one customer can be in multiple classifications. If someone can be a sales and service customer or a service and warranty only customer, or all three, then you have to use something called a many to many relationship. That is a little more difficult. We will get to it in Access Expert Level 7. I also have a TechHelp video called Many to Many. That is where you can find that answer. Yes, it can be done. It takes a little work.

Here is a big one: How can I keep people from changing the design of my forms, reports, tables, etc., or poking around in my database? Securing your database can take some work. I have a whole seven-hour course on just properly securing your database. It is called the Security Seminar. That is the first link there. It walks you through everything you need to know to properly lock down your database. We add user log-ons, all kinds of stuff, tracking what they do in the database with an audit log. That will keep pretty much the best hackers out of your database.

However, if you just want some simple tips on keeping your average Joe office user from messing with the database, I have that Simple Security video too. That will give you enough information to keep the majority of regular, normal office people out of your database. We will lock it down a little bit. But if you are worried about hackers in your office, I have the Security Seminar.

Access can be very secure, or it can be not secure at all. It depends on how much effort you put into securing it. Watch the Simple Security video first. If you need really, really, really good security, go get the Security Seminar.

This is a popular recent question in my forums, and I figured I would include it here. With the COVID pandemic, lots of people are working from home. Is it possible to run Access over the web?

This is a tricky question to answer. There really is not one easy answer. There is no web-based version of Access. However, there are ways that you can run your database remotely over the internet. I do have a page on my website and some free videos that talk about how to run Access online. Go to that page, watch what is there, and read that page over.

Whatever you do, I see a lot of people trying to do this: Do not use Dropbox, OneDrive, Google Drive, or any of those file sharing services. There is a good chance you will corrupt your database. I see people try to run Access with other people through Google Drive or Dropbox or whatever. Do not do that. That is bad. Go watch that video, read what is on that page, and you will get all the information on this. This changes often. There is always something new coming out, so keep checking that.

Is there a way to make a menu system in Access so you do not have to poke around on the navigation pane to find what you are looking for? We will cover that in Access Beginner 7. I also show how to do that in my blank database template for TechHelp. Here it is right here in my blank database template, my TechHelp Free template. You will hear me refer to this a lot. It is basically a form with some labels and some buttons on it. You click a button, and there is your customer list, your customer form, your order form. We can use buttons and forms to make our menuing system.

There is something called the navigation form. I do not like those. If you want to see why, there is another video you can watch where I talk about navigation forms. Switchboards they used to be called. Back in the old days, they were called switchboards, and they upgraded to something called navigation forms, but I do not like either of those. I like to make my own menu.

Finally, to round out the questions for Access Beginner 1: What do I have to do to back up my database? There is a page right there for backup. Fortunately, everything you need - all your tables, forms, queries, reports, all that stuff - is stored in one file. It ends in ACCDB. It is wherever you happen to store it when you created your database file. Back up that one file, and that is all you have to worry about. Everything is contained in there. Your forms, your queries, your data, everything you have typed in is all in that one file. Go watch my backup video for complete details.

That is the Q&A session for Access Beginner Level 1. These are the most popular questions that I have been asked over the last 20 or so years that I have been doing this. If you have questions, post them on my website right on the page where you find all these videos. Scroll down to the bottom, and you will see where all the other questions and answers were posted. Feel free to post yours there.

Thanks, and I hope you learned something today.

14. Review & What's Next (7:07)

Thank you for watching Microsoft Access Beginner Level 1 brought to you by AccessLearningZone.com. Again, I am your instructor Richard Rost.

Let's take a few minutes and review the concepts covered in today's class. We learned about Access database terminology, what a database is, why you should use a database, the benefits of Access, and the parts of a database: tables, queries, forms, reports, and more.

We learned about planning our database, planning it out on paper, what kind of tables you should have, the different fields for each table, and so on. We learned about the Access interface, creating a blank database, and how to turn on overlapping windows.

We learned how to build a customer table, including different data types and fields. We learned how to enter and edit that data. We learned about the different kinds of sorting and filtering options.

We built a few different customer queries to display the data from our tables in different ways. We built a customer form using Design View so that our user has a friendly interface for entering and editing data. We will do a lot more with this in level 2.

We built a few different customer reports, including some mailing labels.

What's next? The next course in the series is Access Beginner Level 2, and you can find it on my website for just $1. Level 2 is over an hour and 20 minutes long, where we spend a lot more time building forms in Design View.

You will learn a lot more about manipulating form objects, form formatting, using themes, fonts, and colors, working in Layout and Design View, and lots more.

Today is June of 2021 as I am recording this video. I am shortly going to begin re-recording Beginner Level 2 as well. The version that is on my website right now is for Access 2010-2013, but all the material in that lesson is just fine whether you are using 2016-2019 or you have a Microsoft 365 subscription.

However, when the new Beginner Level 2 is finished, you will, of course, get a free upgrade. That is the rule for all of my lessons. If you buy Expert Level 1 or Expert Level 15, whenever I record the next version of that class, you get a free upgrade.

Pretty much all of the material that I cover in Beginner Level 2 is the same. They have not really changed the beginner material much. I will give you a more in-depth look at what is coming up toward the end of this video.

Once again, if you have any questions, please feel free to post them on the Student Forum, which is right at the bottom of the website that you are on now, if you are on my website. If you are watching on YouTube, go ahead and post your questions there as well in the comments section. I check those about once a week, whereas I check my website daily. Of course, feel free to post your questions in my Access forum.

Do not forget to stop by the TechHelp page and check out those videos too. I try to put out at least three or four new ones every week, and there is a lot of material that I have already covered.

The only way I can make my courses better is with your feedback. So please visit my website at the survey page, and fill out the survey for this course. How did you like it? Did I go too fast? Did I go too slow? Did I cover enough material? Let me know what you thought about today's class.

Please be sure to subscribe to my blog, follow me on Twitter, and find me on YouTube.

As a quick preview of what is coming up, I have many more Access classes available. I am currently in the process of re-recording all of these, just like I am recording Level One that you are watching right now. This is probably the fourth time I have re-recorded this class. You have to do it every couple of years because minor things change. However, all of these classes are just fine from Access 2007 all the way up to now. It is 2021. The rest of these classes are just fine.

In Beginner Level Two, we do a lot more with form design, working with the different properties of the form control elements.
Beginner Level Three takes us back to tables, and we do a lot more work with tables, learning about the different field sizes, number types, data formats, input masks, default values, validation rules, and more.

Level Four covers indexing, backing up your database, and compact and repair.
Level Five covers more advanced queries, including multiple criteria and parameter queries. That is where Access can pop up a little box that says, what state do you want this query to run for? Type in New York and then you get New York.

Level Six covers working with pictures.
Level Seven is where we build that custom main menu form. We get something that looks like this. We can click on different buttons to open up different tables, queries, forms, and reports. We will learn how to build a continuous form and then make a button so you can scroll down through the different customers, click the button, and open up that customer.

Level Eight covers combo boxes. A combo box is where you can pick a value from a list of options, like picking the state from a list.

In Lesson Nine, we start tracking contact history. You have customers, and every time you talk to a customer, you might want to track that contact history. We will make a second table and start tracking that information, so we can build a contact history for each of our customers.

That is just the beginner series. Then there is the expert series, which covers a lot more material. The expert series starts off teaching about relational database concepts, relating multiple tables together. For example, you might have a list of drivers and a list of cars, and each of these drivers is associated with each of those cars. That is one example. You can also do customers to contacts, customers to orders, vendors to products, all kinds of different relationships. I will show you how to make queries that relate multiple tables together, handle customers, lead sources, shipping information, and more.

That is a little preview of what is coming up in the next couple of classes. I have hundreds of hours of different Access classes, seminars, templates, and all kinds of things on my website.

What is next for you is Access Beginner Level Two. You can find Level Two on my website. It is just one dollar. I will put a link down below that you can click on. You can get that and continue learning.

Thanks for watching Access Beginner Level One brought to you by AccessLearningZone.com. Once again, my name is Richard Rost. I hope you learned something today and I look forward to seeing you again in Level Two.