Lessons

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Subscribe to Access QuickStart 1
Get notifications when this page is updated

Intro

In this lesson, you will learn the basics of building a database in Microsoft Access, including an overview of what Access is, the main components of an Access database (tables, queries, forms, reports, macros, and modules), and how to plan out your tables and fields before creating your first database. I will show you how to create a new database from scratch, design and build tables, enter and manage data, create simple queries to sort and filter records, and use criteria and prompts for interactive results. This is lesson 1.

Transcript

Is [email protected] or [email protected]. I have a bunch of them.

Let's get started with today's class. This is the Microsoft Access class. Access is a database program. In my opinion, it is one of the best database programs available for small and medium-sized businesses, and even for big businesses, because Microsoft Access integrates well with other database servers.

It's a database program by itself, so you can have just Access on your computer and you can build a database and you can work with it and use it all by its lonesome. But if your business grows and you need to have more power, you can get a database server like Microsoft SQL Server or Oracle or any of those big guys. You can run your Access front end—your Access database looks and feels is called a front end. We'll talk more about this in a future class—with a more powerful database server in the back end.

Let's talk a little bit about what databases are. Microsoft Access is a database that can store information and allow you to develop a nice, pretty interface for other people to work with your database. You can build queries to view the data in different ways. In fact, I have a sample database here. Let me move the screen out of the way. Here's my sample database. This is actually the database from the last class. Let me move this over here.

Now, I'm going to be moving windows around a little bit on the screen simply because I've only got a tiny window to work with, but it's pretty tolerable.

Here's the main menu. We're going to show you how to build a main menu, and we're going to build a customer form to track customer information, things like first name, last name, company name, phone number, address, and so on. We'll show you how to make little buttons to open up other forms like a contact form so we can track contacts on these customers each time I talk to them.

We're going to make an order entry. We're going to spend a lot of time making an order entry system. This is part of my favorite product class, where you can pick a customer from the list of customers. You can pick a sales rep, and you can pick products from the list of products like G's Pizza in the order.

There's a lot going on behind the scenes here, but this is an example of something that we're going to build in this class. We're going to do some reporting. We're going to make up an invoice, for example.

Here we go. As a matter of fact, you can see the whole invoice there. We'll do a lot of fun stuff.

Let's talk about what a database consists of. This window that you're looking at says Rix Online Web Seminar Database. This is my database window.

A database is comprised of six basic components. They've added a new one and a couple other ones we'll talk about later, but six main basic components. You get tables, queries, forms, reports, macros, and modules.

I'd skip over pages because I don't like them, so I'm not going to spend a lot of time on it. It's called data access pages, and they're for making a database work on the web, but they don't work very well at all, so we're going to skip them.

Let's talk about the different things.

We got tables. What are tables? Let's take a look at a simple table. Here's our customer table.

A table basically looks like a spreadsheet. You got customer ID, you got first name, you got last name, you got information in rows and columns. That's a table. All of your data, your information in Access, is going to be stored in one or more tables. We're going to build a customer table. We're going to build a contact table. We're going to build an employee table, an order table, a whole bunch of different tables.

But tables themselves aren't very useful. They have limitations. The whole limitation is if you got 500,000 records in a table, let's say you got 30,000 customer records, and you want to see a specific set of them, let's say customers from New York with a credit limit over $500, it's kind of hard to go through the table and pick that information out.

So we have things called queries. Queries will allow you to use a bunch of queries. We're going to build a whole bunch of queries in class. But here's a customer query. I want just a simple list, sorted by last name and first name. You can break that information down into smaller steps. You can also use queries to change data, manipulate data. We'll learn about that in class eight.

But again, looking at tables and queries, it's not very user friendly. It's still rows and columns.

So we can take that information and we can build them into forms. Now, forms like our customer form here are nice and pretty, basically. A form is a nice user interface for your database, especially if you're going to have other people working with it.

If it's just you, the developer, you can poke around on your tables and query a lot. But a form is a good way to build a database for non-computer experts to work with, and it gives you extra functionality. We can build things like buttons, list boxes and text boxes and combo boxes, and even put some Visual Basic programming in our forms. So forms not only make our database easy for other people to work with, they can make it much easier for us to work with as well.

I run my entire business out of an Access database, and I wouldn't be able to do that without the functionality that forms give.

Then we have reports. Reports are basically things you print out. Here's a credit limit report, for example. I'll zoom in here. Company name, phone number, how long they've been the customer, and their credit limit. That's an example of a report. That will print out. We'll do a whole bunch of different reports. Basically, reports are things you print, forms are things you work with on the screen. Can you print forms out? Yes, you can.

Again, pages we're going to skip over. Data access pages are designed to take your database on the web. But again, they don't work very well in my opinion.

Macros we're not going to use a lot. In fact, we didn't build any macros in the last session. I had it as an optional component. If you have time, macros are basically ways to automate repetitive tasks. Open a form, go to this record, print it out, things like that. However, macros are really kind of being phased out. In fact, Microsoft only includes support for macros still for compatibility with older versions of Access that were really in macro-heavy use.

But now we have modules. Modules are essentially Visual Basic programming code. We have a lot of modules; I just don't have any in the module window here. We build them inside of our forms. You can use modules to really take your database to another level.

The good news is that with a little bit of programming, you can make your databases phenomenal. The more good news is that you really don't have to know any programming to build a decent database. In fact, we're not even going to look at programming—I think—until class four. So we're going to build some really cool stuff before we even touch any kind of programming.

Also, don't be scared if you've never built a database before. Again, if you haven't downloaded the outlines off the website, I suggest you do. I try to follow the outlines as close as I can in class. I jump around a little bit, and sometimes I change a thing here or there. Hopefully, it's the same stuff that I found.

I hope to start first, before you actually build your first database, or if you've been building databases for a while and you're going to build a new database, I strongly suggest you sit down and lay out your database on paper before you start hacking away.

Now, the important things to get are: What kind of information do you want to have in your database? Who's going to use the database? What kind of user are you building for? Are you building for people who don't know how to use a computer at all? Are you building for people who are fairly savvy? Because if you have people that are fairly savvy, you don't have to put as many bells and whistles in your database.

