 Hi, welcome to Microsoft Access 2013, part one. My name is Sandra Batekis and I'm here to take you through the basics of how to create a database in Microsoft Access. Now, I've been with Access quite a few years now, enough to date myself back to version one, very quickly followed up by version two. And me being almost overwhelmed at the power of this new database system that even ran on Windows. Yes, I even worked on the DOS based version of some of its predecessors. So having a chance to work with Access both in the classroom and in working and writing databases. I'm here to tell you that this is perhaps one of their best products yet. And I really look forward to sharing some of this knowledge with you. In this lesson, we're just going to get started with Access. Nothing too deep here. We're going to take a look at the Access environment, learn how to create a simple database, and also learn how to find the help. So with these steps, our entire intention is to get you comfortable with what a database is all about and how to move around the screen. Topic A, we're going to get a chance to get oriented with Microsoft Access. Which means we'll define what a database application is all about. We'll learn about transferring data into knowledge. Meaning how do you take this list and come up with some good information? We'll also have a chance to compare Access to excel in some of the other database programs so that we know when Access is appropriate and when it may not be. We'll learn about the Windows components, which allows you to navigate Access as a program. The forms, the views, how to navigate records, how to create or work with a very capable query and what a query join is all about. So the first question is, why do we use a database application? Well, a database just contains data. And I always tell people, everyone uses databases on a regular basis. They just don't recognize them as a database. Let's think of the old phone books. I know that no one uses them anymore. So we'll still try to talk about that good old paper phone book or better yet, how about that address list on your phone? Well, what is a list? Or what is a database? A database is simply a list that is organized. That's it. So if I was to look at the phone book, you get a phone book by Regents. So let's say the North Shore of Boston. And then once you have your regional phone book, you have addresses in alphabetical order. Now in some cases, you can also have them in an area where maybe it's by town or businesses by town. Or let's go to the yellow pages, we have by types of business. So why are you going to use a database application is instead of just having all of this data, we're going to keep it organized in a manner where we can find an answer. Now of course, if you take that phone book and you make it electronic, then we get a chance to not flip through pages. But to say, show me all the heresies in this phone book that happen to live in Peabody. And based on that, you can start narrowing your results until you find the type of statistics you're looking for. So it is all about looking for findings in data. So in this particular case, you're going to see that department 1, 7.5, department 2, 3.2, department 3, 4.5. Here are some numbers for some sites. Here are some revenue numbers. So we have all this data, what do we do with it? Well, we might want to see who produces the most amount of defects, or where's the largest amount of accidents? Or what product lines bring in the most amount of revenue? So here's your data and the information that you could ask. And of course, that's how we obtain knowledge about the data in the first place. So again, we just take our data. It has to be organized to find any amount of meaning. If we just had every single phone number in the United States put into a book in no logical order, yes, it would be a whole lot of phone numbers. But it really wouldn't have any meaning at all. If it was not sorted by state, and then by region, and then by last name, you'd never be able to find anything in that book. So the data itself is meaningless. So when we transform data into knowledge, we have to organize it so that we can start finding the meaning. Once it's organized, we can analyze it so that maybe I'm looking for statistical data. Again, how many accidents happened per site? So we're going to start analyzing it. That's going to give us our knowledge. So if we know that one site in particular has 10 times the accidents of any other site, then now we have that knowledge so that we can work towards some sort of agenda that helps fix the problem. So the big question is, why do we pick access over Excel? Excel, just to talk a little bit about that, is a spreadsheet application, but it does have database ability. When I say database ability, I could have a database with 60,000 records in it without batting an eyelash, and I could sort them all by last name or however it is I want to sort them. So why do we need a database? Well, even though Excel does have the power to control very large lists, it's lists that are what I will call very flat. It's not a relational database. So based on that, we can sort it, we can even filter it, and there are even a couple formulas that allow us to total based on some criteria. But it isn't optimized for this. It is a list management tool, but when the list gets long or complicated, access is really a better place to put it. So access is really optimized more for the database management and allows us to really manage some complex relationships between different tables. Now I do realize this is pretty early in the course, so you may not even really know what a table looks like, but you'll see the meaning of that the further we get into class. We also have support for data input validation. So in Excel, I can put anything I want in the cell. So even though it's looking for a numerical value, if I decide instead of putting in $10, I put in the word Wednesday, then it really doesn't hold any weight, it just allows the input. So we get a chance to control what type of data is in there. We can look at different tables and the relationships between them, and we have a lot, a lot of utilities for the report generation. So if it's a simple list that needs to be sorted, go ahead and keep it in Excel. Once it gets a little more elaborate, you'll find very quickly, access is a better place to put it. So what about access over other databases? Well, access is very widely used. It is relatively inexpensive, meaning if you own Microsoft Office, you already own it as long as you have the right version. So most people use it because it's something that they already have. Now, it is a lower startup investment than a lot of the enterprise systems. So you could go to a database application like Oracle, if you have tens or hundreds of thousands of dollars, and you'd like to go ahead and pay a developer more per hour than most people make per week. So again, for a simple database, it's already something you have, it's very accessible, and it is written to be used by most end users. So there is no need to go to that enterprise system unless it really is an enterprise-based need. And even at that, access can be a one-up because we can go to SQL and really take this access database and continue to let it grow and even migrate to enterprise-wide applications if that's what we need to do. So let's take a look at the window components for access. We always have the title bar. The title bar is going to contain the name of the database. It'll also give you the full path so that you know where it is, and it'll tell you the format of the database, meaning what version of access it's in. We have our quick access toolbar to put your favorite shortcuts. We have our ribbon that we've all gotten used to with Microsoft Office, and we're about to take a quick tour of what that's all about and where different things are. And then we also have this navigation pane. So in the navigation pane, we get a chance to look at the different types of objects that make up a database. Now throughout this course, we'll get a chance to learn what each of these object types are, but you will see that it's organized by object type. But we do have some menus here, and our menus allow us to decide what types of things we want to see. We also have, let me clear the screen so we can show it, this little symbol. So if you really need more real estate on your screen for the data that you're working with, if you click on that, it'll actually condense this entire navigation bar to just take up a small footprint. And always keep an eye on the status bar. A lot of times it will tell you exactly what you need to know, but no one thinks of looking there like right now. It is ready for you to do something. It might tell you that you're editing a record. It might give you a navigation tools to edit records. So keep an eye on the status bar as well. So let's go ahead and take a look. One thing I like about Access is there's a lot of forms and a lot of views that you can create. So a form isn't anything that contains data, but it's kind of a view into data. So let's say I've got a data sheet that's 15 columns wide. Well, for me to try to type in and tab over and tab over and tab over to try to put in all the data, maybe I'm further down in the records and I can't see the titles, it really doesn't become a nice way to put some data entry in. And by the time you're all the way over to the right, you can't see the first few columns. So it's very easy for you to lose a spot, maybe be in the wrong row. So you can create a form which is really just a graphical user interface that allows you to arrange it into an area that it almost looks like a dialog box or it is a dialog box so that you're clearly looking at one record at a time. Navigation is easy and data entry is easy. You can also create different views so that it really displays different types of data meaning this view may not be all the fields because you don't necessarily need to see them for this particular purpose. When you're looking at records, you will see that you have a navigation bar down at the bottom of your screen. So if you look for the navigation bar, you're going to be able to see a few things like the current record that you're on. Right now we're on record number 1 of 49. If I wanted to go to record 10, I could click right in here, type in 10 and hit Enter and it's going to bring me to that record. You can go to the previous record or the next record or the first record or the last record. This one is a popular one because this is how you create a new record and it's not always clear with different screen colors but it does have an asterisk on it so that asterisk that you see just means create a new record. So if right now you are on record 49 and then you click here to create a new record, it becomes record 50. So it basically goes to the end and then creates one at the end. If you have any filters applied, it will tell you and if you're searching for something, all you have to do is click right here in the search box and type what you're looking for and it will navigate to the first record that it finds it in. So let's introduce the subject of queries. A query is simply a question. So if you have a list of data, we usually don't just list data for no particular reason, we list it because we want to be able to ask it questions. Now it could be you're looking for someone's phone number, it could be that you want statistics on how many accidents on a particular site. It could be something as simple as a mail merge, send this letter to all of our Massachusetts customers. Whatever it is, we can accomplish it through a query. Now when you create a query, sometimes you join data from more than one table. So an example, I might have a customers table where I have the customer number and the name and address of all the customers and then somewhere else I might have an orders table. Well if it's a table on orders, we probably have who ordered something and then what they ordered. Well the who is a customer and it's probably a customer number so we can look at the customer number and the product that was ordered and the date that it was ordered on but in order to query a customer and everything they've ordered and also to have information I might have the two tables joined, they're joined by their customer number. So I'm going to pull in a query, the customer's name and address out of one table and all of the products that they ordered out of another. So if you're creating a join for the purpose of a query, we have let's say in this example two tables. Now in the tables here we have an inventory table. So we have the product code, we have how many units are in stock, we have a reorder level. So eventually I'm going to say if I have less than the reorder level it is time to reorder and bring the inventory back up and we have the supplier ID that we would order that part from. So over here we have a supplier ID and we have the first name, last name and contact phone number of who to call over at Arbor Harvest in this example if we need to reorder something and by looking at the numbers we probably do. And you'll see here we have another supplier and you'll see its contact information and we have another supplier and of course their contact information. So again we have all sorts of information that can be pulled but I might want to actually ask a question of which products do I need to order and for the products I need to order who do I call and you're going to see right here we brought it all together and I didn't do any math yet so you'll see units in stock to reorder level are still exactly as they were but I now have two tables of information, our product code, our units and reorder levels, the supplier ID and the contact information all in the same spot and you'll see we even have contact email which is technically part of this table we just didn't have room to display it.