Learn Microsoft Access - The Complete Microsoft Access Course for Beginners

1. Welcome to Access for Beginners: welcome to the Microsoft access course for beginners. In this course, I'm gonna teach you how to create a simple database from scratch. And I'm gonna teach you all the ins and outs of access while we're creating that database. But before we get too far into that, let me just start off by answering the question. What is a database and why would you want to use Microsoft access instead of, say, Microsoft Excel? Because I think that most of the people coming to this course are probably going to have some experience with Excel, but have an idea that access might serve them better for what they're trying to do. And if you don't have any experience with Excel, that's fine, too. I really I'm gonna teach this course from the very beginning. But most people who are interested in the scores have probably been using Excel to do things that they should be using access for. So let's talk about why you would use access well. Access is a database. Management software and databases are great for storing towns and tons of information and creating relationships between all of that information so that it's easy to sort through easy to find and easy to use. If you think about the last time you went to the grocery store and they're taking your items and they're scanning in the numbers for each of your individual items, what they're really doing is putting the number for, say, your salary into their store database. And that database is sending the price. The description, any discount information for that salary back to the cashier so they can process your order . And if you're a member of a rewards program and you give the cashier your phone number and they entered into the computer that is also connecting to a database and it's pulling up your information now, whether you like having your phone number in their system or not is irrelevant. The point is they have a database that is storing your buying history, so it has information on how much you spend and the types of things that you buy. And that's all made possible because the grocery store has a database that stores and organizes and relates all of that information, and you'll see examples of databases almost everywhere you go. Any time you log into a bank account, online, where you do a search on Google or on Amazon, you're using some form of a database. Now access is designed to create smaller scale databases. Obviously, Google is using a gigantic database that would not be practical to create with access. But the benefits of access are that it is actually quite powerful. Even though is designed for smaller databases. It's relatively easy to learn. It's a little bit harder to learn access for the first time. That is to learn, say, Microsoft, Excel, but not too much harder. And by the way, by the end of this course, it's going to seem a lot less intimidating to you. And it's also much less expensive than a lot of other database management software. Access is great for doing solutions from the very simple to the very complex. A simple example would just be a contact list. That's a very simple version of a database. We just store all of your contacts and can generate reports that show alphabetical listings with phone numbers and things like that. A more complex database would be an order tracking system that you create for a small business. You can do one time projects. My brother has a great example. He's an engineer, and they track the wear on machine parts using a simple access database. One of the advantages that access has over Excel is that the tables, which are essentially like Excel spreadsheets, are relational, so you can relate a number of different tables to each other, and this gives you a lot of advantages. It makes data entry and analysis quite a bit quicker, easier and more accurate. That saves you time because you don't have to enter the same data over and over. And it also prevents errors, user input errors, and we'll talk about that more in the course. But an example would be if you have an Excel spreadsheet and you're typing in a customer name, one person might type them in a Steve, and the next person might take them in as Stephen. Then, when you try to do a report pulling up all of the orders from Steve, you're going to run into problems. Your misinformation, because it was entered in different ways. In access, you can create restrictions on the way data is is entered and forced them to select from a list of customers. So then your only option when you're entering would be Steve. There is no Steven in the system, so you have to select Steve. So then, when you go to sort your data and sort through your customers to see how many orders Steve created, you don't have those same errors. And that's a really important thing that access databases provide. Now, before we get into actually creating our own database from scratch and access, I want to show you an easy way that you can just dive in tow, access and start using it right away. So we're gonna play around with one of the existing templates that has already provided an access, and this would allow you to go right in, and in about five minutes you could create your own contact database. So whether that's just a list of friends and families, phone numbers and addresses, or whether it's amore complex business contact list, I'm gonna show you how to use one that's already created and then up next in the course will get into creating our own database from scratch. But if you're on the start page here in access and you search for contacts you're gonna see this contacts template. So you click on that and then you're gonna name it and created. Now, you can go through this tutorial. You can watch videos on how the template works if you want, but we're just going to click on Get Started, and it brings us right into our contact list. Now, of course, you'll you'll need to enable the content here. This is Microsoft's way of protecting you if you've downloaded something from a questionable source. But where this is on access template that you could just quick enable content. Get rid of that again. And now we're right into the fully functional database, and throughout the course, we're going to get into definitions of all these different parts of the access database screen. But for right now, let's just look at tables, forms and reports. So let's just see how difficult it would be for us to create our own contact list. Using this database template, we can go in here and we can type in a name. Steve McDonald. The company will just call itself can tape in the job title instructor, and then we can select a category personal. We can add email addresses, phone numbers, etcetera. Let's put it another one. Pub burns, and here we'll put it in his phone number. I'm gonna click down to the next line and let's go explore the forms. Click on contact details. It pops up a form that has all of the details for Bob Barnes. If I want to go enter another one, I can click, save and new, and it's gonna clear it and give me a new form. So let's just put in Aaron Arnold. We'll leave her in personal. She's also self employed as an instructor groups as an instructor. Let's give her a phone number as well. Let's give her a mobile phone to and an address. Of course, you can fill in all the rest of this information, but I'm just using this as an example. So we can either click, save and new, and that will bring us to a new entry where we can just click close and that will bring us back to our list. Now, if I refresh this, it's gonna update all of the information in this list. And there she is, Aaron Arnold. Now, if I go to my directory I now have Aaron included in this alphabetical contact list. And if I go to my phone book, there she is with her business number and her mobile phone. So in this contact database, you can create thes, customizable reports, whether it's an alphabetical phone list or a complete contacts list. And that's something that if it was personal, you could email to your friends and family, or if you're in business and this was an employee contact list, then you could print this out. You could create a PdF or an email attachment, and you could send up to date customizable phone lists to your boss with just the click of the button. And that is only the tip of the iceberg of what you could do, even with a simple access database. Now what I want you to do is click on file and new, and this will take you back to the start page, where you can create a new database from scratch 2. Take a Tour of the Access Dashboard and Ribbon: So when you first open up access, you're going to find yourself on this screen, and this is basically just an entry portal to get you where you need to be. And there are a couple of options here. And, by the way, this is very similar to excel if you've used Excel before, but for the purposes of this course, I'm going to assume you've never used Excel or that you're not completely familiar with it . So you have a couple of choices here. You can go over here and see your recent databases. So this shows things I've worked on today, things I've worked on yesterday, and then you can navigate toe other files that you've say worked on in the past. Or you can created blank database, which is what you'll probably do most of the time when you're starting a new access database. Or you can go into one of these templates. And these templates are not as extensive as the templates that you'll find for Excel, but you do have a number of things you could choose from. For instance, if you want to create a contact list, they have a pre made contacts database you could do a list of students you could do. Event management task management appear we have asset tracking and so forth. This north wind is, ah, order tracking system. And there are also custom Web apse, which allows you to create a database that's accessible through an app so you can share it around and use it on. Multiple computers in multiple locations in this course will be focusing on essentially desktop databases that you use on your computer, which can be shared, but they're hosted on your computer. Now. If you scroll down over here, you can go down to Mawr and Mawr templates. And then if you want to search for a template, you can search right here. But since we're going to be creating a database from scratch so that you could learn how to do that, we're going to just go here and click on blank database, and it's going to get us started now. The first thing you'll notice is, it says, should I create an access app or an access desktop database? That's just a help article that will help you decide which is better again. In this course, we're going to be doing the desktop database, so we will leave it as is and continue. Now we'll name this. I'm just gonna name this practice Davis, and then you can click here to navigate to where you want it to g O on your computer. So if you have a documents folder or a specific access folder where you want to keep all your files, you can navigate to that here and then you click create, and that will bring you into the access program, and you'll notice that it pre populates a table in here to start with. That's basically just a placeholder table so that there's something in the program when you open it up. But what you want to do is just click out and close that. So you're starting with completely blank slate, and they'll come over here to create, click, create and table, and then we'll have a table that's ready to begin now, before I get into creating tables and the other objects in your database, I want to give you a tour of the access ribbon and some of the tools, So this area here is what's known as the Axis ribbon. This is where all your tools are, and you have tabs along the top here that allow you to work with the different ribbons. So because I've created a table, it's created a special ribbon called table Tools, which allows me to do some ad. It's for the table so you can see this allows me to edit fields. This allows me to edit the table itself, and we'll get into that later. But just so you know that whenever you're in a table or object within access, it's going to give you these specific tools that are only available when you have something open. So we'll go back to home so I can show you the more general tools that you'll find. So you'll probably recognize some of these from Microsoft Word. These are just formatting tools you have. Find options so you can search and find things within your table. The's Air tools for Working with your records, which is essentially working with your data entry, these air sorts and filters. This is your clipboard, copy, cut format, painter and paste. And then this is your view, and the view is something that you will use a lot. You can either do the drop down here, and right now we're in what's called data sheet view. This is a data sheet, but the other one is designed viewing. If you click on design view, its first going to ask you to save it was called This practice table Click. OK, and now we're in what's called design view, and again, I'll get into this more as we go and build our first table. But just know that if you either use the drop down here or just click on this, you'll switch back and forth from design view to table view, and you'll go back and forth between the two of those as your editing and modifying and creating tables. OK, so that's our home tab. The file tab is what's going to actually take you back to where you can open new documents , open existing documents, save your documents, print your documents, close access, get into your personal account, leave feedback for Microsoft and explore different options and settings within access. So to get back to the main screen, just click this back button and that puts you back in your database. So we're on the home tab. Let's look at the create tab. This is where you're going to actually create different objects in your database. So I already showed you how to go to create and table, and that's how we created this table. But we can go to weaken, create queries here. We can create forms here in this section. We can create reports here in this section, and then you can do macros and visual basic in this section. Then there's external data, for instance. I mean, the most common would be importing a spreadsheet from Excel that you want to turn into a database. Then you could go to here and you can import an Excel spreadsheet using this link. So this section here is for importing. And then this section here is for exporting as various things, and you can see you can export it to excel. You can export as a pdf and obviously much, many more options. Then we get into our database tools, but you have compact and repair database. You have your macros and visual basics. This offers an alternative way to create relationships, but you can actually create them right within your tables as you're creating them. You can analyze your database, you could move data, or you can go and grab additional Adan's to bring into access. So I already covered the table tools and whatever object you're in they have opened here will pop up a different tool. Then finally, this is the Tell me what you want to do. It's essentially like a help function, although it's a little more effective, so you could just type in what you want to do in access, and it will often bring you right to the operation. Or it will bring it to a help screen that will help you to find the answers that you need. So that's the ribbon. And then we also have the quick start menu here, which allows you to quickly save, undo, redo. And then here you can actually customize the quick access to a bar, and all you have to do is click on any of these things, and you can add them to this toolbar appear. So if you want to have a quick print on here, you just click that and see it adds the icon for quick print, and it makes it quicker to use. You can also go down here to find more commands that you can add to the quick access to a bar. And this is something you do after you've been using access for a while and you start to get a sense of things that you will use frequently and you want rapid access to. So those that's the options there. I'm gonna get rid of the quick print, and then the last thing I haven't showed you here is the navigation pane to the left of your table here, and you can actually close the navigation pane. If you just want to work within your table, you click that again to open it. But this will show all of the objects in your database, and you can organize these in a number of ways. But generally you'll have, like tables, queries, forms, reports. And then you'll have all of the tables that you have listed of all the reports, all of the queries, all the forms, and it'll all be here so you can just quickly click into these and bring them up on your main screen. And you can use this button here to sort your navigation pane so that best suits the project and allows you to find things more easily, so that's a quick tour of the access interface and the ribbon and tool bars. 3. Learn About Access Objects: Tables, Queries, Forms and Reports: in this lecture, I'm going to give you an overview of the database that we're going to be creating together in this course. We're gonna be creating an employee contacts database, and I'm going to show you some of the pieces in parts of it in just a minute. But first, let me explain one very important part of access and that is access. Objects and objects are basically the building blocks of your database. You can think of it sort of like a car. If a car is your database, then the objects in your car would be the wheels, the axles, the body on the engine. Well, in access the parts of our database are tables, which is where all of your data is stored. These will look familiar to you if you use Excel because they look a lot like spreadsheets , queries and the definition of a query is a question. So essentially, these are questions that you ask about the data in the tables, and we designed knees to do certain searches essentially to bring back data that's held in our tables. Then we're gonna look at forms and forms. If you think about our cash register analogy, the form would act as the interface for the employees. Sometimes forms are used to display information. For instance, when the grocery clerk scans or types and your phone number in a form, it will display the information but forms. Air, primarily used as an interface for entering data and then reports, is kind of just what it sounds like. The document used to display information from your database so you can think of reports as something that you would print out or save and send to your boss. So your boss says, Hey, I want a phone number list of all of the employees in the region. Then you could create a report from your database with the phone numbers of all your employees and send that to your boss, and we're gonna create a database step by step using all of these objects. Now there are a couple of other objects included in databases, and those are Mac rose and modules. But we're not going to get into those in this course because that's a much more advanced topic. So now that you have a basic idea of what these building blocks are, that we're going to use to create this database. Let me just show you a couple of them. So you know what they look like. So tables, like I said, are basically look a lot like spreadsheets in an Excel program. You can see that we have columns with headings. So we have our employee I d. First name, last name, home phone and office. I d. And then we have different rows with the entries. So here we have Employee number one is Bob Blake. Here's this phone number and he works in Boise. Now, one distinction is that in access, the headers across the top are called fields. So this is your employee I d field. This is your first name. Field. This is your last name. Field. This is your home phone field. And this is your office. I d Field and the rows are actually called records. So this record has employee number one Bob Blake, his phone number and his office. This record is for stand ford and so on. So the columns air called fields, and the rows are called records s. So that's gonna be our main table that hosts the information for this employee contact database. Okay, I'll close that and let me show you the one other table that we're gonna create. And this is a list of the offices where these employees work. So it has the office i d. As the city, state of the office and the work phone number for that office. We're going to connect these two tables so that we can pull this information from here into our employees table. As you see here, you see, the office location is listed here so that you can start to get an idea of how you can create relationships between tables, which is where the real power of access begins. And actually, in our final table, this office I D Field is just going to show the office i d number. And then it's going to reference the office I d table and I'll get into table relationships later in the course. So those are the two main tables that we're going to use that we're going to get into queries, and I'm just going to show you this one. As I said, a query is basically just a question that you're asking of your data in your tables and So in this case, our query is to find out all the employees in the Northwest region. So we have four employees in the Northwest region, and they're listed by their city. The awesome thing about queries is you create a query and you can run that query over and over with just the click of the button. So if your boss is always asking you for a list of employees in Los Angeles, for instance, than if you have a created query that you just click that button and you know who is working in Los Angeles and obviously these air very small, simple examples of a database. But imagine if you had hundreds or thousands of employees, and you really quickly wanted to just get a list of the employees in Los Angeles. You could do that with the click of a button. Of course, if your boss is asking for information from you, you're not going to really want to just send it to him like this because that's not very professional and presentable looking, so you'd want to send it in a report. So if your boss wants to know who the employees are in the Northwest region, you can have a report that you've created ahead of time, and with just the click of a button, you'll have a printable or email herbal report that shows all of the employees in the Northwest region, the city that they work in and their names. And, of course, these could be customized. You can add employee I D numbers, phone numbers and a whole lot more information, and you can spend a lot of time. There's a lot of art that can go into the design of these as well. This is a very simple, bare bones layout of a report, but you can get as fancy as you want with these, okay? Or if your boss asks for a list of phone numbers for all of the employees, then you've got a nice, clean, printable report right there. So if the list of employees has been changing, you say you've added 50 employees and 35 employees quit. But you've been updating that in your employees table here. Then, as long as that is current, when you run your report here, it's gonna show all of that updated instantly. Okay, so that's your reports, and then Finally, we have forms. And as I said, forms are essentially your interface, your user interface. So if you have administrative assistance anyone doing data entry or point of sale interactions they're going to be interacting with your database through forms they're not gonna get into your tables. Inquiries. They're just gonna be a kind of on the frontline here, where they can enter information in the format that you want it to be entered and they have a button here they can click, and that will give them a new record. They can type in the next name and so on, select their office i d. And then they're ready to go. And forms are really one of the things that makes access so powerful because it allows you to keep front and users out of all of your data, which makes all of your data back in the tables more secure, and it restricts the way that they're able to enter it in, and by restricting the way that they're able to enter it in, you reduce a lot of errors, which means that when you do queries and reports, it means that the information that you're gonna get out of your database is going to be more accurate. For instance, if I was able to type in any office I wanted right here and maybe I put wanted put L. A. Right, I was allowed to go in here and just type in L. A. Because I know he's in the l A office, right? If I hit enter, it's going to give me an error. It says the texture entered isn't an item in the list. Select my Adam for the list or enter text that matches one of the listed items. So I have to say Okay and I have to go back and I have to look at the list. Say, Oh, it's actually lost Angeles specifically. Okay. And that's office code number two and you'll see when I put this in, let's just call this Joe Barnett. What if I spell that right? And we've selected Los Angeles and actually think Barnett is spelled without any. So we click saving next, and that brings us to a new blank record. But we can go back to our employees table and you'll see that now we have a new employee list that we have employee number 13 Joe Barnett, his phone number and his office location. But if we were allowed to enter in L. A here, you'll notice up here we have Los Angeles. Here we have Los Angeles. And then if we have L. A here, and we want to do a report showing all the employees living in Los Angeles So we're specifically searching for the phrase Los Angeles, it would have missed Joe Barnett. And it would show our boss that we have to employees in Los Angeles rather than three. And you can see a special on a large scale. What a data disaster that could create in your place of business. So let me close out of that anyway, So that's your forms and forms can get very elaborate. You can have entire navigational dashboards with buttons that can allow the users the front end users to click and navigate around to even different forms in different points of entry , to make it very robust without them ever being able to get into your actual database. Can you know what? I just noticed One thing. I didn't name this right, and that's driving me nuts, so I'm gonna change it real quick, and I'm gonna show you how to do this later. Just gonna change that to employees. Ah, that's better. Okay, so that's the database that we're gonna create. I'm gonna walk you through every step of this and we're going to do it together. And I'm gonna show you everything that I've done so that when you're done here, you're gonna be able to create this exact database. And you'll have a pretty solid understanding of how all these objects works, tables, queries, forms and reports and how the tables relate to each other and how the reports pull off the queries and how the forms enter information into the tables and a whole bunch of other tips and tricks along the way. 4. Create Your First Table in Access: Okay, let's get started creating your very first database in this video. We're going to create this employees table. This is going to be the foundation of our entire database. All of our queries and forms and reports are going to relate back to this table. And I highly recommend, from this point forward and the course that you follow along with me in access, step by step and actually do these steps because that's really how you're going to learn how to create an access database. So what I want you to do is open access, and in this case, I have it open already. So I'm just going to go to the file new and in the new screen, we're gonna choose blank database and we're gonna name it. We're just going to call this employee contacts. It shows me where it's going to put it, but if you want to put it in a specific location, you can click here and browse to that location and find it on your computer. But this is going to my documents, which is where I wanted to go, and then I'll click, create. And now we have a new blank database and you'll notice all of these table objects are empty over here except for the placeholder table here. I'm gonna actually close that out so we can create this completely from scratch. So to create a table, your first table, you're gonna go in and click the create tab and then you'll see here we have the options of tables, queries, forms, reports, macros and code. So we're going to go to the tables section and we're just going to click on table. This allows us to create a new blank table. You'll see. It looks basically just like the one that we had before. So this is the data sheet view. What we want to do is go into the design view and to go to designer. You can get a click on this icon up here where you can click the drop down and you'll see it selected on data sheet view. We can click design view there, or you can also right click on the header there and click designed to you immediately. It's gonna ask us to name the table. We're calling this table employees. Then we click OK, and you can see it's now saved it as table employees. Now a note on naming conventions. The reason I'm putting the table tbl employees there is actually kind of an old fashioned naming convention in previous versions of Access. It didn't do a very good job of sorting your objects between tables and queries and reports , so people got in the habit of naming them with the type of object that it had at the beginning. Access is better at sorting these out, so you really don't need to do that. But I'm showing you that because you'll probably see that out there. In that way. You know what you're seeing. If you want to do, though, it would be OK to just name this employees, but there are a couple of guidelines when it comes to naming objects in access that I do want you to be aware of. So let's go into this. Actually, let's close out of this, and I'm going to go into the rename here because you can rename your objects. So the basic guidelines of naming a table is that you don't want spaces or strange characters. Okay, so we wouldn't want to say employees exclamation point exclamation point worry. We wouldn't want to say employee list with a space in it like that. The reason for that is that if you create a big database and then someone else comes in later to do programming, then when they go to write code, this little space here is going to cause them problems, and they're going to have to do certain things in their programming. That's gonna be time consuming for them and a headache to accommodate for that little space in there. And it's just unnecessary. So the way to do this is if we want to call it employee list, then we take that space out. But we would capitalize the words to make it easier to read if we spell it employee list with Justin, a lower case L. It's harder to differentiate, and it just makes it a little less friendly. So they capitalizing that L. It's still easy to read, but it won't cause a programmer problems down the road. So anyway, we are calling this just employees. It's also good to be consistent with your use of plural zor singular. So if I'm going to use employees here than I would want to use that throughout the rest of my objects for consistency. Or if I'm gonna use employees again, I'd want to use that throughout the rest of my objects. So I'm gonna hit, enter and save that. And now we can open up our table. We'll go back to design view, and we'll get started creating it and what we're doing here in the design view because we're going to put in the fields that we want in our table. And if you recall in our data sheet view, these air, the fields that are going to go across the top of the column so these are gonna be your headers. And if you recall for our employees contact list, we want an employee, i d. We want the employees first name their last name and their phone number and then the office I d. Which we're gonna pull from another table. So we're going to go put the office idea in later. But for now we can go to our design view to put in these different fields and assign some properties to the data that's allowed. So we'll go to the design view. So our first field name is going to be employee I d. And when we have it selected on there, I can just type right over it where you can always if you're clicked out of a cell, you can always click back into the cell to type in it, where you can highlight the field to type over it very similar to the way that Excel works . So we have our field now and you'll notice there's this little key over to the side here, and then you'll see it appears, well. Primary KIIS highlight Now Primary key will become very important when it comes to Mawr Complex databases. Where you have several tables here that are going to relate to each other in this field is going to be a unique identifier for each individual employees that we can then use to connect it to other tables. So just know that this primary key allows us to create a unique identify where for each of the records within this table, and we can turn this off if you want. By selecting that and clicking primary key and you'll see that it's now gone, we'll go ahead and leave it on so that we have Ah, employee I d number for each employee, and then it automatically created the data type of auto number. The data type is essentially the type of data that can go into the employee I D field. So this is gonna be auto number. And this is a really cool thing that access does where each time we enter a new record. So a new name Bob, Sarah, Candace, etcetera. It's going to assign them a unique number. So Bob would be number one. Sarabi number two can be North three and so on, and that's a really powerful way to prevent data entry errors. So if we have to Bob Smith's and one lives in Seattle and one lives in Portland, then the first Bob Smith that we put in is gonna be employee number one and the second Bob Smith that we put in. Is it gonna be employee number two? And that's gonna be really significant later when you're doing different calculations and doing reports based on employee data? Because that's the number that is going to be used to differentiate between Bob Smith, number one from Portland, and Bob Smith, number two from Seattle. So we'll leave. That is auto number and it will put in our second field. And that's gonna be our first name. Employees first name and notice. I'm using the same naming conventions that I used in the table name, so I'm leaving no blanks, and I'm not putting any strange characters in there. And to go to the next cell. You just click tab or enter. And now it's saying short text because access is smart and it recognizes that this is probably a text field, and you can see we have all kinds of choices. Long text is for huge entries short Texas. Up to 255 characters. You can do numbers. You can use numbers if you're going to make calculations on something. And one thing that people might be inclined to do is put for us a phone number in as a number. But you'll notice you don't do calculations on a phone number. You're never gonna add to phone numbers together or take the average of two phone numbers, so actually phone numbers you'll enter a short text. But if you're doing calculations like something you would do in excel, then you can enter this number date and time is pretty straightforward. Currency is if you're using money auto number. We've used above here to create a unique identify for each record. So that's very useful. Yes and no is just like it sounds basically gives you a check box. You could click yes or no in answer to. For instance, if you're this field name says on email list, then when you're entering data you can select yes, you want them on the email list or no, they don't want to be on the email list. And then And to be honest, most of these, you don't end up using the majority of the time you use short text. Everyone's while used date and time or number or auto number or yes and no, you can do ah, hyperlink on occasion. If you wanna list like a website and then I'll show you actually how to use the look up withered, which allows you to pull data from another table, which is pretty cool. So we'll get into that. But for the most part, you're gonna be using short text so they click after again. You can put in a description, and this will help people toe enter data into your table. First name is pretty straightforward, right. We click enter again. That takes us to our next field name, and this is gonna be last name. That's also could be short text and then our phone number, and we're gonna be more specific and say home phone so that we know it's the employees phone number. And again, I'm calling this short text because we're not doing any mathematical calculations on the home phone, remember, we're gonna basically treat it just like text. Okay, so now we have our fields entered and we're going to switch to data sheet view, and it's gonna ask us to save our table. We're gonna say yes. And now we have our table essentially ready for data. So we have our employee I d. We have a first name where a last name, Ever hopeful. And we could start to put people in here and we'll go ahead and do that. Now, once you've created a database and you've created forms for data entry, you're not gonna be entering data in directly into the table unless there's some really compelling reason to do. So that's not a great practice because you're forms will be controlling the way your data is entered, which will help to prevent errors. So let's just put in our first employee. I'm gonna hit the tab button because it's gonna auto number this As soon as I start typing Bob, notice it added Employee. I D. Number here automatically and he is number one. So we'll keep typing his information in Bob Blake. And I'm just putting a fictitious phone number in here. Okay, then we can hit. Enter and we're on to the second record and I'll hit Tab to get to the first name field. And I've got Stan Ford and notice again. It populated his employee I D. Number in a sequence. So he's number two. That's his unique employee i. D. Number putting this phone number and enter, and that record is now officially in our database as well. So, while we're here creating our first table, let me give you a couple more little navigational tips here If you are entering data here, which will do a little bit of just so you can build a database and get familiar with it before we have any forms created to navigate through these records. You have a navigation bar down here, so this will take you to the first record. See how it highlights us on employee I. D. Number one and this will take you to the last record. So that takes us to stand. And employee number two and the middle buttons here will just take you to the previous or next so I can go from Bob to stand etcetera. If I click it one more time, I'll take me down to a new record. But let's say we're at the beginning and I want it and say, I have 1000 people in here and I want to get all the way, the bottom and create a new record but its pages and pages down. Then I could just go to this button here and click new blank record, and it's gonna drop me to the very bottom. Okay? And you can also do a search for a record or a name. Any field in here, basically. Okay, so that's just a quick navigation to get around your tables. You can also use the arrows to navigate. You can obviously use your mouse to click into whatever cell you're interested in using. You can use the page up and page down, and if you're in the beginning, you can click tab or enter, and that will take you to the next cell. And one other thing you can do is you can resize these field headings just by moving them over like that. And let's go ahead and close out of this. You can click on the X here, or you can right click on the table and click close and you'll notice. Since I made some changes to the layout, it's gonna ask me to save it, and I'm going to say yes. Okay, so now we have our employees table over here in our access objects. Let's go create our office table with a list of offices where these employees work, so we're going to go to the Create tab click table. We're going to go into our design view, and again we can click here, use the drop down or we can right click here. Or you can even go down into the corner here and click designed view. And when we go into design view, it's gonna ask us to Neymar table. One thing you'll notice that's different between Access and Excel is that when you create tables in access, you name them and save them first. Then whenever you put in a record. So when we put in Bob Blake and his phone number when we clicked off of that row, that record on to the next record, it automatically inputs his information into the table and saves it. Okay, so let's go ahead and name this. We're gonna call it offices, Lips. I'm gonna put table offices again. So we know it's a table, okay? And it's created our table. Kagan. It's automatically offering us a primary key, which is fine. We want a primary key that I could just start typing in my field name and we're gonna go with office. I d. It's already an auto number, which is what we want, because this is gonna be a primary key. So it's gonna create a unique number for each office that we put in. So every office in our region is gonna have its own office number, click tab again, and we're going to do the office city, the office state in the office phone number So first we put in City and Tab Short Texas Good State Tab Short, Texas Good and phone and short text Again. It's fine for phone numbers because we're not doing any mathematical calculations, so we're finished with that. Now, when we click to the data sheet view, it's gonna ask us to save it. Yes, we'll save it. And now we have our table. So you see again, it's got her off Sadie, our city state phone number. So let's put in our first office with Tab and Boise and notice again. When I start typing voice, it put in on auto number for the I. D. So it's number one state of Idaho, but a phone number and click enter and that record is now in our table. Okay, so now we have our two main tables that our entire database is going to be built on coming up. We'll learn how to enter more data in here so we can populate this table with all of our offices and so we can populate this table with all of our employees. 5. Appending Data from Excel into Access: Okay, so we've created our tables. We have our employees table with employee I d. First name, last name and home phone, and we have our offices, table office, I d City, state and phone. Now, we need to put some data in here. There are a number of ways to put data into your tables, and I'm gonna show you several different ways in this lecture so that you know how to do it . And that way you can sort of choose the option that's gonna work best for the given situation. For this lecture, you'll be using the downloads that are available in the course. And if you don't have excel or don't have access to excel, that's okay. I'm going to show you how to import data from Excel. But I'm also going to show you how to do it with a C S V file, which is just a simple text file that should work on any computer. And I've included those files in the downloads so you can use them. So the first way is just to enter it manually and again. This is not the recommended way to do it, especially because when someone is entering this manually. We're going to be creating a form later in the course that they will be entering that data into and, as I've said before forms or a better way to enter data than directly into a table. But just to show you how you go about entering into a table, you would click here on the next record and I want this one to be Los Angeles, California and I can hit enter a tab and then I could enter the phone number, and I'm just gonna copy and paste the phone number. And then when I hit, enter and go to the next line or tab and go to the next line. That record is now part of the database, and obviously, if we have thousands or millions of entries here, manually, entering them is not going to be ideal, particularly if we already have, for instance, an Excel spreadsheet that we're creating this database from, or any organized list of data that we want to create this list from. So let me show you how to import and append data from other sources. If you already have a well established table and you're adding information to it, you may want to upend your table. If you're creating a brand new table, then it's usually easiest to just import. But first, let's talk about a pending. So I have two entries here, and I'm going to add to this list so you can see we have Boise and Los Angeles so far. So let's switch over to an Excel spreadsheet where I have the entire list of offices and office I DS. This would be the perfect time to download the file employees dot x L s X from the course downloads and then save it to, ah, spot on your computer where you can find it. That way you can follow along and upend and import your first spreadsheets into access so you can see we have the whole list here, and we already have Boise in Los Angeles in our access database. So what we'll do is we'll highlight the two of those and we'll just to lead them Now. You also notice that we have the exact same column headings here as we do an access, we have our off Sadie City state and phone. It's really important that this information is exactly the same, so I wouldn't want to have this say work phone and then in my access database, it says just phone because it's gonna give me errors. Okay. And another thing I need to do before I append this is I need to remove these office I ds because access is going to automatically assign them because I already have a primary key set up. So I'm just gonna delete those. Save that. We'll go back to access, will go to external data. We'll click on the import and links section here. We're gonna choose Excel. We're gonna browse to the Excel file that were a pending and if I just type in table E m. P. And the search part's gonna bring back my table that I'm looking for. Click on that. Now it's loaded in there, and now I'm going to click append, and we're bringing that worksheet to the offices table and I'm gonna click, OK, it gives me this pop up because it says that my table is still open and has to be saved and closed in order to continue something Click. Yes, and it will close for me. And now I have three worksheets on this particular spreadsheet this Excel spreadsheets. So I'm going to click on offices. You can also select by named Ranges. If you have groups of cells in Excel that have been selected and named into a range, then you can choose by that as well. But where I have three worksheets, employees, offices and table design in this particular Excel file, I'm gonna choose this worksheet, which is offices, and you can see it's showing exactly what we want to show quick. Next. It's automatically selecting the headers because it recognizes these headers that match with my access table. So it doesn't even allow me to not choose that option, which is perfectly normal, and then get click next. Yes, I want to import it into offices. No, I would not like the Wizard to analyze it and then finish. Would you like to save your steps? If you're doing this over and over like your boss is consistently sending you excel data having a pendant to a table or imported to a table, then yes, you would want to save your steps so you can just do this quickly and easily over and over . But we're not gonna do that in this case. And then close. You can see my offices table is populated now, but you can see now we have all eight of our office I ds, our cities, states and phone numbers all imported, and you'll notice there's another table over here that axis created. We opened this. You'll see. This is just a list of errors for import process. Exit says null value in an auto number field under the office I D Field. And it's showing rows one through six. And so you look at what we imported and we imported 123456 of these. So these arose 1 to 6 from our import, and these were the ones that were blank with the office I d. But because it was for an auto number field, it automatically filled those in for us and they match up just nicely. So it works, but it's not ideal Anyway, we're gonna close out of this. Would you like to save changes? No. And we're just gonna delete the error table and click. Yes. Okay, so that's how you would upend the information in there that you can see. It's a little bit of pain because you have to be really careful for what you already have in there and what you're adding and trying to keep it all straight. But that's how you a pen date it into an existing table structure. So in the next lecture, I'll show you how to import data into a brand new table. It's a little bit cleaner and a little easier to do. 6. Importing Data from Excel into Access: So let me show you how to import data into a brand new table. It's a little bit cleaner and a little easier to do. So an easier way to do that would just be to go here and close this and delete this entire table. I know we just spent all this time creating it, right? But we basically have this table already created in Excel. So I'm gonna delete it. I'm gonna ask me if that's okay. I'm gonna say yes. And now our office Sadie table is completely gone. Also to follow right along with me in this lecture, please open the table offices dot x l s X file from the downloads within this lecture and save it to your computer in a place where you can find it. This is the same table that we've been working with, but in this lecture, I'm using it out of the table offices file. And if you're not using the same file, some of the steps might get a little bit confusing. You can use the employees file that you've been using, but if you use this offices file, all of the steps will be crystal clear as you follow along with me and I'm gonna show you how when you import a brand new set of data into access access concretely eight, your primary keys for you so you don't have any issues with it. So what we're gonna do is just delete this entire field right here, or in the case of Excel, it's called a column. Delete that. And by the way, if you don't have excel and this isn't going to work for you on your computer, that's okay, because I'm gonna show you how to do it with a text file, which you should be able to open in any computer so you'll be able to import import it that way. But most people will have excel. And even if you don't, you'll want to know how to do this. So anyway, we've deleted the primary key office. I d. We're saving that now. We can go back to access and imported so in access our office table is completely gone because I deleted it. So I'm gonna go to external data excel again. We're in the same wizard. But now, instead of going to append a copy, we're going to leave it on import source data. We're gonna browse to our location. I'm going to search by table office and there's my spreadsheet. Excel. Click on that click. OK, and there it's bringing all of our records and city, state and phone number, but you'll notice it's a little different now. It's giving us the option to select the first row as headings, and we definitely want to do that because it doesn't have a matching table and access, so it doesn't know that these are the heading, so we're definitely going to tell it that they are See how that changes now. Now click next. And now it's again because we're not a pending this so it doesn't recognize these is exactly the same as an existing table because we have no office stable. It's gonna ask us to assign some properties to each of these fields. So the first choices Well, we have the field name. You can have the data type. It's gonna default to short text, which is great, cause that's what we want. And then for Index, you can choose to have duplicates, okay to have no duplicates, which would be more similar to a primary key or you can just have it, not Index, and we're gonna click no for now, or you can skip the field by not importing it. But we're we want to import. All of these were going to go to state. Same thing, short texts, no phone. Short text now. Good. That's all we really need to do for that cook next. Now it's going to ask, Do you want access to a sign? A primary key and we're going to leave it on. Yes, you could go here and we could select our own primary key, but we don't have one in here, and we wanted to start doing an auto number. So we're gonna do that, and I'm noticing some I'm actually noticing. It's got a couple of extra blank rows here at the bottom, and it's assigning primary keys to that. I don't want to do that. If your wizard is not showing these extra records down here, it's because I fixed it in the Excel spreadsheet that you downloaded so you can just keep going. But you can still follow along with what I'm about to show you, because it's good to know if you do run into this problem how to fix it. So I'm gonna quickly cancel out of this, go back to my Excel spreadsheet, and I'm just going to make sure that I delete the bottom rows here. Even though they looked blank, I've probably put some information in there that's causing issues. So re save go back to external data, we're gonna just import it again. So excel on the file. We're looking forward. We're doing import, which is correct. Click OK, Yes. It contains headings. Click next and everything. Here is how we wanted it. Short text, Short text. Next. Yes, we'd like it to add a primary key. And now you notice it's not seeing those extra rows or records at the box, so that looks better. Click next, and we do want to import it to this table offices. That's the table that we're creating. And that's actually the name of our Excel file. So if you needed to rename this as a new table, you could do that. Now we're not gonna have the wizard analyze it, and we're gonna click finish. We're not going to say the import steps was gonna close. And now you see, we have our offices table nice and clean. It's got a primary i d. City state phone. Everything's here clean, no extra cells, no error messages. And if we go to the design view, you can see it created for us. A primary key with an auto number. And we could just change the name of this too. Office I d enter if we go back to our view. And, yes, save it. And we have a nice complete table that were imported from Excel. 7. Importing Data from a CSV Text File into Access: Now let's close this one. Let's go to our employees table. Now we need to fill out our employees stable. And since I've already showed you how to do in a panned and I've already showed you how to import on Excel document into our offices table, we're going to do the similar thing with this, But we're gonna pull it in from a C S V file, which is a really simple file that you can store in a note pad. In fact, I'll show you what they look like. So it's gonna look like this and C S V stands for comma separated values. So we have the names just like we would in our employee list, but they're all separated by a comma. So we have Bob Comma Blake, comma phone number comma, and then his office. I d. Over here, which we haven't even put in here yet. The taxes in quotes, because that tells access that this is text. So you see, Bob is in quotes here. Blake is in quotes. The phone number is in quotes because remember, we are calling the phone number text because we're not doing any calculations on it and then this final, um, office. I d. Is a number, so it does not have quotes around it, and now is a good time to pause the video and go to the downloads for this lecture and download the table employees dot C S V file. And that way you can follow along with steps as I go through the lecture and you can practice importing that CSP into your database and just a quick note about cleaning up the source file. You'll see I have these extra lines here, so I'm just gonna use the backspace button to get rid of that. And then make sure that there's nothing that I can't put the cursor down below this so I don't get those extra lines, especially when it's adding a primary key because it's gonna assign a primary key to a blank line. So we have no blanks at the end and click Save that, and we're going to do the same thing that we did with our offices table. We're just going to delete this whole thing and import the entire table fresh and new from that CSB or comma separated Values file. So we'll close that delete this? Yes, I'm gonna go back up to external data and instead of going to excel this time, we're going to go to text file. Click on that again. We're gonna browse to that file and by searching for table employees, it brings me right to that text file. And this isn't a note pad document. It's like that. We're importing it. Click OK, and the wizard is pretty smart. It says your data seems to be in a delimited format. If isn't choose the format that more correctly described your debt data, and it is indeed. And the limited means that characters such as comma or tab separate each field. Like I explained, right, we have a comma separating each field so that it knows where to create column separations. The other version is fixed with, and that would be there already aligned in columns with spaces between each field. But this is a delimited set of data, so we're gonna leave it on that we're gonna click next, and it's asking what delimit er is separating your fields Will The delimit er is the character that separating the fields. So in our case, it was it was Commons right, So we're gonna leave it on commas. There are other options, if that's how your data is formatted. But frequently, especially in a C S V file, it is commas. The text qualifiers quote. I remember I was saying each entry that's considered text was surrounded by quotes, and that's how they know that these air text and then first row contains field names. We're gonna leave blank because that file actually didn't have field names in it. It just started right into our information. So we'll add the field names and later, and they were gonna click next and again, it's gonna ask us to choose the the data type. So, infield one, we have tax. That's good. It's not gonna be indexed, actually, Weaken name these now, So we'll name this first name like we did in our original tables when we created them by hand. Then we go to the next swan, and this is going to be last name. The next one is home phone, and then this one is three office I D, which we're going to later connect up with our office table, which would be cool, and we're going to just go with into your because those are short numbers and indexed. Yes, stupid. It's OK is because we do have we do have duplicates in here. So, like, for instance Ah, offside. The number three, I believe, is Seattle. And you could see we have a number of those in here. Same with number two. I think that's Los Angeles. We have three of those in here, so we do need to allow duplicates. Otherwise it's going to give us an air. And that's fine, because this is not the primary key in this table. So I'm gonna click and I'll tell you more about primary keys. Okay, so then we're gonna click next, and it's gonna ask us if we want to let access at a primary key. And again we do, because that will create employee I. D. S for all of our employees. Now, if you already had employee I. D. S for this list, you'd have to go about it differently, and you'd have to either say no primary key and add one and later or you'd have to have some have the employee ideas already in this file, and you would say choose and then it would allow you to choose it because it would be this first would most likely be this first column. But obviously we don't have employees numbers because we want it to assign them. So we're gonna click on let access at the primary key, and there has done that, so we'll click next. And they were just going to really in this to lower case table. So we're consistent table employees. That will be our new table. We're not gonna have, Analyze it and finish not going to save our steps. Okay? And now we can open up our table employees and everything looks good. We have our primary keys. 123456789 10 11 12 13 14 15. We have all of our first names last names, phones and off Sadie's, and we're looking good. Okay, so now our tables are complete. And in the next video, I'm gonna talk to you just a little bit about table design basics. So I can explain to you why we've decided to break these into two separate tables that will relate to each other later, rather than just clumping them all into one. So I'll see you in the next lecture. 8. Database Design and Table Relationship Basics in Access: OK, now it's time to talk a little bit about table design and table relationships, and this is one of the most difficult but important concepts to grasp when you're first learning access. The reason these concepts are so important is because the whole power of access revolves around the ability to separate your data into different tables like we've done here, and then create relationships between those tables so that they work together seamlessly. And as you've been going through this course, you may be wondering, Why did we divide these tables into two tables rather than just creating one flat spreadsheet? And the best way for me to explain that is to show it to you in Excel? So let me jump over to excel and show you. So this spread she essentially shows you what our entire database with both of our tables would look like if it was just on one Excel spreadsheet. And on this small scale, it probably doesn't look so bad. But I'm going to show you a couple of things that pose challenges here so that you can see why, even on this small of a scale, with only eight columns of data, it would be advantageous to use it in access with multiple tables. So let me just highlight a few things here, and I'll explain to you what each of them is about. So in this spreadsheet, we have our employee I d their first name, last name, home phone, their office I d the city, state and phone number of their office. So let's say that this is for our employees who are salespeople in our retail store. And this is a small section of our order tracking system. So we have Bob Blake in the system as employee number one, so we can identify him whenever he makes an order. What if Bob Blake quits and we want to take him out of the system? Well, if we delete this whole row, you'll notice it not only deletes Bob, but it also deletes the office in which he works. And if you look through the rest of our list, we don't have another entry for our office in Boise, Idaho. So but deleting Bob, we also are deleting the entire office. And on a small scale like this, that isn't extremely significant. But if we have tons of data surrounding this boy's the office, and we have to delete it. In order to get rid of about Blake, you can see the problems that that would start to pose. So by putting this office information in a separate table that it's referred to with our office, I D. Then we can delete Bob out of the employee table without affecting any of the information connected to the boys. The office. So that's the first reason. Okay. Next, let's look at this light purple color here to find another issue that this flat table is created. So we have the information for Los Angeles, California office in here three times and again on such a small scale. This really isn't a huge issue. But imagine that this information goes in every time one of these employees puts an order into the system, so that could be hundreds or thousands of times a day. And imagine that we have 10 additional columns of information for this branch office, including the address, zip code, store manager names and so on. If this data is being entered into the system over and over again thousands of times a day , it's taking up huge amounts of space in our database and with a database of that scale that's gonna have a significant drain on the effectiveness and speed of your database. But if these were only listed once in one table like they are in our access database, then the repetition wouldn't be such a big deal. Another issue that this causes by having this information repeated over and over is that if we want to change the telephone number for the office in Los Angeles, California, we have to go through again, possibly thousands of locations in our spreadsheet and change this number. Whereas if this is in its own table and there's only one instance of this that's referred to over and over by an office i d number, then we just change this number once and every time you have an employee or an order number connected to that office, i d. Number two. It will refer to the new corrected phone number. OK, let's take a look at the dark purple real quick. Let's say that someone type this in as Seattle without a me. Now when we go to pull a report for our boss of all the employees or all the transactions in Seattle. We're only going to come up with one. Two transactions were gonna be missing this 3rd 1 because of this data entry error. But in access, we can restrict the way this data is entered. So you're not typing it in your selecting from a pre existing list of city names. And by doing that, we would have avoided that error. And again, on a smaller scale like this, it doesn't seem like a huge deal. But when you're talking about thousands of transactions in a day, if 3% of your entries have typos, you're talking about 30 transactions that aren't going to show up correctly when you do your report. Okay, so that's that one. And then finally, let's look at the light green down here at the bottom. The reason I have this area highlighted is that if we wanted to add a new office location, we wouldn't be able to do that without also adding in a new employees in this field. Otherwise we'd have blanks in our database, so that's a simple introduction to some of the advantages of breaking this up into multiple tables. And there's actually a name for some of the kind of rules that I've just been talking about , and it's called table normalization and the basic principles of it. If you're interested in looking into this, more are called first normalization form. Second Normalization Form and Third Normalization form their arm or of these count basically sets of rules. But the 1st 3 are the kind of most critical ones for most even large databases. But they have to do with these concepts that were talking about about not having repeating data about not having unrelated data on the same line and things like that. So let me take this formatting off, And that's why we broke it up into a table that's dedicated just to the employees with the employees, specific information in a table that's dedicated just to the office information and that specific information. Because that way each table can stand on its own with its own set of related information and just give you an example. We wouldn't want to take the last name field from the employee list and put it over in the office list, and that's pretty obvious because it just kind of seems silly. But Once you get a strong grasp of the idea of database design, more subtle things, like repeated office names or locations start to seem obvious as well. 9. More Complex Database Design Scenarios: Now let's take a look at a much more complex situation, so I can give you a realistic example of how you would divide up a larger amount of information. So let's take a quick look at this massive information here. This is, you know, again, this is just a Excel spreadsheet and it has customer. First name, customer, last name, customer phone number, customer address, product, price size, color distributor, shipping address, distributor, phone number, service, rap and Cruz over more employees, first name employee, last name, employee address, employee email and the employees start date. So it's a whole bunch of information packed into one table. So let's take a moment and look at how we would make some logical choices to divide this information up into separate tables in an access database. And I'm not gonna actually make you turn this into a database. This is just a example to help you understand a little bit more complex design. So let me just lay some colors on top of this to break it up visually so you can follow along a little bit easier. Okay, so you can see what I've done is broken these into chunks of data that are related to each other and then have given them a name. So first we have these four columns, customer, first name, last name, phone number and address. All of this information is directly related to a customer, and that would go into a customer table. And then we would assign a primary key for this table, which would be the customer I d. Okay, Next, we have product price, size and color, and we could group all of this into a product table because all of these columns are directly related to the product. And then we would identify these with a product I D. Key as well. Moving down the list. We have distributor, shipping, address, phone number and service rap and these all go into our distributor table. And then finally, we have our employees table with their employees first name, last name, address, email and start date so you can see now a little bit better anyway, how we would be able to take all of this information and rather than every time someone enters on order for a product, they have to put in the customer's name and all that information again they have to put in all the distributor information again. They have to put in the employee who sold the product again. And it all goes on this one line and creates kind of a data mess. But if we have each of these separated into its own table that our customer, Bob Barnes, is only gonna be listed once and then his information will just be referred Teoh, when we place an order for a backpack and the distributor of that backpack will just be referred to when that order is placed rather than having all this information repeated over and over. Okay, so that's kind of the basics of how to divide up your information and make decisions about what information to put in each table. Next, we're gonna talk about primary keys and foreign keys, which is something I've talked a little bit about but haven't really defined for you. And I'm gonna show you how to actually set those up because those are really important for making the relationships between these different tables actually were, so that they connect to each other properly and make your database work 10. Primary Keys and Foreign Keys in Relationships: Okay, so we've been talking about database design and breaking up our tables into multiple tables that are dedicated basically to one subject. So our example from this course is our employees table and our offices table. So we've talked about how to take a spreadsheet with all of this information and divided into two tables. But what we haven't talked about yet is how to then reconnect these tables so that they work together. And the way that we do that is through relationships and keys. And if you remember from earlier in the course, when we're setting this up, we created this key, this primary key, and I'll goto our design view so I can show you. Remember, we had this little key over here, and it created an auto number so that each office has a unique office i d. And in this lecture, we're going to talk more about the primary keys and how to create those relationships between the tables. So the definition of a primary key is a column that uniquely identifies each record or row in the table. And the important thing to remember about a primary key is that should be unique. and meaningless. For example, you wouldn't want to use our employees phone number as a primary key because phone numbers changed from person to person. So one person might have a phone number for a year and then give it back to the phone company. And it would go to another person so that phone number is no longer specifically unique to that person or, in our case, that employees you could. Also, especially in the old days, you could have one home phone that was used by several people. So again, it's just not unique enough. The employee's name would also be a bad choice as a primary key, because you could potentially have two people with the same name. So you can either use an auto number, which ensures that it will be unique. Or if you already have a system in place, you could use a customer I D or an employee. I D. Or, for instance, the student I D number at a university. As long as it's unique to each person and you have a numbering system that allows it to expand as your numbers of people expand a couple of other poor choices for primary keys would be a Social Security number. The reason for this is it is unique, but the problem is it's not meaningless. It's actually a very significant number, and in fact, it's something that should be kept secure and private. So to be using it as a identifier is not necessarily inappropriate use of it. ZIP codes or another example. It might seem like a good idea to use them if you only have a NAWF ISS in each city. But if you were to add an office in that same zip code, then you start to run into problems with your numbering system, and you have to start getting creative with How do you use your primary keys? And that's not what you want to do. So you set it up right from the beginning so you can avoid that. And then, finally, email addresses would be a bad choice again. These change frequently, and because of that, your data will start to get messy. So, as we said by creating this primary key, were creating a unique identify rare, and we'll switch back to our data sheet view, and in this case, it's an auto number, so it's automatically giving each record a new unique number. And that way we can consistently refer to this record or this record or any of these records in another table just by using this number here. So we go to our employees table and we have, uh, stand forward and his office I d is office number four. And if we go to our office table, that office number four has all this information to go with it. Portland, Oregon, and a phone number. But how does this table? No, what that four means? Well, to be honest, right now, it doesn't. So what we're going to do now is go create a relationship between these two tables and the way we do that as we go up here to our database tools and we have a spot right here for relationships. Click on that and it's gonna bring up a pop up, and it's gonna ask us which tables we want to work with. Well, we only have the two tables right now, so we're going to go ahead and add both of those and you can double click them and see you . They'll show up where you can highlight it and click and let me slide this out of the way and click add and there it iss Okay, so once we're done adding our tables into this window, we can close this pop up, and now you can see a little schematic of our two tables. So here's our employees table. The employee i d. First name, last name, phone office, I D. And here's our offices, and you can even see that it identifies our little primary keys for each of these. And as we've said, our primary key is the unique identify to identify each record in this table and same with over here. So to connect these two tables and create a relationship between them, all we have to do is go to our primary key left, click it and drag it to where we have that same identify are listed in the other table and release. Now we're going to get a relationships box that pops up, and it's basically just saying here you want connect table offices to table employees, and the field that you want to connect is the office I D field. See what can choose from all the fields and we want to connect it to the office I D field in the employees table. So those are correct and you can change those if they need to be different. And then it's asking if we want to enforce referential integrity and what that means. We're going to say yes, by the way. And what referential integrity means is that because we're linking these two fields in these tables, that this office I D number in the employees table must reference a valid field in the offices table. In other words, we can't have an employee with office number 10 if there isn't an office number 10 in the offices list, and you can see the logic of this because if we were able to put in in office number 10 that referenced nothing, then our database would be incomplete and inaccurate. So once we've done that, then we can go ahead and click create, and you'll get this little pop up. If you haven't closed your tables, then it will basically say it can't finish this relationship because of these are already in use, so we just click OK, and then we actually have to go back and close these. I often forget to do that. Okay, Now we can go back, do the same thing, drag from our office. I'd down to our office. I d go ahead and enforce referential integrity and click create. And now you see this little line that's linking these two together, a couple of more notes on what you're seeing here. The first thing you'll notice is there's a little warm up here and there's a little infinity sign here. This indicates that this is what's called a one to many relationship. And there are three types of relationships that I want to tell you about. The 1st 1 is a oneto one relationship that would essentially be one item linking toe one item, and we're not gonna go into that because you don't see that very much in access. The second type is the one to many relationship, and this is the type of relationship you'll see a lot. And this is the perfect example of it right here, because we have one office I d can apply to many different employees. And if this is that all confusing, I want to just tell you right now. Please don't worry. or stress about it because table design or I should say database design and table relationships are one of the most difficult things to grasp because, as compared to an Excel spreadsheet where it's a flat, one dimensional thing, table relationships have a sense of like three dimensionality, and they're very abstract. So it really takes some thinking to figure out how the relationships to be created between tables to make them work properly. So again, don't be hard on yourself if this doesn't make sense immediately. Even people who have used access a lot often have to kind of sit down and go OK, which is the one in which is the many. And you kind of have to do like some math in your head sometimes to figure that out. And that's also why we're starting with a very simple example. So that is our one to many relationship. And then the third type of relationship is called a many to many relationship, and many to many relationships become important when you have, you know, many, many tables and there's a lot of complex connections between them, and I will give you a quick example of one just so you haven't idea of it, but we're not gonna actually create one case on this example. I have a many to many relationships set up, and this is essentially a very simple order tracking system. So we have our orders. Order I D date ordered. We have our products product at a product name, product price. And then we have what's called a junction table, where we create a one to many relationship going both directions. So on any order, you can have many products and any product can go on many orders. So that's just a really brief introduction to that concept, just so that you know what a many to many is. But for the purposes of the database that we're creating, we're gonna be happy with our one relationship. It's gonna be a one to many between our office table and our employees table. So once we've created this, we can go ahead and save it. Just do you save and then close it. And we now have a relationship between these two tables, and at first glance you don't see anything different in the relationships. But I will show you what is different. If we do a new entry when we get over here and it's time to select an office, I d. Let's look at our offices are choices. We have one through eight, Boise to Denver. Let's try and pick a number nine, an entry that doesn't exist in this table when I hit nine and it enter. It gives us an air you cannot add or change a record because a related record is required in table table offices. So now that we have this relationship between the two tables, this office I D Field, which is now linked to our office table, will only allow entries from that table. So now I can delete that and enter in office Number eight and hit enter, and it's perfectly happy with it. And as we start to create forms and queries and reports, you're going to really see the power of this for being able to access the data in that other table. There's one final definition that I'm going to share with you, which is just some terminology. This employee I D. Is, as we talked about before, called the Primary Key right. And if we look in our design view, it's got the key next to it. That's our primary key. So it's our unique identifier. This office I d down in the bottom is what's called a foreign key, and a foreign key is a key that references a primary key in another table. So we know that that foreign key here, the office I D. Number is referring Teoh the primary key in this table, and we can look at it better by looking here. So here's our office I d. Primary Key. And here's our office I d Foreign Key in the employees table. And one other thing you'll notice is that in those relationships they have to be the same data type. So in our employees table, our office I d. Is a number and in our offices table. The office I d is an auto number. Now that sounds different, but they're actually both a number data type. So because we've created this as an auto number, we've also referred to this is number. So that's an introduction to the concept of primary keys and foreign keys relationships. And remember, we have three types of relationships. Oneto 11 too many and many to many and the one to many is the most common type of relationship, and when we get to forms and queries, I'm going to show you the magic of what that relationship can do. 11. Introduction to Field Properties in Access Tables: okay, In this section, we're gonna be talking about field properties, and at first blush, field properties might look kind of boring. Or at least I thought they did. But once you get into them, you start to realize that they're kind of the backbone of the information in your table, and it controls how information appears and how it is entered. And it really starts to have some powerful influence over your database. And the field properties are found in your design view, so we would need to switch over to our design view. Here are field names, as we've been working with, you knows we have a new section to the window now called Arfield Properties, and as long as we're clicked with in a field, this will show up. If I'm clicked outside of a existing field, you'll notice that nothing is in here. But as long as I'm clicked into the field or the data type, it's going to give me some options here, and we're going to go over a whole bunch of things here throughout this section, and you'll notice that this field properties section or window changes with the data type. So if I click down here on my short text for the first name, you'll notice. It gives me a whole bunch more options, and these are essentially options to control the way the data works in your table. So in this section, we're going to be looking at field size format input masks, captions, default value, validation rules and validation. Text required allows your length and index. We're not going to get into these in the bottom. But by using these different properties, you're gonna be able to really change the way your data works in your field. We're also going to stick with the general tab and not really get into the look up tab. You'll also notice that whenever you're in a data type and you click on one of these field properties, it's going to display a little bit of instructions over here to give you some guidelines for how to use this. So that could be really helpful. If you ever have questions and you'll notice if I click on to a different field property than that, information over to the right is gonna change. One other thing to note on some of these you'll have drop down options. For instance, if I go down here to required and click on that field, you'll notice there's a drop down here, and they're also what's called builder boxes. If you go to Input mask, you'll see this. It's not a drop down. It has three little dots, and if I click on that, it's going to bring up this input mask wizard. And if I click up here on default value, then when I click on this, it's gonna bring me up the expression builder, and we'll get into a short tutorial on how to use this to control how your data is being entered. But this is a very powerful tool that you can spend a lot of time learning how to use. So a lot of things going on with the field properties and like with many things in access, at first it seems a little bit overwhelming. Seems like there's just so many options and buttons to push, but I'm going to show you just a handful of really effective and commonly used properties that were going to actually use in our example, and I'll even add a couple of fields to give you some more examples, and by the time we're done, you're you're gonna feel confident and be able to use it to increase the efficiency and usability of your database. 12. Size, Format & Input Mask Field Properties: Okay, let's just get right into our first couple of field properties here, and we'll start with field size. So as you can see, we have the data type short text selected under first name, and in our field size, it's going to default to 255 characters. That's the default value for a short text. Now. It's not critical that you do this, but it's good practice to limit this number, and there are a couple of reasons to do this. The first reason is to limit the amount of storage needed for your database. The second is to prevent potential errors. For instance, if someone were to put in 200 characters for a first name, there's a pretty good chance something went wrong. Maybe they copied and pasted a paragraph instead of just a name or something to that effect . But by limiting this, we can reduce the chance of that happening. The one thing you have to be careful of is that you don't limit it too much. So, for instance, we could select a field size of four characters here, But for first name, obviously, unless your name is Bob or Joe or jam. You're not gonna be able to fit that in there. And the field size limits will restrict the amount of data that goes in here. So when someone tries to type in a first name, if there are only four letters allowed, it's only gonna let them put in four letters. So a good rule of thumb is between 30 and 50. We'll just be kind of liberal with our space in this case and will do 50 characters now. Where this starts toe have more significance is, for instance, if we go to our table offices and if we wanted to do these by state abbreviations, which is actually a much better idea. This was sort of a lazy move on my part to put the full state name in there. So if we want to change all of these two just the state abbreviation, then we can go back in and we can restrict the number of characters allowed here to to. So let me show you how to do that. So now we can go in here and we can limit this field size, and when we save that, it's going to bring up this message saying some data may be lost. This is a very important message. Says the size of one of your fields has been changed to a shorter size. Right. We took it from 255 characters down to two. It says if data is lost, validation rules may be valid. Violated as a result. Do you want to continue? Anyway, We went back into our table, right? And we changed all of our state names to two letter abbreviations. So we're okay to click? Yes. Here. Now, if we hadn't have gone in and done that ahead of time, then when we change this, we would lose a lot of that data. So, for example, Idaho is five letters, and it's only it only allows to now in our new formatting, so we would lose that data. So we're OK now, though, because we made those changes ahead of times. We'll click. Yes. And now when we go back to our data sheet view, if we put it a new record here and we try and type in Colorado, it gives me those air messages so I can only type in two letters, and if I hit enter, It's happy with it. So for now, we'll go back to design view. So let's just go through both of our tables and edit the field size for all of our fields. So on the auto number again, you noticed this change is down here with the different data type. So on auto number, it's listed as long integer, and we want to leave it that way so that access can continue to add unique numbers to our auto number sequence. The next one is City and the city. We can change to 50 characters as well the state. We already changed, too. Okay, let's look at the field size for the phone number if we have a US number. Even if included all the numbers, parentheses, spaces and dashes, it wouldn't be more than 15 characters. We're not going to store that many because we'll have them enter it in a simpler format, which I'll show you just a little bit. But 15 characters is good for the phone number. Then we could go over to our employees, and again, this is auto number. We're gonna leave that as it is, we have a first name. We put 50 last name will put 50 as well phone again for this one. We can change the 15 and our office I D Number is a number, believe us long into chur. So that's field size. Let's talk about format. The format effects the way that the data is displayed. And it's actually similar to the impact mask, which we'll talk about in a bit, which effects how the data is entered. But with the format. If you're selected on number, it actually does give you a drop down list, and you can choose from one of these if you want. But for the case of our office, I D. Where this is our foreign key. It's just needs to be a long integer. We don't need to do any special formatting to it. So close that let's look at our phone number, which is a short text so in format here, and you'll notice if you try and use the drop down for short text, it's empty. And the reason for that is probably because there are a lot of different codes that you can put in here to modify the format, and I'll show you actually an example of this when we get over to the offices table. But for all of these, we really don't need any form adding any special formatting. So our last name, our first name and our auto number are fine the way they are. So let's go look at our table offices again. We have a phone number. No need to change that. There were state and here's something interesting. This at symbol means that it's a required field and that that simple actually came over to access when we imported this red sheet from Excel. It's not necessary, and we could actually just delete it. Now. One thing with state, we want it always to display as upper case, right? So what we can do here is we can do a greater than symbol. The greater than symbol forces things to be displayed in upper case, whereas the less than symbol forces them to be displayed in lower case. And I'll show you what this looks like when we go back to our date a sheep, you I have to save it, and again it's saying you're changing the way formatting is done, and the way that data is treated within your table And if you don't have things set up right, then some information is gonna be lost. In this case, we're fine because this isn't gonna delete any of our data. Sweden click? Yes. And now, when I go here, if I want to change this to lower case C O, it's going to just automatically changes the upper case. So that's pretty cool. Let's go back. So we will do that for the formatting there for city. We don't need to do anything, but we do have this at Symbol again. We're gonna just delete that, and then our auto number will leave the same real quick, though. Let's look at what it would do if we had a date in there. It's good or employees, and we're gonna add in their date of birth, and we're gonna make this data type, date and time. Now you notice that Go straight to our format for a first option. And when we click on this, we get a drop down. Giving us our options and probably the most common is just the short date. But you can see you have other choices here as well, but we'll go with short date for our format, and now when we go to our data sheet view, we're fine with all that. We can go out and put date of births here and you notice it also gives us a little clickable calendar. But if I start to type in June 24 1978 and it enter notice, it formats it in our short date format, OK, and we can go to stand. We can actually use the calendar here and use that to put in his information. So that's how you change the format to date and again, going back to design view that covers formatting. Now let's look at our input mask in the input mask when were in time and date offers us a input mask. Wizard and I briefly mentioned this before, But the input mask effects the way that your data is entered and again on this. We could go to short date, and then it gives us a chance to try entering it, and you'll see it's setting everything up for us. When we go toe, enter it so it shows us how we need to put it in so we can put in lips October 12 19 83. And it worked. Okay, we assign the mask that we want to use for it. We try it out to make sure it works. Look, next, and then it shows you the actual code so you could use this code. But the Wizard makes it easy for you to essentially access this code, and it'll put in for us. Then we have a placeholder character, and generally speaking, you'll just use the dash Is that just shows you remember back here when we go to try it, that shows you where the spaces are, and you can change this to different characters. But typical. You'll use that underscore and then click next again and you're finished. Click finished and then see its entered in the code for the way that we want our date to be entered. Okay, so that's your input mask for your date of birth. Let's go ahead and look at our office I d. And we won't be putting any input masks on the numbers. It's Goethe, our phone. This is another good one where we can influence the way the Texas entered and in fact make it easier to enter. So we go to here, save our table, and you see, we have different options. So security code, zip code, password, date, etcetera. Obviously we'll be clicking phone number and we can try it. And again, you see, it basically gives us all the surrounding characters so we know what to expect. And we can just go ahead and, you know, type in whatever phone number we want. Okay? And we can see that it works. Click Next again. We'll leave the placeholder character, and there is showing her code again. Look next, and then this one is important. How do you want to store the data? Generally speaking, you're gonna want to store the data without the symbols in the mask. So when someone's typing their numbers in with this nice input mask showing them how to do it, it's not going actually store it like this. It's going to store it without all the dashes and everything, and that just makes it cleaner, and it takes up less space in your database. Casey, click next and finish. So now we have an input mask for our phone number, and let's go take a look at that works. It will save it. And now it's applied this input mask and you can see that the way we originally entered it . I see the way I originally entered it didn't work very well with that mask. So we would actually need to go back into these and finished them up and put the information that we need in here. And this is a really great time to point out that planning out your database from the very beginning before you even have any data is really important because you can see how, even with only 16 entries, what a pain in the butt it is to now go back and copy this. And I mean, you know, in this case, these air fictitious, right? So I could go in and just pace. He's in here, you know, but double clicking into that field. Obviously, all of these people live in different places, so really, we'd have to go back and look where Bob Blake lives and hunt down the area code to his phone number. If we wanted that information in here, which could be important at some point, because we might want to do a search by area code to find all the people who live in Wyoming or Washington or whatever. So eso that's just a good That's kind of a good lesson. I'll just fill these out real quick, so it's complete. Okay, so we'll go back to our designed to you again. So now we have an input mask for our phone number. Let's go to our last name. We're not going to do an input mask for that because it's pretty straightforward. Same with first name and same with I D. And actually this I would like to say, employee, I d. Because you want to be specific about your field names so that you know what is exactly. And so we'll save this. Just made that change. Goto officers again and let's start down phone number will do an input mask for this one as well. Do the same one. Save it. Yep. Like that one. And next. Next without the symbols. Yes, and finish and you'll see when we go back today, a sheet of you again save her. We're gonna have the same issue here and again. This isn't a problem, because this is just a input mask, but it just looks funny. so. But again, where these air? Just fictitious numbers. Anyway, We'll just fill a man, okay? Jumping back to design view. That's our state. We don't need an input mask for that because it's just two letters. City, No input mask and offside he doesn't need and mask either. And where it's a number, it doesn't even have that option. Okay, so that's the input mask. Let's look at captions. We won't really go too much into captions, but just know this. The caption is basically on alternative name or label for the field when you use it in a view. So, for instance, we add a label for this field. It's automatically gonna call it phone. But if we wanted to say office phone, then we could change this caption here to office phone. And when we're creating a form instead of showing up his phone, it's gonna show up as office phone. Okay, so but we won't do that for now, But just so you know what the caption is. Okay, so that's your format input, mask and caption. Next, we'll look at default value validation rules and validation. Text 13. Default Value, Validation Rules & Indexed Field Properties in Access: okay. And this lecture, we're going to be talking about default value, validation rules and validation text whether a field is required and indexed. And we're also going to take a quick peek at an expression builder using this little button with three dots. And for the purposes of our database, we really have all of the properties assigned that we need. So the rest of these I'm just going to show you an example of so that you know how to use them. The default value property can pre populate a field for you with the default value. If there's a value that you would use over and over again, so a better way to show this would be at our offices table going to our design view, and probably the most likely would be, for instance, the city or the state. So let's say that all of our offices were actually in the same state rather than spread around the country, and our state was always going to be Washington. So we would click on art data type or in our field name, and then we would go to our default value. And if it was Washington, we could just type in W A. When we enter, it's gonna wrap it in quotes to show that its text. And then when we go back, Teoh our data sheet view and, of course, save our table. Now you can see that in the next entry. It is by default, putting in W. A for Washington. And keep in mind this is just the default. It's not requiring that we keep it as Washington. We could change this to Colorado if we wanted to, and it would accept it. And then you'll see it's giving us the default of Washington for the next record. The default values really just for the purposes of making data entry easier by saving you the trouble. So if 90% of our offices were in Washington than 90% of the time, you could put in the city tap twice to get past that and put in the phone number, and you wouldn't even have to bother with that. And then the other 10% of the time. When you're, say in Colorado, you could enter your city tab wants change it to Colorado top again and there you go. Okay, let's go back to our design view, and the next one is the validation rule. And whereas the default view is giving us a pre populated field, the validation rule is actually requiring certain things to be entered or requiring us to choose from certain items. And I'm going to actually create a new field for this one, and we're gonna call it employment status so that we can enter in whether our employees is full time or part time. And we're gonna put rules in here for what you can enter. So let's go to our employees list. So we're going to create a new field here called We'll just call em status and it's gonna be short text, and we're gonna put a validation rule in here, and I'll just paste my expression in and then explain to you what it's doing for us. So this word like, basically means that what you enter has to be like this has to match this and then the FT in quotes means the text. FT. So you have to put in either Efty or PT, so that's full time or part time. Okay, so let's go see how this looks when we go to actually enter data into this. Save it. Of course, this is just saying it's gonna update all of our data, which is fine. And we got an error message there because we don't have anything in here yet and of Elevation Rule requires that something be in there. And so, by not having anything in here, were essentially violating that rule. So let's go in and put in P t and see that works just fine. Let's try and put in something that doesn't fit. Let's put in G R. See if it likes that. See, it doesn't like that cause the validation rules that we only enter PT or EFTY. And so this gives us a very awkward error message saying one or more values are prohibited by the validation rule like F T or P T set for table employees. Employee status enter a value that the expression for this field can accept. And most lay people, when they read that are just going to say, What the heck does that mean? And they're gonna have no idea what to do. So we're gonna go in and put a validation text in in place of this to explain what the heck is going on. So I could just get out of that. I'm gonna change this, actually, all just to escape, to get out of that and we'll go to design view again, and we'll put in some taxed here to explain it. And I'll just pay sit in and you can see our validation. Text says entered the employees employment status FT for full time, Petey for part time, Okay? And one other thing you may have noticed was that our formatting was showing lower case. So what we would also do is add in a we're gonna add a greater than and we're only asking for two letters were acquiring that. It's only one of these options and they're both two letters. So we're gonna change this to tube for field size, and now we can save it, and we're fine because we haven't really done anything in here yet. Go to data sheep you. So now we have PT as part time in caps, so now we can go in and test it again. So it's put in g y. Is it and your okay. Now it's giving us a friendlier error message into the employee's employment, says FT. For full time Petey for part time. So now we can go back to say Okay, well, he's full time. So enter Efty. We have another part time. Another full time. Good. So that's our validation rule, along with our validation text to go back into design view, usually a couple of other interesting validation rules that are just worth showing. Let's go up to our first name. And if we want this to be a required field there, one of two ways to go about this we can go to required here and just say yes, change that to yes, and we'll take a look at what that does. That's fine. We're fine. And let's see, we put that on the first name, right? So let's go test this and we'll try to just tab past the first name and enter ah record. Okay. When I hit Enter, it says you must enter a value in the table. Employees first name field. Okay, so in order to get that in there, we have to put something in there. Now I could just sit into her. No, I have officers open. So actually to close that in order to enter a record in here. Okay, but now it accepts it because we did fill out that field that required field. So let's go look at the design view again. So that's one way to do it. But here's another way to do it. We can take this off and we can go to the validation rule, And I'm just gonna paste some text in here is not know, And then we can put a some text in here that says, please enter on a first name. Okay? And let's go see how that looks and go. Today sheet will save it. That's fine. So now when we go to here and we skip over, this was good tape in the same info. Okay? And I skipped over these because they're not required. But you can see our air messages. Please enter a first name so specific directing us back to here so I could say, Okay, come back and say Julie and I can narrow down and it accepts it. Okay. Okay. Now, let's go down to our date of birth, and I'm gonna show you how to use the expression builder. Let's say that we want to ensure that people aren't accidentally typing in today's date under date of birth. Okay, so let's create a validation rule that says that the date has to be before today and we'll go to this little builder button with the three dots and will open up the expression builder box. Okay, and at first blush, this looks really complicated and it can be, But we're just going to do a simple expression that says that it has to be before today's date, and I'll give you a quick tour of this first. So you basically have three boxes here, right? And you're going to basically start here and it's gonna enter things into your expression area, and then you pick from those and then it will enter things into the values area and they picked from those, and it basically is building a little formula for you or an expression. It's kind of like building a mathematical equation so you can go this one, and it has built in functions, and you could do conversions, date and time. Financial, general math, etcetera. You do expressions with text, but you can go to date and time. And if We wanted this field here to match today's date. So you basically go through these different steps to find what you're trying to work with. So if you wanted to do an expression that involved the date and you double clicked on, that's gonna populate an expression that represents today's date into your formula here and then if you click, okay, it'll put that into your field properties, and you can look down here to see a description. So this is the current system date. Okay, so we're not going to use that. So we're gonna do something a little different. Ah, yes, Of constant here, like we just did. That is not no meaning. You don't allow empty spaces false. True on. And then let's go look at the operators. And what we're going to do for this one is we're gonna go to comparison. There's the like that we used before for our part time and full time. But we're gonna use less than by double clicking it, and then we're gonna go back into our built in functions, and we're gonna go back to our date and time, and we're gonna click on our date. So what it's saying is the date has to be less than today. I'll click OK, and then, of course, let's go test it. Save it is always That's one. And that's all so far. Okay, let's try putting in some dates. So let's put in tomorrow's date March 22nd 2017 and hit the down arrow. Okay, one or more values were prohibited by the validation rule. Okay, so it won't allow it. We'll go back and change this back to yesterday and see if it likes that. It likes it perfectly fine. So we'll also go in and we'll put a validation text saying, Please enter a date before today's date. Okay, so that's a used the expression builder. And as you can see, there are a lot of options with this that we're not going to get into. But I did want to show you at least how to use it. So cancel out of that, okay? And the last thing I want to talk to you about is the indexed property down here. What indexing does is if you select yes on index than the database or access will keep a list. An active list of whatever field, you're indexing, and the purpose of it is to make sorting faster, so I'll give you the best example I can is last name. So let's say we're searching in our employees table very frequently by last name by keeping it indexed and selecting yes, on here. And in this case, duplicates are okay because we might have the same last name by selecting yes. Then it's gonna keep a list of it. And when we search by the name Barnes or McDonald or whatever, it will go quicker in this tiny size of a database. It doesn't make any difference anyway, but in a gigantic database it will make our searches quicker. The disadvantage of an index is that because it is updating an active list to make searches quicker when your database is updating, its actually slower because it has to maintain that list. So you basically trade a little bit of overall performance for speed when you need it when you're doing the search. And again, this primarily applies to just very large databases, so we could say yes to pick. It's OK for this particular one, and then one thing you'll note is in our primary keys where we set up a auto number. This is always going to say yes, index and no duplicates. And again going back to what we discussed when we set up the auto number. No duplicates means that it's going to start with number one, and that's gonna go them to. But it's never going to assign the number two to Mawr than one person. So it's always gonna have a unique identifier. And that's what no duplicates means. In the case of the last name, we can say duplicates. Okay, because we might get two people with the last name of Barnes. We still wanted to keep an index for us, but we don't want to restrict having two of the same last names because that could very well happen. Okay, but for most of your fields, you know, just General Fields, you're going to say, Well, that's actually our offside D number for, like, phone numbers, you're going to say no index, because generally we're not gonna be sorting by phone number. Okay, so that covers default values, validation rules, invalidation, text required and indexed. So that covers most of what you're gonna need Teoh know to get started with the field properties so that you can start to assign properties to your data like we've been doing in our table here. 14. Introduction to Access Forms: all right, in this section, we're going to learn how to create forms, which is very exciting because I love forms. They're just they're fun and they're cool, and they're kind of like the wrapping paper on a birthday present. But the first thing we need to do before we get into forms as we need to clean up our tables because we've been kind of going in here and we've been, you know, I've been adding in new fields just so I could show you different field properties and what not, but I haven't filled these all out. So we're going to do that because one thing you don't want to do is have a database with empty cells and gaps in your data. So we're going to clean this up, and I'm gonna do a couple of tweaks to make this work better. So one thing you'll notice if we click into this date of birth field it looks really funny , right? Are three for March is in the left hand side of this two digit thing. So it really should be 03 right? And that's because we have not only formatting, but we haven't input mask And we also want to have this, uh, bring up a little date picker so that you can just click on a calendar. So I'm gonna show you how to do that real quick. So first, let's close our table offices because these air linked so we can go into our design view and we'll go to our date of birth. And remember, I was showing you how to create a input mask so that when you enter this and it shows all the all the brackets and spaces to make it easier to enter. But we're going to go ahead and take that out of there so that we can just use the date picker, which is a lot more user friendly. So let's just go ahead and highlight that and click, delete or backspace to delete it, and we'll go save our table and go back to Dana. She do? Okay, now, when we click into this field, it doesn't show the same gaps and everything. And you almost we have this tiny little calendar so we can click on that and we can select any date that we want, which is awesome, including today. Okay, so we have already got that one filled in. We're just going to select a bunch of random dates for these, Just so they're all filled out. So doesn't it? Doesn't matter. Obviously, people would not have date of birth in 2017. But we just want this complete. You just click in the field, click on the calendar, pick you day, and it's in there. And you can hit the down arrow if you like to. And the cool thing about having this calendar picker in here is that it will transfer over to when we create our form, which we're gonna do in the next lecture. And that way, when someone wants to enter in a day, it will be much quicker and easier for them to do that rather than having a type of it. Oops, I entered a date after today's date and remember, we put that validation role on there to prevent future dates from being entered. I click down here so we'll go back. Fix that. Okay, we got that one. And finally this one. Okay, so that's filled out. We're going to do this as well and I'll show you something interesting if you click control and the quotes. It'll copy the information from the cell above so I could just sit control quotes all the way down. And then I'll change one to part time that I could do control quotes for a while on that one. Because again, these air arbitrated numbers these air just fictitious. That's a cool little keys, keyboard shortcut, control and the quotes. Okay, so now we have a fully completed table here, which is much better. It's gonna work better when we go to do queries and forms and reports. Okay, let's close that long. It will open up our offices table, and we just have a couple of things to finish up here. So we have two options. We could just come in and delete thes entries, or we could just fill them in. And I want to show you something interesting when you do delete entries with an auto number . So once it's assigned this number nine, once we delete this, it's never going to assign the number nine again. So when we delete thes and start a new record, the next record it's gonna show is number 12. Okay, so let's do that. So you can see how it works. Yes. We want to believe that. You just clicking this box on the left to highlight the whole record. Right? Click, delete record and confirm. And then last one. Yes. Okay. Now, when I go into this one, let's say I wanted to put Let's say we're gonna have to locations a new office location in Washington. So we're gonna put in Spokane and see it automatically went to number 12. And that's actually the beauty of the auto number of future is that it will not repeat numbers. Okay, so we'll just have over and finish this out and again. I know that this is not the right area code for Spokane, but these were just fictitious numbers. Okay, so there we go. Now, we have all of our office ideas, and I want to show you just one other thing that you may have noticed that's been around for a little while now. And that's these little pluses over here. These appeared after we created our relationships. Remember, we went to database tools and created relationships, and we created a link between the office I d here and the offside the field in the employees table right there. And when we created that link, it added this little drop down because what we did was we assigned each employee to an office, right? So if we click on this drop down, it is now showing us all of the employees that work in the Boise office because of the relationship that we created. Okay, so you can see Bob Blake there, so it shows all of their information from the other table, which is cool. And it's the same with all of this. Okay, so I wanted to share that, but now we have our information all cleaned up and we can create a form, and so we are going to actually create a form for the employees table. But first, we're gonna close out of these and we're just gonna make sure that were highlighted on the employees table, and we're gonna go to create, and we're going to go to this button here, and this is the auto form. We're gonna click that, and it instantly creates our form for us. Now, let's go to our view, and we can just click on it or we can use the drop down to go to the form of you. And this is the official form where we would enter information has all the same navigation down here that you see for our records and our tables. Right, Left, right, new record surge. But now, when we go to our date of birth, you'll see that it has an option for a date picker because of the changes that we just made . So we can go there and we can change the date of birth really easily right there, which is cool. So that's a form using the the auto form tool. But there's one interesting thing. For instance, it automatically put in our employee I d number. Right. Well, now, if we're entering data into this, we don't really even need to see that employee i d. Number. It's gonna be automatically generated by access. It's gonna give us air messages if we try and change it or delete it. That was me trying to delete it. So there's really no purpose of being there. And if anything is gonna cause people trouble when they're trying to get in there and change it or or deal with it, they feel like they need to do something with that field. So we're actually gonna in the next lecture, we're gonna use the form wizard to create a slightly more customized table, and then we're gonna do some modifications to it. So we can do, for instance, a drop down that shows our office i ds so we can select the offices by city rather than a arbitrary office i d number. And then we'll do a drop down for the employees status to to make it easier. 15. Create and Format a Form Using the Form Wizard: OK, in this video, we're going to look at a different way to create a form, and we're also going to look at some ways to customize it. And we're going to get a little fancier with some of the design. And I'm gonna show you how to select different areas of the table so that you can edit the size of these boxes and at the field names in here. Okay, so first, we're gonna actually delete this one, so I'm gonna close out of it. I'm going to right click here and just caught delete that I'm going to say yes. And now that's gone. Okay, so we're going to go into the create tab and go to our forms. And last time we did the auto form with selected the table we wanted and then we went auto form. This time we're gonna use the form wizard here, so click that it will ask you what table or query you want to pull from and we're going to use the table employees. And then it asked you what fields you want. And if you remember the auto form before just plopped in every field and this is the form where people will be able to enter in a new employees. So we do want all of these fields except for the employee I. D. The employee I d is an auto number, right? And it's generated by access, and there's nothing that we can do to change it when we're entering all of this information . So we really don't even need to see it because it's just going to confuse people and get in the way. So what we're going to do is at all of the other ones and leave that one off, and you can either do that by selecting these and clicking. Add like that or you can double click them. Or you can just click this and at everything and then go back into the employee. I d. Select it and remove it from the selected fields, so this one removes everything. This one adds everything and in these single arrows, add or remove one item at a time. So again we'll take the employee idea out of there, and that way it won't distract our data entry people. So we have all over info over here, and we can click next and we're gonna go ahead with Columnar. That's the kind of most straightforward layout you can play with these other layouts as well. If they work better for what you're creating, we'll click next. We're gonna name this and this is not a table, so we're kind of call it form FRM, and that's it. Now we can either open the form or we can modify the forms design, and this isn't super important. You can just open it. And then if you don't like it, you can go back into design view and change it, or we can go to modify. And if we're happy with it, then we can just go to the view. So this isn't critical. It's just what you want to do next. So we'll modify the forms design that will take us right into design view. Okay, and there we are in design view, and now you can see it's a little more straightforward. Rather than having that auto numbers field up here and people trying to click in it, trying to figure out what to do, they're going to go straight to the first name, and I already showed you that you can toggle back and forth between your regular form view , which basically shows you you're finished form and your design view, and I'll show you one other view that you have, which is called layout view and layout view, looks pretty much like your form view. But you can still use it to move objects and rearrange some of this stuff. So this is best for just kind of quick layout changes, but we're gonna work primarily in design view because it gives us more editing options. Okay, so first, let me show you how to select items in the field that you want to edit. There are a number of ways you can do this. The first way is just by clicking on something and you'll see highlights it, and it gives us some options. If we hover over it just right, you'll see a four way arrow with the four way area. You can grab the whole thing and move it and you'll see my little box moves with me to show me where it's gonna land. But I'm just gonna leave that there for now. You haven't up arrow where you can stretch the box vertically. You didn't put that back. You have a left to right arrow where you can stretch it horizontally and you can see you have these tiny little grid dots which guide the edges of your boxes and allows you to kind of line them up with each other so you can see these have, ah, one dot space between each of them, which is pretty standard, and you'll notice if I grab this box and move it up. The field name comes with it because these two are connected. And let me actually just click control Z to undo that. Put it back where it was, But we can move these independently of each other by grabbing onto the gray box here. So, friends, if I wanted to grab this one and put it on top of that one that I could do that. Okay, again, I'm gonna click Control Z. Undo that. You can also click Undo up in the left hand corner of your screen as well on your quick access to a bar. So that's how you can move and resize. One item. If you want to select multiple items, you can press, shift and click, and you'll have multiple items selected. So now I can with these two together, see, and again I'll control Z to put it back. Or if you want to select larger amounts of things, you can left, click in the grid and draw a box over everything that you want to select. And everything within that box or touching that box will be selected like that. Okay, you can click on the ruler here, and it will select everything that lines up on that line in the ruler. We could do the same thing over here, and you can also click and drag down the ruler and select. So if I wanted to just select these, top three could do it like that, or I can click in the grid and highlight them like that. Good. So those air selections, you can also expand your form horizontally, and you can move your foot or down to give more space underneath or give room, toe add buttons and things like that. So this is your main detail area, where you're gonna have all of your entry forms and text boxes. This is your form footer, which will show you things like your page number and then your header usually has your title and you can add time and date and other items into the header. So let's go into the header real quick and we're gonna resize this. First. I need to give myself some more room so I can reach the bottom of the box. We could just grab the corner of that, stretch it across, then we can go into this and rename this. It's called employee Details. And then you see, we have some formatting options up here which, if you've ever used word or even excel, you see these a lot and we can just click center that will drop it into the center, and we can select that, move it down a little bit if we want, or we can change the font. We could make it bold or change the size of the fault, etcetera. You can even change the background, and then, since we've done some changes, let's go preview that. Actually that background doesn't really work with that, but you can see the changes, so we'll just click. Undo control Z, bring that background back to normal. Go back to home. Let's go take a look at the property sheet over here. This is where you can change the properties of whatever you have selected. So if we got a format and you see this is being called label 12 you can select from different items. Here are data birth label or office I d label and that's all these things right now it's selected on this. You go to format, you can see the name is employee details with caption. Here, you can adjust the width and height and spacing manually if you want. Just by typing in a number, you can change other styles. France, we could go to the border. We can create a solid border around it and you could pick the size of the border. And I'll just show you what that looks like in the form of you is that just puts it into a box back to design view. And there are all kinds of formatting that you can do here. Okay, let's go on and resize some of our fields down here. We can select all these and we can bring them all into the same spot. So they're all lined up nicely. And then if we want to move all these together, we can highlight the whole thing and grab onto it and move them all together like this. And then we can select all these and do the same thing and shrink these up. And then we can click into these just like we did with the title in the Header. It's been clicking to here and edit these so they look better. So I put a space back in. This is just your caption at this point for this field. It's not the name anymore, so it doesn't need to be joined together. The spaces aren't gonna hurt anything at this point. Double click into this one phone. It's fine Office ideas. Fine. We'll leave this one for now. Data births. Fine. And we'll change this to employment status. And we can take all these and Centrum a little bit better under our employees. Details. There we go. And you can also move these just by clicking the left arrow, right, arrow up arrow or down arrow. Okay, it looks pretty good. And we can edit the text in these fields as well. I go into home and doing the edits up here, or you can do the edits down here in your property. Shoot as well. Now, sometimes in these in your form view, you'll have a little scroll bars on the right here. Kind of like you have scroll bars here in your window. It'll often add these in there. So the way to check on that is to go over to here in the format tab. It says scroll bars here, and in this case, we have none selected. But if it says vertical, then I'll show you what it would look like. So you'll have this little scroll bar here, and this is appropriate. If you have, like, a memo field or a description field, that's gonna be a bigger box. But for a single line like this, those just are kind of confusing. So if you see those on there and you need to get rid of him, just go to your scroll bars over here and go to none that will take care of that. So we've talked about how to select different elements within the form. If you want to select the entire form, you click in this little box in the upper left hand corner and you'll notice now over here on the drop down. It's referring to the entire form, and then all of your tabs refer to different ways that you can add it the entire form so you can go in here and you can change the with manual if you want to. So if I want this to be seven inches, let's do that. See how it expanded. I'll go ahead and click Control Z to put it back. See, now it's back to 5.1 to 5 inches. You'll notice this has an option for scroll bars as well. And in this case, you do want to have the scroll bars. Because if this doesn't fit the screen than the user will need to scroll to get to the information and you have all kinds of other options that you can edit in this area, one thing I want to point out is this box on the left is the label name for this entry form , and this field here is the actual field in your table. And when something has entered in here, this is going to actually go into your table. Um, and you can edit the properties or view the properties of this by selecting it and then going to data and you'll notice it asked for your control source, and it's referring to first name. And if you do the drop down, you'll see it has a list of all the fields in our table. Those to click on a different one of these than it would refer to something else. For instance, I clicked on last name. Now, when they type in their first name, it's going to actually go into the last name field, and that would make a mess. So you want to make sure that these air referring to the right fields in the right tables. When you use the form wizard, it automatically does that. But if you for some reason you accidentally change this and for instance, you can go in, you could change this. And if I hit enter now it's showing an error because it's not really referring to anything valid. So that's just a note to make sure you get those referring to the right places in your table. So that's how you rename your fields and select items and readjust your sizes and your formatting and all that 16. Add Controls to Your Form for Better Usability: okay, in this lecture, I want to show you how to use thes design tools up here, which basically just allow you to add different elements into your form. And we're not gonna look at all of them. But we're gonna look about half of the ones that are available up here. Okay, But first, what I want to do, I don't want to mess this form up. So I'm gonna create a copy of this form, and we'll do our practicing on that, and then we'll just delete it. And then later, I'll show you how to out a few things to this form will do some other modifications to but to make a copy of a form. And you could do this with tables or queries reports or any of the other objects is you hover over this and you, right click on it, and you just click copy. And then you right click on the open navigation pane here and you click faced, and then it will ask you to name it and see it says copy of forme employees. That's perfect. That way we know it's a copy, and we're just gonna go delete it When we're finished. Okay, so there's our copy. So we'll close out of our original, so we don't mess it up, and then we'll double click on this guy or we can actually close this. And another way to open it is to go right. Click here, and then we can actually go straight into design view just by clicking that. Okay, so here we are, in our design view. And what I want to show you in this video is how to use these different design tools up here. And you have the whole row of them here. And then we can either scroll down to new ones or we can just expand the whole list. And you can see all of them here so you can see that when I have her over something highlights it with grey outline. And you'll notice also that by default, this tool here is already highlighted. And match your select tool. That's the tool that we use to select items, fields and boxes in our form. Okay, so I'm gonna left click off of that uninsulated those. So let's start with our first tool here, and it's a text box and you'll notice that we already have text boxes in our form, and that's these right here. If I click on this and I go to the property sheet, you'll see right up here it's a selection type text box and the text boxes where a user is able to enter information or view information that connects to one of your tables. Or it can also connect to a query which will create later. But if we cook off of that and we grab this so we're gonna left click on the text box and we're gonna bring it down. Let's just put it at the bottom here. Actually, let's drag this over here so it lines up with the rest of the labels. So say we didn't have this employment status in here when we used the design wizard. Then we could go in and just click into here type in employment status. Then we can select on the text box and we would go to data and we would assign a control source here. So in this case, we would select employment status. And by doing that, we basically recreated this label in text box here. Okay, so let's delete that for now, because obviously we already have that. The next one, which is a really useful tool here, is a button. So again I left click on this to highlight it. And then I come down here and I just click and drag left, click and drag, and that creates a button. So you see, it's already named at command. 20. Weaken. Rename that later if we want. But this button wizard gives you all kinds of choices for how to create your button and what it will actually do. Um, and you see, you see, we have record navigation, and this gives you options to, like find the next record, find a specific record, go to the first record, go to the last record, go to the next record to go to the previous record. See, but you can actually add buttons onto here to make it easier for your users, you can do record operations. So France, if you want to be able to save a record or undo print, duplicate a record, delete a record or at a new record, these will navigate you to do that. These air form operations. So where these air specific to the individual record you're working with. These are specific to the form, the entire form so they can apply filter. They can close the form, open the form printed form, print the current form that they're using, or refresh the form data report operations. We haven't created a report yet, but report operations allows you to work with a report. So if we have a report based on these employees details here, let me move this over. Then we could actually create a button to, for instance, open that report. Maybe that report is a list of all the employees and their phone numbers so that you could just click a button and open that report and then we won't get into the applications and the miscellaneous coming up. I'm going to show you how to create a couple of different kind of navigation buttons. So for now, let me just show you how to. We'll just do a record navigation and we'll just say, go to next record. It will say next. And you notice it's giving us an option of just having a picture that with an arrow, so that implies next, and you can add a picture browse to your desktop and use a picture that you've uploaded, and you actually have two options to their for the existing pictures in there. But you can also just use the text says Next record. Or, you know you can modify the text so we could just say next we're going to click next and you can name this and we could just call this next button. You click finish and you see it's created our button and named it for us and weaken resize the button and we could grab the button and move it. We could do our put our navigation over on this side here if we wanted or down below. If I want to expand this over that I could quit our navigation buttons over here. And that gives the user a nice friendly button to get to the next record. Okay, so that's Buttons. The next one is Tab controls and let me show you how this works is if you have a whole lot of form data that you're trying to get into one form. But for this instance, let's just say that we have a very small former we need to divide up this information, OK? What? We would dio I'm actually gonna expand this for a moment so I can put my tab controls down here. Oops. That's not what I meant to do. You control z and expand that. There we go give more space in our detail area. I'm gonna go up here and I'm gonna left click on the tab controls, and I'm gonna drag it here and create my box and just drop it. Okay? Now, what we're gonna do is we're gonna create two tabs so that we have half of this information on one tab and half of it on the other tab just to take up less room. So the first thing we do select what we want on the first tab and we right click and cut. And we slept on this. No, we right click and we paste. Okay. And that drops these right into there. And then we can go to our next tab, select all of these, cut them again and right click and pace. It will drop him in their them again. Left click Teoh unsa Like those. Now it can highlight this and rename these. And if we actually click into this click on the actual label. You'll see it says Page 22. Up here. We could go toe format and we can change the caption and in this case, we instead of saying Page 22 because it's just assigning an automatic page number for these tabs. We want to call this name so we can just go to the caption here and type in name and you see it changes it to name. Then we can go to the Page 23 here, do the same thing. Go to the caption and where this has, like their offside, either date of birth and their employment says we could just call this one info or whatever describes it. Well hit, Enter. So now when we click on the first tap, But we have name and then we click on the second top, and it's it's their info and you can create additional tabs by right clicking and saying, Insert page. See, it adds another tab, and you can just continue to expand this and create an entire form that fits on one page. And of course we can resize this if we want to be bigger you know, then everything fits differently. Would want to grab this and move it to where we wanted it. Grab it, move it. But that's the basics of how you create a tap control and real quick. Let's just look at it in form view so you can see how it works. So first, see, first we have name and we've got the Bob Blake Records showing when we go to Info brings his info. And then, obviously we didn't fill out anything for that. You can see how you could just tap back and forth between these. And if we had aton of info that need to fit on one form, this would make it a little bit easier to just fit everything in. And then, of course, you contest our next button. So right now on Bob Blake, we click. Next goes to stand forward, click next again stew Bishop, and so on. So forth. Okay, so we'll go back to our design view. So that's the tab Control. Let me show you how to do a hyperlink. And again we left click on this and let's say we just wanted to have a help article just say help, and then we put in an address. So if you have a website that has all of your help information for this particular form, then you could just type that in. So let's just use Google for now, and we'll just have this go to Google. But you would put in the address of your website that has your help article or document for this particular form. You put it in, put put your Web address in there and then you click. OK, and then you can see it's put a little hyper link in there so people can click on that and go to that for help. Okay, so that's a hyperlink. We're not going to get into Web browser control. It's basically just putting a Web browser page into your form. I'm not going to do too much on navigation controls. Not gonna go into option groups. A page rate would be if you just want to break up a form up into several pages in a specific spot, just like you do in word Microsoft Word. A combo box is a really useful tool that I'm gonna show you how to do. In another lecture, you can add charts. And obviously this would be more relevant if you had numerical data in here and you wanted to display a graph or chart here. But the way you do this is you just left. Click it and you drag and highlight and drop it in there. And then it actually ask you where you want to pull your data from. And, you know, this isn't gonna be relevant, really. But just to show you how you do it, because it is kind of cool, you just select where you want the chart to pull data from and then click next, you pick a field. And again, normally, this would be some kind of numeric calculation. But let's just do a chart of our office I ds and click next and ask you what kind of chart you want to dio. And if you've used Excel, these probably look pretty familiar to you. But we'll just get the basic column chart. You can't mess with layout of it here. This is for if you want the chart to change from record to record as they move through and then you just name it, quick finish and then you see, it just drew a little chart right into our screen right into our form, and you can move it and place it wherever you want. Obviously, this chart isn't really relevant to anything, but again, if you are using numerical data, you could have this chart that showed relevant data to what people were entering into the form. So that's kind of cool. But for now, let's delete that we won't get to into dropping in lines or toggle buttons a couple more than I want to show you. You just draw a rectangle. You can. You check boxes, unmanned object frame attachments, option buttons, sub former, several ports. So, in other words, this is a form here, but we can have an additional sub form below that. So where this is an unbound object frame, this is a bound object frame and then an image. And let me just show you quickly how toe put an image. Because that's also cool. Dropped the image where you wanted on your form, and then it's gonna bring you to a browsing screen so you can select an image. And let's just say we want to put an image of these books. Then we do that and it drops the image right in there. And of course you can resize it and said however you want Okay, so you can use that just to basically decorate your forms or to place relevant images that somehow inform your users have to enter, date it into the form. You can also use this button here to insert an image as well. Okay, so those are some of the design tools that you can use now, what we'll do is actually close out of this. And it doesn't matter if we save the changes, because we're gonna believe anyway, So I'm just gonna say, no, I'm going to go into this and because this is our copy that we were just playing with, I'm gonna get rid of that so it doesn't get in our way. So I'm going to right click on that click Delete is gonna ask, Do you want to permanently deleted? Yes, we dio. And then that's gone. We're back to a regular employee form. And in the next lecture, I'll show you how to create a drop down for this so that you don't have to guess at the Office I D Number will also create a drop down for the employment status of it makes it easier for people to choose. And then I'll show you how to put some navigation buttons down here just to make it easier for your user. Teoh Move from record to record. 17. Add Navigation Buttons and Drop Down (Combo) Boxes to Your Form: okay. And this lecture, we're going to create a drop down for the office I D. So it's easier to select that we're also going to create a drop down for the employment status, Also making that one easier to select. We're gonna put a couple of navigation buttons down here, and then we're also going to realign this to make it look a little better. So real quick. First, let's just go realign those Go into our design view and all we have to do is double click here, go into our format and click a line left. We can do the same thing with this one long left, we'll go back to our form view and there we go. I also want to save this so that those changes become permanent. You just see that looks a little cleaner. We can also go in here and take up some of space in here. Grab this box and take it a little tighter like that just to make it look better. Okay, so let's create some combo boxes which will allow us to use these as drop downs. Go back to design view and what we're gonna do is just come in here and delete thes out and then add them back in as a combo box case will highlight those We can either hit, delete or we can right click and click Delete and you see that's gone Now, now we go appear we left click the combo box come down here and we drag and place it in here And we get the combo box Wizard so you can see we have a couple of choice is the one we're gonna chooses the 1st 1 I want the combo box to get the values from another table or query So we're gonna use this combo box to retrieve the office I devalues from our table but some other options you could choose would be I will type in the guys that I want An example where you would use this option is if we had, say, a product list and it had t shirts and we wanted small, medium, large and extra large. Then you could use this option and you could actually just have them select from those four choices. So that's another great way of restricting the information that they're able to put into your form and thereby restricting the information that's able to go into your tables and records. And then this 3rd 1 is allows you to find a record on your form based on the values that you select in your combo box. OK, but we're going to go with Number one click next, and then we want to go to our offices table to supply the values for the drop down, because we want the office numbers and also the Office City so that people can clearly identify which office there selecting without guessing at numbers. So let's look that quick. Next, OK? And then, as you can see, this is similar to our form wizard where we select our fields. So we want the office I D and want the city click next, and we're gonna sort thes by city so it's sorted by name alphabetically, and then we can adjust the width on these if we need to. We can go in a little bit on these because they're pretty small and they'll notice we're hiding the Kikkoman. If we uninsulated that, it's going to show the office i D. Number as well, which we really don't need to show because the people are going to select from the city name. So we'll keep that checked Click next. And then we can either have it, remember the value or store the value in this field. So we're gonna click here and we're gonna do our drop down. And we wanted to store this selection in the office I D field in our employee table, so they're going to select it based on the city name. But the office I d. Number is going to go into our employees table, so select that click next, and we do want to name this and we'll just call this offices Dropped out, I think wants earlier in the course I named a control and I put a space in there. Not a huge deal. But generally speaking, you still want to follow those same naming practices and leave out the spaces and then we click finish. You can see now we have the drop down in here and real quickly we're going to move. This just resize things a little bit, but everything kind of lined up a little bit better with the rest on this side up and let me show you our property sheet on this. You can see it shows officers drop down label. And we can change the caption on here to say we can just say office or we could say office location. We'll just call this office. It's pretty straightforward, and now that's going to be in there. We could also just click into here and change it right here. And one other thing you can do is you can select on this and you can check your data and see your control source or where it's pulling information from Okay, so let's go down to this one and delete this and we'll put a new drop down for our employment status. Will click delete. Okay. As we go up toward combo box again left, click it and drag it on. Drop it. And in this case, we don't have our pre existing employment status for full time or part time in another table. So doing this won't help us. So in this case, we're gonna type in the values that we want. So we'll select on that. This allows us to select the number of columns we want. In this case, we're just gonna do with one column and we can reduce the size of it down. And then we're going to put PT for part time, then tap down and FT for full time. Then we click next and then we want to store. This is well in a table. So we're gonna select that and we want this to go into employment status. I woke up next and they will name this and then click finish. I couldn't resize thes. And then, of course, we need to go in and change this and we'll just tape in employee status, enter and then in a second, I'll show you how to change the tab order. But first, let's go look at our form of you. It's got a form of you. So here we have a good old Bob Blake old buddy. And instead of giving us the office label here and asking us to just enter a number making us kind of guess at it, we could just have this nice, easy drop down, all alphabetized, and we could just select from that we could go down to the employee status as well, and we can just select part time or full time from the list. Let me show you what I was talking about with the tab order. If we click into this field here and hit Tab goes to Blake goes to phone number. If I had to have again notice that jumped all the way down to his date of birth, the reason it does that is because when we deleted the original office I D Field here, it automatically just sent the tap to the next thing in line. But you'll notice we keep hitting Tab that's gonna jump back up to Boise and then it will jump back down to employed status. And that's not a very good user experience. So we're going to rearrange that a little bit and sort it out. He also knows this line of being aligned a little bit funny, and you can't always get that perfect. But we'll work on that a little bit, too. So we'll go back to our design view civic and pull this end a little bit, and that might make it a little bit indented. But sometimes those were just a little funny. Now, before we get into changing the tab order, let me show you one thing that I need to fix here. Never select on this combo box, and I'm going to go over to other. You can see the name of this is combo 13. Now, that's not a very descriptive name. And when we go to rearrange the tab order, If this says combo 13 and this one down here says combo 17 that's not gonna make it very easy for us to sort things out. So what I'm gonna do is I'm gonna change this to employees status, and then I'm gonna change this one toe office. I d. And then I just put the combo behind it. So I know it's a combo box. Okay, now those are named better. Okay, Now I can right click. Go to my tab. Order first. It gives us the option to select from which section we want to organize. Now we don't have anything in our header or footer that needs to be sorted by tabs. So we're really just focusing on the detail section, which is our main section of the form. And then you can see it's showing the order here. So it goes First name, last name, phone number, date of birth office I D and employee status. But if you look to the left here, we wanted to say first name, last name, phone number, office I d. So we're gonna click on off Sadie over here on this little box to the left. See, I got the little area you left click on it to highlight it. And then you have, ah, selector arrow that you can grab it by left clicking on and we'll drag up just below phone . You could see the black line inserted there, and then when you release it, drops it up there. So now this order matches our form, so it will tab nicely. We can click. OK, now, let's go back to our form of you and weaken Tab. Tab, Tab Tab gets us right through there. Okay, So now that we've done that, let's go back to design view and we'll add a couple of navigation buttons down in the bottom here, and our forum will be finished. So I'm going to give myself a tiny bit more space here, and then we're going to go up to our buttons left. Click on that, bring it down here and drop it where we want it. Pull this down a bit and we talked about this a bit in the previous lecture, but we're just gonna create a simple record navigation. We're gonna create one button that says go to next record and we're gonna create another button under record operations that allows them to add a new record. First, let's go to the next, and we're just gonna call it next. We could even make this caps. Gonna rename this and just call it next button on finish. Okay, there's that one pretty straightforward. Go up to buttons again. Drag and drop it. Go to record operations this time and at a new record, and we're just gonna call this one new click next, Call it new button and finish. I was going to set these next to each other so that making the same size we'll grab this guy and pull up it over here, scooting both this way a little bit to center him and let's go to our form view and see how they work. Okay, so here we are, Bob Blake. Weaken. Do next. That's gonna take us right through our different records, and then we can click new. It's gonna take us into a new record so we can start a new one from scratch. And let's just go ahead and enter one in here, and then we can go see how it looks in our employees table. Let's just put in Dan Purchase. Now we can use our fancy combo box, our data birth selector. He was just born last year, and then we can select full, full time or part time. We'll call him full time and then click new. That's gonna enter into the records and we'll go check our employees table. And there's Dan with all of his information. So it's good and close that one more thing that's important is that you'll notice if we click on the office, dropped town it on. Lee gives us the selections that air in our office I d table. And if I try and type in something else like Richmond, it's gonna give us an error because it's not in the office table. So I'm forced to use one of these, which is cool. That's what we want. Okay, One final thing I want to show you is that if you select this drop down here. This is going to give you this edit list items. It basically is allowing the user toe enter in additional things and we don't want them to have this pop up at all. This is just gonna confuse them. So we're gonna cancel that I'm gonna hit escape so I could go back so I could get rid of this. Basically, I'm escaping out of entering this new record, and I'm going to go back into design view and in the employment status properties. We're going to go over here and go to data, and you can see down here where it says limit two list and right now says no and we want to say yes. So that's the list that we've provided. And then there's this one below that says, allow value list, edits. In other words, it allows the user to change the values in that list. We do not want that to happen either. So we're going to say no for that. Okay, Now we can go back to our form view, and when I click on this, you see, it no longer gives them the options to edit this there now just limited to these two things . No confusion. Nice clean form. Okay, so that's how you create thes combo box drop downs and how you do a kind of simple navigation to help your users navigate through your form. 18. Introduction to Queries, Sorting and Filtering: okay, in this section, we're gonna learn how to do queries, and I'm gonna teach you how to create three quer ease that are going to essentially ask questions of the information in our tables. And that's basically what a query is is a question. But it can range from a very simple question to a very sophisticated question. And the cool thing about queries is that once you create it just like a form, it's in a permanent object or tool in your database that you can then use to create reports from and to do calculations without having to reinvent the wheel. So the place where queries are created is under the create tab. Up here, we've already worked on tables and we've already worked on forms. But the queries is right here. But before we create a query from scratch, I want toe help to clear up one thing. And that is what is the difference between queries, filters and sorting. Okay. And if I open up our table employees first, I'm gonna show you what filtering and sorting us, which is something that you do right in your tables. And it's something that you do to kind of get quick answers from your data, but it doesn't produce new answers and new calculations in the way that a query would. But let's look at the filters and sorting real quick so that you know what I'm talking about. So you understand the difference. Now if we go up to the home tab, you're going to see there's this section right here. So the most common way to sort your data is just assorted by ascending or descending. So if you're selected on one of the fields and you click ascending, it'll be lowest to highest. If you change it to descending, it will flip from highest to lowest. So you see now our entire table is sorted from the highest employee number down to the lowest. Now, if we want to get rid of that, we just click, remove sort, and it will get rid of that. We can also go right in here and click on the drop down and do the same thing, okay, and we can click here again to remove it. So let's look at another sort. If we wanted to sort alphabetically by last name, we could use our drop down sort a dizzy. You'll see we have a nice alphabetical listing here or if we wanted to sort it by office. I d. Number. We can do that and you'll notice when we apply the sorting to this column, it overrides the sorting over here. Okay, so once again, let's remove that. So that's sorting where you're organizing the information either numerically or alphabetically. Let's look at filtering filtering. We're gonna actually remove information or hide information based on a certain criteria. So a good example. Let's say we wanted to hide all the people from office number one. We'll do the drop down here and we'll go ahead and click on one. So that's going to hide this and show all of the other ones. Click OK, and now you see that the office I D. Number one is missing. Let's clear that by clicking here, and an even better example would be the employment status so we can fill to this. If we want to just see the full time people, then we'll get rid of this and just click on the full time click OK, and now it's just showing our full time employment people. Now, if you quickly want to look back and see all of their people again. You can toggle the filter on and off, so that turns the filter off. So now I can see are part time people again. And then we could just click toggle again and that'll put a full time back on. If you want to remove it entirely, you can also just clear the filter here, and it will put us back to our normal table. Okay, so that's the basics on sorting and filtering. And as you noticed, though, when we do those, all it does is just kind of rearranges the information here in our table. It's not creating a new tool or a new object that we can use over and over again. Yeah, So now let's go and create an actual query where we're going to create a question of our data that will be dynamic as our data changes and will continue to give us valuable answers . So in this query, we're going to create a list of all the employees by city. So, as you remember, these office I D numbers all refer to a city in our offices table. Okay, so we're going to create a query that will give us our employees first name, last name, phone number, and it will organize them all by city. And by having that as a permanent query, if your boss was gonna ask you that over and over again, as your employees change, you could just pull up that query and run a report for it, which we're gonna look at reports in a little bit. And you could do that with just a couple of clicks of a button. So we're gonna go to create. And whereas with the form we used, the former wizard because it makes creating a form a lot easier for simple forms for the queries, We're not going to use the Wizard because just isn't as valuable as the Wizard is in the forms. So we're just gonna go straight to the query design and we'll just build this from scratch . Okay, so when we click that, you'll see this kind of strange split screen. We have a blank space up here, which is where we're gonna enter the tables that we're gonna use information from, and then this down here, move this over is where we're actually going to structure our queries, and I'll get into all that in a second. But first we have to go to our pop up here and select the tables that we want to draw from . We want both of these cause we're gonna get the employee's name and phone number off of there were going to get the office city off of the office table so we can either double click or weaken. Select it and click add. And then when you're finished, you just click close. And this probably looks familiar to you because this is a little diagram basically of our tables. And you can even see the relationship here that we created in our database tools when we went in and drew the relationship. And this has obviously all the fields in both the tables. And so this allows us to select anything from this to put into our query. So again, we know that we want the first name right? So we're gonna double click that and notice how it drops it down into its own column. Here we go ahead and click. The last name drops it down, and then we want these city where they work and we can double click it or weaken Click it and just drag it down. See, it gives me the little plus sign and then release it and said it. It made like that. Okay, so we're just gonna create a really simple one right now and then another lecture. I'm gonna get into all of what these mean, and we're gonna learn how to move these and put sorts and criteria in here and just understand this really well. But for now, just understand that our query is going to be pulling our first name field our last name field in our city field out of our tables and displaying them in the query data sheet view . 01 thing I forgot was the phone number. So we're gonna double click on phone number as well and noticed that the phone number is over here and I really wanted next to the first and last name. So what I'm gonna do is I'm gonna click out in this little gray bar at the top. Highlights it. And then if I get my selector arrow at the top, I congrats it and slide it to the left and now we have in the order that we want. Okay? And now for the city's we want to sort the city's alphabetically. So we're just gonna do the drop down here on the sort row we're gonna click ascending, and that's just gonna sort them, make them nice and orderly. And that's really all we need to do for a simple queer. Now we can go click on run over here and you'll see what it brings back. Looks an awful lot like a table that's been sorted. Well, in reality, this is not a table. This data doesn't really exist here. It exists in our table. But the query is just displaying the results of the questions that we've asked of the data in our table. Okay, so we told it to include the first name, the last name, the phone number, the city. And then we sorted the cities in alphabetical order so you can see we start with Boise and we go all the way down to Seattle. So now if, for instance, flow Vance moves to Boise next week and we update that in our table as soon as that record has changed in that table. This will update, and all we have to do is run this query to have an up to date view of the city said Everybody lives in, so you'll notice. This still says Query one up here because we haven't named it because we've just created it . So let's go into design view again and you can see weaken, basically toggle back and forth from designed view to data sheet view a lot like we can do with our tables, right? And of course, you can toggle up here to design view and we won't get into sequel view. But if we close this, it's gonna ask us to save it. Will say yes, of course. And we're gonna name this Q r Y for query and then employees by city using our same naming conventions. Click OK, and now you see, we have a new object over in our navigation bar. We have queries as the subheading, and then we have our employees by city query saved over here. Okay, so when our next lecture, we're gonna create another query that allows us to sort by one individual city, and then I'm gonna show you a bunch of additional kind of little tools and tricks to use enquiries more effectively 19. Create a Query Using a Single Criteria: Okay, let's create another query. In the last lecture, we create a query that brought back employees listed by City in this one. Let's do a query that just brings back the employees in Los Angeles. Okay, so again, we'll go to create. We'll go to query design, and we're gonna be using both of our tables. So again we can either double click on these, or you can click one and then hover over the other one press shift and selected us well. That will select both of them, and you could do that with an entire long list to just click the 1st 1 Then go down to the bottom one and hold shift and click it, and then we'll click ab and clothes, and you could move these around, and you can move this around if you can grab it. You can bring this up if you want just to make it easier to use. And speaking of re sizing the window, you could move these two. If you need more space there, you could minimize that. If you need more space there, it's all very customizable. Another important thing to note is that if you've already selected your tables from the original pop up and you want to grab additional tables. You can go to this button right here in the ribbon. Click that and it will bring you this pop up again so you can select tables and notice You can also select tables Or if we want to actually create queries based on other queries. We could do that as well. Okay, when we're done with that, we just close it out. Okay, so we're going to do a couple of additional little cool tricks with this one. We're gonna set it up basically the same as we did for our employees by City query. We're gonna put our first name in there last name, phone number and their city. But now we're going to get into a little more detail with this, and I'm going to explain a lot of these things as we go as well. So this first row is your field name? Obviously. So you see, we got first name, last name, Phone City. We also notice there's a drop down here that allows you to change that if you want. Okay. And if you click into the field, you'll see that the drop down appears okay. And the next road down is your table. So this is which table you're drawing from. So you can see the first name field. The last name field in the phone field are all in the employees table, whereas the city field is coming from the office is stable and again when you're selected in here, you have a drop down and you can select a different one if you want. Of course, we want to change this toe offices. Obviously, we'd have to change that as well, right? Okay. We already looked at the sort row in our previous example where we sort of the city's alphabetically so ascending or descending this next rose called this show. And it's just a toggle button. So you either have it on or off. And if you haven't checked like these, all are. And that means that when we run our query or switch over to data sheet view to display the results of our query that anything here that is checked is gonna show up. Now, if we uncheck this which were actually going to do for the city, then this one will still be relevant to our query, but it's not going to show up in our query results. So that's a really cool thing. And I'll show you how that works and how you can use it in just a minute. Then we have our criteria and criteria is basically your rules for the question that you're asking. So in this particular example, we're looking for all the employees that live in the city of Los Angeles. So what we're gonna do is we're gonna come down here to the criteria we're gonna type in Los Angeles and you can do lower case or upper case. It doesn't matter. Only enter notice out, put it into quotes. The reason it does that is because access recognizes that this is a string of text. And so by putting it into quotes, it is identifying it as text. If we were using numbers than there wouldn't be quote surrounded. Okay, so what we have here is Inter query were searching for first name, last name phone number, and then it's going to our offices table in searching for city, and we're asking it to show anything that matches Los Angeles in the city. So Let's go ahead and run this and see how it looks. Okay, so here the results have brought back four people and it shows their first name, last name and phone number. And remember, we checked the show box to hide the Citi field, which would otherwise be showing up right here. But since this query is going to meet named employees in Los Angeles, then we already know that these employees work in Los Angeles, so we don't really need to have it displayed over here because that basically is redundant . And while we're in the data sheet view, we can resize these if we like just to make things fit better. You can also hover over these and double click, and it'll size it to fit. So in other words, if this one is really wide like this and I go up here, hover over and double click, it will drop it to fit the largest thing that's in that cell. You can do the same thing here and see it expanded it so that that first name field fit correctly. Okay, let's go back to her design view and we're gonna alphabetize the names so we'll go to our sort, and we'll just go ascending now. Just so contrary. A couple of other tricks and tips. Let's say that we had originally added in the state field, but then later we decided that we didn't want it to get rid of this. All you do is click on the top here on the Globe gray bar, highlight it and click delete. By the way, you're not able to use control Z in design view here. So France stiff. You want to bring that back, you click Control Z. Nothing happens, so you'd actually have to go and redo that click on the state again and bring it in. That's something to keep in mind. If you're used to using, like, excel or word or even in your table view, you can do control. Zito. Undo things. The only time that you can use it is if you're typing something in here and you deleted that like highlighted that. I believe that Then you can hit controls the and it'll bring your text back when you're selected within a cell. Okay, we looked at field table sort show criteria real quick. Let's look it or or is basically just an additional criteria that you want to use as well. So we could say that we want the query to bring back all the results from Los Angeles or Denver and we could just type in Denver. And then when we run that you'll see now it's bringing back twice as many results because it's bringing back the Denver people as well. Okay, but we don't want to do that. We're just doing Los Angeles employees for this query. So we're gonna go ahead and go to the data sheet and make sure everything looks good. And you could see now our last names are alphabetized and then we're going to save it, and we're gonna call it Query Employees Los Angeles. And then you see, now it pops up into our queries objects. So that's our employees in Los Angeles. Query. In the next video, we'll look at some more specific examples of how to set up criteria, because that's where some of the power of queries comes in. 20. Criteria in Queries - Many Examples: Okay, so we just created and employees in Los Angeles Query. Now we're going to create an employee's in the Northwest Region Query, and I'm gonna show you how to do that really quickly and easily. And then we're going to spend the rest of this video just working on criteria examples. I'm going to show you just a bunch of different ways to use criteria to make. Your query is really powerful. So instead of recreating this from scratch, what we're gonna do is just going to right click on this and we're gonna go to copy. Or we could alternately say, Just control, see with the keypad. And then we're gonna right click again. And you've seen this before. We put paste and it's creating a copy of this query. So we need to rename it. Otherwise, it will just call it a copy of and whatever we copied, we're gonna call it employees Northwest Region Source A n w region Click OK, and you can see now it's created that So let's go ahead and close our Los Angeles query and we'll open this one up. And when we got a design view, you'll see that it's exactly the same. But we're gonna modify this to make it so that we can search for the Northwest region, which is our cities in Portland and Seattle. So all we have to do to make our new query is to write in Portland. And remember, it doesn't matter if you capitalized or not, and I just narrowed down and then tape in Seattle. Let's go look at it. And these are all of our employees from the Northwest region. Okay, so that's really simple. So now we have three. Query is where you could sort all of the employees by city. You can go for employees just in Los Angeles, or you can go for the employees just in the Northwest region and in this one. If we wanted Teoh, we could actually show the cities, and we could even drag this over to the very beginning. And we could sort this alphabetically. And what that will do is it will show the city that they're in. It sorts the city's alphabetically so they're organized, and then you'll notice it's still sorting our last names alphabetically within each city. So it goes F F S V, and then starting with Seattle, we go back to G. J and s because remember, our sort order in design view goes from left to right. So first, that sorts or cities alphabetically and then it sorts our employees. Okay, so we'll take one more Look at the in data sheet view. That's our employees by Northwest Region Query. Now we're gonna close that one. And yes, we want to save the changes and that's finished. Now let's look at a bunch of different examples of how to use criteria to do your calculations. Let's make a copy of our employees by city, and we're gonna leave this one with the name of copy off because we're just using this as an example. So we don't want to get it confused with our riel queries, so just click, OK, and we'll right click on that and go to design of you. Okay, so this is the original query that we created. And as you remember, it's pretty straightforward. It's just first name, last name phone number, and then the city that they work in, all alphabetized in ascending order. The first thing I want to show you is how to create a parameter query where we do that as we put brackets square brackets into the criteria row, and then when we run it, it's gonna ask us to enter the parameter value. So, in other words, before it gives us any information back in our query, we have to give it some information first. And where this particular parameter is in our city field, we need to enter a city, and it's going to use that to sort the data for a query. So let's go ahead and just put Seattle and hit OK, and you can see it brings back all Seattle. We can go back to design view again, and we can run that again and again. It's going to ask us for a parameter. We commend tape in Los Angeles, and it's gonna bring us back all the employees in Los Angeles. So that parameter is a very powerful way to create. Query is because of the flexibility that it offers you OK, but one thing you'll notice is what we run this. We don't have anything instructing us what to put in here, so that could get pretty confusing if we try and type in a first name like Bob. It's not gonna bring anything back because our parameters for the Citi Field. Okay, so what we need to do is we need to basically enter in some tax tear to give some instructions. So between the brackets, gonna put Enter Employees City. Okay, Now let's try and run it again by going to our data sheet view, and now you can see that it's specifically asking us for the employees city. Or you could word this to say, enter city of Employment or whatever is specific enough so that you know, that if you put Seattle in here, you're gonna get a list of all the people in Seattle. Okay, so that's a parameter. And since this is just a practice query, we're just gonna kind of delete things as we g o and do new things. So let's do a wildcard. A wildcard is essentially when you use an Asterix in your criteria, the wild card allows you to search for a specific string of text, but with some variables. So, for instance, if we put the Asterix and then l A and D, we're going to search for all the city's that have the word L A n d in them and something before L A and D. So when we run this query, you'll see that it comes back with all of the employees in Portland. So our wild card, the Asterix, represents the P O R t. Okay, let me show you a different version of that. You'll notice that Put some syntax in here whenever you just enter text into the criteria. The criteria for that is like in other words, it's looking for something like L A and e. Okay, if we move the Asterix to the end, let's run that. See, we get nothing if we have the city Lander, Wyoming L A N D E R. Then it would show up. You can let me show you one more version of that we put in Asterix right at the beginning. So in other words, L. A n. D. Is surrounded by Asterix. We'll run that and you know it's now it's showing or Landau because it's allowing characters on either side of the text string, L. A and D. So that's how we using Asterix to create a wild card search. Let's talk about what we do if we want to find any blank records we just type in is no. And when we go to the data sheet view, you'll see it's coming back with nothing. But if we had in our table, if we had one that said first name, last name phone number that was missing the city information, then it would bring that one up. And so that isn't always a great way to look for essentially missing data in your database . Okay, so that's no. Let's get rid of that. Let's do some numerical criteria. What's at our employee? I d in here and I'm gonna drag this to the front of the columns. Okay, so a few simple ones are greater than less than and equal to. Okay, so if we say greater than 10 and we run that query for our employee I d numbers so you can see it shows all of our employees I ds that are over 10. And of course, we could do that as less than a swell. And we'll run that. You can see now we have all of the employees ideas that are less than 10 and obviously, when we do equal to 10 you can imagine what we're gonna see. We show employee I d. Number time. Okay, so those are some really simple calculations that you can dio. We can also say use the words between, say, five and 15. So you can see we have all the numbers from five up to 15. We can do less than five or greater than 15. Clean that up a little bit, and that's going to give us the numbers that are outside of five and 15. So less than five or greater than 15. Run that. So you can see we have the numbers of the lesson. 51234 and then the numbers that are greater than 15 16 17 18 the night. Okay, so those are some examples of just simple calculations that you can create in your criteria . So let's actually get control Z and go back to our previous one between five. And 15 and then we're going to go to the cities here and we're gonna put Seattle. Okay, so this is going to do all of the employee I d. Numbers that are between five and 15 and are located in the city of Seattle. Okay, So there we go shows we have two people that are between five and 15 for their employee I D numbers and are located in Seattle. Of course, we can change this back to our less than or greater than 15 and in Seattle, and you'll see we get a different result. So now we just have Francisco Garcia. Okay, so that's the and criteria we've already looked at the or criteria where you essentially say Seattle or Portland and put that down here and you'll notice that if we want to do it and criteria, it always wants to be on this same row. But if we're doing an or criteria, then it would be below it in the same field. And you'll notice also that you can do a whole bunch of or criteria. So if we wanted, we could say Seattle or Portland or Boise, for example. Now let me show you how toe create a formula that will do a calculation, and one thing you'll notice is we are first name and our last name in separate columns, and this is good because we want to sort by last name or we want to sort by first name. It makes the query process more effective. But let's just say that we wanted to have both of these in the same field. We can actually create a formula and a new field that will do that. So what I'm gonna do first, I'm gonna select both of these in select that one, then press shift and select, and then I'm gonna delete them. Now, I'm gonna create a new column, and I'm gonna put the formula right into the field name here, and I have this copied, so I'm just gonna paste it. No, expand this out so you can see what we're looking at. Okay, so this first section, where is his full name? And then you see the little colon? This is the new name of the field. So instead of it being called city or first name, it's now called full name. And then the colon separates it from our formula, which is this. And remember, we used the's square brackets down here for a parameter. In this case, the square brackets are now defining the fields that we're using our formula. So our formula is taking the first name field from here and then it's adding one space between them. And then it's adding the last name field from here and let's run this. We have to show it by clicking that and we're gonna drag it over to a better, more logical spot. So we'll drag it over right there. Okay, now let's run that and see what we get. Okay, now you can see instead of showing our separate names, it's actually grabbing the first name and last name out of two different fields in combining them along with the space in a new field. So that's just kind of a simple text calculation now. One important thing to note is that if we type this in wrong, for instance, we say first aim and we forget to put the and in there. Then when we go to run this, it's gonna get confused and think that we're trying to create a parameter. So it's going to say what is the first aim? And obviously that's not gonna work. So the moral of that story is it's very important that you have these field names within the square brackets when you're doing a formula exactly right so we can cancel out of that Put her and back in there. We're on that again, and there it is again. Okay, so those were just a few ways that you can use criteria to manipulate your data in your query to get more specific results from your data on what will do with this is just a lead . This we'll close it from here. Doesn't matter if we saved or not. When we go to here and right, click and delete it, Yes. And then in the next section, we're going to actually use these queries that we've created to design reports that you could just generate with the click of a button. So if your boss said, Hey, I need a list of all the employees by city alphabetically, I think you're just gonna be able to click a button generated really nice printable report that you could then email in. That way you can impress your boss and make them really happy 21. Introduction to Reports - Create Your First Report: okay. In this section, we get to create reports and reports are awesome because they allow you to take all of the data that you've been entering into your tables and sorting with your queries and present it in a professional and easily viewable way. And creating report is pretty simple. The easiest way to create one is to click on the source that you want to create the report from. So, for instance, if we want to do a phone list of our employees by city, look, select that we'll go up to our create tab and we'll go over here to this report button, and this is going to draw the information from our query automatically. So we click that. And just like that, it's created our report, and right now this is showing our report in what's called layout view. And if we go look at our different views, you'll notice that they're a little bit different from what we're used to seeing with our tables and queries and forms. We have what's called report view, and I'll click on that to show you that's basically the finished version of your report that you would then either export or print to share with, say, your boss. We have the layout view, which we just looked at, which is sort of like a simplified version of design view. In layout view, you can do sort of large scale layout changes so I can shrink this down, for instance. But you'll notice when I select one sell the entire column moves as well. So this is sort of her broad sweeping changes. We also have our familiar design view, but design views gonna look a little bit different. We have our familiar grid and are selectable fields like we did when we created our forms. And, you know, you can resize these, drag him around while controls you. To put that back, you can click into here and rename, and we have familiar sections. In this design view, we have our header where we can go in and rename our report. We have a page header for our detail area, which is the labels that go along with our records. Then we have our actual detail area, which is going to be the layout of all the records as they're listed in our employees phone directory, and then we have our floater. And then finally, we have our print preview, which just gives us a exact look at what it's gonna look like when we printed out. And using this, you can scroll down and see how it lands and how it lays out on the page and in print preview. You can change the page size up here. You can change the page layout. If this fit better horizontally, you could change toe landscape layout. This is just giving us a warning that our area is wider than a page with Let's click. OK, but we're just gonna look at it. I guess you can change. The landscape can go back to portrait you consume to make it easier to see depending on how your data is laid out, and then you have options for exporting to excel. A text file, pdf email and more options here. Okay, and you can click here to close print preview where you can just go back here and click through to wherever you want to be to edit your report. So let's take another look at our report view. And really, what we have here is just a employees phone list right so we could clean this up if we wanted to go back to our designed you. And let's say we didn't want to include the cities in this particular report. We could just go in and delete that we can change these headers to just look a little cleaner. We could modify the report title was called this employee phone list, and we can shrink that up if we want to. I gotta go. Hold it just right. No. And if we press, click, even shift and click weaken, select both of these, and then we carefully find the edge of it. So we have the double length arrow. We can shrink that down. You can do the same over here. We could drink this down to match. Let's go view it, and I shrink that down too far. Got those squished. We'll go back to design view, and we can either scrunch up these a little bit or weaken. Expand this back out a little bit. So if it's better and there we go. Okay. Now we just need to close this and save it so it could close and yes, and then we'll rename it. We call this our P T for report and employees phone list. And now you see, we have a report in our objects navigation pane called Employees Phone List. Okay? And the next lecture will look at some more options and we'll learn some or strategies for ways to create reports. 22. Create Two More Reports: Okay, So in the last video, we were working on the employee phone list report, and when we finished up, it looked about like this. And one thing I wanted to do before we moved on is clean up this line right here because I don't like that. So we're gonna go into our design view, I think I'll noticed, Like, click right here. There's still a text box in there. And actually, if I shift and click on this one, there's still one there, too. So I'm gonna hit the lead on those, and then when we go back to our report view, it should be gone. Okay, there we go. So see, it was leaving this line out long here because I had actually a field still in there, so that's just a little cleaner and better looking. Okay, so we can close out of that now and now we're going to create a report based on our Northwest region employees. Quit. Before we create that report, I'm going to change a few more of our employees to the Northwest region offices just so that you can see it better when we create the report. So we're gonna go into our forms. And I'm just gonna go through some of these and I'm gonna change them to Seattle and Portland. That one's already Portland. Let's make stew. Portland, He's our Seattle trip. One more to Seattle. Okay, that's good enough. We can close out of that now. And those who records will be updated in our employees table. So now we're going to create a report based on this employees of the Northwest region. And you remember how before we clicked on this and we went to create and then we just created a report and it just instantly created a report, right? We're gonna do it a little bit differently this time. This time, we're going to do it using the report, wizard. And when you're using the report, Wizard, it doesn't matter what you have selected over here. We could have this selected, for that matter, because when we use the Wizard, it's gonna walk us through it from scratch and I'll show you right now. So we click on that and it brings up this wizard, and the first thing that allows you is to select from the queries or tables in your database and in this case, we're using the query employees of the Northwest region, so we'll go ahead and click on that. And then it gives us a choice of the fields that we want to include in this particular report. So if we wanted to just have first name last name and city, we could do that. Or if we wanted just first name, last name and phone number, we could do that as well. In this case, we want all of these fields so we can just click the Double Aero to bring them all over, and then we'll click next. And then this is one thing that I really like. So this allows us to essentially organize our data, right? And you could see the way it's separated here. It has the first name, last name and phone number, all grouped by city. And you'll notice if we go down their employees, then it just puts everything on the same line. Okay, but by doing it this way, we can kind of separate out the data. So it's going to say Seattle and that's gonna have all of our employees in Seattle, and then it will have another heading that says Portland. And so and that's just a little bit Maura peeling in the report. So look like next and then you can actually ADM or grouping so none of these air relevant. But if we wanted to break it down by, say last name, then we could add that as a group and see, it just creates another tier, But in this case, that doesn't really make sense. So we're not gonna do that. We'll get rid of that. So we're just gonna group it by city click next. And then, of course, we can sort. And we're gonna sort by last name because that's just the most logical in ascending alphabetical order. Click next, and then this gives you options for the layout. Remember last time when we created that from our employees phone list? It just gave us a standard layout. Here we get some choices so you could do a stepped layout, so this would have your city and then your employees name and phone numbers, or you can do it block. And this is basically gonna have the city here and then straight across from it, it will start to list your employees. We could do an outline, which is just kind of mawr, even more stepped than stepped. So we're gonna go with Block because it's gonna be a little cleaner for this particular layout, and then we'll leave. That checks a little, just the field width. So all the fields fit on the page, and then we'll click next. Oh, and by the way, let me go back. You can also choose between portrait and landscape what you can change later. So that's not a big deal Next. And then we name it and we're just gonna call this report Employees of the Northwest region , and then we're just gonna go ahead and preview this report, so click finish. And so there it is. You can see its first organized by city. So all the Portland people are listed first, all the Seattle people, or listen second. And obviously we need do some formatting here, shrink up these fields to getting fit, so we'll go ahead and close out of the print preview and we'll go to the layout view. So these are all selected and you can shift and click on the header to and then scrapped the whole thing and drag it, and then we can select thes same way shift and click on the header and move the whole thing over by left, harrowing. And then we'll shrink that one down, too. And same here. Click and then shift click to click the whole calm. So all of our records and same thing arrow over and shrink those up. And then last but not least, our phone numbers click and click and then press shift and click Arrow that over a swell. I'm just holding down the heirarchy and then this one, we need to expand. Well, not that much. Okay, We can go into these and we will fix us. They look better. First name, last name. Okay? And we can go in here is well, and clean this up and let's go to report view and take a look at it. So there we go. That's a little better. We could organize this. My first name, then. Last name. If we wanted Teoh by just going into our design view and we basically just click with last name and then shift and click the other last name. We grab it, pull it over here, and then we do the same thing with this one. Click on it, shift and click, and then for this one will just narrow it over to move it. And then I could re click on this one again. And same thing. Oh, and actually need to shrink this one up to get out of the way, do that and drag it over and we can slide this guy over. We'll shrink it up a little, too. And then bring our phone number in. Yeah, let's take a look. So there we have the first name on the left last name on the right and you'll notice it is still alphabetized by last name. So we'll close out of this. And, yes, we want to save it. And one thing I want to mention for you when you look at this later is I went into our employees table. This is a total side note, by the way on, and I noticed we had to Julie swans. And so I deleted one of them. So you can see actually, our employees go 14 15 16 17 and then skips 18 goes to 19. That's because I deleted a record out of here. I just want to mention that. So if you see it later in the course, it doesn't give you any confusion. OK, so that's our employees by the Northwest Region report. Let me just show you one more thing about how to use the Wizard to pull things from various places. Let's say that we wanted to do, ah, report that had the employees by their city offices, we could go to create and use the wizard again. And first we're gonna draw from our employees table and we're going to get our first name, last name and phone number. Then we're going to go to our offices table. We're gonna get our city. So we're pulling from two different tables for this report, whereas before we were just using a query, this is just a little bit different way to do it. We'll click next. And we could just say, you know, first name, last name phone, and then their city or again, which I kind of like to do weaken, sort it by city just to make it a little cleaner and easier to follow. I don't need that. Any grouping in this case will sort it by last name because that makes sense and we could sort of secondarily by first name, so it's gonna first sort by their first by their last names. If we had any of the same last names, then it would sort by the first name next. But we don't, so it doesn't matter. But click next Goethe Block cooked next, and this one will be called Report Employees by City Preview. Right there again, we're a little off the screen, but you can see what it's done here. It's created our city and then a block of last names, first names and phone numbers that are in that city. So we have our whole Los Angeles section. We have all of our Seattle employees. We have all over Portland employees all together, and they're all alphabetized by last name. So that's pretty cool. And then, of course, we would just go in Here, too, are either are lay of you or design view. We did lay out for you last time, so it's good or design view to clean this up, and we just to click and shift click to adjust our city Sam thing than last name. And we could just narrow it over Not too far. Sprinted down a little bit too Same thing with their first name. Carol it over. You can grab these and dragged them to. But arrowing is kind of nice because it keeps it on the same line. And finally, our phone number one more And that one needs to be a little bigger Can it will go in and adjust their names. We can adjust these headers if we want to. You know, we could click and shift, select all of them or again we can click out here and drag across to select him all we got our format we could change these too bold could make him a little bigger if we wanted You know, whatever you want to do with your formatting, you can dio make this a better a little bigger if we wanted. I'm not topic and I won't go of course up to here and fix this too. Anyway, we'll go back to report view. So there we are with our finished employees by city report and now we have ah employees phone list. We have a list of employees by city all sorted and then we have just a specific report for our employees from the Northwest region 23. Options - General and Current Database Option Tabs: okay, in this lecture, we're gonna talk about the access options. And if you go to your file tab, the options are found right down here, and it gives you a whole bunch of ways to customize the way that access act. Now, before I do that, I'm going to create a copy of the database that we've been working with just so that if I forget to change any of the options back, it won't mess it up. So I'm gonna go to save as and we're gonna do, save database as and access database, click, save as. And then it automatically adds a one to it. But we're going to call it options demo, hit, enter or click save, enable contact. And now we have a new name. It's employed contacts, options, demo. So we can play around with it without directly affecting this specific database. Okay, so now we can go to file and click on options, and we get this pop up window that has access options and down the left, we have our navigation. So we have general current database data sheet object designers, proofing language clients, settings, customized ribbon, quick access to a bar Adams and Trust Center. I'm not gonna go into depth with all these, but I will go into some detail with most of them. So let's start with the general tab now. The general tab is going to affect your access program overall, so any changes that you do in the general tab will affect the way that any of your access databases show up. But it's some pretty subtle things. The main one is your user interface options, so you can disable your live previews. This is where when you have her over something, it will actually show you what it looks like before you even click on it. So I like that. So I just keep that checked. This one is for screen tips, and you have the choices of show feature descriptions and scream tips. Don't show feature descriptions and screen tips or don't show screen tips at all and you'll notice mine has been set on this one, And what screen tips do is when you hover over something in your table, it just gives a little pop up while you're hovering there to describe it more fully. So it is nice, especially when you're starting out because it could just save you a lot of time trying to figure things out. And then this one actually shows you the shortcut keys in screen tips. So, for instance, if you hover over the copy, it will say control, see, Or if you hover over cut, it will say control X, just to give you tips on what shortcuts you might use in the future to save yourself time. And then the rest of this is just defaults for how you want to do the file format and so forth. And then you can personalize it as well. Change your theme, that kind of thing. Not too big of a deal in the general tab. The one that gets interesting is the current database. Now, this one gives you quite a few options for changing the way that your current database behaves. And this is why I saved this as a demo version so that if I forget to change something back in this screen, it won't stay in my original version. So just kind of run through some of the more interesting ones. Um, these were just, you know, doing a title or an icon for your app, This display form. If you look the drop down here, remember, we created our form. Employees are form where you can enter the employee's names in. If we click on that, then when you open your access database by default, it will show your employees form. And this is a really great tool if you're creating databases for other people, because then it just pops up whatever you want them to do. Another good one is the document window options. So we've been working with it in a tabbed version, and that's where if we open up several tables and Aquarian a form, then we have tabs across the top of the window where we can move between them. But another way to do it is with overlapping windows, and I'll select that so that we would go in there. I can show you what it looks like. And whatever you do these, if you don't like the way it worked out, you can always come back into your options and change it back. These down here allow you to basically restrict the access to various things within your database. So let me go ahead and de select these use Windows themed controls on forms, enable layout view and enable design changes for tables in data sheet. View these air going to restrict the user's ability to easily make changes to your database , which is cool, and I'll show you those what they look like in a second. But let's scroll down and take a look at some other ones. Okay, our navigation pane, which is our access objects over the left. We can also disable that in our ribbon and toolbar options. We can actually disallow the full menus, which is all of the tabs across the top. I'll see what that looks like, and you can disallow default shortcut menus as well. And then down here we just have some name AutoCorrect options filter, look up options and cashing options. I'm not gonna go into any of those. Let me go ahead and click OK, and we'll go back to our database and I'll show you what some of those changes that we did will look like. And you'll notice this fairly important pop up that says you must close and reopen the current database for the specified option to take effect. So the changes I did won't take effect unless I close this database. So I'm going to just say OK and you'll notice nothing really looks different. So I'm going to go to file and I'm gonna close that that I'm gonna open up again. There's our options. Demo. Now you'll see it looks quite a bit different than what we're used to seeing. The first thing that we have here is a default form that popped up. So you can see already that someone who is not familiar with creating a database will be able to populate into this and see what they have available to them. They know how to navigate through the records. They can use our buttons to navigate, and they can do data entry. But they want him bill to see the tables where they're entering them in. If they want to change it, then they'd have to navigate to the record using the form. Okay, so that's one cool thing, And this you can close, maximize or minimize. And if we minimize it, you'll see it just pops down in the corner down here and we can restore it again like that . And then you'll also notice up here. That only gives us two options. It gives us the file tab and the home tab. It doesn't give us any of the tools. They can't create new forms or reports or anything up here. So none of those tabs air there. And then all of these are just basic, you know, formatting and and kind of tools that are not going to affect the overall functionality of the database. And then one more thing noses. If we have her over this and we right click, it doesn't give us the quick tools. So do you remember before we could right click on this and we could either click close or save or design view notice that we aren't able to get into design view with the way that things are arranged. Okay, so those were some kind of cool things, but there is more you would have to do if you want to make it truly secure database. Because with this set up, they could always go into the file menu, go back to your privacy options and change any of this back, and we're actually going to do that now. So I'm gonna get rid of my this fault display form. I'm gonna put back my controls, my layout view. In my design view, I'm gonna display the navigation pane again and I'm gonna put the menus back in the river. Now I can click OK again And I'm gonna close this and reopen access. Go back into that and now you can see that it's back to normal. Oh, with the exception that I didn't take it out of windows You. So these air still in windows? We can go change it back to tab you, But I'll show you a little bit more how the windows view looks. You can go to different report and it will bring that up as a window. And of course, you can minimize this could minimize both of them. You can go here and look at all your windows by looking at more Windows. It will actually show you how many you have open and you can just select on one to get to it. But we'll go back and change that as well. So we go to current database again and that was under document Windows options and we switched back to Tab Click OK again and we'll have to close and reopen it close on open. And now when we open our forms again, you can see now it's in the tab format. So everything that we opened now will be on different tabs. Okay, so in our options menu, that was our general and current database options. Next, we'll get into data sheet options. 24. Options - Datasheet and Other Option Tabs: So to get into your data sheet options, you go to file and options, and it's gonna be your 3rd 1 down data sheet. This is pretty simple. You just allows you to modify the way that your data sheets look in access so you can get rid of our grid lines that define where your cells are. You can get rid of both of those either horizontal, vertical or both. You can change the default cell effect, so right now it's flat. But you can make it raised or sunken, and we'll just look at what raised looks like you can change the width of your column by default. There one inch. But you could change this, say to two inches, and then you can change your fault default. So let's take a quick look at what that will look like. So we'll just close these and we'll reopen the table and see what it looks like. Okay, so you'll see. Now we have the raised up cells. It kind of looked a little bit three dimensional. Have two inch column headers here. You could still see the grid lines, but if we go back good options again and we go back to Flop. Okay. What was that again? Open it back up. Now we're back to a regular color, but you can see the grid lines from here are hidden. So that just gives you a kind of a more clean, easy on the eyes. Kind of a look. So we'll change those options all back. Put back her good lines, change this back toe one inch and click. OK, then we close that and open it again. We're all updated and back to our original design. Okay, let's look at some more options. We're not going to get into object designers too much, but this is kind of cool just cause you can change your table design view. So, for instance, you noticed when we were doing our field type or data type, it would default a short text. But you can change this to default to something else. For instance, if you're creating a database that is using primarily numbers, you could set this default to member. That would just save you from having to change it on the drop down every time. But you can change the text, field size and the number field size here, you have some options to change your query design. You can customize your form and report design view a little bit, and you can disable different checks for errors to show up. And then this will. You can even change the color, so mind right now is a lime green error color. But you can pick your own color if you want it. Okay, so I'm gonna put this back to short text and let's look at proofing. So this is where you can change your AutoCorrect options by clicking. Here, you can adjust your spelling options, so by default it ignores words in uppercase were to contain numbers, Internet and file addresses. You can have it not flag repeated words and so on. You can customize your diction areas, and it's got some different languages that you can work with. You've got your language tab, and this is just where you choose your editing languages and display languages. Every client settings, so this changes the way that access behaves when you do certain actions. So, for instance, when you hit the enter button when you're in a field, you can either have it stay in the same field you could haven't moved to the next field or you could have a go to the next record. And these are all just different options here. Like, for instance, you can have it automatically select the entire field. You can have a go to start a field. We can have it go to the end of field. And actually, let's just demonstrate a couple of these. Let's say Don't move and go to end of field, Okay? So let's close on. Andrea, open it. So when I click into this, see our cursor goes to the end of the field. When we enter, it stays in that field. Remember before, when we enter, it would go to the next field. But now when we hit enter it just selects on that and stays in the same field. OK, so we'll change those back. We want this to go the next field about that to go selecting terror feel. Let's take one more look at so close out again. Open it. It was slipped into that now and hit enter. It goes to the next field and it selects everything in the field. We do it again and it selects everything in the field. Okay, let's close that. And look at some more options. You can change your arrow key behavior. You can change your default finder, a place behavior, these air confirmation. So, for instance, when you delete a document, it's gonna ask you, Do you want to delete this? You can disable that if you want. Those are nice to have the because they're good fail safe's. You can have the movement that your cursor moves. You never go left to right or right to left. Change your general alignment cursor movement. These are some display properties that you could change printing margins, that you can change general settings that you can change. And then down here are some advanced options, which I won't get into. Okay, the next one is the customized the ribbon. So this is your ribbon across the top of the screen with all of your tools. And so the this is your ribbon and what's in there now and knees, all of the options for things that you can add. And you want to be very careful about doing any major changes with this, especially if you don't have a good reason for it. But if you do have a good reason that you want it to be more customized to your working style, that's OK. But just make sure it's something you really want to do before you change it and keep track of your changes. If you do ever make changes you don't like, you could always go back to customs ations and say, Reset all customers ations and that will put you back to the default settings. So let's just say that we wanted to add this close command to the views Tab kind of random , but I just want to show you how to do it. We select on that click new group. It's going to allow us to add a new one in here. We click on close when we click, add, and now, under our views Tab, we have a new group called Close, So let's take a look at what that does now. So now we have a close option, and we could rename this as well. But in our table here we can do our normal view here. We can also close it by clicking here, so that's just a customization to the ribbon that you could dio. So we'll go ahead and do that. And I can either do that by going in here and selecting that and removing it. Or you could reset it to reset all customers ations. Okay, so that's that. That's how you modify the ribbon. And you consort here by all tabs, main taps, which is what we're in, or just tool taps specifically. Or you can go to this side and you can sort by popular commands commands not in the ribbon all commands macros and so on. And France, If you go toe all commands, you'll see a much bigger list of commands that you could use to put into your ribbon and customize it the way you want it. Okay, there's a similar one with the quick access to a bar. And you may recall, earlier in the course, I used the quick access toolbar appear, and I customized it right from here. But you can also do that here. So you just click on whatever you want in the toolbar and click add and it puts it in there . And when you click okay, it'll populate it in your quick access to a bar. Okay, so go ahead and remove that. Leave it as is, and I won't go into this, but you can manage your Adan's in this options screen. And then you have the trust center, which is just Microsoft, kind of telling you more about safety and security, and you can modify your settings from there. So that's a basic overview of your access options. You can see that the ones you mostly use would be your current database options and your data sheet options. But again, just a reminder. Be careful and kind of conscientious when you're making these changes. Keep track of what you're doing so that if you do something you don't want, you can get back to it and change it back. But that can make some of your work a little easier if you customize these things. Teoh suit your working style 25. Exporting Tables, Queries & Reports to Text, PDF and Excel: okay, This video is gonna be all about exporting your data. So when we go up to our ribbon here, if you go toe external data earlier in the course we learned how to import. Now we're gonna learn how to export, and specifically, we're gonna look at exporting to Excel, exporting to a text file and exporting to a pdf. Okay, so the first thing we're gonna do is export of file to a text file or a comma separated values file. And if you recall, we imported using that earlier in the course. So now we're just gonna turn around and export. So we're gonna use our query. Actually, we're gonna use the employees by city query. We're gonna select on that. We're going to go click on text file, we're going to choose a location, and that's just fine for me and my documents. You can browse to your computer to find a specific spot if you have a place in mind, and then you have to specify your options. Now, if we export the data with formatting and layout, it's not going to give us the C S V option. It's going to try and save it in columns. And so we don't want to do this. We're gonna leave that on checked. I'm gonna click, OK? And then we do want it to be delimited, and we're going to use characters such as Comma or tab to separate each field. And in this case, we're gonna use commas on. You'll notice it's all set up to preview here. So it has the tax in quotes than a common a separate each field and then more texting quotes Commodus separate the next field and so on so forth all the way through and you'll notice we click fixed with then what it does is it's basically spacing them apart. And you can see as you scroll to the right here that it does that all across. But we want to limit it so we could do a comma separated values file. We're gonna click next, and it's pre selected comments, so we want to leave it on there. You could include the field names by clicking here as the first row. We'll just leave that blank, and then the text qualifier is the quote. If you want to change it to either of these other options, but we'll leave it as quotes, then click next and we already have the file location in there. So we'll just leave that and we'll click. Finish. We don't need to save our exports. Steps will close it. I'll go to my computer, then we can open that up. And there we are with a comma separated values file in our note pad document saved your computer, and you could then use this to import it into excel or save it and send it to someone. Or we're even to bring back into access if you want. Okay, so that's the comma separated ice for a text file. Now let's say that we wanted to send a report to our boss. Let's go ahead and send our employees Northwest Region report to our boss as a PdF so that he can print it or send it on to his boss or whatever. So we're gonna go up to the export and PdF, and it's just gonna ask us for a file name and save as pdf Gonna put this in documents as well. And of course, with PDS, you have the option to adjust the size you can if you're publishing it or printing it. You want the full size. If you're just sending out online like sharing it in an email, you can use a smaller size, so we'll go with we'll stick with that default. We'll click, publish and close. Now we could go open that one up that's gonna open up in chrome and there is our pdf. Of course, you could easily attach this to an email or however you wanted to send it along. Okay, And finally, let's go send one of our tables and we'll export this to Excel. We'll go ahead and click on Excel, and we've got a file destination for it. It asked you what kind of file format we're gonna go with XLs Sex for Excel. But you can also choose older versions of Excel if you need to. And then we'll save the formatting with it, and we'll go ahead and open it and that will open up Excel and bring it right up. Click on that and we'll cook. Okay, well, quick, close and we'll take a look at our Excel file and there it is. You can see it carried over all of our headings and all of our data into an Excel spreadsheet. Okay, so those air three ways, you can export data to excel to a text file, comma separated values file and to a pdf 26. Advanced Section Introduction: Hi. Welcome back. And welcome to the advanced section of this course in this section. We're going to start with a brief introduction to VB A or visual basic for applications. We're going to talk about more advanced forms using Vehbi, eh? And by the way, V B A is basically just computer programming language that you can use to supercharge programs like Excel and Access. Some of the things in this upcoming section are going to be reviews of things you've already learned to kind of cement that for you. But most of it's gonna be new information or new directions. You can go with what you've already learned. Now, here's my warning slash challenge to you. This section is going to go faster and particularly these next three lectures about vb A are gonna go quite a bit faster, but I don't want you to get freaked out by it. Vb Achon seem really complicated and overwhelming at first, and I'm gonna throw a lot at you in this introduction. But just keep in mind, you don't need to remember all that. In fact, you don't need to remember hardly any of it. It's just to give you an introduction to what's going on in V B. A. As we get into the Advanced Forms section and we start to work with smaller snippets of V B A in the forms, you're going to start to learn it piece by piece, and it'll make sense. So just absorb as much as possible in these first couple of lectures and start to get excited because we're going to start to supercharge our access and your skills are going to really go to the next level. So I'm really excited to get started in this section. Thanks for listening, and I'll see you in the next lecture. 27. VBA Introduction: in this section, we're gonna learn about VB A. The first step to enhancing an access database and adding more functionality is to understand visual basic visual basic for applications or V B. A. Is the programming language found in Microsoft products that allow you to add more functionality than the software is inherently capable of? V B. A. Is commonly used in programs like Excel and Access that air data heavy to speed up the data entry or data manipulation and analysis processes. So it's a good tool tohave when developing a full database so that you can build features that are unique to your specific needs and ensure that you have a functionality required to use your database efficiently. We will be referencing bits and pieces of VB A throughout these advanced topics in the course to show you direct applications. But to start will go through an overview of V B A and its use in access. First things first, we need to open the V B a window, go to the create tab in the Microsoft window and click on Visual Basic on the right hand side. Now there's an internal macro feature within access that attempts to simplify VB A by giving the user preset functions. But it's limited in its capability and lacks a strong air approving system, which we'll see in a future video. For these reasons, I recommend that you jump straight into visual basic itself, since it a simple and straight forward to learn and won't limit you in capability or in air proving. Now, when you click on visual basic, the V B a window will pop up. You can see over on the left hand side of the screen. There's a Project Explorer, which will list all of your forms, tables, queries and modules that have code associated with them. The lower left hand side of the screen will display the properties associate with whatever you have selected in the Project Explorer. This properties window is useful in tools like Excel, but we won't use it much in Microsoft access. Since access has an internal properties sheet that is a bit easier to use, you can see I have a form preloaded into my VB a window here. This user log in final form will build this in a later video, but you can see this is how items will appear in the project. Explore. Now we can look up the top of the screen here at some of these tools. Obviously, this is the save button here, which I recommend you use often, and the undo button where you can just use the controls. The shortcut. Like all Microsoft applications, these buttons here the pause play stop buttons. We very useful when we talk about air proofing in a later video. These next two buttons will just move the cursor to the Project Explorer or property sheets windows. But the third button here could be quite useful if you get stuck at any point. This is the Object browser, which will show you all the terms and functions you can call from V. B. A. We'll talk more about this later, but keep it in mind in case you get stuck trying to figure out VB a terminology. Next, let's open this debug drop down. You can see here that there are some tools that will become useful for our air proofing in a later video, so keep those in mind. Now let's step to the insert dropped down here. This is where you will go to add a module, which we can go ahead and do now. I mentioned that I have a form over here that has some code in it. The code that exists on this window is time to this specific form, which will learn how to do later. But let's say I have some code that I want in my database that isn't tied to any particular form or object. That's when we would want to use a module, which is basically a library for you to build. Code into that code can then be easily referenced from any form or object without having to repeatedly copy and paste it from place to place. Now you can see in the Properties window that air module defaults to module one. We can go ahead and change that something else. Let's say test module. If you weren't already following along, go ahead and go through those steps because we'll set up a simple code in the next video. So do those steps and then you can keep this module window open and I'll see you in the next video 28. VBA Setup: Okay, So in the previous video, we covered how to create a module in your access database. VB a code. So in this video, we're gonna talk about how to actually set up your VB a code from this point forward in your module or in your four more query or whatever you happen to be writing code for. So your code in V. B. A. Is gonna work in subroutines, which are basically the little bits and pieces of code that actually run on your database. And you could put a whole bunch of subroutines in a single module and string them all together to make something awesome happened in your database. So there are two ways to start your sub. The first is a public sub, so we'll type public sub and then whatever you want to name your routine so we'll just say name of routine open parentheses, close parentheses. Enter. Now, when we do this, you can see the system will automatically put an end sub here. For us, that means everything between this public sub and this end sub is the code, and all of this code is called name of routine. The second type of subroutine that you can have is called a Private's up, So we'll type private sub. And let's name this one message box testing because that's where we're about to do and hit . Enter and you can see the same thing. It'll go ahead and insert your end sub down here for you. So these are two completely different subroutines. The primary difference between a public sub and a private sub is that your public sub can be called from anywhere else so we can call a name of routine and any of our other subs, and the system will inherently know what that code is. Our private sub can Onley be referenced from within this specific module. So if we create another module in here or if we create a form or whatever has code with it , we will not be able to call message box testing from anywhere else in our database. So the next thing we want to talk about or variables now, these variables are what you'll use throughout your code to store different values or different numbers or whatever it is you're trying toe work within your database. So we're gonna go ahead and hit enter a couple of times here, we're gonna type dim color as string. Now, we can break this down for a second. You may have noticed that when you type certain words, they will automatically become blue versus other words which will remain black if it becomes blue. It's the system recognizing that this word has something to do with coat. Whereas the words that remain in black phone are the ones that you're naming your variables , you're naming your routine. You're setting up a value in your variable or whatever that IHS Now, in our case, dim means declare. So we're about to declare a variable. Now the system will recognize whatever this next word is as a variable wherever it occurs in your coat. So in this case, we've called our variable color. The last thing is, as so this is telling, the system were about to tell you what sort of value you should expect in this variable color. And we've told it that this variable is gonna be a strength. Now there are a whole bunch of different options that you can put in here. You can use an integer you can use double both of those would work for a number value. In our case, our color is gonna be a text. So we said string string in this case means text. The most common ones that you'll use are gonna be string integer and double. Now, integer is gonna be used for whole numbers, whereas double will go ahead and include any decimal places. And that's something you need to be really careful about. Is at the start of your code how you declare your variables because that's what the system will expect throughout the rest of your code. Whenever that word appears, the next thing we want to do is actually set up of value to be stored in this variable that we created. So we're gonna type. Color equals. Now the system knows we're about to tell it what to store in this color variable. So in our case, we're gonna say color is blue. Now, since color is a string, you need to start with quotes, blue end quote and this quotes basically tells the system. Whatever's within these quotes is the text that we want you to store its color. The quotes won't actually appear when we call this variable up later, you'll just see the word blue. And all it says is, Hey, this blue is text. If we had done something different like say we were to store the number nine, that wouldn't need quotes. It's not text, it's a number. The system would try to recognize this as a number. We would eventually run into problems. Since this is a number and we've told the system that are variable is text. So we're gonna change this back to blue and move on now, since we're just getting started with V B A, we're not gonna do anything super crazy in this specific lesson. We're just gonna talk about message box. So now we've told the system that we have a variable called color, and we've told it that that color equals blue. So now we're going to check and make sure that the system is actually saving are variable correctly. And we're going to do that by using a message box. We're gonna type message box space color now. One thing you can note right now is this yellow bar that's appeared below the text. You can see the word that's bold right now is prompt. That's what's gonna appear in the actual message box? Then you concede there's comma space buttons as VB message box style that would tell us what the buttons are that will appear in the message, and we'll mess around with all these in a second. But you can see that the common spaces will move on and tell you what to enter into that particular field all the way throughout this message box prompt. But for now, we're just gonna have it spit out or color. So that's all we want. We could move on. So now we want to run this piece of code and make sure that actually works properly. So in order to run your code, put your mouse anywhere within the subroutine. If it's outside of the subroutine or in a different subroutine, it won't work. So we want to put the mouse inside of the subject team and then click the play button. Now you can see it will pop up this little message box, and it says blue, and that's what we expect. That's what we've told. The code to run is a message box that tells us the color, and we know that we've said our color variable to be blue. So this means their code is working. So now we're gonna step into a slightly more complex idea of a variable. So in this case, we've declared our variable within the specific name of routine routine that we've built here. That means that this variable only exist within this specific routine. If I came down into this different routine and I tried to call the variable color, the system won't know what to expect. So there's a couple of ways we can get around that, and we can pass variables between different routines, which could be very, very helpful. So the first way is to use global variables. Now, global variables actually get declared outside of any of your other subs, and they get declared using global instead of dim. So in this case, we're gonna replace the word damn with global, and that still means we're declaring this variable. But now this system will know it can access this variable and use this variable from any other routine that we create. So let's create a variable called ice cream, and we'll tell it that the ice cream is a strength, so a text variable again. OK, so now we've declared this ice cream variable to be global, which means that we can use it in any subroutine. So we'll come down here into our same routine we've been working in and will create a variable pay scream. And let's say ice cream is chocolate. So now to test this, we're gonna change this color right here in the message box toe ice cream. Now, when we run this code instead of getting a message box that tells us what the color is, we should expect a message box that tells us what the ice cream is. And in this case, we've said ice cream to be chocolate. And even though we've declared the variable outside of this the substrate teen, since it's is declared as global, we should be able to use it anywhere so we can go ahead and test this but going up and clicking the play button and we can see the message box pops up and it says chocolate. So we know that this code is working now. I mentioned before that there were two different ways to pass variables through subroutines . So the first way is to use a global variable like we've done up here. And then we could put that global variable in here, set the global variable up to be whatever we want, and we could continue using that variable even though it's not declared specifically within the subroutine. The second way is to use these parentheses that you find at the end of the subroutine. So bear with me for a second, and this will all make sense. So we're gonna type favorite color as string comma space favorite dessert as string and click out. So now what this does is it tells the system that any time we call a message box testing code, we expected to receive these two variables along with it. The first is the favorite color, which should be a text, and the second is favorite dessert, which should also be text. So now we're going to go ahead and call message box testing and then put in parentheses. Now you can see immediately the system recognizes we're calling this message box testing coat so the system will look through the rest of this module and find where that code exists. Now that it's found it, we can see, it's telling us that it's expecting a favorite color and a favorite dessert, and that both of those should be text. So let's go ahead and type color and ice cream. Now, color and ice cream are two variables that we've already declared. And in this particular subroutine, we've set those to be blue and chocolate. So now we're gonna pass those variables into this code using color and ice cream, which we've already set up here. So now we can write this coat down here using these variables that were passing into the code from our previous subroutine that we've already written. So let's create a new variable called message now. So far, we've always set one value into these variables that we've created previously. You can actually overwrite those guys later on in your code. So if we start with our message being hello, my favorite color is now. I'm going to use the and sign, and that just tells the system I have more Texas coming, but this next bit of Texas actually going to be a variable. It's not gonna have quotes around it, but it's still texts were okay. Well, type favorite color now favorite color, remember, is the variable that we've told the system to expect. So whatever it feeds through in this case, it will be the color that we pre set up here. Whatever it feeds through this variable will then appear here. We'll do another and and an exclamation point. Now the system uses these an signs to tell when it should string the whole message together to get one long piece of tax rather than separate pieces of text. And we'll see this when the message box pops up. So next we can go ahead and create our message box. Now we want the message that appears to be this variable that we just created. So we'll type message now. We mentioned these buttons earlier. These next couple of sections will kind of shape how the message box appears. So this particular area is meant for the button. So we're gonna say vb Okay, Onley. And that means Onley an O. K. Button will show up on that box. Now you can see this option that's directly above it. VB Okay, cancel here. This would mean there would be an O K button and there would be a cancer But there's also a VBS No, which is pretty common one, which means there would be a yes button and a no button. When you start to introduce multiple buttons, you need to tell the system how to handle the other. But so if we we're to click cancel what would happen to the code versus if we click? OK, and the code knows to keep stepping through things. So it's a little complicated and we won't get into all of that right now. But right now we just want the box to have an O. K button. Next one we can see here is title. So we're gonna go ahead, entitle this box and we'll just title it. Favorite color. So now we're expecting the system to display a message box that says Hello. My favorite color is and will display whatever variable we've told to pass through this variable here so we can overwrite this variable again. So we've already told it. This is what the message should be. Now we're going to create a new message, so this time will say hello again. My favorite dessert is again use the and sign to string a whole bunch of text together. In this case, we want our favorite dessert variable, and we'll put ice cream at the end. And now we have an entirely new message string. So now this system will run through this line by line. So we'll see here. It's expecting us to declare a variable called message and that it's a text variable. Here. We set that message to be our initial message about our color, and now the system will show us a message box that says, this is my favorite color. So now we're resetting this variable, so this will no longer stand for my favorite color is message. Instead, it's gonna be replaced with this my favorite dessert message, and we can again create a message box that shows our message. We only wanted to have an O. K button and this time will title it Favorite dessert. Now, the last thing we want to do before we actually try toe run. This code is look through our old code and make sure that there's nothing in there that will throw us off. So when we step through this, we see the first line here is telling the system we have this ice cream variable. We've declared it up here. It's a global variable. We can call it from anywhere, and it equals chocolate. So we're good there. Now we're telling the system to expect the variable called color, and it is a text variable. And now I've told the system are color is blue, so we're good there. Now the system is going to tell us with message box what is stored into the ice cream variable. We probably don't need to do this. We're already gonna have it. Tell us down here what our favorite ice cream is and what our favorite color is. So don't need this message boxes. So we're gonna put a single a possibly there. Now you can see when you click out of this line, this entire line will turn green. Anything after this possibly will become green. The apostrophe is the comment in V B A. So is the system reads through this code. It will hit this line and it will realize this is a comment. Look, it doesn't actually count for any code, and it will move past it without trying to read anything in this line. So if you ever have a line that you don't need any more. You can comment it out. Or you can also use the comments to actually put a note in your code and describe what it is that your code does and how it does it, so that anybody who comes in and looks at it later can realize what the system is trying to do. The last thing that this code will do is call our message box testing routine, which we've written down here, and it will pass the color and ice cream variables into this routine, so we should be good to go ahead and test this is we're gonna go ahead and come up here and hit the play button again. Make sure that your mouse is within the name of routine Sub and not the message box testing . So but your mouse anywhere in here and click play and we can see our first message boxes work. It recognizes that the color is blue, which means that we have successfully passed our color variable. You can see the title of the message box is favorite color and that the okay button is the only thing that's available and Now we can see our second message boxes popped up. It says Favorite dessert. So our title is working. Our message looks like it's good. And again, the last thing is the okay button. So this is the basis of writing VB a code. The variables had to declare subroutine comment. Lines are also very important, and we'll get into all of this a little bit more specifically for access as we move through the rest of these videos. 29. VBA Debug: Okay, so we built all of this code in the previous video, and in this video, we're gonna talk a little bit about air handling. So occasionally your system will run into some problems in your VB a coat. Now, V b A is actually really good about telling you where that problem is and what that problem is. And that's what we're gonna mess around with in this video. So we're gonna go ahead and create a new subroutine and we'll call this error handling, since that's what we're learning about and hit Enter. Now, in this error handling routine, we're gonna create a new variable. We're going to declare the variable end, and we're going to tell the system that n is an integer. So now the system is expecting us to give it a number for the value N. And so we're gonna ask the system to do a little bit of math, and we're gonna tell it to divide one by zero, which will definitely cause a problem. So you can see the system doesn't immediately recognize that there's a problem with this line. It hasn't actually tried to execute what we're asking it to do yet, So you can also see that the rest of this line over here in green is a comment that won't actually affect our code in any negative way. They're sometimes that the system will immediately tell you there's a problem. So if I just typed, damn and I enter without telling it what variable it's expecting, you can see it will immediately throw a compile error and will tell us there's a problem here. And if I click OK, this line will be highlighted in red. So it's very easy to identify. Compile errors and these air usually errors. The system can immediately recognize you set up a function wrong. You set up the variable wrong. There are obvious errors, but this an equals one divided by zero is a little more subtle because the system hasn't actually tried to divide one by zero yet, so we can put a message box here to show us, and and now if this were to run successfully, we should get a little and number at the end. So we'll go ahead and click in here anywhere in here and we'll click play just to see what happens. We can see that immediately gives us an error and it says Hey, you tried to divide something by zero which doesnt work now Your options when VB a throws you a message box are to either end your code completely which will just kill the code from running but doesn't tell you where the problem is. If I cook end, it will just bring me back to this screen. It doesn't tell me where the problem waas I know that the problem was divided by zero but I have no idea where in my code that waas But if I run this and I instead click debug, it will take me directly to the problem line. It highlights it in yellow and now I know my problem was right here dividing by zero I can see this is the line that's divided by zero and now I know how to fix that. One other thing that I'll note is anywhere that you have a variable in your code. If you hover over that variable, it will show you what's currently stored in that variable so we can see that right now there is zero stored into end and this will happen anywhere in your code. So when you end up with a really, really long code, you can go back up to the beginning, and you can kind of check your variables as you go through it and see how your code is actually behaving. So we're gonna go ahead and click the stop button, and this will kill the coat that when you debug, usually you're able to fix the slide however you want, and then keep running your code and it'll pop up with a little one. Alternatively, if I run this and I click debug and I was having a problem finding the air or whatever, I could just come up here and click stopped. Kill the code, and we can now see the code is ended. V. B. A has a couple of different ways to handle airs in the code. The 1st 1 is on error. Go to zero. So now this is telling the system. Whenever you encounter an error I want you to go to. In this case, we've put zero. Now the zero just tells the system. Treat errors like you normally treat. There's pop up with the irregular message box. Don't know anything fancy So if I click run, we could see we get the same exact message box that we got before. Second way to handle Ares is to tell it to go somewhere else in your code. So we're gonna type error handler that you could name different lines in your code. So if we come down towards the bottom and we created air Handler line like this and then I type in here message box, we found an error. So in this line, when the system has the problem that will jump immediately down to this air handler line, it will skip this message box instead. Just pop up and say we found in there now to show that this is actually how this will behave if you go up to the debug We talked a little bit about step into before we mentioned that this would be important. You can also use step into just by hitting f eight. So I click on this the first time Now, this is how you would walk line by line through your code so you can see we started on our private sub line. It says, Hey, we're about to run the sub It's the air handling sub. I'm gonna push f eight. Now we're telling the system this is what I want you to dio when we have a problem, push a fade again. You could see it will jump over this declare line because the system doesn't actually do anything. We just told the system what it should expect. So this is the line that we know is gonna have a problem. If I click f eight, you could see it completely, skipped our message box line and instead jumped down to the end here with our error message box. If I click my feet again, you can see well, actually get the message box saying that we had an air. This is now on the end sub. If I push a fate one more time, it will completely endorse up. So walking through your routine like that could be very, very helpful. When you have a really long routine and you're trying to find the specific line of your code that's causing problems with specific variable that's getting messed up somewhere. So definitely keep in mind, debug Step into which is F eight, the third type of air handler you can use is on air resume next. If we use on air resume next. We don't actually need this air handler down here so we can get rid of that now. This will tell the system when you encounter an error on air resume next. Which means just keep going to skip over whenever they are. Problem line was and try to keep working through the coat. So if I run this now, you can see it will pop up a message box, and it just defaults zero into our variable value, which we know isn't right. We were trying to do some math, and here we know that this line caused the problem. But we've told the system ignore any errors that you have and just keep going. So it went. I had tried to display a message box with their variable. Now we can get rid of this and let's just say an equals one. Now, in this case, we know we're not gonna have a problem. And if I run this, we can see our message boxes one. So that's a quick overview of how you can use the air handling system in V. B. A. And how you can debug your code to try to find the details of what's going around 30. Advanced Form Properties - Part 1 : hi and welcome back. So in the next few videos, we're gonna talk about even Mawr advanced forms and properties that we've been doing so far . And in the next two videos, we're going to go over property sheets. Some of it will be a little bit of review, but there will be some new stuff, too, and it will really be helpful to get a set up to dive into our advanced forms that we're gonna create. So in order to do that, we're going to go up to the create tab, go over to blank form and click that option. You can see that it will automatically open in the layout view. And if you right click on the name up here, you'll remember there are three different views that we can put the form into layout view is gonna create columns and a sort of organized table for you to put your text fields and labels and all of those items appear into I don't typically use layout view. I prefer to use design view, and I'll talk about that in a minute. And, of course, form view will show you what the form will actually look like from the user perspective in the database, so this could be very helpful as well. But for now, we're gonna go ahead and put it into design view, and you could see in design view there's a grid that you can space everything on, which is pretty helpful. Designed view will also give you more direct access to some of the code and different properties that you can put into the field and text boxes that you can't do with layup view . So now that we have this form created and we're in design view, we can go ahead and we could take a closer look at this over here. So right now, this is your field list the societal tie, certain text boxes and things that are on your form to a field in a table somewhere else in your database. And we talked about this in previous video. So instead, we're going to talk about a different pain that you can pull up over here. So if you go to the design tab at the top here, you can see add existing fields or property sheets. So we're gonna click on property sheet. So now you're going to see the properties, which is this list of any object that you have selected. So right now we don't have any objects on her actual form. So they click in here, you can see selection type section, and right now we're just looking at detail. We're just looking at the form itself. We don't actually have any items on here that we can select and mess with properties for So you could see that these air, all of the properties that we can change for our form. And usually this list is a whole lot longer when you're looking at text boxes and stuff like that. So they actually break it out into different categories for you, so that you can actually toggle through these and find the specific property that you're looking for. For now, we're just gonna change the back color. Justus An example. So right now you're back Colors said as a default to background one, which is usually something white. Depends on what you have your theme set to in your database. Over here, in your themes and colors. You can change these in a change of presets. But for now, we're gonna click this drop down and you can see all of the other themes that are available is a part of your theme and your color set nine times out of 10. I just used these three little dots right here that you can pull up and we'll show you all of your theme colors and standard colors. So it's a little easier to pick from this so we can pick a nice blue for our background and our actual form. So now we can apply things on top of this form so we can look up here and these are all of the options of what we can put it. Let's start with something really simple. We're just gonna put a little box in here, so click the box click and drag to create your box. Now we can see over here we have a lot of other options that are in this property window so we can see Rectangle Box zero. Box zero is the name of this box, and we'll talk a little bit about naming in a second, but you can see that right now the box is highlighted in orange because selected, and we see Rectangle Box zero if I click back into the form and UN selector box now we see section detail and we see that the boxes no longer highlighted. So when you want to highlight something, you want to see the properties for that specific object, and you should see it highlighted orange. And that's how you know you've made the right selection so we can take a look at the actual properties that are available over here for this box, and you can see we have back color again, which is the same as the form that we just changed to blue. So if I click this little drop down, we can click something else. So let's say we make her box green, so now you can see it automatically fills this box in green, and we could see her back. Style has changed to normal, which just means solid. We can change this back to transparent if we don't want to see it anymore, and you see the color goes away. But for now, let's leave. This is normal so we can see our box really well. The other thing that I usually changed with boxes as you have a couple of different border styles usually leave minus. Sell it, but you can see there's a whole bunch of other options in here that you can dio, so we'll leave. It is solid for now, border with. We can make the border a lot thicker if we want to. So let's just make it something drastic so we can see a difference. And we can change the color of your border as well. So let's change it to be black instead of this gray that has selected. So now when I click out of this, we can see all of those properties have actually changed. So let's go up to your form tab, right click and click form view. So now this is what the user will see when this form opens so you can see it went ahead and it applied the background color this blue that we selected across the rest of the form. So if you don't want to do that, you can actually set your form up as a little pop up window instead, which is super convenient and very helpful. But for now, you can see that our boxes showed up our properties air correct. So we know all that's working, so go ahead and right click on your form tab and go back into design view. So one of the other super helpful properties if we go ahead and click on the box, let me go back over here. Let's talk a little bit about this visible property so you have the opportunity to hide things in your form. So this could be really helpful when you're developing your forms in your database. Maybe put a little note over here. That kind of reminds you of Hey, I did this because X, y and Z you can hide it to make sure that your user can't actually see that note or whatever it ISS. Or maybe you have something on your form that you don't want to be visible until they filled out a certain text field or whatever. We can set that up, and it all operates on this visible property. So right now I have the box selected and we can see visible. Yes, the only other option is this is no. So let's just say no goto form and go to form view. We can see that the box is gone. It's no longer visible. It doesn't matter what I do where I click the box is not going to show up. But if we go back to design view, we can see the box is actually still here. It's visibility. Property is just set to know. So for change Suspected? Yes, Go back to your form, right click form view. And now we can see the box showed up again. So this is a really helpful property. I strongly recommend that you take advantage of this property and use it. The only other major thing that we need to talk about in the properties is this one right here name. Let's name it something else. We'll call it Green Box and click out of it so you can see that the name updated here as well. So when you have a whole bunch of things, you can actually click this drop down and you could toggle between the different things that you have on your form. So it helps if you name them something that you're going to recognize that when you have a whole lot of text field or something, it's easy to jump between them and change the properties that you need to change for now, we're gonna leave Green box selected. Now there's another reason for naming things other than just finding them in this menu. There are actually two other reasons. The first thing is, when you use visual basic, if you want to reference an item on your form so you wanna auto fill it to something you want to save that value somewhere special in your database. Whatever it ISS, you need to know the name of that item. So it helps if you name things really well. And if you remember those names, you're coding will get a lot easier. And we'll talk about how to tie your code two forms in a future video. But keep this in mind. Naming your items could be really important. The second reason that you might want to do this is this tab order up here and we'll talk about this again later. But if I click on tab order in this list this custom order right here, you're going to see a whole bunch of different text boxes and drop down menus and anything that you put on your form for your user to interact with. Now you can reorder them in the sections so that as the user hits tab on their keyboard, it will walk them through these text boxes or drop down to whatever in whatever order you put them in here. And I'll show you an example of this later. But this is the other reason that you should name things clearly so that you know exactly what you're messing with when you go into this tab order. Okay, so we're just gonna keep going in reviewing these advanced properties in the next video, and then we'll dive into incorporating some vb a into the forms as well. Thanks for watching, and I'll see you in there. 31. Advanced Form Properties - Part 2: Okay, so the last thing I'm gonna show you in this section are some of the other properties you might see. So just come up to this insert Manu, appear and click this little text box and click anywhere on your forum to drop it in. Now we can see we have this label and this label just says, Hey, this is what this text box is. And then you have the text box that the user would be able to enter into on a form. So Justus his example. If we go ahead and put this informed view, we can see our label and we can see that weaken type whatever we want into this text box. So go back into design view. And let's look at some of these properties now you can see right off the bat. This is a much longer property list. There's a lot more that you can do with these interactive items thing you can with something simple, like a box. So we're gonna go ahead and break this down a little bit, so go over to format and this will show you everything that you can do to change just the format of what you have selected here so we can see some of the same options. We can change the background color or the border. Let's focus mainly on these font options for now, so we can change the font size. That's pretty straightforward. Say we want it to be 10 instead of 11. What could do that? We can change the alignment so we can say, Hey, align this to the right instead of wherever we had it before. Fought Weight is whether or not something is bold so we can go with bold and we can see our text Bolds over here underline. It's pretty straightforward. We'll just say Go ahead and underlying this and then italics weaken Say, go ahead and italics this as well. So now we can see our text in here is gonna be bold. It's gonna be underlined. It's gonna be a tallix, and it's gonna be aligned to the right edge of this box. Four color is actually the font color, and it could be a little misleading because it says four color instead of font color. All this means is that anything in the foreground of this item in this case, the text is gonna be in this color. It's to contrast with back color right here, which is gonna be the background color of this box. So just as an example, let's change this. Let's make it light yellow, and we could see the light yellow is filled in the background of this box versus the four color. Let's change this to something like red, and we can now see our text is red and these properties will carry over to your actual form view. So if we right click and go to form view and I start typing, you can see all those properties apply to the text that I'm typing into this box now. So let's jump back to design view now. Names will be in the all tab. It will always be at the top, so we don't want to name this text. One. Let's name this yellow text box. Generally speaking, when you use names in V B, it's better not to have spaces, so I recommend that you don't put spaces into any of your naming. It's better if use capitals to designate any word, so that would be my recommendation on naming. Now you can go to data control source would be tying this to something else somewhere else in your table. So if you wanted this to be associated with a certain column in a table or whatever, and we'll mainly talk about linking these up in the context of V B A, since other videos have covered how to properly link things in an access database. So we're gonna jump over this section. Good event. And these are all the different things that you can do in VB a or with code in general. So you can have this text box to do something specific when the user clicks on it, or after the user is around. Update on this text box I'm get Focus is very similar to clicking into the text box when they double click. With all of these things, you can have a code that's associated with it. So it may be that when they fill out this text box, you wanted to show up a different text box down here, and this is where you would give the database that instruction for what to do. Other is anything that didn't really fit into any of these other tabs but usually it's easier to just get to it from the all tab. So this is the other item that drops in with this text box. It's the label, so you can see here it's called Label to. We can call the label whatever we want to call it, so we'll call it Yellow Box Label. Caption is the text that actually appears in that label, so we'll say yellow box with a colon. Now when I click out of it, you can see that it is updated here. Now we can see that, actually, the boxes too short so we can grab this little edge and drag it over. And now I could see the whole thing. So we're good now If you want to space these things relative to each other, that's when you use these gray boxes that you see in the upper left corner and that will move an individual item relative to the other one so I could move these right next to each other. Otherwise, if you click on one of these and you drag it, it's actually gonna move both because the system recognizes that this label is tied to this box, which is super convenient. So I recommend you leave them linked. And if you're trying to space them relative to each other, just use these little gray boxes in the upper left corner. So now, just to kind of wrap everything up, we're gonna drop one more text box onto our form. I'm gonna put it above the yellow box here, and I'm gonna come over here and I'm gonna name this text box. So in the all tab of your property sheet under name, I'm gonna name this text box. White textbooks. Okay, so now I have a white text box and I have a yellow text box. If I go ahead and go into form view, we could see the cursor automatically loads into the yellow text box. And if I had tab, then it goes up to the white text box. Let's say that's not quite how I want it, because my white text box is on top. I really want to start with my white text box. So go back into design view, go up to Tab Order. And this is what we talked about earlier. You can see I've named my boxes yellow text box and white text box, so they're very straightforward. I know exactly which one is which, and I want to start with white text box. And the way to do that is to highlight it just by clicking on the line. That's kind of in between the gray and white here. And then we're just going to click and drag it up to the top, and we can see white text boxes now at the top. So I'll do that again just to make sure we're clear. Select the line just by clicking on the little grey box. Click it, drag it up and now yellow text boxes first. So we want our white text box first. So I'm gonna flip these one more time. So now we can see our white box is gonna be first and then the yellow textbooks, and that's what we want. So click OK, Now, go ahead and put this into form view so you can see our Christian house starts in the white text box, which is what we want. And if I hit Tab, it goes to the yellow textbooks. So this could be really helpful. Kind of the last step you should do when you create a form is to arrange your tab. Order to be user friendly so it makes sense to your user which areas of your form they should be filling out first. So this more or less wraps up the majority of the properties aspects of making an advanced form. Thanks for watching, and I'll see you in the next video. 32. Create and Format a Blank Form aka Intro to Advanced Forms: Okay, so in this section we're going to talk a little bit about some of the more complex features that you can use to build access forms. So to start off, we're gonna go up to create and select blank form. Now, we have a new form here. We're going to right click on it and change it to design view. So now the form we're gonna be building we're gonna call the product form so right, click and save, and it will prompt you for a form name. We're gonna call this product form and click OK. And again, I recommend that you don't put spaces in between your words for the purpose of V B A which will start to become clear later in this section. So click OK. And now we have a product form. Now is a general set upon forms. I like to go ahead and color the background of my form, something different. So it's really clear when I've put these white items onto it. So I'm going to go into my property sheet. You can see we have details selected, which is good. This is this form area. This is what we want So we're gonna go to color, click the three dots and change it to a nice light Blue. Okay, so now we're gonna do a quick review of these items that are up here. So text boxes were pretty straightforward. This is just what a user types directly into the label is just text. It's just like instructions that you might have. So I'll type instructions in this label. You could see that this isn't something that the user will interact with. It's just something that will appear on the form for the user. This is a button so we can put this button somewhere on here. This is the button wizard. This will set up an automatic action for the form to take. When you click on this button, we're actually going to start to custom make our own actions. So we're gonna click. Cancel on this. This is if you have different tabs and stuff that you want on your form. So if I insert this you can see I can have two entirely different pages. Two entirely different tabs. This would be for hyperlinks. This is Web browser. Most of the time, we're not going to use this. This is a navigation control. Most of the time again, you can use the tabs instead of the navigation control. This isn't something that's commonly used. This is a frame. So if you have, let's say that we wanted this text box and this label to be a frame I can put a frame right around them. You can select from this list, but nine times out of 10 you're not gonna do that. Just click cancel, and you can actually change the frame name and here, So if you have, like general customer information, that's something you might want to use a frame for. And maybe in this frame you would have phone number, name, email, that sort of thing. That's all information that gets grouped in together. So the frame is really useful for clarity, purposes on forms. But for now, we don't need the frames. We can go and get rid of that. We can go and get rid of this tab Control as well. Movie over. Another one that will talk about is the combo box, and this one will actually talked about a lot so we can go ahead and put the combo box on here. We're gonna manually control or combo box. So just click Cancel out of this window and go ahead and add one more combo box down here as well, and click. Cancel again. This is a line. This is just for organizing your form. So if we wanted to separate out some of this information, we could draw a line in here and you'll see this line when we go into form view and these air toggle buttons. And these were like, Yes, no option a option B. So go ahead and insert one here. You could see that this is a very big button, and I'll show you what it looks like when it's pressed in a minute. List box is very similar to combo box, but in one of them you can select multiple options and then the other you can I select one option. So the combo box. You can always select one option from the list, whereas with list box, the user can actually select multiple options by holding down the control key. Usually you're gonna want to use a combo box, so that's a good default. If you're not sure which one to use this box we actually used in the previous video. Just add some color to our background check boxes will talk a little bit about so you can go ahead and insert one of those anywhere on your form. This is to put an image on your form. This is an option button. You probably use the check box instead, so we won't talk too much about that. And there's a sub form, so sub form is a way to embed a form within another form. Well, maybe talk about this in a later video, but it's not that commonly used. So we're gonna jump over this one for now. So we've put some stuff into this form, right? Click on this and go to form view just so we can see how it all looks. So I have a text box that's pretty straightforward. I can type into it whatever I want to type into it. This combo box would usually have a whole list of items, but because we haven't put anything in here yet, you're not going to see anything. But this would drop down a list, and they could select from the list the check box again pretty straightforward, and you might have noticed when we started this check box, it actually pre filled it with a little black square. It's because the system wasn't sure whether or not we wanted the check box to start unchecked. Her checked and we'll talk about how to control this. Using Vehbi, eh? A little later. And then this is the toggle button so we can click on this toggle button. You can see it changes colors. It's very clear that this is selected. If ayan click this button, you can see it goes back to its original color. So this is good. If you want to have, like, a yes, no sort of option on their you don't want to use a check box. For whatever reason, you want something that's a little more obvious. Usually I use check boxes so we won't talk too much about toggle buttons here, So that's a quick review. Go ahead and go back to the design view and in the next lecture will start creating real combo boxes 33. Adding Working Combo Boxes: OK, in this video, we're gonna put actual working combo boxes into our form that allow the user to select from the product and category records in our table. Now in the example database. You should have this products table here, So we're gonna go ahead and double click on this and we can see these are the available products that it put into the database just preloaded him. So we see each of the products. Each has a cost. We can see that each has a category, a description and a name. So this is the table that we're gonna be using to make our little product form, and you'll be able to select the product that you want. You'll be able to see the description and you'll be able to see the cost and be able to select it from category toe limit down your list a little bit and filter down what you have available to choose. So let's go back to the product form and delete all this practice stuff there. And now if we look at the product table, we want to be able to choose from this list of products. Nothing else. We don't want anybody to add anything else to this product table. We just want to be able to choose from this list. So that means we're gonna need a combo box for this list. So go back into your form. Go to design. Go over to combo box, which is this one right here. Click on it and drop it anywhere on your form. Click Cancel to get out of this. If we have time in a later video, I'll show you how to actually turn this off in your settings for your database if you don't want to use it. But for now, just go ahead and click. Cancel now. I showed you earlier how you can change this label by clicking into the caption of the property sheet over here. You can also change it just by double clicking into the text box. You can highlight and change whenever you want, so we're gonna call this product. This is gonna be the final product that we choose. Now. Go back to the product table. The product description. We probably don't want to choose the description. We don't want to see it, necessarily. It might help us choose which one we want. So maybe we show this in our combo box dropped down. But really, we only care about what product we're trying to select now. They're nine items on this list, and that doesn't seem like a lot in this video. But when you're working on a full database and there could be hundreds of items, it helps a lot. If you have something that you can filter by in this case, this would be our product category. So we're gonna go back over here, go to design and enter another combo box. This is gonna be the product category. So cancel out of this double clicking here. I let it all and we're gonna type category. So now we're going to select the category. Maybe we choose something like tea. And when we choose tion that combo box were only going to see these options. We're not going to see all nine of these options anymore, which could be really helpful. The last thing we have is the cost per unit. So maybe on our product form, we want to see the cost per unit. But really, what we want to do is we want to be able to tell the system how many of these we want. So let's say this is an order form, and we're gonna need a button. So cancel out of this. And to do that, I jumped up here and click this guy with all the accident. This is the button. So we're gonna have a button that we can click on that says add or say a for something like that. And then we're gonna have a quantity, which is gonna be this text box. Insert this text box on here. Let's go ahead and change this guy so we know what it is. Se quantity. Okay, now we can click on this, and right now it says command 21. That's just the name of the button. We don't actually want this to say. Command 21 so we can change it by double clicking into it just like we do with labels. Or we can come over here and change the caption in the property window. For now, we're just going to double click in this. We're gonna call this add. So now we have the start of our product form. I'm gonna insert a label at the top of this. And I'm going to just call this product form shift enter to go down a line. Now, I'm going to say this is the form we use to order a new product. Click out of it to go ahead and set that up. And this looks good. So let's go ahead and right click on our form and go form view just to see what we're working with. And so far, so good. We can see this is shifted a little bit over to the side. You could line these up, however you want. For now, I'm gonna align mine on this fourth line like so now At least they're all aligned. Usually I would control the vertical spacing a little bit too. You could play with that if you want. Okay. So that gets our product form started in the next lecture. We're going to dive into even more good stuff 34. Setting Combo Box Options: OK, in this video, we're gonna look at some options that we can change for our combo boxes. And the first thing we're gonna want to do is fill out what options are available to be picked from these boxes. So we're gonna click on category first. Doesn't matter which one you start with. I'm just gonna arbitrarily choose category. I'm going to go ahead and change the name of all my combo boxes. So I'm gonna call this my category box and I'm gonna grab product box really quick. And let's change this to product box. This guy will be quantity box and again trying not to use spaces and go ahead and grab your button and we'll go ahead and change this to the ad product button. Okay, so now everything is named. We know we can get to it pretty easily from wherever we are in the database. Let's start with category, click on category and go over to the data tab, and you can see control source bound column, Rose source, Rose source type. So this is a table or query value list is something that you would use if you just wanted to type directly into it. So if you had, like, red, orange, yellow, blue something that doesn't need to be driven from a table. But in our case, we're gonna drive from a table. So go ahead and select table query if it isn't already going to row source and click thes three dots right here. Now, when you click this drop down, it'll show you any tables that you currently have in your database so we can use products if we want to use products. But we're actually going to do something a little different and click on the three dots, and this will let you drive a query. So we're gonna build a query. It's not query that will be used anywhere else in the database. It's only gonna be used for this combo box drop down, which is pretty cool. So double click on products to add the products table here, and you could see here's your products table. Here's all the categories and all the columns that are in your products table. Go ahead and click close to close out of the show table case. You ever lose the show table and you need to bring it back. It's right here so you can add us many tables as you want. This is a great way to relate items. So if you have a certain item number that might tie in somewhere else in your database, you can go ahead and put that item number from this table and then tie it to another table over here, whatever. And you can link up all your tables, but we won't get into that in this video. All we need is a products table to be here so quick, Close. Then go ahead and expand this guy so you can see everything that's in it. And all we want in this combo box is the product category. So we're just gonna double click on product category and we could see it shows up right here. Nothing special. This is exactly what we want. So right, click on this guy and click, save and then close out of it. And then in your product for him, you could see it's written on this little line right here. We can expand this if you want to see the whole thing. Select products product, Cara, Gory products. So this is something that you can use in V B A if you want to. But nine times out of 10 you're just gonna let access to this part for you. So you don't have to pay too much attention to that line. You can shrink this back over. And now if I go right click on form and get a form, view and click category, you can see all my categories have shown up. Now we can see this one's gonna cut off a little bit. You have two options for how to fix that. The first is to just highlight this box and expanded a little bit. Now, if I go back to my form view, I can see medium coffee shows up just fine. Now, the other way to fix this if we want to leave it at that size, is to go ahead and adjust the width of the drop down and let it exceed the width of this box, which is gonna happen over here and your property step. So if we go over to the format tab and properties, make sure you have your category drop down selected. Go over to the format tab. You could see a couple of things in here that are gonna be helpful. The first is list with and we're also going to look at with right here. So with just playing with plain height plane top plane left, all of these are just for the box itself. So right now this is telling us this box is one inch, which makes sense because we can see the box runs from 3 to 4. So that makes sense. It's Wantage the list with controls the width of the drop down list. So when I click on this drop down the width of the list, that shows up. So we know that right now our list is a little bit too narrow. We could see the width of our boxes. One. Let's just double it and make our lists with two inches instead. So now when I right click on the form Goto form view and click this drop down, we could see our list has expanded its twice the width of the box, which is nice. We can see everything and nothing gets cut off. That's awesome. That's what we want. Go back to design, view the other option. You might want to pay attention to its list Rose. That sets how many rows actually show up in your list. So if we always want to see three items at a time, go ahead and change that to three in list Rose, Go back to form. View up here, Click on this. You can see now I only see three items in here. You could scroll using those little scrolls over here to get through your list for our purposes. Nine is not a whole lot of items. So we can go ahead and leave this the access default, which is 16 and again. When I go back to form view here, we can see all nine options and we don't need to see any of the other options in here. So we're good. Go back to design view. 01 other thing. When we look at this, we see we have two options that are both strong coffee. Two options that are medium to light. One option for decaf and two t. So we really don't need these repeats in here. We really only need to see strong, medium light, decaf and t so we can go ahead and take those out. So go back to design view. We're gonna go click on this box and we're gonna go back into our query that started to build this list. So just click on these three dots right here and go back into your query and we can see we're back in the query. Now appear you'll see totals. Go ahead and click on totals. You'll see this new line just showed up in your query. This totals line. There's a couple different options you can do in here. You can sum up average, take the minimum, take the maximum count. All of these options are available. What we're interested in right now is group by. What that means is it's going to take all of these options that we saw in our list those repeats and just group them up into one line instead of having multiple lines. So if we right click, save this and then close out of it, then go back to our form view. Click this drop down. We can see. Okay, Now there's only one of each option, and it has alphabetized this list, which is really helpful. So I strongly encourage you to use the group by feature when you're building your queries to allow you to have a combo box list. Let's go ahead and go back into our design view. Let's talk about a couple of these other properties over here. Down column. We're actually going to talk about when we do our product, so we'll jump over this one right now. Limit to means your user can't select an item that isn't in your list or enter a new item that isn't in your list. So usually we're going to say yes. Limit this to my available list. Allow value list. Edits this would be Can I change things in my list? We don't want anybody changing anything, so we're going to say no. And these are the two main things that you're gonna want to do on most of the list that you work with. Okay, so that gets our combo boxes set up pretty much the way we want in the next lecture. Lose further refinements toe how they work 35. Further Refinements to Our Combo Boxes: okay. And this video, we're just going to do some further refinements to our combo boxes. So now we can go ahead and apply our product query. So click on your product box. We're gonna do the same thing. We want to create a query. So this is good going to row source. Click the three dots. We're interested in our products table, so double click on that. Expand this so that you can see everything. We want to know the product name. We're gonna double click on this and we can see product name shows up down here when we're picking our product would probably want to see the description that we're picking as well. So I'm gonna go ahead and I'm gonna click on description. Now, I've got two columns in here, and we know that on Lee, one of these columns is actually gonna be selected from the list. We're not selecting the name, and the description were just selecting the name, so we'll talk a little bit about that in a second. The third thing I want you to add is go ahead and add the product category, and this will become clear why we're doing this in just a moment. But go ahead and add it. So it's in there, right? Click this, save this guy and close up. So now we're gonna go ahead and we're gonna go to the format area of this product box, and we can see right here this line column count. So column count right now is set to one. That means there's only one column that showing up from our query when we look at the drop down May. So if I go to foreign view, open this up. We could see only one column has shown up. We know that in our query we actually have the description, and we have the category over here, but right now it's only showing us one list. Go back to design view Change Column count, too, right click Go to form view. Drop it down Now I can see it's trying to show us two columns. It's trying to show us our product name, and it's trying to show us our description. So now we want to expand these lists so that you can actually see the first column and the entirety of the second column you're going to do that in the same way we did with the category video. So go back to design. Make sure you have this selected. Now this width is set to auto. We're gonna go ahead and expand this a lot. Let's expand it to five inches and then we're going to focus on this right here. The column widths. So now you can separate all your wits by commas. So let's say we want our first column to be one inch in our second column to be four inches . We're just gonna type one comma space for when I click out of this, you could see access will automatically format it the way that it wants it, which is one inch little quotation marks. Semi colon, four quotation marks. But we can go ahead and right click and go back to the form view, and it could take a look at this We could see were almost right, probably need to expand both of these categories a little bit more, so we go back into design view. Let's go ahead and expand the list with, ah, lot again. Let's just go up to eight and then appear Let's James, this 22 and six, and that will probably get us close. Yeah, that looks like it captured everything. So we know we're good. We can see everything that we want to see now, No notice. When you click on one of these, let's click on regular roast. The description doesn't show up. All that showing up is regular roast. There's no description over to the side. So the next thing we want to do is take advantage of this category drop down. And like we said before, we want to use this as kind of a filter for what shows up in our product category. So if you choose strong coffee, we want to drop down toe only shows the strong coffees that we loaded into the database. So go ahead and go back to design view. And in this guy, we're gonna go ahead and open up its square again. So now we can see we have a product category down here. We just need to figure out how to link this up to what we have in our form. We're going to use that in the criteria section. So in this criteria, we're gonna type a little formula that will link this category. This column what is currently on our form. So we're gonna take, like, Kama forms. So we're gonna take this and you can see the option pops up here, which means we've spelled everything correctly. We know that this is an option that we can use taken exclamation point to step into the next category. So now we've told it, Hey, I want you to look at forms. It's now going to display all the forums that it could find in the data base. So we want to use our product form. So we're gonna type product form and we could see its popped up. We know he spelled everything correctly. We could move on, put a little exclamation point, and now it will get even more specific. And it'll tell you, here are the things that I found on the product form. So we're interested in our category box. We want to know what's in this category. What category do they have selected that can then update our category here and only show us what we want? So we're gonna type category box now when I click out of this, you can see again access will auto form at this, the way it wants with little brackets around each category. This, like, is telling access. I want you to look at what's in this box that I've told you to look at the category box that's on our product form, and I want you to filter this list in our query to show me on Lee the things that seem like what I selected in this list. This will make sense when we look at the form. So we're gonna close. Got a formed view we could see It's only gonna pick things that seem like what's in this list. If I have medium coffee selected, it's only gonna pick things that its season, this drop down that are similar to this category that we've selected. Let's go back into design view because we're gonna change one other thing in this formula that we've written. Go ahead and expand this so you can see everything we're gonna put a little and sign and then quotes Astra's quotes. Like I mentioned in the V B a video. The and sign just tells it, we're going to string together a bunch of stuff. This is all text, so Now we're saying I want you to look for anything that's in this category box and this asterisk now, the asterisk gave access acts as a wild card, which means this could be anything else. So it's gonna look for whatever selected in this category box and anything else following it. So this caveat will let you include blanks. So if I open the form and I have nothing selected, it's gonna show me everything. All the product names, it's not gonna fill to them all based on category. Whereas if I took this out and I clicked on my product category and left it blank and I want to put my product name drop down, you wouldn't actually see anything. So we want to put this asterisk at the end to make sure there's always something in the box . It could be selected, so go ahead and save this and close out and go back to your form. When you cook this drop down, we could see right now it's automatically filter to my breakfast roast and my regular rest which of the two that are medium coffees in my table here. So if I go back to my table and we look at Onley, the medium coffees We conceive breakfast roast in a regular roast, which means we know that this is filtering correctly filtered our list here, which is awesome. But if I change this to something else, let's say change it to tease. And then I go back into this drop down You can see the drop down didn't update its because this dropped out. Is Onley gonna update whenever our query or ends? And right now our query is only running right When we opened the form initially So right now if I unsolicited everything that is in this box, leave the box empty and we leave categories selected his tea If I go back to design view and I tricked the form into reloading itself by going back in the form of you Now you can see only my T options are showing up. So we need a way to make sure that this product dropped out is gonna re query itself every time that it changed the category. And here's where those event properties start to come into play. And that's what we'll look at in the next video 36. Run an Event on the Category: OK in this video, we're gonna run at event on the category, dropped down whatever it updates to make sure that our product up here is requiring. So I have my category box selected. I've gone over to the event tab and I'm interested in this line on change. So this means every time that this box changes, we're going to do something to the rest of the form. So click on the three dots right here, and it will pull up some options. Macro builder, expression builder and code builder. We're gonna be using code builder throughout all these videos. So select code builder and click OK, And your VB a window should pop up, which looks something like this. Now, this is the code we're gonna be writing. I've gone ahead and typed it just to make life easy. The private sub line is gonna automatically enter for you in the end sub line that's gonna automatically enter for you. It's all you need to type his product box dot re query. So category box is the name of the box that we've told it to run code on. So, whatever you name your box and this is Why again? Avoiding spaces when you name things is important. Naming things while is important. We know that this sub is gonna run on category box. Now this underscore means there's an action that's gonna happen on this category box and it is change. So now we've said, OK, every time I change this item, every time I action this item change category box, this code is going to run. So now what we've typed down here is product box dot re query and this happens in the same way. So we've said, Hey, this is what I'm interested in. I'm interested in the product box, which is what I've named my actual product box. So if you go back and we double check, we click on this. You see, it's called category box. If I click on my product box, you can see it's called Product Box. We've put a little dot, which means I'm about to do something to my product box. And we told it re query. Now, as you type this, you can see a list pops up right here. We talked a little bit about this in the V B A video. These are all the things that you can access and things that you can do to the product books. So we've selected re query. There are other things that you can dio. You can change the font, for example, to be bold or not bold or whatever you want, but all we need to do is require the product box. So now we've told it, Hey, every time I change my category box, I want you to requiring my product box. Let's go ahead and save this. Go back in the form of you right click form view we could see Right now it's tea and when I opened this I c T. So we know it's work. Let's click our medium coffee. Can I go back in here? And now we see our medium coffee so we know that it's properly requiring the system for all of these. So the other thing is, if I have something selected in here, say I select French roast strong coffee and then I changed my mind, actually decided that I wanted decaf coffee. You can see it automatically clears this box for you because it's required this box, and it knows that if I had to require this. Probably whatever was in here is not right. So that's a good thing. And we want to leave that alone. Okay, so that takes care of the category box. In the next video, we'll modify the quantity box. I'll see you in the next video. 37. Modify the Quantity Box: Okay, So in this video, we're gonna modify some details for our quantity box. So if we go back to design view and click on the quantity box, we're gonna go over to format and under the actual format line right here, we're gonna make sure that we are on Lee picking numbers. So click general number. And now we know Onley numbers will get entered into this quantity. If it doesn't, the system's going to realize that we've done something wrong. It's going to alert the user that they've done something wrong. So we only want numbers to be entered into this quantity box, and we're going to use the quality box to ultimately tell the user about how much money they're gonna end up spending on whatever it is that they've selected here. And to do that, we're going to use this ad button. So in ad, go over to event and go to on click. Now, this means every time we click this button, we're gonna have something that's gonna happen. So click these three dots and go to Code Builder. Now we can see again. It's automatically generating our private sub line and our end sub line. And we know that this is gonna happen whenever we click on the add product item, an honor form. Obviously, add product is the button. Now there are a few things we can do right off the bat, and that is to go ahead and set up