But the main thing, the important thing, is to figure out what kind of content you're going to have in your database. Essentially, you're going to try to determine what kind of tables you need. That's just a matter of thinking: What kind of stuff do I want to store in my database? Well, I'll ask for information on customers. So I'll need a customer table. All the related data will go in that table.

I want to store information on my products. So I'll need a product table. I want to track orders. So I'll need an order table. I'm worried about breaking it down in more detail a little later.

Once you have a list of all the different tables that you want, then we want to break down each table into fields. If I open up one of these tables, let me open up my customer table here. I'm going to design it. From the customer table, hit design. Again, we're going to go through this together a little bit. You can see here that a table is based on our results with numerous fields. A field is just a fancy name for the type of stuff you want to store in here. For example, first name is a field, a text field. Last name, company name, phone number, address, city, state. You want to break down the data as much as you can.

Those are the different fields you're going to need in your tables. Different tables will have different types of fields. For example, here's my product table. Let me design that and show you what that looks like. And again, I just have to move stuff around on my screen so I don't have a lot of window to work with here. Normally, you don't have to do this as much as I am doing here.

A product name, a description, a unit price, and whether or not that product is taxable. Is there more stuff you can store in this table? There's a whole ton of stuff you can store on products. Yeah, quantity on hand, and all that good stuff. But we keep it simple and fast.

So those are the different fields that your tables will need.

Now, basically, we're armed with enough information to go ahead and build our own database. So let's create a database. Let's start off. I'm going to close this database down. If I close the database window, you effectively close your database.

I'm going to come up here. I'm going to go file, new. I'm going to create a blank, new database.

Now, there are wizards. In fact, I want to go ahead and cancel this. I want to close Access down, and I'm going to just start Access from scratch, because I want to show you this window that comes up.

Normally, when you start Access, you get this window. You can create a blank Access database. You can use the Access Database Wizards Pages and Products. I'll talk about those in a minute. Or you can open an existing database that you're working with. Here's one I just had open. I've got some other ones here.

Now, blank Access database is what we're going to start with. I'm going to show you how to build databases from scratch.

There are database wizards that will build databases for you. Some wizards are good. Some wizards are evil. Are you a good witch or a bad witch? The Access Database Wizard, I think, at this point, is an evil wizard because it will build a database for you, but it's not going to teach you what it's doing. And when you're done, you'll have no clue what it just did. So that's not why we're here.

We're here to learn how to build databases from scratch. So we're going to pick blank Access database. If we have time after class eight, I'll walk through an Access Database Wizard for you. However, in the 10 plus years I've been working with Access, I've never had a need to use a database wizard, because I've found that even after I've learned the wizard, it was faster to build it from scratch.

So I'm going to put it in my document folder. It's Access, so we need a name for our database. Unlike a lot of programs like Word and Excel, where you make up your file and then save it afterwards, Access wants you to save it up front.

So let's call this—how about access_online_database. And I'll fill the data in here starting on 9/6/2002, and we'll hit create.

Now my screen's there for a log on. Yours might not. I have security set up on my system here with an Access work group. We're going to talk about security and work groups in classes 7 and 8. I'm going to type in my password. You may or may not need this depending on how your office is set up.

Here is my Access database, my shell. I've got an empty, basically an empty window. There are some shortcuts here for doing some things. We'll talk about these later. But basically, it's an empty Access window.

On the left-hand side over here, we can see tables, queries, forms, reports. All right, they're blank. If you click on them, you'll see they're all blank.

Let's get started by building our first table.

Now again, if this is your first time taking one of my online classes, I strongly recommend you just sit back and relax and watch during this live session. If you have any questions, you can ask them in the chat. I want them to have time to answer them. Come back after this class is over and watch the archive off the website. We basically replay the class. You have to sit through it a second time, but I find, first of all, people get more out of it going through a second time. You can pause the replay, and you can stop, and you can go through and do the examples, do a little bit, stop, play a little more, do a little bit, stop, and so on.

Let's create our first table. I'm going to click on tables. Now, there are some options here: create a table in design, create a table in the middle of it. I don't like using the shortcut myself. Maybe partly because I'm old school. I didn't have these before.

But I like to click on tables. I'm going to come right up top here and click on new. That's going to ask, how do you want to build your tables? There's a bunch of different things you can do to build a table. There's datasheet view, design view, table wizard (evil wizard, again, it'll build a table for you but it's not going to teach anything). You've got import and link tables. We'll talk about those later. That's for getting data from an existing data source.

But basically, you got two options: datasheet view and design view. Now, design view is the one that we want. It lets us build a table first by specifying what kind of information we want, and then we go through and fill in the specific data itself.

Let's look at the test results example. Datasheet view is kind of backwards. It says, "OK, go ahead and put your data in, and then we'll define what the fields are later." So I don't like datasheet view. We're always going to use design view, always. So let's click on design view, hit OK. Now we have a blank table window. I'm going to just resize this real quick.

Now, pay no attention to the stuff down here, which is general and lookup. Just worry about the stuff up top here.

Let's start putting in our first fields. I'm storing information on customers. So what kinds of information do we need on our customers? Usually, when I'm building a table, I ask myself, who, where, when, how? So I'll start off with the who. First name. Let's put first name and last name in here.

FirstName. Now, I want you to notice: capital F, First, capital N, Name, no space in the middle. Can you use spaces? Yes, you can. May you use spaces, no, you may not in my class. It's a Rich thing. This is a Rich instructor thing. But I'm speaking here as a developer who teaches, not as a teacher who knows how to develop. Meaning, my 10 years of experience with Access teaches me that it's best not to use spaces in my field names. Why? You'll appreciate why when we start doing programming, because if you get spaces in your field names, referencing these fields in Visual Basic code becomes a nightmare. You've got to remember brackets around everything, and it just becomes a pain. So we're not going to use any spaces in our field names. The capitals are just to make it easier to read. You don't have to capitalize if you don't want to, but again, I recommend you follow my convention. Camel case.

Tab over to DataType. There are a bunch of different kinds of data types. We'll talk more about them in just a minute. But if you click on this little down arrow here to drop that box down, you can see you get text and memo and number and date/time, currency, and a whole bunch of different types of data.

Essentially, just to give you a quick rundown right now (we'll talk more in depth about these different data types in a little bit and in much more detail in the next class), text is basically any printable character, A through Z, 0 through 9, all the wacky stuff, dollar signs and decimal points, and all that crazy stuff. So pretty much anything can be a text value. Text fields can only be up to 255 characters long, though. Sometimes you have a need to store more than that.

If you have to put long descriptions in, then we have memo fields. Memo fields, essentially, can be very big—thousands of characters, in fact. However, they can take up a lot of space in a database, and there are some limitations. So you don't want to overuse memo fields. Use them, they're OK to use, just use them sparingly.

Numbers are for counting numbers: 1, 2, 3, 4, 5; negative numbers, all right, integers. And you also have floating point numbers or decimals. We'll talk again more about these in a bit.

Date/time fields, special type of field that's set up for storing dates or times or both. So you can store like January 1, 1980, or you can store 3 pm, or you can store a combination of that. Again, we'll talk more about dates and times in a bit.

Currency fields, currency data types, are specifically set up for storing dollar values. Use currency instead of numbers if you're working with money.

AutoNumbers are cool. They're a special type of number that starts at 1 and just counts up. They're great for ID codes. We'll make our customer ID in just a few minutes. We'll use an AutoNumber. So we don't have to worry about making sure everyone has a unique customer ID, or an order or an invoice number.

Yes/No values are basically yes or no, true or false, OK. Is this person a customer? Yes or no. For example, say you're doing a nursing home, and you have "is this a patient," yes or no.

Somewhere at the bottom, as there always is, there's OLE objects; basically that's for object linking and embedding. You can cut and paste pretty much anything as an OLE object. These can be pictures, but some of them are used for pictures or sound clips, but you can put anything in there. You can make a table and store an Excel document right in one of the fields as an OLE object, and we'll do this later if we have time.

Hyperlinks are basically those blue things that you can click on on websites, the underlined blue text. Those are hyperlinks. They jump you to a web page or to a different file.

Lookup wizard is an evil wizard. It lets you look up a value from another table. We're going to do this with a thing called a combo box on a form. So we're not going to use lookup wizard; evil wizard.

So out of all these different types of data, which one do you think best fits first name? Well, text, probably. Let's pick text. I don't have to tab. Now I'm kind of jumping ahead a little bit, but on the bottom down here with this field size, that's how many characters are going to be in that text field. We'll talk more about field sizes in the next class, but just keep in mind that you get up to 50 characters for that first name. So it's kind of a lot.

And yes, it is wasting some space. We'll hold on to that next week.

Description is kind of for you. Yes, it has more uses which we'll delve into deeper later, but essentially description is kind of for you. So you can put in here like "customer's first name" if you want to. Yes, that will show up on the form sometimes, so don't put anything nasty in there. But you don't need to use descriptions.

Just as a recap, someone's asking in the chat, can I repeat all of the objects? OLE stands for object linking and embedding. Essentially, if anything in Windows can be cut, copied, or pasted, it can be a sound clip, it can be a picture. Usually, you use these for pictures. You'll see when I put an employee picture in the table—store the picture in the table. But you can put basically anything in there. You can put a Word document in there. You can put an Excel spreadsheet in there. You can drop a whole other database into an OLE object field. We'll talk more about OLE objects in a future class, like class six or seven. So basically storing stuff in your table.

LastName. OK. Text field again. I'm going to skip the description. If you want to type them in...

Now, what about Mr., Mrs., Miss, a title? What about a middle initial? What about a suffix, Jr., Sr., that kind of stuff? Can you put these things in here? You certainly absolutely can. It all depends on how much detail you want. Me personally, in my customer database, I've got first name and last name, that's it. I don't really care about anything else.

If you do a lot of mailings and you want to use all that information, that's great. I strongly suggest you break it down into each specific field. If you want to put title in there, make a title field, which will put the Mr., Mrs. in. If you want a suffix in there, make it a separate field. Don't just pack it in the last name.

I'm not going to bother with them for class. Just going to try to keep the tables nice and small. But you can put all that information in there if you want to. It is much, much easier to break it up into separate small fields up front than it is to try and split the stuff apart later. So, when in doubt, break it up in more detail.

Like the address. We'll do in a minute. I'm just going to do address, city, state, zip. Some people break it down into more fields than that. You can break it down into street number, street name, address one, address two, right. There's a lot of detail. Use as much detail as you think you're going to ever need.

So I've got first name and I've got last name, that's pretty good for now. How about company name? CompanyName. We'll do that text as well.

PhoneNumber. Now, you'd think you want to store this as a number, but I'm going to store it as a text field. Why? Well, basically, text is easier to deal with. Numbers can be a pain. My litmus test is: am I ever going to need to be doing calculations on this value? In other words, am I going to sum up the column of phone numbers? Probably not.

So some things, even though they're mostly numbers (or all numbers, even), are easier to store as text fields unless you're going to be doing calculations on them. Think of it like this: like I said, I'm never going to be taking an average of a column of phone numbers, no. Phone numbers are just for looking up their phone numbers.

Social Security number is another good example. Yes, they're always numbers. But are you ever going to be finding out the average of a column of Social Security numbers? No, you're not.

Are you ever going to be adding a bunch of zip codes together, for example? No, you're not.

So those things—store them as text fields. Numbers are for things you really want to use to calculate values. Also, you might have a phone number that's like 1-800-FLOWERS. I don't really want to have to stop and say, you know, after 3, type in a 3, L is 5, a 5, and have to type in 800-FLOWERS.

Zip codes, for example. Obviously, for Canadian zip codes, you need to have letters in there as well. But zip codes can also have a leading zero. Some zip codes start off with 0-something-something, so if you have just a number type, you're going to lose that zero.

So phone number is a text value.

Address. How about city? Now, you can do state, or I'm seeing a lot of places now call it region because you might have a state or province, but for the most part, I just leave it as state. I'm going to assume that the vast majority of your customers are in the United States. If not, you'll have to adjust your database accordingly. Just to keep it simple again for class, I build it assuming you have some in Canada—sorry—but if you get a lot of overseas clients, then you might need to adjust accordingly.

City, state, zip—text as well.

How about a note field? Let's make that memo. Notice I just typed in M without actually dropping the box on it; it puts memo in there for me. So if you know your data types, you can just type them in.

How about number of employees? OK. Let's make that a number. I'm going to make that a number because I might want to do some calculations on employees. I'm going to add together all the employees for all my customers in New York, for example.

How about customerSince? Let's make that a date/time field, so I can see how long they've been a customer for. Scroll down a hair here.

CreditLimit. Currency. How much money can they spend?

Here's one—Active. I don't like that. Yes/No value. Here's one where you might want to put a description in. Because what does "Active" mean? Does it mean the customer exercises a lot? Active means that he's an active customer; he's made a purchase in the last six months. So, for the database purposes, let's say "purchase in the last six months," so people know what Active means. It's an active customer; you have to close inactive customers.

Now, we've got all this good stuff in here, and it looks fine, but we really don't have a way to uniquely identify each customer, do we? And databases like that.

Databases like to be able to uniquely identify things: people, products, orders, they like things that are not ambiguous. If I asked for John Smith's record, the computer's going to ask, "Which one?" So we need some kind of an ID, some kind of a tag, so we can put in here some kind of a field that uniquely identifies each customer.

Let's put in a CustomerID and let's make that an AutoNumber. It'll start off with the first customer at one and every new customer will get assigned a new number.

Can you reorder these numbers later? No, you cannot. If someone's assigned an AutoNumber, that's their AutoNumber for life. But, I mean, it really doesn't matter. AutoNumbers are pretty much used for database tracking internally. So, you cannot change them, but I've never really had a need to.

Now, I like to have the AutoNumbers at the top of my table. That's just me. It's a Rich thing. It's a preference. I'm going to move this up to the top. I also do it in this order so that I can show you how to move fields around. Yes, I have evil tricks that way.

So, what I'm going to do is take my mouse and I'm going to click—see how if I move over this gray box here, I get an arrow that's pointing to the right—I'm going to click, that'll highlight the row, let go of the button, don't hold it down. Now I've got a white pointer arrow and I'm going to click and drag up toward the top of the table. There we go. Drop that right up to the top. I'm going to move stuff around here.

Now, let's save our table. Let's hit the floppy disk up here to save our work. You only have to save tables after you make design changes. The data is actually stored as you type it, as you move from one record to another, it saves the record. So, you don't have to keep saving it.

Let's call this our CustomerT for table. I like to end all of my tables in T, I like to end all of my queries in Q, my forms with F, and my reports with—take a guess. The reason why will become apparent in a week or two, and you'll see why—I don't want to go into the explanation now, but just trust me, end all your tables in T.

There is no primary key defined—is the next message that comes up on my end. What is this? Well, essentially, a primary key is the one unique field in your table that can't be duplicated, and that is, again, unique for each record. Now, we know that it's the CustomerID, but Access doesn't know that yet. We haven't told it yet.

So it says, "Do you want to create a primary key now?" Say yes. Now, the reason why I let it give me this error message is because I never remember to set the primary key myself. Notice now the little key next to CustomerID. You can do this manually by clicking here and then clicking the little key button, but again, I always forget to do that, so I let Access remind me.

These are the chat rooms I asked: can you specify the parameters for the number of the AutoNumber field? Not really. AutoNumbers are going to come in as long integers. Let me scroll down so you can see this here.

You basically get one of the two choices: you can either get incremental numbers or you can get random numbers. You really can't change them, so you can't specify a list of values like "start at 500" or whatever. So actually, for example, if you want to inflate your AutoNumber to start off at like 1000, for example, you can just create 1000 blank records and then delete them, and then the next one will be 1001. So there are some tricks you can play, but for the most part, no, you cannot really do much with AutoNumbers.

But they are very handy and they do work well and we're going to have an AutoNumber for each table.

Let's close our table down. Customer table is all set and built and ready to go and ready to receive data.

Let's go ahead and let's put in some data. If you're following along in the book, we're going ahead with lesson five, entering data. Let's open up this CustomerT. We can double click on it here or you can click on it and hit Open. This will open your table up in data entry mode, which kind of looks like a spreadsheet.

Now we're on CustomerID. It says AutoNumber. That's because it hasn't been assigned anyway yet. Let's hit tab. Let's move this over to first name. I'll type myself in, Richard. Notice as soon as I start typing in, it assigned an AutoNumber, that's my number. Number one. I'll have it for life.

Tab. LastName, Rost. Tab. CompanyName. Tab. Amazon. Tab. Phone number. Now, bear with me. I want you to type in the phone number as just 10 digits: 7168374685. What about parentheses and dashes and all that? We'll deal with the punctuation later. We only want to store the numbers in the table. I will show you how to format that properly in the future.

Tab. Address. I'll put the PO Box 1308. Tab. City: Amherst. Tab. New York. Again, two-digit abbreviations. I'll show you in the future how to limit that so they can only type in two digits. Zip code. Do you want to put the plus four? That's up to you. Notes. "Solid guy." You can type in pretty much as much as you can in that note field. Yes, there is a limit; we'll deal with that later.

Employees, notice how the default starts off at zero. Let's type in, let's put in one. Customer Since. Now, Customer Since is a date field. So, you can't just type in 1994. It's going to yell at you, "It is not valid." You have to type it in as a valid date, like 1-1-94. Or you can type it—there's many different ways you can type it. You can type in Jan 1 1994, and it'll convert it for you. You can type in 5-Jan-94. It'll convert it for you. So it'll accept a lot of stuff, but it's got to be a valid date.

I strongly suggest you stick with 1-1-94.

Now, a note on two-digit years. Anything you type in that is 00-29 is going to default to 2000-2029. But if I type in 1-1-02, it'll assume 2002. Anything you type in from 30 to 99 is going to assume 1930 to 1999. So that's going to happen—1-1-31—you get 1931. So be careful. Let's say you run a geriatric hospital, and you're used to typing in birth dates like 1-1-28, you get 2028. So be careful.

1930 is a cutoff year. That's the thing you can change in Windows, not in Access. If we have time at the end of class, I'll show you how to do it. It's in your regional settings on your Control Panel.

So let's flip it back to 1-1-94.

Credit limit. Now, it's money I'm allowed to spend in the store before I get yelled at. It's $5000. Active. That's a Yes/No value. You can change the box with your mouse by clicking, or you can use the space bar on the keyboard. I hate having to stop and grab the mouse.

Let's take a moment right now and just put a couple of extra records in. If you're just following along with the live session, you can just sit back and watch. If you're following at home afterwards with the archives, you can go ahead and stop and put in the records that I put in, or you can type in your own.

I'm just going to put in four or five different records from different states so that I can have some different values to build queries on.

So let's put in Joe Smith with XYZ Corp. Here's his number. 1 Main Street, Buffalo, New York, 14222. We'll skip the notes. Ten employees. Customer Since 1-1-95. He's got a $150 credit limit and he's active.

How about Bill Williams from 123 Inc? His area code is 512. He's at 5 Bull Avenue in Steerville, Texas. I have no idea what the zip code is for Texas, or if there even is a Steerville. Maybe someone from Texas can straighten me out on that. I've actually lived in Texas for two years, so I don't know if I'm making it up or not. I wasn't in Texas for a little while. He's got a $1000 credit limit. Let's put in two employees.

Let's put in Amy Smith with Smith Co. I'll give her 100 employees and a $250,000 credit limit. We'll make her not active.

And one more. All right, so we're all set. We've got our sample customer records.

Do we need to save anything at this point? Nope, we haven't made any changes. Actually, we have made some changes, but I don't think I'm going to make any more.

Sometimes, if you do things like—next thing I'm going to show you is how to rearrange things a little bit here—if you want to make these columns narrower or wider, you can just click by moving your mouse to the little border between the column headers, and just like in Excel, I'm resizing these, and I make the customer name a little wider. If you do things like that, when you go to close the table, it's going to say, "Do you want to save the layout changes?" You just made some layout changes, which aren't really important. It just tells you how narrow or how wide the columns are, their order, and stuff like that.

So that's putting data into our database. And there's a whole ton of other things we're going to talk about later, like we can sort that and filter this stuff. We'll get to that a little bit later. But for now, that's how you put data into a database. Any questions, drop them in the chat room.

Moving on to lesson six: building a query. Now, we've got all of our customers in a nice customer table. I only got five; let's pretend we got 50,000. And, of course, that's more difficult to manage than just five.

Let's say I want to get a list of these customers sorted alphabetically—last name, first name. Now, in the table, they're in no particular order. With a query, we can sort them. Yeah, you can sort them in the table too, but trust me, use the query for now. You'll understand why in a few weeks.

Let's click on Queries and then New. Again, we're given a slew of options for building queries. There's Design View and Query Wizard—all the Query Wizard for now are evil wizards. We're not going to use them. Although the Crosstab and the Find Duplicates are really pretty cool. If we have time, we'll go over them, they're optional topics, but they're not something that you really use in everyday life.

Part of what I'm going to do here with this course is show you 90 percent of everything you need to build effective databases. Something like a crosstab query is useful, but you might never need to use one, nor do you really know what one is at this point. If you don't know what it is, you don't know what it is. I'm just kidding.

Let's go to Design View. Now, in the background here, you can see I'll move this out of the way, you can see here's a query. It's blank right now. The first thing we have to do is put tables into our query. You tell the system what tables this query is going to be based on. Right now we only have one table, so let's make sure CustomerT is highlighted and then click on Add. When you do that, you'll notice that the customer table appears in the background here. Now we can close this little "Show Table" window.

Here we go. Here's our query. There we have it. Essentially, what we're going to do is take the fields from the table up here that we want in the query and just bring them down. For example, I want a list of customers sorted last name, first name. Pretty basic. So let's grab our fields. I need first name; click on it, hold the mouse down, drag it and drop it down here on this first column.

There we go. Let's get the last name right here. Click on it, hold down, drag it, drop it right over here on the second column. Boom. There's our query.

Now let's run the query and see what we get. Right up here in the toolbar, there's a little exclamation point. That's run. Access developers sometimes call that the bang. I don't know why, but they do. Click on the run button and there's the query. Now, it's not sorted, but that's what we asked for. We asked for first name and last name.

Now, how do we sort this thing? Let's go back into design mode. Right over here is a little T-square, upper left hand corner, design mode. How do we sort these things? Notice down here—field (that's the field name), we've got the table it comes from (because you can have multiple tables in here, we'll do that in the next class), and then down below that you've got sort. What does sort mean?

Let's click in sort here, drop the box down, we've got ascending, descending, and not sorted. All these default to "not sorted." But we want to sort these, so let's click on ascending. Now, remember, this is very important. Ascending has nothing to do with the back of a donkey.

So let's pick ascending. We want to see what we get. I'm going to click on our run symbol back up here again. It's sorted—it's sorted by the first name field: Amy, Bill, Joe, and so on. Now, that's good, but we want to sort by last name.

Notice one thing real quick: if any of you are used to Excel, remember how in Excel you have to remember to highlight everything or else it will scramble your names and stuff? The good thing about Access is you just pick what field you want, and it will keep the record together. That's a benefit of the database. Don't worry about the record being separated.

Let's sort the last name. Let's go over here in the sort under last name. Let's pick ascending there. Now let's run the query. No, it's still sorted the old fashioned way. What's going on? The thing you got to remember with Access is, unfortunately, in queries, the sorting goes from left to right. So if you want last name to be sorted first, you have to put it to the left of first name.

Now again, I do this on purpose just to show you how to move these things around, because you're not going to get queries perfect the first time through, so you need to know how to do this stuff. I'll move last name to the left of first name. How do you do it? Notice this gray bar right across the top here? See this little gray text box? As I move my mouse over, I get a black downward-pointing arrow. I'm going to click at that spot and it will highlight the entire column. Let go. Don't hold your mouse down. Notice now that I have a white pointer. I'm going to click and drag to the left now. OK. Let me do that again. Let's say I want to move it again: click, let it go, click again and drag. That's how you move things around. You can also click and drag this way, back and forth.

Now let's do "far"... Now if I run my query, there we go. Sorted by last name and then by first name. That's the benefit. Notice I've got ascending here, and I've got sort ascending in the first name as well, so it'll sort by last name and then by first name. If I didn't have this sorted here, then the first name wouldn't be sorted at all.

So there we go. We got our first basic query under our belt. We've got it sorted. Let's save our query now. We're at lesson 6.9. Let's save the query. I'm going to ask for a query name. I'm going to call this my CustomerQ. No spaces. Q on the end. Hit OK, and now save. Now let me close the query out.

What's the benefit? Well, again, like I mentioned earlier, you can go into a table—here's our customer table—and yes, you can do sorts in the table. For example, here's the A to Z sort, the ascending/descending buttons right up here. I can click on first name and hit the sort button, or last name and hit the sort button. You can sort your table if you want to.

But, number one, you got to come back on the table and do it. Number two, you got to know what you're doing. You have to come here to click on the field and hit the sort button. Number three, you got to come back to the table. That's bad because you don't want your end users playing with your table directly. In fact, I'm going to teach you by the end of this course how to make it so they can't mess with your table directly. You only want them to play with the things that you give them access to, because if the users have access to your tables, you're going to start losing stuff.

What you want to do is you want to build them a query, you can say, "OK, just go run the customer query, and you'll get the data that you need." There it is.

That's the benefit of it. Now this is a real simple query, but think about it—let's say I want a sorted list of customers all from New York with credit limits over 2000. You can build a query and then with one click, boom, there it is. You don't have to know what it's doing, you don't have to go in there and mess it up.

Now, let's say the boss says, "OK, that's good, but I want phone numbers on there too. I still want the original query—don't lose that one—I like the sorted customer list, but I want another one. I want another query that also has their phone numbers on it so I can call full."

Well, we don't want to lose the original query, so let's make a copy of it. How do we do that? Right click on it and go to copy, then just right click out here somewhere and go to paste. It will ask for a query name. Let's call this CustomerPhoneQ. You can copy and paste however you are comfortable. Now I'm assuming you know how to copy and paste. If you don't, here's a real simple example with Notepad: here's Notepad, we got "Hello, my name is Lava." If you highlight something like "name," you can right click on it. There's cut, copy and paste. You can cut it out. You can click somewhere else, right click and paste. You move it around. All we did was we made a copy of it: right click, copy, right click, paste, then we got another one.

If you're not familiar with how cut, copy and paste works, go to my site and watch our Windows Basics courses, and we'll explain cut, copy, and paste in detail.

So now I got a copy of my customer query. Let's open it up in design mode. There we go, look exactly like the other one. Let's add phone number to it. I'll come in here, oh, here's PhoneNumber, and it will pop it down in the next available column. Let's run the query again. There we go. We got the same first name and phone number. It's amazing, isn't it?

Now, let's say the boss says, "That's good, but"—again, pretending we have 5,000 customers in here—"I only want to see the customers from New York." The boss says, "I don't want to see all these 30,000 other customers." Well, in order to limit the people in here by something like state, we have to add the state to the query. So let's find State. There it is. I'm just going to scroll over so you guys can see. You can go right, it's pretty much as many columns as you want to. Let's bring State down here. Let's run the query. There we go, we got all of our states. Now we got one from Texas.

I want to limit these out so I don't see anybody but people from New York. How do I do that? Notice down here at the bottom, one of these rows says Criteria. Let's click in Criteria and type in "NY." Now if I hit tab or enter, notice how it puts quotes around it. That's fine. Don't worry about that. We'll talk more about that later.

If I run my query now, there we go. I'm only seeing customer information from New York. If I want to see customer information in Texas, I can get rid of that and put "TX" in there. Run it. Now there's just customer information in Texas.

Melissa is asking what is the * up here in CustomerT. I'm going to talk about this in class three. Basically, if you want to bring all of the fields down from the table into the query, use the *. You can't do certain things like limiting on state. We'll talk more about that later. Good question, though. We will use that * a lot as we get more into queries.

Now, let's say I also want to limit it based on customers that are active. Well, let's find Active. Here is Active. Bring it down to the query. Let's set this back to New York. If you look right now, I've got a summary, so you can answer. I've got three that are active and one that's not, so I want to limit it out, so Active has to be yes.

Now, there are special keywords you can use. Notice how "yes" doesn't get any quotes around it. That's because I actually picked the yes/no field and they use "yes." You can use yes or no. You can use true or false. Those are also keywords. You can use on or off. You can use zero or not zero—that's tricky, we'll talk more about that later—but basically anything that's zero is also no or false.

Now, if I run it, there we go. I only got New York and active. Actually, that would be kind of tough to believe that data out of the database just using a table. So that's what you can use a query for.

Let's say I also want to limit it based on companies that have more than, let's say, five employees. Come down in here again. Let's make some room down here. Scroll to the right. Let's find NumberOfEmployees. Bring that down. Now Criteria, I want this to be more than five employees. So I'll say greater than five. More than five employees. There's a little table on the book on lesson 8.3—greater than or less than. Greater than or equal to looks like this: >=5.

If I run it now, I'll only get two people. There's Employee, let me shrink this up here. Sorry. So I can continue to read data out of my database.

The boss says, "OK, get rid of that Employees thing, we don't need the numbers of employees." How do you delete a column out of here? Just simply click in that gray box that I showed you earlier, then hit delete. Goodbye. That will get rid of a field off of your query.

Now, boss goes back and says, "Hey, this is great, I love this, this is beautiful, but we have offices in 13 states, so I need one of these for each of our offices." So that means you've got to make 13 copies of this. Wrong. Wouldn't it be nice if we could say right here under State, instead of specifying New York, I would like to say, ask the user when he runs the query what state he wants? That'd be great.

Well, here's how you do it. Inside of square brackets, type in whatever prompt you want, like "Enter the state." Whatever you put inside of those square brackets is your prompt.

What do I mean by prompt? Watch this. I'll run the query. It asks me, "Enter the state." I'll type in NY and hit OK. There's my New Yorkers.

Let me save this query and close it. Now Joe in the mailroom needs to run a report for the Texas customers. He can come in here and run the CustomerPhoneQ query, and it'll ask him "Enter the state." I'll type in TX and there's the Texas customers. So now my query is a little interactive. Now I don't have to rely just solely on specifying the criteria myself. I can let the user specify the criteria. You're on your way to becoming a developer.

We are at a great spot to break for the day. It's almost 1 o'clock, and the next lesson is lesson 10: Data Entry Forms. Feel free to read ahead if you want to. We'll cover these first thing in our next class.

People always ask me, "Why are there only six outlines and there are eight sessions for this course?" Well, basically, when I... This course was designed to be taught in my training room, and I teach it as six three-hour classes. Doing it in one hour sessions on the web, I need a couple extra sessions. Usually, I can actually cut it in six classes, but I let the advanced stuff take extra time because, you know, there are questions or whatever. Classes 1 and 2 almost always run long. Classes 4, 5, and 6 usually run short. So we'll make it up to eight classes; things tend to be about what the last session took.

If you have any questions, please feel free to email me, [email protected] or [email protected]. I will be in the chat room here for about another 10 minutes or so to answer questions if you have them.

I hope you've enjoyed today's class. We'll be back next week, same time on Friday at noon Eastern. We're going to cover data entry forms, we'll build a basic report, and we'll get started on our contact manager.

Quiz

Q1. What is Microsoft Access primarily used for?
A. Creating presentations
B. Building and managing databases
C. Editing videos
D. Designing websites

Q2. Which of the following is NOT one of the six main basic components of a Microsoft Access database?
A. Tables
B. Queries
C. Macros
D. Slides

Q3. What is the purpose of a table in Access?
A. Storing image files only
B. Organizing data in rows and columns
C. Printing database reports
D. Running VBA code

Q4. Why are queries useful in Access?
A. They store non-editable data only
B. They allow viewing and manipulating data in various ways
C. They back up databases automatically
D. They encrypt the entire database

Q5. What is the main advantage of using forms in Access?
A. To create graphics
B. To provide a user-friendly way to interact with the database
C. To export tables to Excel
D. To sort reports

Q6. What are reports in Access mainly used for?
A. Data entry by users
B. Printing formatted output from the database
C. Storing web pages
D. Adding new fields

Q7. Why does the instructor recommend NOT using spaces in field names?
A. Spaces take up too much storage
B. Spaces make referencing fields in Visual Basic code more difficult
C. Access does not support spaces in field names at all
D. Spaces disable queries

Q8. What data type should you generally use for a phone number in Access?
A. Number
B. Text
C. Currency
D. Date/Time

Q9. Which data type is best for storing large open-ended pieces of text?
A. Memo
B. Number
C. Currency
D. Yes/No

Q10. What is the function of an AutoNumber field?
A. Stores currency values
B. Links tables together
C. Automatically generates unique numeric values for each record
D. Encrypts confidential data

Q11. What is a primary key?
A. A field that contains only text
B. The field used to uniquely identify each record in a table
C. A printed version of a query
D. A type of macro

Q12. What benefit does the instructor mention about using queries instead of directly sorting tables?
A. Queries are always faster
B. Queries allow end users to access data without risking changes to tables
C. Tables cannot be sorted at all
D. Queries automatically back up records

Q13. What happens if you use a two-digit year between 00 and 29 in a date field?
A. It defaults to 1900-1929
B. It is invalid and not allowed
C. It defaults to 2000-2029
D. It erases the record

Q14. How do you prompt a user to specify a criterion in a query (e.g., for "State")?
A. Type the value directly in the criteria box
B. Enter a prompt in square brackets in the criteria row
C. Use a macro to prompt the user
D. Create a custom form for criteria

Q15. If multiple sorting fields are used in a query, in what order does Access sort?
A. From rightmost field to leftmost field
B. In the order the fields are listed from left to right
C. Alphabetically by the field name
D. Randomly

Q16. Which convention does the instructor recommend for naming tables and queries?
A. Tables with _Tbl, queries with _Qry
B. Tables with T at the end, queries with Q at the end
C. No special convention
D. Naming all objects with numbers only

Q17. What does the Description field in table design view usually serve as?
A. A place to enter field formulas
B. A note or description for the developer or users
C. A macro location
D. A sorting indicator

Q18. When should you use the Currency data type?
A. When doing calculations involving decimal numbers
B. When storing phone numbers
C. When working with monetary values
D. When recording dates only

Q19. What happens if you delete a field from a query in design view?
A. The data in the table is lost
B. The field is removed from the query results, but not from the table
C. All queries become unusable
D. The form interface breaks

Q20. Why does the instructor recommend laying out the database design on paper before building it in Access?
A. It saves money on software
B. It ensures a clear plan for tables and fields, reducing the need for changes later
C. The Access wizards require a written plan
D. Paper designs are faster to implement

Answers: 1-B; 2-D; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-A; 10-C; 11-B; 12-B; 13-C; 14-B; 15-B; 16-B; 17-B; 18-C; 19-B; 20-B

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

Summary

Today's video from the Microsoft Access Learning Zone covers the basics of getting started with Microsoft Access, which is an excellent database program for small, medium, and even large businesses. One of the key advantages of Access is how well it integrates with more powerful back-end database servers like Microsoft SQL Server or Oracle, while also being a robust, standalone database on your desktop.

In this session, I want to begin by explaining what a database is and how Access allows you to store, manage, and present information using various database objects. Access enables you to create a visually appealing interface for working with your data, using queries to pull and display specific information in different ways. For example, I've got a sample database open from a previous class, displaying a main menu and several forms: a customer form for tracking customer details and buttons to open related forms like a contact form or an order entry system. In fact, we'll spend a good chunk of time in this course building a complete order entry system where you can select customers, sales reps, products, and process orders in a streamlined way. We'll also cover how to create reports like invoices and summaries that you can print and share.

Let's break down the core components of an Access database. The six main database objects in Access are tables, queries, forms, reports, macros, and modules. There's also a feature called data access pages, but those aren't especially useful and I don't recommend spending time on them. Here's why: data access pages were designed for web integration, but in practice, they don't work very well, so we'll set those aside for now.

First, let's talk about tables. A table in Access resembles a spreadsheet with rows and columns, where all your essential data is stored. Over the course of the class, we'll be building different tables for things like customers, contacts, employees, orders, and more. However, tables alone are not a practical way to find specific data when you're dealing with tens of thousands of records. For example, if you need a list of customers from New York with a credit limit over $500, searching a giant table manually would be tedious.

That's where queries come in. Queries let you search for and manipulate data. You can use them to extract specific lists (like sorting customers by name), summarize information, or even update and change data, which we'll discuss in depth in later classes. While tables and queries are the foundation of your database, their default view is still pretty technical and not especially user friendly.

To make things easier for yourself and, most importantly, for other people who may use your database, you can use forms. Forms transform the data into a user-friendly interface, giving you the tools to include things like buttons, dropdown lists, and even some Visual Basic programming. I run my entire business using Access, and the flexibility and power of forms are a big reason why. They're especially valuable if your users are not computer experts, as forms simplify their interaction with your database.

Reports are another critical object, especially when you need to print out summaries or structured documents, such as credit limit reports or invoices. Essentially, think of reports as attractive printouts drawn from your data, while forms are for on-screen interaction.

As for macros, these are tools for automating repetitive tasks, like opening a form or printing a report. However, they're largely being phased out and exist mainly for compatibility with older Access databases. In modern database solutions, you'll almost always use VBA modules instead. Modules let you write Visual Basic for Applications code to automate powerful features and custom logic. You don't actually need programming skills to build a solid database, and we don't really get into programming until after you've got the basics down in Class Four.

For those new to database design, I strongly recommend planning on paper before you start working in Access. Sketch out what kind of information you want to track, who will use the database, and what their technical skill levels are. List all the subjects you want to store information about: customers, products, orders, and so on. With your list of tables, you can then break each table down into the specific fields you need. For example, for customers, fields might include first name, last name, company, phone, and so forth.

When designing tables, think carefully about breaking information into meaningful fields up front. This makes your database easier to work with later. For instance, if you want to store someone's full name, it's much easier to split that into first name and last name in the beginning rather than trying to separate them later. The same approach applies to addresses and any other multi-part information.

Let's go through creating a database from scratch. When you first launch Access, you're given the option to start with a blank database or use built-in wizards. My advice is to avoid the database wizards if you want to really learn how things work, because while they give you a database structure quickly, you won't understand what was created or how to maintain it. Building from scratch, although it takes a little longer initially, gives you complete control and knowledge of your system.

When you name your new database, Access will require you to save it at the very beginning, unlike programs like Word or Excel. Once your new blank database opens, you'll see empty lists of tables, queries, forms, and reports, ready for you to populate.

I always recommend watching these online classes in two stages: first, just sit back and watch the live session, then review the archive to pause and practice at your own pace. This way you can absorb the concepts and practice the steps as much as you need.

Let's walk through building your first table. Access offers different modes for creating tables; I suggest always using Design View. This view allows you to define the structure of your table before entering any data, making it easier to specify the field types and properties up front.

When naming your fields, use camel case (for example, FirstName, not First Name) and avoid spaces. This is my own convention, but it's one I've developed over ten years of experience to make programming and referencing fields far less troublesome later. If you have spaces in your field names, VBA programming becomes much more complicated.

Field data types are important to consider. Text fields handle standard characters up to a certain length. For long notes or descriptions that may exceed 255 characters, use Memo fields. Numbers should be reserved for data you intend to calculate with, like amounts, counts, or other numerical values. Dates and times get their own field type, as do currency values. AutoNumber is perfect for creating unique ID fields, so you don't have to manually assign (or keep track of) unique values. Yes/No fields are for true/false or on/off-style information. OLE Object fields can store things like images or documents, though be cautious as these can balloon the size of your database. Hyperlink and Lookup Wizard fields exist for more specialized needs, but for now, stick to the basics.

For each table, create a unique ID field as the first field (like CustomerID using AutoNumber). This ensures every record is uniquely identifiable, which is essential for effective data management. Access will prompt you to set a primary key field when you save your table, which enforces this uniqueness.

Once the table is saved (I recommend naming tables with a "T" at the end, like CustomerT, queries with a "Q," forms with an "F," and reports accordingly), you can begin entering data in Datasheet View. Remember, only enter actual data in fields you plan to use. For example, only type numeric values into fields where calculations are necessary — for things like phone numbers, social security numbers, or zip codes, use Text fields. This avoids formatting issues and maintains data integrity, particularly when you need to store values with leading zeros or letters, or when actual calculation is unnecessary.

As you add data, Access auto-saves each record as you move through the fields, so manual saving is only needed after changing the structure (not the data) of your table. You can also adjust the column widths in your tables for readability; these layout changes are saved if you're prompted.

Next, we move on to queries. Say you want a sorted list of customers by last name and first name. While tables let you sort the visible data, queries give you far more flexibility and keep your raw data untouched. Always use Query Design View rather than the Wizard, since you'll learn and control far more this way.

To build a query, add your table, drag the desired fields (like last name and first name) to the query grid, and use the sort row to specify sorting order. If you want sort order by last name then first name, ensure last name appears to the left of first name in your grid — sorting in queries works from left to right.

Queries also let you filter data. To, for example, show only customers from New York, add the State field, then specify "NY" in the criteria row. You can further narrow results by adding other fields and conditions, such as showing only active customers or those with more than a certain number of employees.

To make queries interactive, you can use parameter prompts. By putting a prompt like [Enter the state] in the criteria row, Access will ask the user to enter a value each time the query runs. This lets others use your database for their specific needs without having to edit queries directly.

If you need to make variations on a query (like adding extra fields or criteria), simply copy and paste the existing query and modify the new version. This allows you to retain the original while extending or customizing functionality.

It's important to keep your users away from the underlying tables — queries and forms provide safer, user-friendly access points to your data.

We're now at a great stopping point. The next lessons will cover building data entry forms, creating reports, and expanding our contact management. Feel free to email me if you have any questions; I'm always happy to help. Don't forget, you can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.

Live long and prosper, my friends.

Topic List

Introduction to Microsoft Access as a database program
Understanding what a database is
Main components of an Access database
Purpose and structure of tables
Building a customer information table
Choosing and naming fields for tables
Data types for fields in Access
Field naming conventions (no spaces, camel-case)
Detailed explanation of key data types (Text, Memo, Number, Date/Time, Currency, AutoNumber, Yes/No, OLE Object, Hyperlink)
When to use Text versus Number data types
Designing tables: breaking down data into fields
Setting up a primary key using AutoNumber
Saving tables and naming conventions (ending with T for tables)
Setting and understanding primary keys
Moving and reordering fields in table design view
Entering data directly into tables
Handling date fields and date entry pitfalls
Using Memo fields for longer text
Formatting and saving table layouts
Creating and using queries for searching and sorting data
Building a sorted customer list with a query
Adding criteria to queries (filtering by state, active customers, etc.)
Adding and removing fields in queries
Copying and modifying existing queries
Using parameter prompts in queries
Saving and naming queries (ending with Q for queries)
Differences between working in tables and queries
Practical examples of queries with multiple conditions (state, active status, number of employees)
Interactivity in queries by prompting users for criteria