Access Beginner Tutorial

good day one and all and welcome to Microsoft Access 2016 module one as you can see my name is Dan McAllister and I will be your instructor today in module one we're going to talk about the parts of a database the objects that create it how they relate to each other but I think the first thing we should do is go grab some practice files so I would recommend put your video on pause go get the practice files that we'll need for today's class they're going to arrive in a zipped version and I'm going to drop mine out on the desktop and then we can talk about how to extract our files from the zip version never try to work on files inside a zipped folder always extract them first so put your video on pause go get our practice files download the zip folder and when we come back we'll talk about how to extract them from the zip folder so put our video on pause go get your practice files all right welcome back I'm gonna now step out to my desktop where I have downloaded my practice files and here they are in the zipped version so I'm going to double click on them to open up the zipped version of this folder you can see the filename extension here dot zip and again I was mentioning a moment ago never try to work on files while they are zipped so the next thing we need to do is extract all those files so I'm going to click on this button right here to extract my files and when I do that you can see that because I place mine on the desktop the extracted version is going to be on my desktop as well so that's what I'm going to recommend that you do with yours now depending on what browser you're using when you downloaded yours they may have gone into the Downloads folder I'm going to recommend that you maybe copy them out to the desktop and then you can do what I'm about to do which is extract these files so again it's about to extract a folder out onto my desktop and I'm going to click on this button down here that says extract you see a little progress bar go across there and now out on my desktop I'm actually going to have two copies of these files one of them zipped and one of them extracted let me show you what that looks like here so I'm gonna close this window again I'm gonna head to my desktop maybe I can use my alt + tab to get out to my desktop and now out on my desktop here is the zipped version and here is the extracted version I'm going to actually take the zip version and chuck it in my recycle bin if something becomes corrupted in the extracted version then I'll still have the zip version but I won't get myself mixed up by opening them from the wrong place so put your video on pause do what you just saw me do extract those so that you can now see this extracted folder and then I'm going to recommend that you ditch the zipped folder drop it in your recycle bin all right everybody welcome back so when you double-click on your new folder this is what I'm hoping you will see access modules 1 & 2 3 & 4 5 & 6 7 & 8 we're gonna of course go to access modules 1 & 2 these are the folders that you should see so the first one that I'm interested in is named Northwind now when you extract yours you may or may not see the file name extensions out here don't worry about that turns out that's a Windows thing not necessarily an access thing and the file that I'd like to start with is this one named Northwind dot accdb so again take take a moment put our video on pause extract those files and then the last thing you want to do right here is open up Northwind a ccdb I'm about to double click on that and open it up actually I'm going to go a slightly different way I'm going to start Microsoft Access first and then I'm going to tell access to open that file so here's what access looks like when I first started up got a whole bunch of templates available out here so these templates the first place I could remember seeing templates pop up in a Start screen and a Microsoft Project was in Microsoft Publisher several years ago they would have all these cool templates with things like greeting cards and calendars and cool stuff like that and then the next place that I saw templates on startup was on the Macintosh side of the office suite now every time you open up one of the Microsoft Access now anytime you open up one of the Microsoft Office programs this is the first thing you'll see whether you're in Word or Access or PowerPoint or any of your favorites I guess except outlook outlook doesn't really have templates so as I scroll up and down here I can see templates for things that would go on the web by the way to create a web database of some sort you have to have a sharepoint server to upload it to and i don't have that available today so we will not be discussing the web apps but the advantage of creating a web app is that your users could use it through their internet explorer they wouldn't actually have to have a copy of Microsoft Access so most of the choices in here are pre created databases they don't have any data in them but they would have a couple of tables and a query or two and right now any of you who are beginners that won't really mean much to you so I'm not going to start necessarily with one of those what I would like to start with is one of those practice files that we just downloaded and extracted so specifically I'm going to go over here to my open button and then I'm going to navigate out to my desktop because that's where I stored my files I can do that by browsing down here and when I click on browse first place it looks like all good Microsoft and Adobe programs is in the Documents folder the documents library in this case specifically the My Documents folder except that's not where I extracted my files too I put mine out on the desktop so that's where I'm gonna head I'm gonna go over here and click on desktop and now I can see my access 2016 samples now I don't know how many of you are familiar with this favorites area over the left side but it can be very handy for example I'm sure that most all of us who work in some kind of corporation have times when we have to navigate to a shared Drive maybe that s Drive for the shared stuff or that n Drive for Network a shared network folder so sometimes you find yourself having to go to that end Drive and then into a folder inside the N Drive and then into a sub folder inside the folder on the N drive that lives in the hustler Jack belt what I would like to do is make a little shortcut to my access 2016 samples folder here now in this case it's not gonna save me a lot it's only gonna save me one click next time I want to find it I don't want to have to go to the desktop and then go find the folder over here so what I'm about to do is grab this access 2016 samples folder and drag it to the left and I'm going to put it right here in my favorites area I have to be careful not to drop it inside another folder that would just be recreating the problem of having to go to a folder in a subfolder so I'm gonna look for this little horizontal line between the folders as I drop it over here in my favorites area and the advantage to that is the next time I have to go to XS 2016 samples I won't have to go to the desktop first it'll be sitting right here and I can get to it with a single click so I'm gonna recommend that you take a moment and do that you're gonna go to file in access and open and then go to your X s 2016 samples which was out on in my case out on the desktop and then you saw what I did I grabbed the folder and dragged it over here into the favorites area so why don't you put the video on pause take just a moment and do that and then we'll come back and we'll open a file named Northwind all right everybody welcome back I'm headed to my access 2016 samples folder right now it's in the mods 1 & 2 folder with a double click and then let's open up this one right here Northwind you may or may not see the file name extension dot accdb so just double click on Northwind when it pops open it should come up looking like this and because it originally came from a website it's got this security warning just making sure that you trust where this came from and if we do trust where it came from hopefully we do then we can click here on an able content and then what you're going to see right now is the opening screen called the splash screen giving you a chance to sign in as someone named Andrew Chand Cheney so why don't you put our video on pause go open up that Northwind database and when it opens up you'll probably see the warning about certain content being disabled you'll click the enable editing button and your screen should look like mine so put the video on pause and then come on back and we will do some stuff together welcome back one and all congratulations on getting our first database opened and on screen so when we left off we were looking at this log in dialog window it's called offering us a chance to sign in as Andrew Ching Cheney I would like to turn down that offer to sign in as Andrew Chan genie so that we can see the basics of the database before we try to start using it as any one particular user so here's what I'm going to recommend right here in this dialog window it's got its own close button I'm going to recommend that we do that please click on that close button and so now you can see that we have this database open and we can see the ribbon up here and we'll talk about the parts of the ribbon for just a moment and over here at the left side we have something called as you can see the navigation pane so at the top of the navigation pane I see these little double Chevron's and when I hover over them it says shudder bar open and close button so I'm gonna click on that the little double Chevron's here and when I do that I can see a little bit more about my database named Northwind now I know that I'll have a combination of veterans and beginners here looking at our first module let me talk to my veterans for just a moment you veterans you're used to seeing the tables in one list and the queries in another list and the reports in another list and you're not seeing that right now so for my veterans in fact for everybody let's go up here next to the word Northwind and we'll click this little pull down arrow it so called a list arrow and when I click on it I see several different ways that I can navigate through the objects of my database first thing I'd like to do is change it from the name of the database Northwind traders to group things by object type by the way I can't click on either these gray choices here they're not clickable choices their names their titles so right here is the group called navigate to category and I would like to navigate by the object type so when I click on object type now I can see the tables versus the queries versus the forms by the way the reason I'm seeing all of them is also here under the little list arrow the top says navigate by object type the bottom part here says I could show myself just that or just the queries are just the forms the other choice down here is I have selected all database objects all access objects so I'd like you to put our video on pause and do that for just a moment we had the splash screen that we closed so that we weren't signing in as andrew chan cheney we had the expander double Chevron's over here and then we went to the pulldown list and selected navigate by object type so take just a moment and do that to begin navigating by the object type separating the tables from the queries from the forms put our video on pause do that much and then come on back and again we will do some stuff together so now over at the left hand side I can see tables and queries and forms and reports well that's great for my veterans to see things that way but for my beginners maybe you're wondering to yourself right now okay so what the heck is a table what's a query what's a form it just so happens I have a little diagram that I have created and this is not the last time we're going to see this diagram I'll be using it a lot through our lessons so the most important part of your database these guys are right here these tables the tables are where all of your data is stored so you really can't have a database without some tables next objects that we'll be talking about after the tables are these guys over here the queries queries let you ask questions from your tables by the way I've got some arrows here talking about how data can be transferred between these different objects so the data will be stored in the tables when you run a query it will pull certain records from the table and then display them on screen but then if you begin typing information into the query it will actually feed back into the table that's what these two arrows are about so when you run a query it pulls information from the tables and displays it and then while the query is open if you begin doing data entry you will be feeding the data back into the tables where they are actually contained a little bit later not necessarily right now but in another module we'll be talking about forms forms are a little bit more graphic way to pull information from tables and to actually do data entry into tables so again this is a two-headed arrow here data flows both ways but it's actually stored in the tables in the end our fourth object reports reports are much like forms in fact when we do the two modules about forms and reports you'll see that they're very similar in their design layouts but reports are all about putting things on paper and you also might notice that this is a single headed arrow here there's nothing you can put in a report that will feed back into a table so reports pull information from tables and then do some special formatting to make your reports do things like add things up running totals grouping by customer grouping by product that you sell and so forth and you might also notice an arrow coming from a query so if I wanted to remake a report about information from more than one table Microsoft suggests first make a query that gathers the comp columns the fields that you want in your report and then use the query as the record source for the report with the special formatting going on paper so man we got a lot of arrows here but just in basic things that I want you to notice here queries can pull and pull it the push information to and from tables and the same is true for forms pull and push information to and from the tables we will return to this this diagram more than once this is not the last time you'll see this today I'd like to introduce you to the names of the objects in the interface here I'm going to start at the top of the screen so it's a little bit muted up here the colors are a little bit muted but what I'm looking at at the very top here is called the quick access toolbar now the fact that it has the word access in it does not actually refer to this database program name to access there's a quick access toolbar in Word and Excel and PowerPoint it's just the name that's set up for these buttons up here at the top and again they're a bit faded out in this particular session but here is my Save button here is an undo button as I hover over them they'll tell me their names here's an I undid too many called the redo button and then I have a little pull down list over here that I can add things to my quick access toolbar and we'll be doctoring up the quick access toolbar a little bit later in our lesson but notice checkboxes next to the things that are in there and then another list of about 10 more things that Microsoft knows we might want to put in there and new to 2013 carried over into 2016 the ability to add a button to go into touch mode I'm gonna go click on that for just a moment I don't necessarily need you to do it but I'd like you to see it when I click on the touch Mouse mode it adds another button up here and then if I click on it I get a couple of choices do I want to turn it on for the mouse or do I want to turn it on for a touchscreen when I click on touch I'd like you to watch what happens to all these buttons back here touch see how all the buttons got a bit bigger um this is largely for people who work on tablet computers handheld computers with much smaller screens than I'm used to seeing on my desktop so the ability to have the buttons a little bit bigger is gonna make it easier for me to finger touch those things on a relatively small screen in fact I've got some friends who work in touch mode all the time even though they don't even have a touch screen because they like having the bigger buttons down here I'm gonna go turn that back off I'm gonna go click on that button I'm gonna switch back to Mouse mode which gives me a little bit more working area down here makes all my buttons a little bit smaller so that was called the quick access toolbar and again the way I added that touch mode was clicked on the little pull down arrow over here notice as I hover over it it says customize my quick access toolbar and I slid down and I turned on the one for the touch mode all that did was give me a new button and then to actually turn it to touch mode I had to click on the button and switch it from Mouse to touch so if you'd like to try that feel free put our video on pause go add yourself a touch slash mouse mode button to your quick access toolbar and then come on back now to continue with the names of things right below my quick access toolbar I see the words file home create those are called command tabs and every time I click a command tab I get a new set of buttons down in this area just below called the ribbon so if I click on the Home tab I get the home ribbon if I click on the external data tab I get the external data ribbon and one of these tabs is not like the others one of these tabs is not the same come on you Sesame Street fans one of these tabs is not okay maybe you're not Sesame Street fans the one I'm talking about right here is the file tab when I go over and click on the file tab I got this whole new screen comes up this is called the backstage view and microsoft says the backstage view is where you do things to your files rather than in your files for example to open a file or to save a file to close a file to compact it to encrypt it with a password so this is not ways to add new records to my database but it is ways to do things with my database like making copies of it and so forth and anytime I want to step out of this backstage view I just click the back button right here and I'm back to my normal view so again if you'd like to try that for yourself put our video on pause click that file tab take a peek at the backstage view and then when you're ready I click that back button and you'll be back in your normal view with me take a moment undo that so I've the command tabs that produce the different ribbons the ribbons are broken down into groups of buttons for example under my home tab I have a group of buttons called the views group and then the clipboard group and then the sort and filter group you could see the group names right down here towards the bottom as I go to the database tools ribbon that is when I click the database tools command tab to produce the database tools ribbon then I get the tools group the macro group the relationships group so just the names of things up towards the top of the screen so we've got the title bar at the top right now it's slightly off screen you can't see it right below that I have the command tabs which for each produce ribbons with groups of buttons and then down towards the bottom of the screen I have this area right now it just says ready that's called the status bar and things will appear down there as we begin doing things so that's kind of the names of all of the pieces let's go look at one of our first objects here we'll tackle so that's kind of the names of all of the pieces of the interface in a moment we're going to open the first of the objects that is we'll open a table it's time to explore the in one way the most important part of our database and that is the tables they are the most important because that's where all of our data is stored so when I click on tables it expands in this case to show me man there's a bunch of tables in this database here's the beginning of my queries list down here so in this particular database we're keeping track of our sales we've got customers we've got employees we've got inventory we've got invoices so let's take just a look at our customers here for a moment I'm going to point at the table name customers and give it a quick double click and when I do that it opens up the customers table over here at the right hand side now in a lot of ways this looks like Microsoft Excel I have rows and columns of information I can scroll left and right to see the the various columns by the way another name for columns to a database geek it's called a field so you'll notice I don't have a column a a column be a column see I've got an ID column I've got a company column and to a database geek that would be called the ID field and any field and what I would call a row in Microsoft Excel we call a record in database geek terms so I'm looking here at the customers table notice it has a tab up here at the top instead of the sheet tabs being down here at the bottom which is where they would be in Excel so I can scroll left and right by using just arrow keys I can also use my mouse to go down here to my scroll box in the scroll bar drag it left and right so in a lot of ways this looks like Excel but there are certain things that are different about this compared to Excel for example if I was in Excel and I hit the enter key it would jump down to the next row Here I am an Access watch what happens when I hit the enter key here see how it jumps sideways instead of down that's just our first little example of ways that access is different than Excel and again looking at the various parts of the interface that we haven't had a chance to talk about yet because we didn't have a table open till just now I can see some things happening down here just above the status bar the status bar says that this is the datasheet view and then right above the status bar I can see that I'm looking at records and specifically I'm looking at record number one of 29 total records now in Excel you've probably seen arrows like this to go to the next sheet or the previous sheet in this case it's not the next sheet it's the next record so right now I have highlighted record number one of 29 records when I click on the right arrow you'll see now it's highlighting record number two and record number three I have to say it's pretty rare that you're ever gonna know hey I need to go to record number seven of this table but just notice that as you move from record to record it does show you that I do have a left arrow with a bar when I click on that notice it goes to the very first record I have a right arrow with a bar when I click on that goes to the very last record and then just pass that one this little arrow it's a little bit hard to see it's supposed to have a little flash on it called a gleam and you notice as I hover over it it says new blank record and when I click on that sure enough Here I am creating record number 30 right now I'm not actually go through with that but you can see there are some ways that this acts differently then Excel so why don't you take just a moment put the video on pause and try some of the things you've seen me doing where I clicked and then I used arrow keys to move from field to field arrow keys to move up and down from record to record I was also playing with the little navigation arrows down here and if you want go ahead and click on the button to create a new record just don't actually go through with creating a new record so you're just going to take a moment and play with navigating inside this table so pause the video and get comfortable with that and then we'll look at a couple other things and I'd also like you to see one other thing about a table right now we're on what's referred to as the datasheet view of the table but somebody had to decide what sort of things we were gonna keep track of in our fields the fact that we want to keep track of a company name the contact person's last name and first name and email address that's done in something called the design view and all the different objects we're going to look at here in the first couple of sessions they each have a user side where the data entry person does things and there's also the design view sometimes I call it the architect side let me just show you a quick way to get into the design view I'm gonna go to the home tab and then under the Home tab in the home ribbon there's a nice button over here called the View button and at first it kind of looks like some old old style draftsman tools over here I'm using some third-party software to zoom in on it like this you won't be able to do that you won't be able to zoom in like I just did so I've got the pencil and the triangle and a little ruler here when I click on that particular button it takes me to what's called the design view of my table and so in here I have two parts to it I have a top part which as the field names and then I have a bottom part that has the properties to the fields and we'll be playing with these in a few minutes but one of the things I'd like you to notice is in my design view for the customers table I have a field named the ID the company the last name the first name the email address we saw those just a moment ago only instead of going up and down on my screen they were going sideways well if I go back to that view button and I click on it Here I am back in my data sheet view where the field names go sideways across there so why don't you put our video on pause for just a moment and go up there click on the Home tab and click on the button to go to the design view please try that out everybody and then come on back and we'll talk about it a little more so here in the design view I have several columns here I've got the field name column and then you can see for each field name there's also a data type now most of them say short text but as I use my arrow keys to move up and down through the various fields here again most of them are short text one of them says long text as you can imagine that has to do with how many characters you can type in there I also see a hyperlink field down here I'm in fact here's one for attachments if I click on the little list arrow right here here's a whole bunch of different data types that I can have in my tables and we'll be talking about these more as we go and then there's a third column here it says description as you can see it says that's optional that's a place to write a little note to your data entry people to let them know hey this is what I expect you to be typing in this field so again we'll be doing some exploring of that in a few minutes when we start working with data right now we're just introducing the objects and mostly what I want you to walk away with in our introduction to the objects is that there's the user side and there's the design side so this was the design view of my table I'm gonna go up and click on this button to go back to my data sheet view so if you haven't already take just a moment to do that go to the design view scroll up and down to see all the different field names in the design view try not to actually change anything in there and then click on this button each time you click on it just takes you back and forth between the design view and the datasheet view when you're done come on back here and we'll be looking at the datasheet view of our table now over here at the left-hand side in my navigation pane I do have other tables that are available if I click just once on a table its selected but it doesn't open up if I double click on it does actually open that table and I've got another one down here for my invoices so I've got tables about my customers I got tables about my employees I've got tables about who ordered what on which days and those are all stored in various tables now maybe I don't want to have all three of them open at the same time I can right-click on one tab here I'm gonna say close that sheet I'm gonna go to this right click and say close that line in Excel that would be called a worksheet here in Access it's called a table and I've got another choice here says close all hmm sounds kind of dangerous but I'm gonna be brave and click on it notice it didn't close the whole database I still have all of my navigation pane objects available over here it just closed all of the tabs that were open let's do a little bit of data entry here for a moment I'm gonna open up my customers table with a double click and this Anna B Dex let's say she would like to be known as Anabel from now on so I'm gonna do my first editing here I'm clicking right at the end of Anna and I'm adding the Bell nice southern name and a pencil appears and that pencil means that I am editing that record and it also means that that record hasn't been saved now you Excel people you're thinking yourself of course it hasn't been saved um you would have to go to the file menu and choose save well that's true for Excel but it is not true for access in the access as soon as you finish editing a record and tell it somehow that you've finished editing that record it updates the record right away no special saving required and one of the big reasons for that is Microsoft Access on like Excel access is built to be a multi-user program in Excel if one person has a file open and another person tries to open it they will get a read only copy in Access that's not true so as soon as I change a record and tell it I'm finished editing it it will save it right away and the other people who might be working in that table it'll take it a couple of seconds but they'll be able to see the new data so how do I go about saving a record well a couple of things here I could either hit the down arrow key to move to another record or I can grab my mouse and click on the little pencil here I'm gonna do that I'm gonna go click on that little pencil and that has saved to that record and if I close and reopen that table you'll be able to see that it is no longer Anna it will remember that we've changed it to Annabelle so I'm doing that right now I'm right clicking to close just that table I'm gonna double click to reopen that customers table and there is our new lovely lady Annabelle bdx why don't you take just a moment catch up with me there so I had had two or three other tables Oh simply by double-clicking I did a close I did a close all and then I went in here and I changed Anna to Annabelle and remember once you finished doing that as long as that pencil is there it's not been saved yet you go click on the pencil and it'll finish saving that record so take a moment and do that everybody do have just a little bit of data entry into that table please over here at the left I have my group of tables is open if I grab this scroll box and the scroll bar and drag down I can find the end of the tables list in the beginning of the queries list now I can have both of those lists open at the same time but I'll spend a lot of time scrolling so here's what I'm gonna do I'm gonna collapse the list of tables by clicking this double up Chevron right here notice that collapses the list of tables and now if I click on the word queries it expands the list of queries so that's what we're going to talk about next I was looking at my customers table I seem to have a customers query has the name customers extended I'm gonna double click on that to open it 10 so at first it looks like a table it's got rows and columns but if you look closely at the index tabs up here you'll see there's a subtle difference between the icon for a table versus the icon for a query the icon for a query is supposed to kind of look like two tables overlapping each other there you'll get used to that you'll be able to tell the tables from the queries with just a little bit of practice so every time I run a query it pulls information from my table for example I'm looking at my Anabel bid X down here having changed it in the table now when I run the query it changes it here in the query as well but I'm noticing I have a column over here for last name and a column for the first name well let's say Annabelle she's really finicky now she would like to be known just as an e now you'll notice I am NOT in the table right now I'm in the query I'm gonna go over here and click on Annabelle's name I'm gonna use my backspace key as soon as I start doing that that pencil appears everybody remembers what that means it means you're editing the record and it has not been saved yet so I'm gonna change that to just an E and then another way that I can finish editing the record is to jump to the next record now if I was in Excel hitting the enter key would jump down to the next row notice here in access when I hit the enter key it stays in that same row um so there's a couple of things that I might want to talk about there you've seen that if I click on the pencil it will finish saving that record we just also mentioned if I click on some other record here notice the pencil disappeared and Annie is now Annie shoes to be and shoes to be Annabelle now she is Annie in this query I sure hope that means that she's Annie in the original table well let's go see here's my customers table sure enough here's my new Annie bead X and that's because of this this diagram I had up here earlier so I had Annie in a table and when I ran a query had pulled information from that table and found that she's Annabelle and then in the query we entered data and it fed that through this arrow back into the original table that the query was built from so those are interactive just remember where all the data is actually stored however is in the tables not in the queries on the other hand if I go back to my Access database here so here I have a table here I have a query there being really interactive here and I did say that all of these objects have a design side to them so I'm over here working in the query now I've just clicked on up and now I'm gonna go to the design view of that query so this looks a little bit mysterious because we've never seen one of these before but down here I have information about what I want in the query and how I want it sorted so we'll come back we'll be talking about that in a few minutes but right now here's all of the fields that are in the customers table from which this query is built and then here are the fields that I want to see in there and if I want to go back to the datasheet side of that just go back up here to my View button again and click on it and now I'm back into the datasheet view of that query so a query pulls information from tables and if you then edit information in the query you are actually storing that new information in the table or multiple tables from which the query was built we haven't seen a multi table query yet that is coming up down the road somewhere along the way now there's one other piece of information I'd like to talk about with data entry and this is true whether I'm working in the table or in the query I'm gonna go to the table and literally flipping a coin here so I'm going back to that customers table and I talk about the idea of making a mistake while you're doing your data entry so let's say I'm here working on miss Annie man I'm changing a name all over the place maybe I'll change your phone number this time so I'm gonna scroll over to the right we have some fake data here looks like everybody's got the same phone number that's a little surprising um so let's change the phone number for Annie bead X so I'm gonna change this to one zero one zero one at the end of this and sure enough there's my pencil all right now let's let's say that I have maybe I've hit the enter key thinking that that's gonna save my file notice it did not pencil is still there and now I'm starting to put in a home phone and I realize well wait a minute that's not actually her phone number it's not zero one zero one it's zero one zero two now I haven't saved it yet so I can go back there and change it right now zero one zero two no no you don't click somewhere else as long as I haven't gone to another record it hasn't been saved so as long as I'm still typing the pencils still there nothing has actually been saved and I can make changes right then let's see what happens if I tap the Escape key I'm gonna do that right now tapping the Escape key can you see what that did it took away the pencil and it put this record back to what it had been before I started typing there at all so that's a little moral to the story there if you are entering a record and you haven't left that record it hasn't been saved yet you can tap the Escape key to put the record back the way it was for example maybe I couldn't remember what the original phone number had been and I'm partway into the data entry and I realize I'm editing the wrong record I can always tap the Escape key to back out and you know just remove things that I've been typing as long as they haven't been saved yet but what if they have been saved let's see so if I click here and I make that a zero one zero one again and then I click on the pen all right I'm trying to hit the escape key right now tap tapping on the escape key too late to get me out of that to go back and fix that zero one zero one but I do have an undo available so I'm gonna go up to my quick access toolbar and here's the undo button when I click on that there's an e bead X and if I scroll to the right there his or her old phone number is back in there now let's see how many undos I actually get here so I'm gonna change Annie's number to 0 1 0 1 and then Antonio agora to coaster arbol I'm having trouble pronouncing that one I'm gonna change that number as well now notice as I click on the next row the first one's pencil has disappeared and now this one's going to be 0 1 0 2 all right so I'm gonna tap my pencil over here there I've just edited that second record now it's too late to hit escape if I move down to the next record and I start making this as 0 1 0 3 as long as I haven't saved it I can tap the Escape key and it will put that one back the way I found it now I'm wondering about escape again could I get back oh you know 0 1 0 1 up here I'm tapping the escape key right now too late for that now I'm going up here to try to undo too late for that I get 1 undo in other words if I go up here and I change this record to 0 4 and then I move to another record or I click on the pencil it's too late for me to hit escape but I can undo that one but I only get one undo if I have saved 2 records I can undo the previous one but not the one I did before that so let me wrap a little bow around that while you're typing before you've saved it you can tap the Escape key and put it the way it was before you started typing anything once you have saved it you get one undo you can undo that last record that you edited but if you've made a mistake on some record that was two records ago there's no undo there's no escape to get that back there's a little shiver going up my spine right now because this is a big difference between access and Excel in Excel I can make all kinds of mistakes and when I go to close that sheet it'll say hey do you want to save the changes and if I realize I've made a bunch of mistakes I can say heck no I don't want to save that I made mistakes but it is too late to do that in Microsoft Access so data entry people little shiver ought to be going up your spine right now because you only get that one undo now I've just been tooling right along doing stuff and I haven't given you a chance to practice these things so now would be a good time to put our video on pause come on in here into our query name customers extended try changing a couple of Records for example here we have Elizabeth Anderson near the top of the screen right now scroll up towards the top so feel free to change a name in here in the query make sure you click on the pencil or some other way to tell it that you've finished editing that and then go back here to the customers table and see that it has in fact been changed in there and then maybe change something in the customer table make sure you click on the pencil to finish saving that one and then come on back here to the query you should be able to see the information in there as well so put our video on pause take just a moment and try that out do data entry in the query see it happen in the table do data entry in the table and see it happen in the query and then we're gonna switch gears and start talking about forms so take just a moment pause our video try those things and then come on back and we'll talk about forms in our last session we were comparing queries to tables now I'd like to go one step further I'd like to start talking about forms remember our diagram here so I'm going to right-click and close all and I want to talk about form so I'm going to hide the queries now I'm going to click the double up Chevron's to hide the queries and I'll expose the forms list so we were working with the customer table and the customer query maybe we'll look at a customer form now there's a couple of them here one of them says customer details one of them says customer list I'm going to start out with the one called customer list with a double click man it sure looks like a table it sure looks like a query got the company names I've got the first name and last name of my people here's my auntie Bea Dex so this is called a forum notice it's got a little different icon than the queries or the icons had and if I go to my little diagram here forums can pull information from tables and if you do data entry into your form it will feed back into the table that it came from so let me demonstrate that for just a moment here so here's my form see this Ana bedeck she's been she's been moved around and chopped up and given a new phone number and all kinds of new stuff there about I'll look at Antonio gunner out to coasts and we're gonna change his title over here now he's an owner let's call him an owner manager so I'm gonna change that owner / manager now you notice I'm typing this right into the form a pencil has appeared I'm gonna click on the pencil to finish saving that information about Antonio by the way as I scroll left his employee number 2 so if I go back to my list of customers in the tables who the tables way up here so I'm going to expose the tables I'm going to go to my customers list double-clicking so here's Antonio of the new owner / manager this is in the table remember the table store all the data remember that we can do data entry in either the queries because they can feed back into a table or in this case the forms because they can feed back into the table and that's what we're doing right now feeding back into a table so I change that in the customers list form and it now has changed it in the customers table now there was another form over here called customer details let's look at that one for just a moment so back into my list of forms here and I'm doing my customer details this time notice this time I see one record at a time now this can be handy if you've got lots and lots of fields in that record so then instead of having to scroll left and right and left and right you know back in this table or some kind of a linear query or a table like form that we saw just a moment ago this details form shows me one record at a time and I can navigate from field to field to field and maybe fit all the fields on one screen this way also notice I could conceivably have a picture of this person she is she is not really faceless there that's just a placeholder for it but what I'd like you to see is there are two kinds of forms here one of them looks like a table and that one's great for looking up information like all the people who live in New York and you could see them all at once I can do those kind of searches here search for what state or province they're from but then I would see all the people from Oregon one at a time and I could navigate from record to record to record as I have been doing with these other objects navigating from record to record using the little arrows down here here I'm clicking on the arrow to go to a record number two and record number three so I can still navigate from record to record as I could with the tables as I could with the queries that's just this kind of detail form I'm only seeing one record at a time so this kind of a form is usually used for data entry but not usually for looking up a whole bunch of people because we can only see them one at a time the other form we were looking at that looked like a table that one is more often used for the lookups where you could see all the people from Oregon or California or whatever you're looking for but for data entry you'd have to scroll left and right that was this forum okay so we got forums who got queries we got tables and you'll get used to seeing the different icons up here so I just switched from a customers table to a customers forum and this was the multi-record forum a version of it but again I can do data entry here I'm not going to necessarily do it but if I type here and I begin typing you can see that pencil appearing I'm just going to tap the Escape key and step out of there so that's the idea of a forum and as with the other objects the forum has a design view as well so if I go up here to my form my pulldown list arrow under the View button I can go to the design view of a forum and here's what that looks like so again we'll be navigating back and forth between the design view and forum view the design view and the query view of the design view in the datasheet view and so forth just again each of these objects has the user side and the architect side and again I'd like to leave you just a little bit of time to practice so why don't you work on record number two whether you want to be in the customer list form or in the customer details form either way it's fine go to record number two and change Antonio from an owner to an owner slash manager make sure you finish saving that and then go check it out in the customer table to make sure that changing it in the forum has changed it in the table and then if you'd like to try it in the other direction change something in the table and then go look it up in the forum so just practicing for a moment to see how the table and the forms work together for data entry one more type of object that I'd like to look at is called reports so let me bring up my little diagram here again so the tables are the most important none of these other things really exist without the tables and notice a single headed arrow in this case reports can pull information from tables but there's nothing you can put in a report that will feed back into a table on the other hand reports are all about fitting things on pieces of paper so there are some things that you can set up like the margins and what do you want in the header of your report what you want to and in the footer of a report you can print tables you can print queries but for example if you tell it to print a query in landscape orientation the next time you go back and try to print that query it won't remember that you wanted it to be in landscape orientation so if you want to remember details about the print outs you need to do that in a report and that's what we're gonna start talking about right now so I'm going to head back to access you know use my clothes all to close this table and form that I was working in in the last part do a close all here let's see I'm looking at a long list of forms I'm gonna collapse that I'm also looking at a relatively long list of tables so I'm gonna collapse that and let's talk about reports given that one a double-click actually just a single click will open it and I'm gonna look at my customer address book here so I'm gonna double click on that here it comes notice kind of fancy-schmancy stuff going on here a little rolodex look to it where I've got things alphabetized all my people my customers whose last name starts with an A starts with a B and so forth so there's some extra formatting going on here that's not really available in the forms in the queries in the tables and so forth so why don't you do what you just saw me do put our video on pause close any other objects that you have open any tables and queries and so forth and then go to the list of reports and open up the customer address book report so that your looking at the same thing I'm looking at right now pause our video go open up that to report and then we'll come on back okay so as I scroll left and right I can see information on here scrolling up and down just rolling my scroll who else there's my list of customers in an address book report and it also has a design view if I slip over here to my view button this time I'm going to click on the little pull down arrow for the View button I can see there's a report view as a print preview a layout view and a design view we've said all of these objects have design views so let's just take a peek at that one for a second clicking on the design view so here I've got something that appears at the very top of the report called the report header down here towards the bottom is something called a page footer and if I scroll down some more there's a report footer section it's kind of hard to tell but this is the part that does the little the read a or the read B that's appearing there page header appears at the top of each page and the detail section is the layout for each of the records so again there's the user side and there's the design side and those things are found under the view button here so I'm going to switch back to the original one that I was looking at called the report of you now I'm in the report view scrolling down towards the bottom of this thing it's kind of hard to tell how much fits on a page here this is kind of like a web page view or it's sort of like a roll of paper towel with a bunch of printing on it's hard to tell how much it's gonna fit on one page so I'm gonna go click the pull down arrow to look at print preview this time so it says there's a section that's wider than the page width and no other items in that space so some pages may be blank we'll deal with that as it comes let's click OK and so here I am in the the print preview and now as I click on the little arrows here it's not about go to the next record it's about go to the next page here I've just clicked to go to page two it says well I got a blank page there it was promising that a moment ago I'm going to go to page three oh there's information there and then I'm gonna go to page four that one seems to be blank the problem here is that the information is wider than the paper is so I might need to do things like switch it to landscape orientation it looks like it is already and then turns out there's a couple other things we can do to try to squeeze information on to a page and we'll deal with those things later basically what I wanted you to see is there's a difference between something called the report view which is just a big list versus the print preview that shows you exactly how much will fit on each page and I'm going to close the print preview here's the button to do that so why don't you put our video on pause for just a moment head over to your home tab and switch views so far you'll be in the report view if you want to try the layout view so far you know that's a little bit tough sometimes to figure out what's going on in there let's take a look at the print preview this is the one where he can actually see how much will fit on each page and this is also the one where you have a blank page on every other sheet and later on we'll talk about why it's a little bit too early right now it's just the introduction part of our reports right now so again switch back and forth between the design view and the report view and the print preview if you will and then when you're done you can do what I'm about to do here close the Print Preview and you'll and then maybe actually go to your report view when you're all done with that so take a moment and catch up with me check out the other views of our report we won't be doing any data entry in there because you can't really do data entry in a report and feed it back into a table since the end of our last discussion I have closed the reports list it was open like this and I just clicked on the double up pointing Chevron to hide all of the reports and now I'd like to expose the names of the tables so I'm gonna click on that then I'm gonna scroll down and find a particular table named products now single clicking on it makes a change color but I don't actually get to see it you have to either double click on it or here's the way I haven't mentioned before you can right-click on it and tell it you want to open it by the way notice you could go directly to the design view when you right click I'm just gonna say to open that products table so whether you'd like to double click on it to open it or whether you'd like to do as I just did right click on it and then left click to open up please open the products table and then come on back we're gonna talk about some of the tools that you need for whatever kind of database you keep track of alright everybody so hopefully by now you have that products table open and again we're keeping track of products right now there are other kinds of specialized database programs out there for example maybe you have used a program named act a CT that's a database program that handles your business contacts maybe you have used QuickBooks that's a database program that keeps track of financial information well access can do that kind of stuff it can do keeping track of your customers can keep track of your products keep track of your financial information but no matter what kind of information you're keeping track of in your database there are certain skills that you need and we're going to talk about a couple of those right now the first one has to do with sorting so by default this table right here the products table is being sorted by this ID column the ID field and that's because the ID field is something called the primary key in this table and in a few minutes when we start setting up tables from scratch we'll talk about what the heck is a primary key and why do you need one and what's it used for and blah blah blah so right now this thing is being sorted by the ID column now as I scroll to the right looking at my products I can see there's a field over here called category and I've got several products that are in the condiments category several products that are in the baked goods and mixes category maybe I'd like to put all of my beverages right next to each other all of my condiments right next to each other that can be done by sorting so that's what we're going to talk about first how to sort so I'm going to tell it to sort by the category column by clicking anywhere in the category column and then appear under my home tab I see one button for ascending and one button for descending and that's all about sorting this is in the sort and filter group you can see right here so I'm going to tell it to sort ascending by that category field so now as I scroll up a bit here's my baked goods are all right next to each other then my beverages then my candy then my canned fruits so you can see that it has created groups now maybe within the groups I would like to sort a different way maybe within the baked goods I would like to see the most expensive baked goods come before the less expensive baked goods but I still want them sorted by baked goods versus beverages well here is a common mistake that I'll see people make so watch me but don't do it with me maybe what you could do is take just a moment and do the sort that I have done already click anywhere in the category column and then sort ascending put our video on pause take just a moment to do that and then come on back alright welcome back you should have everything's nicely sorted by category now don't do this next one this would be a mistake I'm gonna scroll over here and I'm going to say within the category I would like to sub sort by the standard cost okay so over here right hand side I've got big goods and mixes I've got four of those so maybe I'll just highlight those four records here so we can remember which ones are talking about then I'm gonna go over here and I'm gonna say all right I would now like to sub sort this by the standard cost so I'm gonna click anywhere in the standard cost column and then I'm gonna sort that one in how about descending order all right well now here is my most expensive product is at the top of the list and as I scroll down my less expensive products come up well what about my beverage isn't stuff I scroll over here to the right uh-oh I don't have my beverages all next together I don't have all my dried fruits and nuts together anymore so that was a mistake but it's a common mistake so I wanted to tell you hey I'm making a mistake and then do something that I've seen people do so often that if you try to do it you'll think to yourself oh no I remember that was a mistake which makes the question okay how do I tell it that I want to keep the category part together as I do this sub sorting by the price well first of all thank goodness for undo now you may remember when you're doing data entry you only get one undo I'm here me move my view up just a little bit while I'm doing my recording so here's my undo button I'm going to click on that and so once I've done that I'm gonna scroll over here and I can see all right I've got all my baked goods next to each other again so that's the position that you're in right now all right so let me show you the good way to sort by more than one field in Excel you would have something called sort by then by here in XS we don't really have an exact parallel for that but we do have something called advanced sort so I'd like you to watch me first and then you're going to try it with me so I'm going to click on advanced you can see it's a list arrow here and I'm going to say I want to do an advanced filter slash sort so you're just going to watch it first I'm clicking on advanced filter sort and now down here at the bottom I can see that I'm sorting by a category in ascending order however I need to readjust that so I'm gonna just click right here let me zoom in a little bit so towards the top of this little grid down here there's this light grey area if I could get my mouse right on that when I'm not zoomed in I get this black arrow when I've got the black arrow I can click and it'll highlight that that category and then on my keyboard I'm gonna tap the Delete key and it gets rid of that so why don't you take just a moment and do what you've seen me do there I went up here it didn't really matter what column I was sitting in in that table I went to the Home tab I went to advanced I clicked on advanced filter sort got me into this window and then I put my mouse right at the top of that first column got my black arrow Microsoft doesn't call it this I call it the black arrow of death and when I clicked it lit up that column and then I tapped the Delete key so please do that much go to this window through the advanced filter sort and delete that first column all right welcome back everybody so here's the next thing I want to do I want to make this field list up here a little bit taller I can do that by grabbing this corner and getting a funny-looking two-headed arrow and try to make this whole thing a bit taller once I can get that two-headed arrow that can be a bit of a challenge so here I am I've got my products table and here are a list of all the fields that are in that products table and so here's the deal I'm going to put in the two fields that I want to start by but in this program it likes to sort left to right so I want to be able to sort it by the category and then within the category I want to sort it by the price so first I'm going to bring in the category may have to scroll down a bit to find that field called category there it is and if I double click on it it finds the first spot available in the query design grid down here and drops it right in there so there's my first one category and then the second one I want a sub sort by is going to be the list price so then I'm going to double click on list price and then you'll notice the second row here says sort when I click in there I get a pulldown list or I can sort ascending or descending I'm gonna sort my categories in ascending alphabetical and then within the alphabetical list of categories I want the list price to be sorted descending in other words the highest price in any category should come first and then through the rest of the list of that category have the price go down so why don't you take a moment and join me here let's say you're already in the advanced filter sort you saw me scroll up and down to find the fields I double clicked on the field name category double clicked on the field name list price and then made category sort ascending list price descending do that much with me and then we'll have a little drumroll and we'll see it in action so catch up with me for a moment all right time to see this thing in action so I'm gonna go up here to my ribbon under the Home tab we've just set up a filter to get here now we're gonna toggle the filter that is we're going to turn it on so I'm going to click on toggle filter and I'd like you to do the same thing click on toggle filter do that right now okay now let's go look at our results so here are my big goods all right next to each other I'm going to select those four records just so we can see that those are the ones we're talking about and then as I scroll to the left to look at my standard cost among those four here's the highest standard cost among the beverages and then lower lower lower and then I got a $34 one here and that's because that's the beginning of a new category over here the beverages so now among the beverages they are sorted in descending order by their standard cost so checkout yours try your screen make sure that you're getting the same results so that in our design view of that filter we had the category on the left and then the standard cost on the right and that told it to first sub sort by the category field and then within the category field sub sort by the standard cost so compare yours with mine I hope things are going well all right welcome back hopefully you've checked yours out so far now what if I decide that I want to put them back in their original order well there's a couple of ways I could do that I could scroll over here and click anywhere in the ID field and then tell it to sort by that that's what they originally started by but as I'm looking up in the ribbon up here I do see a choice about remove the sort and as I hover here it's kind of hard to see but it says clear all sorts and that's what I'm gonna do so I'm gonna click on remove sort and I can see they've shifted around and as they look more closely I can discover that they are now sorted by that ID field which was the original so try that on your screen everybody go up and click on the remove sort button and then notice that they are now sorted by that ID field again catch up with me put the video on pause and remove that sort the next tool that we're gonna look at is finding things in our database as in find the next match find the next find the next one highlight the next one find the back start so we're going to be using this right up here looks like a little magnifying glass the find command so for this first one we're gonna look forwards anywhere in this table whether it's in a supplier ID column or the product name column or just a lot by using the find command so I've clicked just anywhere in the table that is it has basically selected the first record I'm going to go up and click the find button opens up a little dialog window here says what is it you want to find notice there is a find and replace command here although if I want to look for certain records and replace them with something else we'll see that there's a query to do that with later that actually will give us more control than this would so let's say I want to find the word dried in here so in the find what box I'm going to type the word DRI DRI edie and why don't you do that with me so it doesn't really matter what record you're sitting in just make sure you're in the products table with me so open up the products table and then under the Home tab let's everybody click on the find command opens up this Find and Replace dialog window right here and I'd like you to type in the word dried so put the video on pause catch up with me that far alright everybody welcome back so now I get to tell it do I want it to only look in the current field well the current field is the supplier ID that's what it's got lit up I hate to tell you it's not going to find the word dried there so instead I'm gonna say look in the current document that is look through all of the columns look through all of the fields now if I leave this one where it says whole field then that would only find records where the only thing in the column has just the word dried and I doubt that that's going to happen so I'm gonna click on the list arrow here do I want the word dried to have to be the first word in the field or is it okay if it's the second or third word in the field I'm just gonna say if it's got dried in any part of the field I want it to find those and then in the third list box here I can say well searched up from where I am there aren't any records up from where I am or searched down from where I am or search all of them just in case I'm down on the 25th record here I could say you know I don't have to worry about oh is the one I want above here or below here just find all of them so I'm gonna go with that so catch up with me so you've clicked on find you've typed in dried now join me in these other things current document any part of the field and all do that much and come on back take just a moment put the video on pause and do that alright here we are we're ready to see it in action little drumroll Shh and I'm gonna click on find it next and it seems to have found the word dried pears over here in the product name and I'm gonna click find next again and now it's found the word dried in the category field dried fruits and nuts find next there's another category dried fruits and nuts here's another product that has the word dried in it so that was this part down here where it says current document don't just look in one particular field so go ahead and do that click on a couple of fine Nexis put the video on pause and do that much all right very good now what if I just wanted to find all of the products by supplier J so I'm gonna cancel this one for the moment and I'm gonna scroll to the left and I'm gonna click anywhere in the supplier ID column and then this time when I do my find I'm going to tell it only look in the current column don't just look everywhere so I'm gonna click my find button it's ready to look further word dried I don't care about dried anymore I want to find all of the products by about supplier D and in this case I want it to just look in the current field that I'm standing in here that is I'm in the supplier ID column and it can be any part of the field and I want it to search up and down and all of the records all at the same time and find next so here's one by supplier D number ID product number one and product number 34 is also by supplier ID here's the next one supplier ID is one of the suppliers for product number 43 and again that was any part of the field as long as the word supplier D appeared then it should find that record fine next fine next until it can't find any more of those supply or DS so I'll put the video on pause and try that with me make sure you have clicked in the supplier ID column first before you tell it look through you know current current field so that was going to be look through the current field any part of the field search up and down and all and find next find next find next for supply or D so catch up with me again make sure you're standing in the supplier ID column so instead of current document should be current field any part of the field all fine next fine next fine next you should be able to find several of them put the video on pause and try that with me so we just found finished finding all of the records by supplier D so we've done a couple of different finds we had to find where we searched through all of the fields and then we just had to find where we searched through one of the fields so I'm going to end this particular lesson and when we come back we're going to talk about filtering for records and this means hide all the records that don't match criteria that I'm looking for so there's going to be kind of find on steroids if you want to think of it that way so that's all I have to say about find at the moment our next subject of discussion is called filtering rather than find the next one find the next one find the next one I would like it to hide all the records that don't match a particular criteria that I'm looking for and the way I'm going to do this is through the auto filtering arrows that are at the top of each column here in Microsoft Access again any of you who work with Excel you've probably seen Auto filtering arrows before they're basically going to work the same way here but I would like to mention a common mistake so as I look at the product code there's an arrow right up here and I can use that to look for a particular product codes when I go to supplier ID you click on there I've got the different supplier IDs to choose from but then I'm noticing in the actual data itself not the column header when I click here for supplier ID I see a little arrow there and I want to make sure that we don't mix those up in our brain so if I'm in one of the records here and I click the pull down arrow I am making an assignment I'm doing data entry so if I say supply or see and I get rid of supply or D and I choose ok see the pencil I'm actually editing that record I am not seeing just the records by supplier see right now I'm actually changing that data record thank goodness for the Escape key since I've not clicked on the pencil or anything it hasn't actually been changed I tap the Escape key goes back to what it used to be so there's a big difference between using the arrow in the column header versus any arrows that might actually be in the data rows so let me show you the difference here if I go up to supplier ID in the column header and I unselect all and I choose supply or D in there when I click on okay it hides all of the records that don't involve supply or D that's way different from find the next find the next find the next so join me there put the video on pause and let us click in the column header that says supplier IDs and go ahead and search for supply or D there and then click ok and you should get the same results that I get all right good work so far now let's bring back all of the supplier IDs so we're gonna go up and click on the little filter and we can do it a couple of ways we can either say select all or we can say clear the filter from the supplier IDs and when I click OK there are all of my records again so join me click on the pull down arrow at the top you can either use select all or you can say remove filter I did select all so if you want to try remove filter clear filter you can prove to yourself that those either of those would work to show you all of the records so pause the video and remove the filter for the supplier ID please all right good work so far now what if I want a filter for not one particular thing but maybe a range of numbers like I would like to filter for all of the products that cost between fifteen and twenty dollars so this one's going to have a little bit of a twist on it I'm gonna go to the auto filtering arrow at the top of the standard cost column and then yeah I get a whole bunch of check boxes here and by eyeball I could select and unselect the ones that match my criteria but sometimes man there's a lot of things to choose from there so rather than me check marking this and that I would like to introduce this guy right here number filters with only arrow next to it when I hover over that it tries to bring a list out to the right hand side but in this case I'm so close to the right hand side of the screen it puts the list on the left and you can see it brings up things that look like English so equals a certain amount or all those that don't equal a certain amount or greater than some threshold or less than some threshold or between two thresholds like 10 and 15 so I'm clicking on number filters first of all I've clicked on the pull down arrow up here under next to standard cost and then I'm ignoring all the checkboxes and instead I'm pointing at number filters to have these things pop up in English these are called natural language filters by the way if you care and I'm going to click on between here comes a dialog window and I'm going to say the smallest ones I want to see have the ten dollar standard cost and the largest ones I want to see have the 20 dollar standard cost and when I click on OK instead of seeing all 45 records I see only the 11 that fall between my threshold numbers so remember what I did here I am no longer filtering by supplier ID or anything like that so I'm filtering by the standard cost and instead of using a whole bunch of check marks I went with number filters it up between set an upper boundary or a lower boundary at 10:00 and upper boundary at 20 click done okay and now I'm looking at 11 records come join me there do a filter to filter for everything between 10 and 20 in the standard cost field please put the video on pause and then do that and then come on back alright welcome back now let's filter by two things at once so among these things that have their standard costs between ten and twenty dollars I only want to see those that are how about beverages or sauces so while I have the current filter going on that's got me down to eleven records I'm now going to go to the category field and I only want beverages and sauces so I'm gonna unselect all I only want the beverages and the sauces got a scroll down a little bit to find the sauces and I okay that Oh much shorter list this time so everything on my list is either beverages or sauces and among those they have to be within my price range there that was between ten and twenty under the standard cost which means this might not be all of the beverages and sauces this is only the beverages and sauces that fall in between those prices that I had so what if I wanted to see all the beverages and sauces well the answer is I can go here and either select all or clear the filter from standard cost and when I do one of those things I get a slightly longer list it still doesn't give me all 45 products because I do still have that other filter going on that says it has to be beverages or sauces so put the video on pause and catch up with me there so we started out by doing the the standard cost between 10 and 20 added to that that we only wanted to see beverages and sauces and then decided well maybe I'd like to see all the beverages and sauces without worrying about the cost so I went back here and removed the filter for the cost but kept the one for beverages and sauces and that wound up with eight records so take a moment and catch up with me there by doing some multi filtering everybody all right welcome back hope you're having some geeky fun here for being able to choose things by checkbox or choose things from pulldown list or choose things through the natural language filters that look like English but actually do produce like upper and lower boundaries of all right everybody good work hope you're having some fun for geeks let's go ahead and close the products table I'm right-clicking I'm choosing to close it when it asked me do I want to save the changes to the design they're talking about do I want to save it with the filtering that's going on that is the next time I open this table - I want it filtered like this I'm going to say no thank you so please join me in that will close the products table and we say don't save the changes to the design of this all right now I'd like to issue a little challenge to you I would like you to go into our customers table we can go open that together right now so let's go open the customers table and so as I go over here and look in the state province field I can see two letter abbreviations and right now for some reason in my brain I'm thinking of a national public radio presentation that I was a big fan of click and clack the Car Talk guys they are not on the radio anymore I'm sorry to say but as part of their as part of their broadcast they used to always say click and clack from Cambridge Mac as in Massachusetts so here is my challenge to you in the customers table I would like to see only the people from Wow or mad or care actually the cat talk guys they always had that little accent there so that's what I want that's what I want you to show me all of the customers from cat or Matt or wack put the video on pause do that when you come back I will have my version of that and we can compare our results so put the video on pause go find the Mac and woz all right I am about to join you so I'm going up here to the state province filtering arrow I'm gonna unselect all I want cat and I want math and I want wet my little drumroll here I'm clicking okay or hitting the enter key I am down to seven records I hope that that is true for you as well whoo or maybe just a single anyway all right good work everybody and let's show all of them again you can either clear filter or select all and I am back to my twenty nine different customers good work everybody good work on filtering there years ago I was doing some database work for a jewelry store and I was doing it in Excel I got talking to one of my geek friends and he said oh if you'd convert that over to access that thing you're trying to do would be easier so I got myself a copy of Microsoft Access and I didn't know how to bring stuff in from Excel into access so I retyped several things that I already had in Excel and put him in Access and then figured hey I'm gonna go get a total at the bottom of the column and I discovered at the time this was beckon access couldn't get a total at the bottom of a table so I started reading about how to do that and it turned out I had to do it through something called a summary report and I figured out how to do the summary report and said hey this is way too hard I'm gonna go back and do this in Excel and didn't touch access for several years until I could hook up with a guy who could kind of explain the ins and outs of it to me so the good news is nowadays since the 2007 version you can actually do a grenading kinds of totals when I say aggregating I'm thinking of things like sum or count or average or those kind of things you can now do that in a table and I'd like to show you how to do that for just a second here in the products table so as I scroll over to the right in my products table I have a numeric field here the standard costs so what if I'd like to total up the standard costs of one of each of my total products here well if I scroll down towards the bottom I should probably scroll to the left here for a minute I can see a row where I would add a new record so right now I've got 46 records excuse me actually I've got 45 records that have stuff in them and if I were to click in this next row down here I would be entering a new 46th product but I'd like to introduce you to this Greek letter Sigma right up here says totals and when I click on that look I get a new row down here so this is actually below the new record row and if I scroll up a little bit you can see that the total row will still be available even when I'm up here doing other stuff in my in my table so in my total row I'm going to scroll to the right hand side and talking about this row right down here where I'm going to total up my standard costs and if I just click in that new totals row ooh ooh that sneaky list arrow I click on the list arrow here are these different ways of aggregating saw more average or count are a couple statistic things here standard deviation variance I just want to be boring I just want to sum it up and there it is there is the sum of my standard costs so why don't you put the video on pause open up your products table and again the way I got that totals row to appear was clicked on this totals button that made the new totals row appear and then I clicked in the totals row under the standard cost and I chose to sum them up so why don't you do the same thing there please catch up with me totalling that up alright assuming that you have done that now maybe under the list price column tell it you'd like to find the average list price go ahead and do that and then I'll catch up with you alright assuming you've got that done I'm gonna catch up with you I'm cooking here I don't want to sum this time I want to average and there's my average list price now if I think that I'm gonna maybe copy this sum here over to that column I am afraid I'm sorely disappointed there is no little auto-connect button there is no little quick copy button I would not be able to copy and paste so that's one thing that can be a little bit irritating about the totals row you have to go to the bottom of each column and tell it what kind of aggregating you want to do for each column but good work we summed and then you guys did an average and I am so proud of you well done we'll be using that more in different modules till now we've been working with a database that was presented to us our next subject of discussion we're gonna start talking about what if I need to create a database from scratch ooh a little bit scary a little bit daunting so let's close out what we've been working on here I'm going to close the customers table when you close yours it's probably going to ask you do you want to save changes to the design of it I would recommend that you say no and then what I would like to do is start a brand new database but when you're designing a database from scratch it is really not best practice to just start in Microsoft Access what I always recommend is maybe start in Microsoft Word and then start thinking of what kind of information do I need to keep track of so for our purposes we're going to start building a small database for a veterinarian's office so first thing I would recommend is we're going to go to word and we're going to start like keeping track of ok what sort of information are we going to need for this so I'm going to switch over here to word crow kept a copy of Microsoft Word here and I'm just gonna start a blank document heylook templates here in Word just like we had templates in Microsoft Access so I'm going to start with a blank document so again we're talking about things that we men might need to keep track of for a veterinarian's office so those of you who have animals you're probably way ahead of me on this thinking about what kind of information have you had to give to your veterinarians office so I'll start talking about a couple of things and we'll start making a list and then I'll put the video on pause and I'll make the list longer so you don't have to watch me type stuff um so in general just kind of brainstorming so as far as when I take my pet in there for the first time I need to tell them my pet's name so pet name and maybe what else about my pet the type of fat that is like dog versus cat etc then let's see maybe the the breed which kind of dog do I have a do I have a German Shepherd have I got a angora cat etc so somewhere in there we're going to have to have some information about the owner of the Pat so it might have the owner name might have a date of first visit something like that still need to know about the Pat let's see we got the path to type the breed maybe why did they come in what was the purpose of the visit and let's see more information about the owner like where do they live so we're gonna have to have like the address and city and stayed and phone number and stuff like that so maybe in your head right now you're thinking of this kind of information that we need so I'm going to pause my end of the video and add some to this list and you know you may have some more that you're thinking of and and we'll take it from there so I'm going to put the video on pause and add some more to this list so here is a partial list and I've changed the layout a little bit of it so that I can talk about the fact that some of these things should go in a table about the Patt and some of these things should go in a table about the owner and then later we'll have to figure out so how do we gather information from both of those tables and get them into one kind of some kind of printout so let's see the pet name should certainly go in the pet table the owner name should go in the owner table the phone number probably in the owner table type a patch probably go in the pet table date of birth probably not really worried about the owners date of birth now a lot of times when I go into the into the veterinarian's office they calculate the age of the Pat but we can always do that calculation if we know the date of the birth Pet gender and we might also need to deal with things like spayed and neutered and stuff like that the breed yeah that's probably about the pat street address city state zip code those are probably all things I need in the owner table personally I'm hoping this one about the weight it has to do with the Pat then and not mine as the owner I'm hoping and we may come up with more but that's our first idea is what should go in what table now if I start talking about why did the pet come in on that day then maybe I need another table about each visit and if there are certain things that there's always the same charge for every time we do it like spaying and neutering and that kind of stuff then maybe we can have a separate table of the different procedures and how much they cost and then keeping track of what doctor did what kind of stuff so I'd need a table about the doctors so this can grow you know exponentially pretty quickly but I just want to corral it down to like two tables here to start out with so now that I've got some of these ideas maybe now I will go over to Microsoft Access and start creating these two tables so I'm gonna keep this document around here we will refer to it as we go in the meantime I'm going to go back and crank up access again and we're gonna start making a thing here from scratch so come with me to the file tab everybody and we will click on new and it opens up this list of different templates that are available but notice the one just in the upper left corner here is blank desktop database and that's actually what we want so again come with me up to the file tab to enter this area called the backstage view in the backstage view we're gonna click on new and among all these different templates we're gonna go with blank desktop database and mine is ready to create a database named database one I think I'd probably eventually like to give it a better name than that and you may or may not see the file name extension here the dot accdb that is that's a Windows thing not necessarily an Access thing also notice where it's ready to put this in the Documents folder under my name I think I'd like to change the name from database one to veterinarian so why don't you come do that much with me just so far we went to in our access database window we went to file and new in the background here we clicked on blank database that got this window open so come join me that far put the video on pause and come to this spot all right everybody I'm not quite ready to click the Create button because I don't want to put this database in the Documents folder I'd like to put this database in the same folder as the databases that I downloaded from the website so I'm gonna click the little Browse folder button here come do the same with me and I made a little shortcut out here on the left-hand side so that I could get to my ex s 2016 samples folder so I'm just gonna click there if you didn't do that with me then you would need to go to your desktop or your downloads folder or wherever you downloaded your practice files to and I'm gonna see this thing I'm about to create it's really an hour module one so I'm gonna put it in that folder access modules one and two folder and I'm gonna name it veterinarian and let her rip click on OK so join me there and let's put our new veterinarian database even though it doesn't have anything in it yet into that access mods one and two folder of your 2016 sample files so I'm clicking ok and so that tells it what to name it and where to put it and then last but certainly not least click on create a little spinner on screen and now you can see it's about ready to create something called Table one and it's added a column called ID and it's got another column here so it's click to add and so even though we haven't had a chance to save this table its generically on the list as Table one so hopefully you have done that put the video on pause click on your Create button and your screen should look just like mine right now having created the structure for our database and given it a name might be a little bit hard to see but it's up here in the top veterinarian database um we are now in the datasheet view where we could actually begin trying to enter records but we don't really have the structure for our table yet traditionally that's done in the design view so under my fields tab here I'm going to go over and click on the draftsmen stools to switch to the design view of this table the first thing it wants to do is name it I'm going to call it pets and then I'm going to hit the enter key or click OK and Here I am now in the design view of the pets table so join me there tell it you're gonna create your first table and you're gonna switch to the design view of that table please put the video on pause and catch up with me alright welcome to the design view of our first table you should be able to see its name here pets and should be able to see the name of it over here as well and then here is my first field and it's got a funny little symbol over here I see the words primary key and then there's supposed to be a little miniaturized picture of a key over there so when you create a table it's not required that you have a primary key but it is shall we say strongly suggested so what the heck is a primary key well it's a term that you'll hear in pretty much any database class you ever take and what it means is it's generally called a unique identifier and that what that means is that no two records in that table can have the same value in that particular field now it wants to name this ID and when I go to create a owner table later it's also going to try to suggest a field named ID and that can be a bit confusing later on when I start trying to bring things in from more than one table so I'm going to be a little more specific here I'm going to change the name of this I'm clicking right where it says ID hitting my left arrow key and I would like to call it Pat ID and the next thing I'm noticing is there's a data type over here says auto number one of the nice things about an auto number data type is you will never have the chance to accidentally have two records with the same value in this field because it's going to automatically number the next run each time you type stuff in this field and then there's a third column here that says description notice it does say this is optional this is where you write notes to your data entry operator telling them what's expected of them for each of the fields and I try to always put in a description of some sort so I'm going to say the description on this one is don't try to type here just tap enter key so again don't try to type here just half enter key I want to take a moment and put that in the description column put our video on pause catch up with me there all right now down here towards the bottom of the screen I have some properties for this field and we're going to be talking about properties as we go for this first one we don't really need to mess around with them very much so what I'd like to do next is get half my screen to look like this access window and the other half of my screen to look like my word window and you don't have to worry about doing this so I'm gonna grab this thing and drag it over to this half of the screen and then I'm going to bring up my word window and I'm going to drag it over to the left half of my screen this is called snapping to the edges of the screen so now I'm going to be able to help myself remember what was supposed to go in my pet table over here see let me hide the property sheet for a moment okay so we had the the pet name was supposed to be in our pet table so I'm gonna fill that in as the name of a field I'm not putting in the pet name yet I'm building the categories that we're gonna keep track up so pet name is one of my fields I'm hitting the down arrow key the type and then we had maybe the breed would be good to have here and then how about the gender and maybe the date of birth so you got the name the type date birth gender got the breed and the weight again of the Pat not of me and you might notice as I'm typing each of these things they all think that they're going to be short text and very often that's the case like the pet name that's going to be a relatively short text and so what does that mean well let me open up my window a little bit more here so short text means that the maximum number of characters I can type in that field is 255 now I'm probably not gonna have a pet name that's that long although every once in a while I will stumble across on television they'll have like this year's dog show the best in breed dog show and they the dogs have names like thering tanaan Smyth from Avon the third but even then I don't think they're gonna take up like 255 characters one other thing to know is that it will set aside 255 characters of storage space even if the pet name is only five characters long and what that can mean is as you get lots and lots of records in your database you're wasting a lot of storage space storing you know this many characters where you're only going to use a few so we'll talk about some of these other properties down here as we go so for pet names short text it's probably appropriate and then a little note to my data entry operator could say what are they supposed to type in here I'll type something like fluffy or spot if I can't think of a way to describe what I want him to type I'll give them little examples like this with the word fluffy okay so now type I'm hitting the down arrow key and so type could be like dog cat or turtle or zebra or something like that let's see the breed so that could be like poodle Sami's see for a turtle I only know of two the box and the snapping so I'll go with snapping so again these are just just suggestions to my data entry operator telling them what sort of information I expect them to type in so gender let's see I'm going to have this be a single character so it could be like M equals meow F equals female and equals neutered or s equals spayed now date of birth that one's fairly straightforward and it turns out there are several different formats that we could have for date of birth so I'm going to tell my data entry operator that what I'm expecting is mm / DD / YY now just typing that there doesn't force it to be that there is summation allure matting that will have to do for that and then the weight so maybe I want to wait to be in decimal pounds rather than pounds and ounces so I'm going to type that in meaning like 4.3 pounds instead of trying to type in four pounds eight ounces or something like that right so I'm going to call that decimal pound and again this is information for my data entry operator and and we'll see how that shows up for for them all right so take a moment and put your video on pause and come fill out what I have filled out please so I went to the design view of that pets table and so far you know we still have to do some stuff here in the data type column but fill out what you can see on my screen so far please so put the video on pause and catch up to me there there's one of them that you can't quite see off the bottom and that was weight in decimal pounds so put that video on pause and catch up to me filling in that much please alright everybody now we're going to concentrate on the datatype here so pet names short text is appropriate for that but I probably don't need 255 characters so I'm gonna fill that one in as maybe c-13 tanaan Smith and Avon the third picking a number kind of out of the air 25 I don't think I would ever use more than 25 characters for the pet name and if I decide that eventually I do need more than that I can always change it later so short text initially was set to use up 255 characters we probably only need 25 so let's change that pause the video and do that right now please alright next thing I'm going to do is go down to the type of animal here and this is gonna be again like cat dog turtle sea giraffe is kind of long rhinoceros there's a lot of letters so I don't know maybe 15 for this one so that'll be my field size this time is going to be 15 now you can see there's a lot of other field properties down here and we'll talk about them you know as they are appropriate along the way I'm not gonna talk about every single one by any means but we'll talk about several of them along the way so the type field before the pad you know dog cat etc 15 characters field size catch up with me there pause the video fill that in alright the breed could probably get away with let's say 15 there the gender if it's spelled out like this I really only need one character there so again the breed was 15 characters the gender one character alright continuing on in our list here the date of birth now this could be text but eventually I'm gonna want to be able to do math with it like how many days how many years old is this animal and you won't be able to do that with text so I'm clicking and I'm noticing the little list arrow here and click on the list arrow and you'll notice there is a choice that can store our dates and times so that's what I'm gonna change that to instead of short text for the date of birth it's gonna be date and time so do that one catch that one up with me then we got one left here the wait I said I wanted it in decimal pounds so if I needed that spelled out in pounds and ounces like X number of pounds and blah blah once's that would have to be text but again I may decide I want to do math without like how much heavier or lighter is my animal this time than last time so I'm going to click and I'm going to use the list arrow and we've got choices here like one of them says number one of them says currency like when I need to keep track of the you know the balance do that's probably going to be a pretty important thing in one of our tables somewhere along the way yes no would simply be a checkbox that you can check in hyperlink could be you click on it to go to a website or you click on this field to open up a Word document that's the contract that our customers have to sign things like that you could attach a picture of the animal could calculate certain things and these are you know several of these are things we'll talk about along the way but I think right now for this one I'd like it to be number all right so there is one more thing to talk about number here because I'm looking at the field size property it says long integer and so I'm digging up some dead brain cells now trying to remember what the heck is an integer well it turns out an integer is a whole number like 2 or 3 or 4 if this is going to be an integer it cannot be 2.5 and yet I said I wanted the pounds and unroll entered as decimal number so I need to change something about that field size I'm going to click right there where it says long integer and as I do that it tries to give me a little bit of help over here on the right hand side and I'm gonna use some third-party software to zoom in on that you won't be able to zoom in as you just saw me do so it's trying to tell me about this the size and the type of the numbers to enter into a field most common settings are double and long integer and if this field is joined to an auto numbered field in a relationship coming up later then it's got to be a long integer but in my case if I want to enter it in pounds and ounces it can't be an integer of any sort so I'm going to click the pull down arrow and I'm going to make it something called single precision and you don't necessarily have to know what the difference is between single and double but basically what I'm the reason I'm using it is that it's not an integer and I can enter decimals in there there is actually a choice here for a decimal as well click on that all right join me there make that wait a number and make it decimal alright everybody so that's the definition of all of the fields in our first table and some instructions to our data entry people what I'd like to do now is switch from the design view to the datasheet view by clicking on this little button right up here and it will tell you hey you have created a structure here that you've got to save you must first save the table do you want to we're gonna click yes and then here is the place for our first record so do that much go ahead and click on that datasheet view have it come out here we're gonna take a little little break at the end of this particular session when we come back we're gonna start entering data into there so put the video actually you don't even have to put the video on pause this is the end of this particular session right now so having created our table in the previous session we are now ready to try to do data entry into the table now right now my cursor is flashing in the pet ID field even if it's not flashing there on yours it turns out the pad ID field is the one that's active right now and you may remember we wrote a little description for our data entry operator that said don't try to type here just tap the Enter key and I wish that that would appear in big red letters across my screen it doesn't it's way more subtle than that it's happening way down here in this area called the status bar there's my note to my data entry operator don't try to type here just tap the Enter key so let's do that let's tap the Enter key and now it's going over to the pet name field and down here in the corner it says alright that would be like fluffy or spa so let's see our first pet maybe this will be maybe this will be a cat named fluffy so fluffy as far as the name of the Pat the pet name and then I can hit either the right arrow key or the tab key or the Enter key to move over to the next field remember unlike Excel where if I hit the enter key would jump down to the next record and the pencil would disappear because it would have saved it by the way also notice what happened in the pet ID field here this is Pat number one we our pet number one get out that big pink foam finger pet number one is fluffy so that was automatically numbered that was an auto numbered data type all right fluffy is a cat more specifically fluffy is an angora cat gender fluffy is a female cat now remember we set the length of data at one character so if I try to type female it's just generally beeping and laughing at me there so I limited it to one character therefore that's all I can type in their date of birth ooh there's a little mini thing going on over here called a mini date picker and when I click on that gives her a little calendar and if fluffy was born today then I can click Lee today button or I can click on any date here or I could actually type a date so I'm gonna go like override the little date picker I'm gonna click in here and type the date will say fluffy the cat was born on oh how about Valentine's Day of 2012 ah Valentine's Day and then as far as the wait I'm clicking in the wait column or hitting the right arrow or tab or Enter key and I'm noticing down here it says it's supposed to be typed in in decimal pounds so we'll say fluffy the cat right now as of you know today's visit is 6.3 pounds and then I hit the tab key and because that was the very last field in that record it assumes that I'm ready to enter the next record and the little pencil that used to be over here has disappeared even though there was another column called click to add by the way if I click on click to add it says all right what kind of field would you like to add here a yes/no field the date/time field whatever and then I could give it a name so I can actually create fields right out here and for the next table we create we'll actually be doing it that way rather than the traditional way which was to go to the design view up here so for the moment I don't really want to add that new field and I'm not really ready to add a new record either so join me there fill in that information for fluffy the cat and then we're gonna go create another table here so put the video on pause catch up with me so far we'll see you in a minute it is time to create our second table when we first created the database it handed us a table one and we changed that name to pets and filled in information in the design of it and then now we've filled in data in it it's time to create our second table and this time I'm not going to do it through the design view so to create the second table come with me to the create tab up here and then in the section about tables if I were to click table design that would take me to the design window if I clicked on SharePoint list I would be creating a table that I could store on a SharePoint website I do not have such a thing here so instead what we're gonna click on is just this one that says table creating a blank table so I'm clicking on that and I would like you to do the same thing please click on the button that says table pause the video and do that much all right welcome back you can see the generic name here table 1 let us right click on that tab and we'll save it I know we don't have anything in it yet but this will give us a chance to name it something better than table 1 like owners hitting the enter key or clicking on okay so now we have a new table name to owners it doesn't have much in it yet but there it is so far so pause the video and do that much please so here it's got a generic name for my ID field I would like this to say owner ID instead of just ID so here's what I would recommend let us right click where it says ID get a little pop-up menu about that field and notice one of the choices in it down there is to rename that field and to rename it I'm gonna name it owner ID and I'm not gonna put any space in that I'm gonna make it one one big long field name there or owner ID so please pause your video and do that with me when I hit the enter key that becomes the new name of it and then it moves over here to the click to add so right click on that first column rename it owner ID and then maybe hit the enter key or the tab key and a jump sideways here alright now I could say that I'm going to enter a short text field and have it be the owner's name but there is one thing that I should mention about people's names usually in the database we will separate them out into the first name field and the last name field and we do that for a good reason one of them is the sorting so if I were to just have a field named owner name and I filled it in like Bill Smith and Mary Brown then later if I decide I want to sort that I can only sort it by the first character in the field I'd be sorting by the B and Bill or the M and Mary so for years we should get around that by entering the names like this Brown comma space Bill Smith comma space Mary and that worked for a lot of years until the form letter came along where you're taking a list of names and then mixing them with a word document and it's supposed to look like it was written just for them won't that feel all warm and fuzzy when you get the form letter and it says dear Smith comma Mary you may have won the ten million dollars and so we're usually going to separate first name and last name and I would like to do that here as well now I could do them one at a time but starting with the 2013 version of access and definitely carried over into the 2016 version there are some shortcuts that we can use for entering things like names and addresses and so forth and I'd like to expose those to you now watch my screen here I'm gonna go up here where it says more fields click on that I've actually got quite a list number field long text short text currency euro um just all kinds of things in here but I'm gonna scroll way down towards the bottom there's a kind of a cool area down here called Quick Start and so one of the quick starts I'd like to expose you to is this one called name so again how did I get here well I'm sitting with my cursor in that second field having renamed this first one owner ID and then it went up here to under the fields tab clicked on mo fields scrolling down till I can see quick start and then watch what happens when I click on name boink last name first name fills it right in there for me of course I'll have to do the data entry for myself but save me a little bit of work with that quick step there so please do that that was more fields scrolling down towards the end Quick Start click on name and your screen should wind up looking like mine pause the video catch up with me all right well done let's do another one of those quick starts in that next column over here so click to put your cursor in that next column you're gonna go back there to more fields and we're doing gonna do a quick start for address when I click on address I get a street address a city state or province a zip code postal code a country field now maybe I don't need all of those but it sure is handy to have that Quick Start put in columns for all of those things so your turn remember you want to have the cursor in the text box right under the part that says click to add and then that was more fields Quick Start for address so pause the video and catch up with me there please all right good work now I could continue adding more fields out here let me go to my field list that we had created earlier here so we've got a section for the owner name got a section I'll just mark these in red as I go street address city state zip code we got placeholders for those we don't seem to have one for a phone number and I'd like to kind of do a special thing with the phone number field and I'm gonna switch to the design view for that now a couple of things that we might want to just stay out here and do just to prove to ourselves that we can you may remember that one of the details one of the properties of a field at least if it was a text field was how many characters we could put in there and you don't actually have to go to the design view to do that I see a box right here for the field size so let's do a couple of those right out here let's click under the last name column and we'll set the field size therefore gosh even if I have a woman with a hyphenated name I'm probably not gonna have more than I don't know 20 25 characters so that's what I'm gonna make this one 25 and I'm gonna hit the enter key and then for a first name I don't know 15 maybe address now this is just the street address not including the city and the state and the zip code and all that kind of stuff so if I had like extra thing on the end of an apartment 4b something like that so my street address five one five North Main Street Apt 5b all of that probably wouldn't add up to more than 25 City why don't we say 20 and I'll slow this down in a second state province if I'm assuming that none of my customers are going to be from Canada and it could just be a state then why not set that one for just two characters zip postal would do a special thing with that we don't know country-region I think I don't really even need that field so let's try this let's click I'm moving my mouse into the top edge where it says country region and I'm clicking to choose that column and then I'm literally going to tap the Delete key to take it right out of there it says are you sure you want to permanently delete this record and all data that's entered right now we don't have any data in it so I can get away with yeah go ahead and do that also if I'm not gonna have any Canadian customers I could just call this one state instead of state province so I'm gonna right click on that and I'm gonna rename it as just state all right now that's a lot and I did those kind of fast so when I go back and you know do it a little bit slower I can see that I mistakenly entered 20 as data in the city column rather than the field width so so I'm tapping the Delete key to get rid of that and I'm clicking in to what my cursor there and I'm gonna set my field size for the city to I think I said 20 so I'm tapping 20 hitting the enter key so some data may be lost if you've got longer city names in there already are you sure you want to do this yeah I don't have any data in there yet to worry about so I'll say yeah let's do that 20 is still showing up in a funny place there all right we'll deal with that in a minute so just going across here the last name we've set for 25 first name 15 address 25 city 20 state only two postal code we haven't made a choice there yet so it still says 2 55 so if I switch to the design view you'd be able to see those numbers easier so I'm going to do that I'm gonna switch to the design view ah you still can't see them any easier you have to click on each one to see the lengths so put the video on pause catch up with me there then you might need to write these down because it's kind of hard to go back and forth between them unless you pause in between so last name 25 first name 15 address 25 city 20 state 2 postal code we haven't filled in yet put the video on pause catch up with me that far please all right now I'm wondering do any of these need to be something other than short text name address city state looks like they're all going to be text fields now the postal code it's going to look like numbers but I'm never going to be doing math with it and if I tell it that the postal code is a numeric field it turns out there are some zip codes that start with a zero there are actually some zip codes that start with two zeros and if I tell it that that's a numeric field then when I fill in the zip code if it starts with a zero it'll drop that leading zero and I'll wind up with a four digit zip code so you know probably want to do something about that actually yeah we'll we'll deal with it as we go but short text is is basically what we want to start with there all right now I'm noticing that the owner ID is again an auto numbered field and that's what happens any time you let Microsoft Access make the primary key for you now there's a term that I've only used once let me remind you that primary key is a field where no two records in that table can have the same value in the primary key field and that makes auto number kind of a cool way to do it because it automatically numbers the next record there are a couple of things about Auto numbered fields that some people don't like but I also don't want you to think that every single time you put in a primary key that it has to be Auto numbered so let's talk about how we might change that up so right here where it says auto number I'm clicking to get my list arrow I'm going to tell it I want to make it short text instead so do that with me please change the owner ID it's still going to be the primary key I just don't want it to be Auto numbered I would like to change it to short text so pause our video and do that with me all right it's time to put in some descriptions over here so lastname firstname you know that part's probably fairly obvious but I usually put in some kind of description anyway enter last name and her first name overkill is better than underkill address is going to be street address and apartment number if needed we can say the city to city name state enter to character abbreviation Sydney didn't move down there we go for the state enter two letter abbreviation by the way there's comedian named Steven right and one of my favorite lines of his is don't you think the word abbreviation is too long I agree with him there all right zip and postal code let's talk about a special thing here it's called an input mask now you have seen input masks on various screens you just never knew they were called input masks so I'm you're gonna watch me first then you're gonna try it with me so I'm clicking right here in the property for input mask and when I click there I get three little dots over here at the right-hand side and then down here in the bottom right corner it says this would be a pattern for all data to be entered into this field for the zip or postal code so far so I'm going to click on the three dots opens up a little dialog window says you got to save the table first I'll say yes and then here comes my dialogue window with special kind of input masks meaning it will automatically fill in for example for a phone number will automatically fill in parentheses around the area code a little space and then a hyphen between the last three and last four digits their social security numbers it would group them into three digits add hyphens for me automatically zip code so right now we're talking about zip postal so I'm gonna go with zip code and then down here I see it says try yet so I'm going to hit the tab key to jump into the try it box and my zip code could be six five six four five and you notice it's still got four more characters in case I know the last four digits of that and if I know um I can fill them in but if I don't know them I can actually leave those last four are blank as it happens and we'll talk a little bit about why so I'm filling in the first five there and I'm clicking next and then it says do you want to change the input mask and it's got a bunch of zeros and nines here and all I want to tell you about that is zeros mean that they are demanded and the nines mean that they're optional so I could enter the five digits and I could skip over the last four digits if I don't know but if I get on here and try it and I only fill in the first four digits and I try to click on hello let me click down there excuse me let me hit tab key to jump down there if I only fill in the force for numbers and I try to move on says no you can't do that for the input mask with this coding you have to fill in five characters now they don't really spell that out so it would be kind of nice if they would say that in English but I'm just telling you because of the layout here that said five zeros you can't get away with just the first four characters you have to fill in at least five and then if I start filling in the last four and I don't finish the last four it actually has no complaint about that now I could also change my placeholder character maybe instead of underscores maybe I'd like to see pound signs and if I hit the tab key to try it out now then you can see I see pound signs instead hashtags if you want to call him that so let me back up a little bit because I would like you to try this now and again it's complaining that I didn't fill in at least five characters there so there we go so I'm gonna back up here and I would like you to join me now so again the way that I got there I'm just cancel for the moment so the way I got there was I was in the zip postal code field and in the properties down here I clicked input mask click the three dots yes save the table and let's go with the zip code input mask we'll click Next it'll set it up like this you don't really have to play with the placeholder if you don't want to click Next and then it's got one last question here how do you want to store the data you want to store it with the extra symbols in there or do you want it to just store the numbers and early on in my database life I used to say hey just store the numbers but then I realized later when I tried to copy it into Excel I'm in access for like for a phone number I could see the parentheses around the area code and then when I copied it into Excel later it didn't bring in the parentheses because I hadn't stored them so nowadays I usually say yes start with whatever symbols are part of the input masks store them with that I'm gonna click Next and it says you are all done and I'll click finish so catch up with me there make that an input mask for a zip code and just run it through its paces there and you should wind up with this property at the end all right welcome back now I'm starting to talk about how many characters do I need in there so it's five digits plus store the - plus the four more digits sounds like ten to me so instead of using 255 characters to store that 10 digit zip code we're gonna make that 10 for the field size all right we have just set up our owners table so we've set our field size to be ten to match up with the with the input masks that we've got created and we don't really need to necessarily drop any information for our data entry operator other than maybe enter just the numbers we'll add - and then I'm going to hit the enter key to finish typing that in so let's end this session right here and in the next session we're going to come in and actually fill in data but there's one more thing I would like to write a little note to my data entry operator that my owner ID needs to be first three letters of owners last name followed by or maybe just then three digits of street address and if that's going to be the case three letters of the owners last name and the three digits of the street address then my field size could be six characters instead of 255 so put the video on pause take a moment and do that and then we're gonna take a little break and then we'll come back and we'll do our first data entry into this new table so here we go I'm going to end this session right here hopefully you've caught up with me with all the stuff that we've been doing so far having created our second table it's time to now fill in information about the first of our owners so I see a little note down here about what I'm supposed to put in the owner ID field involving three letters of their last name and three digits of the street address and maybe I don't know what that is yet so I'm gonna go start filling in last name so last name is McAlister first name now my real name is Daniel but I sometimes go by a pseudonym that I made up for myself Fernando street address 1 2 3 Elm City of Bakersfield state of CA and if I try to put in any more than that it won't let me because it's only allowing two characters one thing that I want to come back to in a minute if I enter lowercase letters so far they look like lowercase letters there is actually a property that I can change about that and then the zip postal code 9 4 3 3 3 ok so now I'm gonna say I am done with that record I'm going to hit the down arrow key to go to the next record and they get an error message it says you must enter a value in the owner ID field so why is that well this is why there's a couple of things you need to know about the primary key I mentioned that the primary key is there so that I have no to records with the same value in that particular field second thing that I've mentioned in passing is that a table is automatically sorted by the primary key and then here's the third thing that we haven't talked about yet if your table has a primary key it cannot be left blank when you're doing data entry so that's what it's complaining about right now and it kind of did this on purpose to demonstrate this idea here so it's time for me to click on ok and go back here and make sure I actually do enter the owner ID that's supposed to be all right owner ID and click it in there supposed to be the first three letters the owners last name let's see that would be MC a followed by three digits of the street address so what if the street address is like 1 0 Elm then maybe I would have to put in a leading 0 like 0 1 0 for the last 3 characters in my case it's 1 2 3 1 2 3 Elm Street seems like there was a Freddy Krueger movie or something like that one of those slasher movies now I'm feeling really old as I remember Nightmare on Elm Street anyway when I hit the down arrow key to move to another record this time it has no complaints because I did actually fill in that primary key alright so put the video on pause and catch up with me you don't actually have to fail to enter the owner ID if you don't want to and just fill it in right now but I did want you to see that if it doesn't if you don't fill in any information in the primary key you should get an error message so take a moment put the video on pause and enter that data for Fernando McCallister all right welcome back so Fernando and I mean explain Fernando for a minute just give you a little brain break here so occasionally I will eat at Boston Market and Boston Market is one of those places where they take your order and then they call your name when it's up and for some reason every time they say order up for Dan three of us come walking up so I was trying to think of what to do about that and I saw somebody else do something that gave me an idea so this guy was probably college aged and he's at the counter and the lady behind the counter is saying so we'll call you when your order is up what would you like us to call you and that may have been the wrong question to ask this young man cuz I'll never forget his answer he said oh really would you call me fuzzy-face Sharkboy see how long I've remembered that but she went with it so she was typing that in or tongue was sticking out of her mouth out of the side of her mouth while she's typing and then five minutes later when his orders up you hear order out for a fuzzy finish Sharkboy and everybody turns to look but only one person came up so I don't go so far as fuzzy-face Sharkboy but when she tells me that she'll call my name when I up I tell her my name is Fernando and so far that's been working three of us don't come walking up actually I don't say for an endo I say fun and a little bit of a sexy twist on it anyway more information than you needed to know and then I was trying to think one day where did Fernando the name come from and I think it was from Billy Crystal on Saturday Night Live doing his impersonation of Fernando Lamas you'll feel as good as you look on your look mahvelous I think that's where Fernando came from in my brain anyway not at all related to access but a little bit of a brain break there in the middle of things trying to have a little bit of fun in what can be a rather dry geeky subject so that's where Fernando came from so anyway feel free to fill in a name and it can be different than that one if you like and then I'd also like to talk about how can I get these characters to look like capital letters even if I didn't type them that way so I'm going to take just one minute and talk about that so pause the video fill in some information there and then let me go tell you about how to make the sea like capital letters so here we go to get this CA to look like capital letters I'm going to go to the design view and in the properties for the state did set the field size to be two characters and then there's a property right under that says format and here's kind of a weird one if I put a greater than sign right there then no matter what I type uppercase lowercase it will just be displayed as uppercase letters it will actually be stored in the database however I typed it for example in this case I typed it in lowercase letters that's how it'll be stored but when it's displayed on screen in just a you know a little printout of the data of the table it will look like capital letters now any theories on if I put in a less than sign instead of a greater than sign yes it would actually make them look like lowercase and usually the next question people ask me is is there some way to do title case the answer is no there's not a format for that okay so we've let's go check it out I've put in my greater than sign I'm switching to my data sheet view says you want to save the changes to the structure yes I do and now as I look at the state it says California it looks like capital letters if I click in there I can see that it's actually being stored as lowercase but the greater than sign for the formatting makes it be displayed as uppercase letters so what did we see out of all of that well let's see we've seen a new property that can make things look like uppercase letters we've seen that a primary key field doesn't have to be Auto numbered that it can be combinations of other things now in this case this little method that I've used three letters from the name three numbers from the street address when I get up around you know 500 records in the air a thousand records I may run into by coincidence to different people who have the first three characters of their last name and first three numbers of their street address but it probably won't happen very often and if it really becomes a problem then I can change what the rules are about that but mostly what we've seen there is it doesn't have to be an auto numbered field now why would I ever not use Auto numbered fields well let me just mention one thing that messes people that you know causes some people to not like Auto numbered fields if I go over here to my pets table and I start putting in a new pet name like Bill the the puppy dog you can see that it's entering a two here for the next record okay well that part's cool well what if I realize oh I don't have the information about Bill yet I need to not enter this data anybody remember what key I can hit right now to stop entering this data and take back what I've done already hopefully the word escape has just leapt to your tongue I'm going to tap the Escape key sure enough since I hadn't like moved on to the next record I don't have to worry about undo but watch what happens when I start putting in the next name it's a number three now there will not be a number two even though I never finished record number two it has thrown number two out it has retired that Jersey shall we say and if I have filled in a record and then I later I delete that record and it was pet ID number five what's pet ID number six is not gonna like crawl up the list and become number five or anything like that so that's a couple of things that are you know just special about Auto numbered fields that bug some people but Mason basically what I'm saying here is you can have primary keys that are not Auto numbered fields all right I'm going to tap the Escape key again a couple of times to stop entering that record well in module 1 we opened up an existing database one that we had downloaded called Northwind and we saw that it had lots of tables and lots of queries we saw that each of our tables has the user side where we do data entry called the datasheet view and it also has the design view where somebody has to decide what kind of fields are going to keep track of and so we went in and set up our tables for the veterinarian's office in two different ways the first one we built it entirely in the design view the second one we built most of it in the datasheet view where we could have been doing data entry and we eventually came back there and did do the data entry and that allowed us to see those quick starts where it could put in the first name and last name fields for us and the address city state zip country fields through one of those QuickStart setups then we started doing data entry we saw that for an auto numbered field you are not allowed to type in there but for an auto numbered field if you begin entering a record and then quit entering that record that number will have been used up I mean that's not an awful thing but you know just one of the side effects of having an auto numbered field we were also able to make the owners table where we made it have a primary key but not an auto numbered field and we were allowed to do whatever we wanted in there a couple of things that we've seen about primary Keys first of all I haven't really mentioned this but a table doesn't have to have a primary key but it's strongly recommended and in our next module when we start creating relationships between tables the primary keys will become even more important so if you have a primary key in a table it is a unique identifier no two records can have the same value in the primary key field in that table and it's the automatic sort order for a table is by the primary key and for data entry people you are not allowed to leave the primary key field blank so that's a little wrap-up of what we've seen in module one as we move into module two we're gonna start talking about what makes a database a relational data and part of what that will involve is how about later when I need to do some kind of report about a particular pet and the owner of that pet so this idea of having information in multiple tables means that eventually I need to be able to pull information from more than one table at a time and in order to be able to do that I'll have to figure out some way to create relationships between the tables and that will be the big discussion of module 2 so come on back for module 2 so far for module 1 the creation of the tables and what the heck is a database this is Dan also known as Fernando McAllister signing off good day one and all and welcome to Microsoft Access 2016 module two as you can see my name is Dan McAllister and I will be your instructor today our modules usually start out with practice files and that will be true here for module two as well so you will need to go grab your practice files depending on how those practice files come to you you may see one folder named practice files you may see another folder named homework files we're going to be using the practice files and they usually come in a zipped folder so you'll need to download that zip folder and then never ever try to work on files inside a zip folder you would need to extract the files from that zip folder I'm going to be putting mine in a folder name to access 2016 practice files and I'm gonna put that folder out on my desktop you can choose to put them wherever you like but that's where I'm going to put mine and if you want yours to work just like mine then do yours that way so go grab your practice files and then come on back for the beginning of our next lesson alright let's get started assuming you have your practice files extracted from the zipped folder so I would like you to start Microsoft Access 2016 put our video on pause and take just a moment and start that up when you do this should be the start screen that you see you may or may not see over here at the left hand side the veterinarian database and the Northwind database those were used in module 1 so if you played along in module 1 you should see those over at the left side if you didn't then don't worry that you don't see those over at the left-hand side what we're going to do is we're going to click on open other files so take a moment and start access and then we will click on open other files alright welcome back everybody so let's go ahead and click on open other files and then we're gonna navigate to wherever you extracted your zip folder so I'm going to click on browse and when I do that like all good Microsoft and Adobe programs first place it looks is the Documents folder but that's not where I extracted my files too I put mine out on the desktop so I come scroll up here on the left hand side and click to go to my desktop and then over at the right hand side I can see my access 2016 sample folders I'm going to double click on that and then we're talking about module 2 so the next place double click on access mods 1 & 2 you know I'd like to start with the very first one on the list here alphabetically beyond clean first draft now you may or may not see the file name extension out here the accdb or the MD B's as they might be don't worry if you don't see those they're not terribly important and that's not actually controlled through access that's controlled through windows so let us open the one name beyond clean first draft I'm gonna double click on that right here here's how it opens up for me and because it came from a website initially it has this security warning so certain content has been disabled macros for example are being disabled and for most of our lessons we're gonna come up here and choose enable content by the way thinking in the future when we get to module 8 when we open our first file in module 8 we will not choose enable content but obviously that's on down the road so I'm going to click on enable content and then here's what my screen looks like once I have done that so please take a moment put the video on pause go open that file named beyond clean first draft please now over here in my navigation pane I see the title tables and there seems to be one named work order now something that we talked about in module 1 but we know that not everybody does our modules in order was this lookup list right here this little pulldown list when I click on that I have two groups of choices here how to navigate and how to filter so right now I'm navigating by object type and I'm filtering for only the tables now if I want to see tables and queries and forms and so forth I can say show me all the access objects in this case when I do that it turns out all I have is this one table anyway so let's take a peek at it let's double click on the work order table and it opens in what's called the datasheet view at the right side of my screen so please pause our video and double click to open the work order table please so the general idea of this database is that I run a custodial company so I hire janitors I provide them with the training I provide them with the supplies and then I farm them out I hire them out to other so for example I've got a client named a door cleaners and I've got information about that company got another company named the art shop I got another one named Beecher as you can see here and I've got information I've got contact information their street address city state zip code their balance why that's a pretty important one and then as I begin scrolling to the right I can also see the name of this custodian that I've assigned to them and the custodians address and the custodian city and state and zip code and their hourly pay well the problem with the way this is set up is not so much that there's a lot of fields but it's that there are two ideas in one table here I've got information about the clients and then over here towards the right-hand side I've got information about the custodian assigned to the client and that means that every time I assign a custodian to a different client way this is set up right now I'm retyping their name I'm retyping the address in the city in the state and the zip code for all of my custodians now the more times I have to type that the more storage space I'm using in my database wherever I'm storing it and the more times I have to type it the more the chances are of shall we call it pilot error for example there are already several things that are wrong with the data entry and this table only has three records in it take a look at it and in here you should be able to find a couple of mistakes that are already happening yes for example I see Michelle Lee with 1l and Michelle with two L's we've got a Dunlop Street over here we've got Liberty estates well then we got Liberty estates so the more times I have to type that the more chances there are for pilot error I've got two different custodial pay rates over here later on when I go to print out a list of my custodians access won't know that Michelle with one L is the same as Michelle with two L's so really what should be broken down here is one table for the custodians and one table for the clients and then some way to tie them together this creates what's called a relational database so we'll be talking about how could we separate out these two tables and then more importantly how can we make a relationship between those two tables so now that you can see what the problem is here let's see our first step towards a better solution so in the next section we're going to open up another file named beyond clean without the words first draft so having seen so having seen that the more times we have to type something the worse off we are for a couple of reasons using more storage space and chances of making mistakes let's go see the first version of an improvement to this when you have one file open in excess and you tell it to open another one it will automatically close the first one so we're gonna go up to the file tab you're gonna tell it to open a file we're gonna head to our folder where we extracted our downloaded files this time I'd like to open one name beyond clean that doesn't have the words first draft in its title so I'm about to double click on that beyond clean in my mods 1 & 2 folder you'll notice this time it has two tables you are going to see the thing about certain content being disabled you'll need to click on enable content so pause the video and go up on the file named beyond clean alright welcome back so one of the first things you're gonna see here is that this time there are indeed two tables one for my clients and one for my custodians so let's open the client table I'm going to double click on that why don't you do the same thing so pause the video and open your client table please alright so here you'll see that I've apparently managed to get myself some more clients and as I scroll to the right I see contact information for the clients but I don't see the information about the custodians I do see one column here labeled custodian the ID and I do have the same custodian ID in more than one record here so let me throw a term at you that we talked about in module 1 if you didn't do module 1 maybe you've heard of this term before anyway a primary key so tables don't have to have primary keys but they're strongly suggested and they're gonna come in handy for some of the things we're going to do in this module so I'm wondering is it possible that this field could be a primary key in this table so those of you who know about primary keys one glance at this would tell you no this cannot be the primary key because a primary key field is used as a unique identifier that is no two records can have the same value in this table in that one field called the primary key and you can see right now I have custodian ID number two has been assigned to one two three different of my clients so this cannot possibly be the primary key of this table however if I scroll to the left I'm noticing that each client number is different so I have a suspicion that that's my primary key and in fact database programmers are usually nice to each other and they'll make the primary key be the first column but you can't guarantee that so right now it's kind of hard to tell for sure if this is the primary key but those of you who were here in module 1 or those of you who are kind of experienced in Microsoft Access you would know that if you switch over to the design of you you would be able to see these field names and one of them might be marked as the primary key or at least you'd be able to tell that there wasn't a primary key so here's where I can go to my design view under my Home tab and my home ribbon I'm gonna click on the little draftsman stools in the View button and Here I am in the design of view of my table if I look closely I can see that there is a little key symbol right here for my client number and that means it is the primary key and it says so right over here so yes that is the primary key of this table I'm gonna now click on the datasheet view button and go back to the data entry side so if you'd like to try that on your screen just pause the video go to the design view side and prove to yourself that that actually does have a primary key take just a moment to show you something that has happened in the background when we created our look-up list to join the two tables in a relationship I'm going to go up to the database tools command tab which produces the database tools ribbon and there's a nice button on here labeled relationships and I'm going to click on that and it opens up a relationships window that doesn't really seem to have anything in it just yet and there's a button here labeled show table I'm going to click on that and I'm gonna choose these two tables the client and then maybe I'll well here's an easy way I'll click on client and then choose add and it drops that table back here and then I'll choose technician and then I'll click Add and it drops that table in the background and then when I close this window they discover here's these two tables with a little join line here and the join line goes from the technician ID to the technician ID the foreign key and the primary key so I'd like you to do that I would like you to go to the database tools command tab and in the database tools ribbon click on the relationships button to open this window and at first you won't see either of these tables let me just hide them again here for a second so at first you won't see any of these tables you can then click on in the relationships window here you could click on show table and that would give you a list of the available tables and you can click on one and click Add and then you can click on the other one and click Add there's other ways to do that but that's as good as any and then close this window either with this close button or that close button and you should see the relationship line sometimes called a join line between those two fields the foreign key and the client table joined to the primary key in the technician table so put the video on pause and do it you just saw me do alright we're gonna build on that in our next session also I'd like you to try this I'm gonna point at that join line and I'm gonna double click on it tap tap opens up this window that says the technician table has the technician ID field and the client table has the technician ID field and this is creating a what's called a one-to-many relationship that is in the technician table there is only one technician with technician ID zero zero two for example but in the client table there are many clients that are assigned to technician ID zero zero two so a one-to-many relationship the one primary key hooked up to the many records with the foreign key that have the same technician ID alright and again that's leading to something that we're gonna talk about more in this next session so if you would like put your video on pause go to that database tools ribbon go to that relationships Wynn to open up this thing we added those two tables the client and the technician and you should be able to see the join line now the new thing was double click on the join line to see this information about the fact that this is a one-to-many relationship pause that video and check those things out well alright welcome back so we can see the relationship line between those two tables that was created through our lookup list in the next session we're going to see another way to use this relationships window to create relationships between tables but we'll be doing it with a new database for this next session I would like to use a new database that we haven't seen before so we'll go to file we will choose open a file we're gonna browse out to our mods 1 & 2 folder and this time I'd like to open one name query please query now mine is asking me if I want to save changes to the design of the client table that was with the lookup list and so forth I'm gonna say yes please do save those changes do I want to save the changes to the layout of the relationships window yeah I would like to do that as well you're probably gonna see these same two questions so put the video on pause and join me in going to the file menu and choosing to open the database name query then if you get questions about you want to save the layouts of things choose yes and yes and then in this window of course we will choose enable content and this is how your screen should look once you open the database named queries so pause the video and do those things catch up with me here welcome back now right away there's a couple of new things happening over here in the navigation panel let you use my third-party zooming in software to zoom in on that navigation panel notice the funny names of the tables here names of tables starting with TBL and then having all the words switched together with no spaces in between I don't know if I've just created a new word of smushed probably not so and then my queries we haven't really played in any queries yet other than we were exposed to them a little bit in our first module the names of the queries start with qry in this case and then no spaces in the names of the queries these are following something called the Lozinski naming conventions le z y NS ki if you care name for a fellow named Stan Lozinski who was a bigwig at IBM years and years and years ago and he did not invent this but he kind of adapted it from some things that were around already this naming convention makes things easier for people who know how to program in a language called SQL structured query language and things are a little bit easier in structured query language if you don't have spaces in the names of things and the in structured query language it can be handy to know that something is a table compared to a query compared to a form so we're not introducing this because we think it's the most wonderful thing in the world for just introducing these Lozinski naming conventions because you are fairly likely to run into them somewhere in our database life so we're going to introduce them here so I have any table the name starts with TBL a query name starts with qry we'll see a couple others along the way as we go but right now in this particular database that's all we have is tables and queries now there's also Lozinski naming conventions for the names of the fields in the tables so for example I want to open up a table named personal data I'm just going to double click on it and why don't you do the same thing so open up the database name query you've probably done that already and then let's double click on the table name TBL personal data so pause our video right here and then you catch up with me all right welcome back we are in the TBL personal data and we can see that we've got some kind of ID probably a primary key first field I don't see any repeating data first name separated from last name got the address got the city got the state so let's go to the design view of this one of the ways to do it is to point at the index tab for it right click on it and go to design view there the other way was available under our home tab if you go to the home tab there is the views button and if you click on the View button that will take you right to the design view so here's my primary key called the STR employee ID and then there's an STR first name and an STR last name and there's no spaces in the names of the fields this is more of that Lozinski naming convention stuff going on so not only do we have three-letter prefixes for the tables versus the queries as I look here I have three-letter prefixes inside the table for a text field STR this is a really old programmers term a string variable strname yeah you to guess that no I doubt it then I see one here called DTM higher date it says that one is date to time date time DTM so these naming conventions have been around for a long time not every company uses them I just want to introduce them here because you're likely to run into them somewhere along the way in your database life now I'm notice saying that I've got a field named STR first name but when I go out to my data sheet view doesn't say STR first name it says first space name and yet they are the same thing let's see how that can be possible when you go back to the design view here and I'm noticing that for the first name and we click anywhere in the row here for the first name and now I'm looking at the different properties down here at the bottom so the name of the field is STR first name that's for structured query language programmers but down here in the properties it has a caption of first space name and you'll notice that when I go to the datasheet view the caption overrides the field name so this is how I can have a field named according to the Lozinski naming conventions and yet have them show up here for my data entry operator instead of saying STR first name it says first space name that's because that's the caption in my design view so the caption will override the actual field name in you know certain areas along the way that's all I'll say about that so far so the next thing I'd like to talk about is relationships so let's go ahead and close this table you could for example right click on its name and close it if you've made any changes to its structure it'll say you got to save this we didn't make any changes so it didn't ask us that and then I'd like to go to that relationships window again and I'm wondering if you remember which of the tabs it was under so you one two three four five you got a one in six chance of remembering which of the command tabs contains the button for the relationships window it is in fact database tools so I'm gonna go click on the database tools command tab and then here is my relationships window I click on that and in this case it immediately opens my show table window without me having to go click on the show table button and I would like to show all of the tables now one of the ways to do that is to point at a table and double click on its name tap-tap there it is it's showing-up in the background another way to do this is to click on one of the names and then hold the shift key and click on another one of the names now that was the ctrl key I said it was going to use the shift key let me do that again so I clicked on the first one this time I'm holding the shift key notice when I use the shift key and click it chooses all the ones in between when I clicked on the first one and held the ctrl key it chooses those two but not the ones in between you may have seen that in other programs so I'm going to click on the first one I'm going to hold the shift key and click on the last one and then down here at the bottom I'm gonna click the Add button and it drops them all in there and then let's close the show table window now you might have noticed that I put in the department table by double clicking on it and then in the show table window I still selected the department table and then shift click on the last one down here and then I added those and that means I actually have the department table in there twice and if that table shows up twice the second time it will say T Bale department underscore 1 there have even been times when I mess this thing up enough then I got the dreaded to underscore 2 and that means the things in there three times it would be a bad idea to leave it in there more than once it can create some problems the Euro need so I kind of did this on purpose and I hope you did it with me so put our video on pause let's just start that over again so I so feel free to join me there remember the way I did it was in the show table window I double clicked on Gmail Department and that put it in there the first time and then I kind of made a mistake on purpose I left the TBL department selected I shift clicked on personal data to get all four of them selected and then I clicked on add and this is what I wound up with I only have four tables over here but I've got five entries into my through the show table window into my relationships window so put the video on pause and feel free to join me there making that mistake alright so now let's see how to clear out a mistake I'm noticing the underscore one here let's go up and click on the title bar of that with that window that little table name click once on its title bar and then tap the Delete key and it will take it right out of that window so join me there keep to the TBL department but get rid of TBL department underscore one you just click on its title bar F the Delete key it's gone it hides it so put the video on pause join me when we come back we should have these four tables in there one time each alright now I can tick the tables and grab their title bars and move them around and stuff and I'm noticing for a couple of these tables I'm not quite seeing all of the field names so around my company this thing I'm about to do sometimes we call it landscaping like trimming the trees and the you know the lawn and stuff here's what I'm going to do I'm going to touch the bottom of this little dialog list here and I'm going to get a two-headed arrow and stretch it down until I can see all the field names and I'd like to do that for this one but I might not be able to see all of the whole name of the table so if in fact I'm more specific and grabbed this corner I can actually make it wide enough to show the name of the table and tall enough to see all the field names so basically what I want to do is set it up so I can see all the table names and all of the field names without having to scroll up and down in fact I need to tell you something this is the first place I go when somebody just hands me a at abates that I've never seen before I go to the database tools ribbon I click on the relationships window and if all the tables aren't showing right away I bring them in through the show tables window and bring them in like this and very often I will see the relationship lines already there's one other cool thing that I'm seeing here let me use my third-party zooming in software here I'm noticing a little symbol right there you may recognize that that is the primary key symbol so when somebody just hands me a database and I come to this window I can see the names of the tables that are available I can see the primary Keys what I don't see so far are those little join lines between the tables because we haven't done any lookup lists or anything like that what I'm about to show you is another way to create relationships between tables here so let me just back up for a second I mentioned this in passing in the last session that in order to create a relationship between two tables two things have to be true you may need to go look those up in your notes I'm gonna give me a moment to do that put our video on pause maybe go check out your notes and see if you can remind yourself of the two things that have to be true to create a relationship between two tables all right maybe you're baffled maybe you found them the two things that have to be true is number one a shared field one field that's in both tables number two in at least one of those two tables where there's a shared field that shared field needs to be a primary key so those are the two things that have to be true to create a relationship between tables and so far the only way we've seen to create relationships between tables was through the lookup list starting in the child table where the shared field was a foreign key and then looking up from the parent table where the shared field was a primary key and that created join lines well I don't see any join lines here but we are about to see another way to create relationships right here in this window so for example so I'm looking through these tables as I look over in my parking table I see a primary key named STR parking lot code just as a reminder STR means it's a text field and then I'm looking for some other table that has maybe a foreign key that I could join up with this primary key named STR parking-lot code I'm wondering if anybody sees one some of you are probably shouting at me through your monitors right now saying hey Dania TBL Human Resources data has an STR parking-lot code now I need to warn you that they don't have to have the same name in both tables but they do have to have the same data type for example these are both STRs meaning they are text fields so that's actually good enough I have a foreign key here notice no primary key symbol I have a primary key symbol there I could make a relationship between these shared fields I have the same field in both tables and then at least one of the tables the shared field is the primary key whew I am good to go so here comes a new way to make a relationship by dragging in this window and I'd like you to watch my screen I'm gonna grab the primary key STR parking lot code and I'm gonna begin dragging it sideways and at first it looks like I'm doing something illegal I'm seeing the Ghostbusters slash there but I'm gonna be brave and just drag over here and my Ghostbusters slash disappears and now I'm going to point the tip of my mouse arrow at STR parking-lot code in as a foreign key in the Human Resources data and I let go and it opens up this nice little dialogue window so I would like you to try that with me let me remind what I did we went to database tools relationships window we added the tables in here and now we're grabbing this primary key STR parking lot code in the parking info table primary key and we're dragging it on to the STR parking lot code in the Human Resources data foreign key and when we let go it opens up this dialog window so put the video on pause and do that much please all right welcome back you will notice on the left hand side we have the name of the here I'm going to use this term for the second time today parent table parking info table is the table where the shared field is the primary key that makes it the parent table in this relationship and it always puts the parent table on the left so the shared field called parking lot info in the parent table called TBL parking info is on the left and then the shared field that we dragged it on to STR parking lot code in the human resources data table is on the right hand side primary key on the left foreign key on the right parent table on the left child table on the right we'll talk about these checkboxes in a few minutes notice this says it's a one-to-many relationship in the parking lot code there's only one parking lot code for each parking lot primary key in the human resources to add a table there may be several people who park in the same parking lot that would be the many and of my one-to-many relationship and I'm going to finish it off by clicking create and now I can see that join line from parking lot code to parking lot code alright put our video on pause and I'd like you to do what I just did I grabbed the primary key parking lot code dragged it under the foreign key parking lot code it opened up the dialogue window and I basically said create there was no okay button it just said to create so put the video on pause catch up with me so let's try this again and notice in my relationship line here at least one end points at a primary key alright I'm seeing another one over here I'm looking at primary keys in the TBL department I see one named STR department code primary key now I'm looking in the other tables for a an appropriate foreign key that's charity Department now they don't have the same name but I did mention as a little side note they don't have to have the same name if you're the one creating the database be nice to everybody give it the same name in both tables but I'm kind of wondering this STR Department would that be the same as the code or the department name I'm wondering STR Department well let's compare them here so I would like to look at the department table I can do that easily enough I'm just going to go point at its name over here and double click on it TBL department it has a field name Department Co here it is two characters now back in my relationships window the other table I was talking about in this case was Human Resources data with its STR Department I'm wondering is that the two letter abbreviation or is that the longer department name so Human Resources data table I'm going to double click on that one and I'm looking for STR Department Eydie hours pay a parking lot code Department code wait a minute wasn't it called Str department a little bit confusing here in TBL department I have a two-letter Department code and in Human Resources data I have a two-letter Department code and yet my relationships window said that should be called STR department somehow in my Human Resources data how can that be well let's see it's something we touched on for just a second I'm gonna go into my TBL Human Resources data but I'm gonna switch to its design view with the right-click so I can see a field named STR Department but as I look down here Department code is the caption so that's how I can have a field named STR Department and yet the field shows up with the name Department code and as I go to my data sheet view I can see in both cases it's that little two letter Department code so these are the same things here even though they have slightly different names that's a very long-winded way to say I want to go to my relationships window and I would like to join STR department in my human resource of data with STR Department code in the department table so that's what we're gonna do next so you can go check that all out if you want but I think you probably understand what's going on here what I would like to demonstrate right now is when I drag one field on to another it doesn't really matter whether I grab the primary key and drag it under the foreign key which is what I did last time or whether I grab the foreign key and drag it under the primary key which I did not do that last time so I'd like to prove to you that it doesn't matter which way you drag that when you have a one-to-many relationship so watch my screen I'm gonna grab STR Department the foreign key drag it on to STR Department code the primary key and when I open it up it still takes the parent table where the shared field is the primary key as TBL department and puts that on the left as the parent and then the human resource of data or the shared field was the foreign key puts that on the right it doesn't matter whether I drag primary on Dafoe or foreign unto primary it we'll work it out it'll put the parent table on the left and the child table on the right one-to-many relationship I click create here's my join line kind of going behind this window right your turn to try that so feel free to grab the foreign key best hair department in the human resource of data drop it onto the primary key and TBL department when I double click on the join line it shows me TBL Department the parent is on the Left human resources data table the child table is on the right so go join those two together everybody put your video on pause catch up with me come back and we'll link these last two personal data table with the Human Resources data table because they both have an STR employee ID but in this case they are both primary keys special case here so come and join me catch up with me so far so in the last session we saw that when we were joining a primary key to a foreign key it really didn't matter which one you grabbed first and dragged on to the other it always lined up the parent table on the left the department table where the shared field department code is the primary key and the child table on the right the human resource was data table in this case where the STR department is the foreign key now we come to a relationship where I have the STR employee-id shared field in the personal data table and we go join that with this shared field over here STR employee ID in the human resources data table and this is not going to be a one-to-many relationship these are both primary keys which means when I'm done it's going to create something called a one-to-one relationship meaning for every one record in the personal data table there is one and only one matching record in the human resources data table and vice versa now in the end what that means is either these tables has the potential shall we say of becoming the parent table so how do we decide in this case a one-to-one relationship primary key to primary key which one will be the parent table it's whichever one we grab first and drag on to the other one so whichever one we grab first that'll be the parent table on whatever we drag it on to that will be the child table and right now we're not experienced to know well so what what does it matter which one is the parent table well under certain circumstances the parent table has to have a record before the child table can have a matching record and so far that's all I'll say about that because any more than that I'll have to go into a big long-winded thing and I'm already doing that so here's the deal I want to join the personal data table to the human resources data table and in my case I would like to start with the personal data table becoming the parent and the human resources data table becoming the child so here's how I'm gonna do it I'm going to grab the primary key in the personal data table first I'm dragging that one on to the primary key of the human resources data table and when I let go notice it takes the one that I grabbed first personal data table puts that on the left as the parent and the one I dragged it on to even though it was also a primary key that human resource of data table has become the child on the right so please do that the same way I did it and notice down here it doesn't say it's a one-to-many relationship it says this is a one-to-one relationship so do what you just saw me do I'm going to click create to finish creating that and then here's my joint line so please do what I did grab the employee ID from the personal data table making it the parent and drag it onto the employee ID of the Human Resources data table making it the child and so that's the end of our discussion about using the dragging fields method to create a relationship between tables over our last few sessions we've seen two different ways to create relationships between tables the first one was start in the child table go to its design view and do a lookup wizard to join it with the parent table where the shared field is the primary key our second way was we went to the relationships window here that was under database tools and the relationships button to get into this window and then we dragged fields between tables and if it was a one-to-many relationship it didn't matter which way we dragged it when it was a one-to-one relationship whichever one we grabbed first that became the parent table and what we dragged it on to became the child table but there is a little bit of a difference in the long run between these two methods we never did a look-up list in this database so let me show you the difference now between using the dragging method of creating relationship versus the lookup list method so first thing I'd like to do is close all of these tabs up here I'm going to right-click on either one I'm going to choose close all it may ask me about saving the layout of different windows if it does I'll say yes please I worked hard at getting those relationships created so what I'm going to do next is I'm going to go to that Human Resources to add a table double clicking on it and so I've got the department code here if I had done a look-up list I would be able to click here under department code get a little pull down arrow where I could assign a new department that would be most useful when I'm creating a new record down here but we don't have a lookup list because we never created the lookup list through the lookup wizard we created all of our relationships by dragging in the relationships window now that does mean that I get the little plus signs here for any sub tables so if I'm in the parent table I get to see the child records from the sub tables but when I'm in the the child table of a relationship as I am over here then I don't get to look up list so personally I prefer the lookup list method of creating relationships rather than dragging things in the relationships window so what if I decided that I would like to make a lookup list for the department code well let's see I could go to my design view of this of this table I'm going to right click on it I'm going to switch to the design view and in the design view the field I'm talking about here is maybe the department field so I can click over-the-air and I can say hey give me that look up wizard but it's going to tell me sorry you can't change the data type or the field size of this field it's already in one or more relationships if you want to change the data type of the field or in this case we want to change the lookup part you'll have to delete its relationship in the relationships window so in this case I'm talking about the department field matched up between the Human Resources data table and the department table itself right now I don't have a look-up list there I would like to create one but they said in order to do that I got to go the relationships window and destroy the relationship between STR department and the department field in the department table so let's see how to do that I'm going to go to the database tools window that's where I can get to my relationships window I'm gonna click on the relationships window and in this case I am talking about this relationship from TBL department primary key parent table here to the foreign key STR Department in the human resources data table child table here so here's what I'm gonna do I'm gonna point at this joint line and double click on it tap tap here's the relationship so for the moment now I'm thinking maybe I could get rid of it by choosing cancel not true instead I'm gonna right-click now on that joint line and you notice when I right click I could edit it that's the same window but when I right-click I also get a choice to delete that relationship so come with me and let's do that put your video on pause come here to the relationships window let's look at the relationship between TBL department and Human Resources data joined by STR Department and I'd like you to right-click on that join line and then click the delete button then a check to make sure you're really wanted to do that you will say yes and that relationship has been broken so please join me and do that put our video on pause go to that relationships window right click on the relationship between TBL department and Human Resources data and then choose to delete it when it says are you sure you'll say yes so catch up with me there alright welcome back now that that relationship has been broken I can go to my child table Department to code being the foreign key in human resources data and do a lookup wizard from TBL Department so here I go I want to go into the human resources to add a table just so happens I have it open here so I'm going to go to the Human Resources data table by the way if you don't have it open already you can point at its name over here right click on it tell it you want to go to the design view of that human resource of data table so pause the video and join me there please alright trying to create our lookup list for our STR department in the human resources data table you may remember to do that we're going to click on the little pull down arrow over here next to STR department and we're going to go to our lookup wizard now in the lookup wizard the first choice here says I want my lookup field to get values from another table or query that's what we want we're gonna click Next we're in the table name Human Resources data we need to look up from the table named talking about department here from the table name TBL department so come join me get into that lookup wizard in the first step we said we wanted it to get our values from another table or query in the next step we have to tell it which table our query it will be the table name TBL department and let's click Next right hopefully you're catching up with me I've only got two fields over here I would like them both to be on my pulldown list I want to be able to see the two character Department code and I'd like to see the department name spelled out now I could click on one and then wedge it over and click on one and wedge it over but in fact there's the double wedgie here if I click the double wedgie it moves both of the fields over to the right hand side and I can move on by the way if you've ever had a double wedgie you know how painful those can be and I'm just gonna leave that alone here I'm gonna click Next will let it sort by the primary key next I'm not going to hide the key column I want to show it next actually store the department code choose a field that uniquely identifies the row that would be my primary key called STR department code next so now I'm looking up here at the top and it says what label would you like for your lookup field TST our department why don't we make it kind of look like English will just say de PT Department notice we are out of next so now so we'll change that label from STR Department to just the word department and then finally click finish so says alright you got to save the table before the relationship can be created you want to save it now we will say yes so apparently we just created a relationship that way and remember the advantage of this is we should get the lookup list at this time instead of just the plus signs in the parent table showing me the the child table related records this time when I'm in the child table which is where I am right now human resource a data table I should actually get the lookup list this time so I'm gonna switch to the datasheet view and in my Human Resources data table when I'm entering a new record down here I can click in the department code field and I do get the lookup list that wasn't there five minutes ago you may remember when we just had done the dragging to create the relationship we had plus signs in the department table but we had no lookup list in the Human Resources data table this time we get benefits on both sides we hit the little plus signs in the parent table and we get to look up lists in the child table so that's my more favorite way I'll call that best practice so if you'd like to try that hopefully you've been playing along with me you deleted the relationship and then now you're just maybe in the last step of recreating a relationship through the lookup list finish that off and then go down in your human resource of data table the child table where we have you know multiple RS Department codes so this is the foreign key if you go down here and start entering a new record this would be a nice way that you could look up from the parent table and fill in the the Department for the child table so pause that video and catch up with me there and then we will tackle a new thing together I'd like to explore a particular property of our lookup lifts that we created in the last session so for example when I click in the Department code field I see the lookup list I can choose from the lookup list and then I can choose another record or I can click on the pencil and it finishes saving that let's see I'd like to put that back to what it was before it's too late to tap the Escape key but I do have my undo choice may remember from our earlier module if you took launch of one you get wanna undo for your data entry so I just clicked on my one to undo and put it back to what it was before but here's a little loophole that's set up in this thing and I don't have a really good reason for why let's say that I dragged across the choice that's in here and I'm going to type a choice that is not on the list there is no xx Department and then when I go click on the pencil it seems to have no complaint about that so we have these terms a parent table and child table the parent table is the table where the shared field in the relationship is the primary key and then we've got the foreign key in the child table that's the many end of the one-to-many relationship so the table where the shared field is the primary key that's the one end of the one-to-many that's the one called the parent table and it has parent records in it and then that other table that you're joining up where the shared field is the foreign key that's the many end of the one-to-many relationship and that is the child table in that particular relationship so to carry that over into the next phase we have just created and this is the official name for it an orphan record it's a record that has no parent so I've got an X X Department code here but in my department table there ain't no X X Department so that was a little loophole here that I would like to close so first thing I'd like to do is undo it put it back to whatever it had been you know when it was actually one of the choices on the pulldown menu and I'd like to look at a property of that lookup list so so now that we've seen the problem and if you want to try that go ahead and put the video on pause and click in one of the one of the records that already exists and change it to ax ax and then click on the pencil you'll see so far it has no complaint about that so pause the video and try that out if you like right so either you have tried that or you haven't tried that so let's go maybe look at a cure for this so we're gonna go to the design view of this table and specifically I am talking about that department field in this case and noticing down here at the bottom we've got all these different properties and we're gonna talk about some more of those but what I'd like to draw your attention to right now is that there is this tab called the lookup tab so I would like you to click on that with me put your video on pause and come click on the lookup tab here in the design view of our human resources down at table make sure you're in the row about the department and then click the lookup tab and we're going to talk about a special property down here called limit to list right there and you notice that the default setting for a limit to list is no and that was why I was able to type something that wasn't on the list and it didn't give me any kind of error message there now I'm not quite sure why that's not automatically set to yes it seems like that would be a good default setting but they don't seem to ask me as to what I would like so all I can do is report the way it is but we can change that here we can say limit to list I notice a little pull down arrow here and so I can click the pull down arrow and I could choose yes instead of no or I could type in a yes it would not be enough to just type a why if you're gonna type it you got to type the whole word and if you use a little list arrow here you don't have to worry about typing the whole thing and then I'm gonna hit the enter key so I've just changed that property to limit to list is now a yes so take a moment and do that pause our video make sure you're talking about the department field up here go to the lookup tab in the property sheet and we'll change the limit to list from no to yes so do that all right welcome back now we're gonna go try it out we're gonna switch back to that datasheet view yep gotta save the change to the table you just changed the structure a little bit so we'll say yes I want to say that so now I'm gonna go in here and pick some row and try to change it to xxxx a moment ago it had no problems with that this time when I click on these save the record button it says no sorry the text you entered isn't an item in the list select an item from the list or enter text that matches one of the list items so basically what they're saying there is I could get away with typing say s s because that is on the list but gosh why would I work that hard when I could just use the pull down arrow and choose an SS that way but it will let me do that if I type a choice that is on the list and then click on some other record or click the Save button over here it allows me to do that I'm gonna undo it to put it back to whatever it had been before okay so you try that out hopefully you did that you went to the lookup tab on the design side of that department code field you changed the property four limit to lists from no to yes and now if you go up here and try to type in something that's not on the list and then tell it to save that record you should get that same error message that you saw me I'd like to look at another field here in our human resources data table it's the parking-lot code now you may remember from our relationships window that the parking lot code is joined to the STR parking lot code from the parking info table many and the child table here the one and the parent table over here but I would like to demonstrate something that's still kind of a loophole in here that can create some problems for you if nobody every warns you about it so I'm gonna go back to our Human Resources data table I want to talk about this parking lot code field now this relationship was created by dragging between the two fields in the relationships window therefore when I click in this column I don't get any lookup list so let me talk about a thing that can happen that can create some problems for you so I have this field as a foreign key that refers to a primary key in the parking info table so I'm gonna just double click for a moment in the TBL parking info table and I see that there are only three parking lots with three parking lot codes primary key over here BL MA and WI and then if I go back to my Human Resources data table in my parking lot code I have BL s and mas and Wis and I seem to have some people that don't have a parking lot code maybe they bike to work maybe they're new employees and they just haven't been assigned a parking lot code will maybe talk about that more in a little bit but in the meantime I'd like to go to the parking info table and I want to demonstrate a little problem that can arise here let's say that we're gonna change the parking lot code for Blossom Road from BL to BR so I'm gonna do that right here in my parking info table and it changed that from BL to B R and then I can either click the next record or hit the down arrow key or click the little pencil and you'll notice it has no complaints about that I just changed the parking lot code for Blossom Road parking lot from BL to BR but now if I go back to my Human Resources data table I don't have any be ours here I still got BLS that's gonna create a little problem later when I go to produce some kind of report everybody who's parked in the parking lot BR I won't find any because they still say BL over here so I have just broken the reference link between these two tables there is something that we could do that would prevent that from happening I'm hoping everybody sees the problem here if you'd like to try that out for yourself feel free to go to that parking info table change the BL to BR and then tell it to finish saving that either by clicking the next record or clicking a little pencil and you discover so far it has no complaints but in my Human Resources data table I now have a whole bunch of orphan records here be owls that have no parent be Alec because it's been changed to be R so there is something called enforcing the referential integrity between these two tables and so I'd like to show you how to do that now that we've seen what the problem is that I can change the parent record and it doesn't like automatically update the child records or anything like that that creates orphan records so let's see what we can do about that I'm going to go back to my other table mark parking info table first thing I'm going to do is put this back to be al now I do have my one undo here still so I'm gonna go up and click my undo change that to BL and now we're gonna turn on something called enforced referential integrity and this is a change to the relationship between the parking info table and the human resources data table and I'm gonna try to make that change here in the relationships window I say I'm going to try sounds like I'm giving you a little tip that maybe something is gonna go badly here and and in fact that's the case but you know we'll we'll deal with it as it comes here so I'm talking about this relationship in my Human Resources data window with the parking lot code from the parking lot table so I'm gonna go to the relationships window here's my Human Resources data table here's my parking info table there's the relationship line that joins the parking lot codes I'm going to right-click on this join line I'm going to tell it to edit that relationship parking lot code from the parking info table parent table child table here parking lot code foreign key in the Human Resources data table there's a little check box here that says enforce referential integrity one of these tables referring to the other one I'm going to check mark that one for my one-to-many relationship and then I'm going to click OK and I get an error message here and I'm doing this on purpose because it is definitely gonna happen to you let me magnify this through my third-party magnifying software the database engine could not lock the table TBL human resources data because it's already and used by another person or process now in real life it is conceivable that there could be another person working on that because access is normally a multi-user program but in this case it's just me there's nobody else using this thing so it's not complaining that another person is is working on this thing here's the problem I wish the error message was a little bit more specific the problem is that I cannot edit a relationship between two tables if at least one of those two tables is open I can create the relationship if I have the parking info and/or Human Resources data table open but I can't change the relationship once it's been created as long as either of those two tables is open so that's what the problem is here and I'm doing that to you on purpose so you can either you know make that happen do what you just saw me do you know see it see it do that same thing for you or you can just keep watching so put the video on pause if you like and try it out yourself otherwise let's keep going so again what I just did was turned on the enforced referential integrity and you should do that much so we right clicked on the join line between resources data parking info we tried to enforce referential integrity but we got an error message because at least one of those two tables is open so here's what we'll have to do everybody I'm gonna cancel here or you could click the close button and then we need to close that table cuz it's part of the relationship and that table so I'm gonna right click to close parking info I'm gonna right click to close Human Resources data and now I'm gonna try that thing I was trying just a moment ago I'm right-clicking on the join line I'm gonna edit that relationship I'm gonna turn on the enforced referential integrity I'm gonna click OK and this time it has no complaints in fact this time I get a couple little special symbols I get a one at the one end of my rennie one-to-many relationship and I get an infinity symbol at the many of my one-to-many relationship so once you've seen that happen a couple of times you'll realize that anytime you look in the relationships window and you see that it means the enforced referential integrity property has been turned on so go do that please right-click on that join line well first of all you'll need to close the human resource of data table you need to close the parking info table then come back here right click on the relationship line edit that relationship turn on the enforced referential integrity when you click OK this time you should get no error messages and you should get the two special symbols let me remind you of a problem we had in our last session before we had turned on the enforce referential integrity I was able to go to TBL department and change the primary key from BL to BR and it had no complaints even though I still had a whole bunch of BL people in my child table human resource of data so now we have attempted to cure that by enforcing the referential integrity let's see if we have been successful at that so I'm going to go back to my parking info table I'm going to double click to reopen the parking info table and I'm gonna try to change that BL to a br Here I am BL changing it to BR click on the pencil oh I'm getting an error message this time and as i zoom on Ana using my third-party software it says this record cannot be deleted or changed here in the parking info table because the table Human Resources data includes related records so I didn't get that error message last time I changed BL to BR but now it won't let me do that because I have child records that are be ELLs in the human resources data table well I'm going to click OK and then I'm going to tap the Escape key and it puts it back to BL so I'd like you to try that pause our video go to the parking info table try to change that BL to a br as we have done let's say five minutes ago and last time it didn't have any complaints this time it should have a complaint so pause the video make it complain like that and then click OK and let it go back to the BL alright so maybe what I'm thinking now is well it was complaining about the fact that Human Resources data has a bunch of be else so now I'm thinking maybe I could go to the Human Resources data table change the BLS to be ours there and then I could come back here and change it to BR well it turns out it's not gonna work out that way but let's prove that to ourselves so I am now going to go over to that child table Human Resources data table where I have a bunch of B towels over here in my parking info and so I'm gonna try to change this BL to a br maybe that'll satisfy it so here in the child table and changing the BL to a br I'm going to click on the finish saving that record button oh man I get an error message and this time it's complaining about the other direction you cannot add or change your record because a related record would be required in TBL parking info when I train change the parent table from BR to BL said no no yeah you've got children in the child table so I'm basically protecting its protecting me from myself if you want to say it that way to not accidentally create orphan records so I'm gonna click on OK I understand so you let me do that I'm going to tap the Escape key to put it back to the BL when I'm figuring all right I get it turns out there is one little loophole here I can go and just delete one of the pieces of data and then when I click to save that it actually has no complaints so they don't have an official term for that like an orphan record I'm gonna maybe make up a term I just created like a stepchild is waiting for a parent or something like that so in the No Child Left Behind rule there is this one break in there there is this one method that I can do to delete the the shared field and maybe I'm waiting to fill it in later so for our purposes I'm going to undo that I'm gonna put the BL back in there I just wanted to demonstrate that that is the one little loophole in our No Child Left Behind rule I can have blank records where somebody is not assigned a parking-lot code and maybe it's because they don't park there anymore or they're a new employee and they haven't been assigned one yet or whatever the case may be so that's the little loophole in the No Child Left Behind rule created with the enforced referential integrity now there are a couple of other check boxes in that dialog window about enforcing referential integrity so I'd like to go explore those for just a moment so we've seen what the enforce referential integrity does now I'd like to take it one step further so we'll do that in our next session now I would like to reexpress relationship here for a moment I'm gonna right-click on it on the relationship line here I'm gonna edit the relationship and I would like to talk about this checkbox right here now right now it's not turned on cascade update related fields by the way if I turn off the enforce referential integrity these two checkboxes become unavailable can you see how they are suddenly grayed out so I can't even get to that second checkbox until the first one is turned on and now I'd like to check mark the second one here that says cascade update related fields come do that with me hmm I'm just noticing something I'm trying to edit this relationship between two tables and they're open if I click okay I'm gonna get that error message about we can't lock the table so you know the drill we're gonna have to close these two tables and then come back and we'll edit that relationship so I'm closing the Human Resources data table closing the parking info table now I'm going to right click now I'm going to edit the relationship now I'm going to cascade the update related fields and I'm going to click OK so please do that close those two tables they're open in the background here Human Resources and parking info and I'll come right click on this relationship line edit it and turn on the cascade update related fields please so pause the video do that much and then we're gonna go see what we have accomplished with all of that all right welcome back so this time we're going to go to the parent table the parking info table so let us reopen that the parking info table I am double clicking on it over here at the left in my navigation pane I'm gonna go and change the parking lot code from BL to B are now the first time we tried this before we had done any enforced referential integrity and allowed me to create orphaned records with the referential integrity being enforced now it won't let me create orphan records it wouldn't let me change the BL to be our five minutes ago let's see if I can do it now so I'm changing the BL to a br here in the parent table I am clicking to finish saving that hmm it has no complaints this time so I'm thinking back about the name of that check box that said cascade update related fields you may be way ahead of me here as to what just happened I'm gonna go back to the child table here that was the human resources data table and I'm gonna go look in the parking field parking lot code who I've got a br here I got another BR down here so changing the BR in the parent got cascaded down into the child and so everything that used to be a BL is now a br so that's a combination of enforce referential integrity and the second check box that wasn't available until we turned on their first referential integrity that says if you change the parent record it'll change the child records I wonder if it'll work the other way what if I take this BR and try to change it to a BL here in the child table I'm clicking on the finished saving button no I still can't do that because I don't have a related record in the parent table but if I change the parent record it changes the child records automatically I just can't change a child record and have it change the parent record automatically so I'm going to tap the Escape key so if we'd like to try that now that you've got the Cascade update related fields in there if you haven't already go to the parking info table change that parking lot code and have it cascade that over into the parking lot code of the human resource with data table and then if you like you can try wait just saw me do try to change the child record and you'll see you still get an error message there all right so please feel free to put the video on pause and try any of that all right welcome back there was one more checkbox in there so I want to go back and revisit that it will be editing the relationship between parking and human resource of data might as well just close them now I know I'll have a problem if I don't so I'm closing those two tables I'm gonna go back one more time and edit this relationship window and this time there's no checkbox down here and as i zoom in on it it says cascade delete related records now it sounds a little dangerous and in fact it is the thing that you're thinking it's gonna do is in fact exactly what its gonna do that is if I delete the parent record it will go find all the child records in the child table and delete them there's a little sugar going up my spine right now cuz there ain't no undo for this so this is a little bit dangerous and in fact if what I wanted to do is actually do that delete some records there's a better way to do it with with a special kind of query that we'll talk about a little bit later in another module so I'm gonna click on OK having turned on that cascade delete related records why don't you go try that with me put the video on pause let's edit this relationship remember it had to close the two tables will cascade delete related records and okay back so do that much and then come on back all right here we go we're gonna go see this in action now so I'm gonna go to the parent table in this relationship the parking info table the one end of the one-to-many so I'm going to go back to my parking info table all right I'm gonna go delete this be our record koukin Anna I'm tapping the Delete key right now says a relationship that specifies cascading deletions is about to cause one record in this table along with related records in a related table to be deleted are you sure you want to do this I refer to this as the last chance for gas before the desert warning because again there's no undo for this but I'm gonna go through with it just to demonstrate I'm gonna say yep I wanted to delete those records so I've deleted the BR out of the parent table I'm clicking yes now I'm gonna go to that child table human resource of data I'm looking for B ours or B owls I have neither of them I actually have fewer records in there I'm down to 18 records I didn't really tell you how many I was starting with so it's kind of hard to compare that but we can definitely see that there are no be ours or be owls in there we deleted the parent and therefore it went and deleted the children so go do that with me you'll see that happen just be aware that there's no undo for up my undo button is grayed out if I try to do ctrl Z it silently laughs at me so go try that out remember you can only edit that relationship if all of the tables involved in it are closed and I right clicked on it and I turned on the check box for cascade update deleted records and then we went and deleted a record in the parent table and we have seen that it deletes the records in the child table our last couple of discussions in this module have to do with some other properties that you can set up when you are in the design view of your tables so why don't we close all of the windows that we have open right here all of these little tabs quick way to do it right-click on any of the tabs and choose close off and if it needs to ask me about saving changes to layouts of things it will ask so do I want to save the change to the layout of the relationships window yes please and apparently that's the only design thing that it had any complaints about so do the way you just saw me do right-click and close all all right so let's go to the personal data table we haven't really looked at it very much here so I would like to double click on it to open it and so these are my employees and I can see that most of them seem to be from either New Jersey or New York trying to do my Jersey and New York accents there um so I could set it up so that when I'm entering a new record that they are automatically entered as being from New York and I only have to type something in there if they are from someplace other than New York maybe they could actually be from someplace other than Joisey or New York so what I want to do is set up a property of the state field that says the default value is New York unless we specify otherwise then I've got another property I want to talk about for the hire date now as I look through here I've got some people who have worked for me for a long time but I would like to set it up so that when I'm entering a new record for a new employee that I cannot accidentally put in a hire date that's later than today so these are really going to be a couple of things that we're going to control through some properties of this table in the design view so let's head to the design view of the personal data table please a quick way to get there would be to just right-click on its name and go right to the design view I didn't do that because I wanted to show you the data that's in there so far so I can do that now I can switch to the design view or I could right-click here on the tab and go to the design view or the very first way we saw it was to go up here and click on the design view button so whichever way you want to do it somehow get to that design view so here we are in the design view and the first one I want to talk about is that state field so I'm gonna click anywhere in the row for the state I'm noticing we don't have any descriptions in here telling our data entry people what they're supposed to do we're not gonna make you stop and do that now but it would be a good idea when you're setting up your own database to fill in that kind of information remember how it appears down here in the status bar when they're doing data entry so for our purposes in my state field I said I wanted to set it up so that it would say New York for new records and that way my dad Ann tree person could just over that and go to the next field as long as the new employee is from New York I'm seeing the caption down here that says state you may remember from our earlier discussion in this module the field name is STR state and that spelled that way mostly for structured query language programmers and yet for my data entry people I just want the column heading not to say STR statement just state remember how the caption overrides the field name well it's time I want to talk about default value for the state field and I'm gonna type it in right over here I'm clicking in the box I'm noticing over here way at the far right it's trying to tell me about that a value that would be automatically entered in this field for new items notice it says that this is not gonna retrofit all of my records that are already in there and make everybody suddenly from New York this is only for brand new records when we're doing data entry so my default value I'm gonna fill that in as n live for New York and then I'm gonna hit the enter key and you might notice that it suddenly put quotation marks around that now I could type it with the quote marks but the good news is I don't have to type them it'll put them in there for me so I'd like you to try that let's go to the design view of the personal data table and for the state field will put the default value in there for New York put the B on pause and join me all right welcome back while we're here let's go take care of that higher date thing so we're gonna go down here to the field name date time higher date so click anywhere in that row so that we're now looking at properties of DTM higher date field and I'm seeing a couple of things down here one of them says validation rule the other one says validation text so a rule is usually some kind of mathematical formula and validation text will be text that appears if they have somehow broken the rule and then somehow we have to have some way to let them know what the rule is that they may be breaking so there's gonna kind of be a three part thing so first of all for the higher date we're gonna give a little note to our data entry people by filling in the description for the higher date field we're gonna say enter a date no later than today that doesn't mean they need to do the entry no later to then today it means what they enter needs to have a date that is no later than today so that tells them what they're supposed to be doing it doesn't actually enforce that rule right now they could still do it even though we've told them not to so now we're gonna go down to the property for that field to our higher date called validation rule we're gonna click to put our cursor right in there and I see a little dot dot dot these are called ellipses and as I look at the explanation over here an expression that limits the values that can be entered in the field and if you want to see more information you could tap f1 to see a little bit of help stuff on validation rules I'm not necessarily going to tap the f1 right now but just be aware that that's there to try to find more information about it so my validation rule is going to say this date has to be less than or equal to and then I need the name of a function that would normally put in today's date now Microsoft Excel has one called today Microsoft Access has one called now so I want to have a validation rule that says the date that I'm about to type in is going to be less than or equal to now I'm typing in now and it's trying to give me a little bit of help here says this would return a variant of a date type specifying the current date and time and one of the odd things about this is that it has to have parentheses and so I had kind of zoomed in on it and then zoomed out on it and all sudden it looks like it says equals now zero that's not actually true it now says less than or equal to now open parentheses close parentheses that is shift nine and shift 0 and that probably won't happen to you right away so you'll go with less than or equal to now and then you probably have to type in the opening and closing parentheses there and by the way any of you who have ever used the equals today property in Excel it's the same way equals today open parentheses close parentheses no arguments inside the parentheses but the parentheses have to be there all right and then I want to have a validation text that will only appear if they didn't do what I told them to do up here enter a date that was no later than today so as soon as they click in that field if they look down at the bottom of their screen in the status bar they should see this instruction and then they're entering the date and then it applies this rule and if they haven't followed the rule then we're gonna have this validation text pop up in a little message box that's gonna say bad data please re-enter a date no later than today then I'm gonna add one more thing that I would never be able to get away with in real life I told them what they were supposed to do here they didn't do it so they're getting this error message bad data please reenter a date no later than today and I'm gonna add you dummkopf so again I would never be able to get away with that in real life this is my one chance to insult my data entry operator probably not a great idea to insult your data entry operator but I'm just trying to build in a little teeny bit of fun here into our lesson so you don't have to insult them if you don't want to but feel free to put in this validation text remember the validation text will only appear if they have broken the rule that you have turned on here all right put the video on pause and do all those things put in the message they should see before they type put in the validation rule put in the validation text all right time to go check these two things out remember we change two things we did a thing about the hire date then we did a thing about the state being automatically set to New York unless we override it for a new record all right here we go time to go try it out I'm gonna switch to the datasheet view it's gonna say you've changed the structure of the table do you want to save that we're definitely going to say yes data integrity rules have changed there may be existing data that's already breaking the rule that is I may have a higher date that's already later than today and it says it's gonna go check that data out do I want it to check the existing data - to test against our new rules we're gonna say yes it also says this process may take a long time it's not gonna take a long time this is a really small database so I'm gonna click on yes do your worst go ahead and test all of that and it seems to have no complaints about somebody being filled in with a higher date that's later than today so pause your video and come catch up with me there alright so I'm gonna scroll down to the place where a new record could be inserted and notice it already says New York right there so that's my my automatic setup my default value for the state field was set for New York and then I'm gonna scroll over here to the date and when I click in the date part I get one of those little date picker little mini calendars and so here's my today button and I'm gonna make a mistake and try to click on they were hired three days from now so I click on that and everything seems to be hunky-dory until I tell it that I want to try to save the record and I'm getting that error message bad data please reenter a date no later than today you dummkopf right well I'm gonna click okay having walked away stunned and insulted then we're gonna try a date that is not later than today like maybe they were Heidi yesterday where were you born yesterday rehired yesterday and now when I click on my pencil oh you have to enter a value in the employee ID field what's a complaint about here you must enter a value in TBL personal STR employee ID field it's complaining that my primary key has not been filled in my employee ID field you may remember any of you who took the module 1 if you have a primary key in a table you cannot leave that primary key field blank so that's what it's complaining about there alright and in this case I'm not really ready to go forward with it but we have seen the little thing about having the default value filled in and and entering a new record and having the the error message pop up if we broke the rule so it said we were supposed to enter a date no later than today and I tried to do that and it slapped my hand all right in my case now I'm gonna tap the Escape key to stop trying to enter that record at all so feel free to play with that or you can just say hey I saw how that works and I don't really need to test it out for myself that's totally up to you so thanks for hanging out and talking about some of these extra field properties and also the things about cascading related records and all that kind of stuff in our relationships so that's module 2 everybody talking about creating relationships we created relationships through lookup lists we created relationships by dragging from one field to another in the relationships window and then we also went into the relationships window and modified some of those relationships we also saw that you can't modify a relationship between two tables if at least one of those two tables is open so we saw some of the pitfalls and we saw ways to get around them in our module two all about creating relationships when you get a chance come on back and join us for module 3 you are will start talking about using those relationships to create queries grace allow you to ask questions of your database and queries can also allow you to put things together for more than one table as long as those tables are related so come on back and tackle module 3 when you have time to try it out for the moment this is Dan McAllister signing off out of module 2 all about relationships thanks for watching don't forget we also offer live classes and office applications professional development and private training visit learning comm for more details please remember to like and subscribe and let us know your thoughts in the comments thank you for choosing learn it [Music] you

Channel: Learnit Training

Views: 151,520

Rating: 4.9294653 out of 5

Keywords: free access 2016 tutorial, access 2016 expert tutorial, Access 2016, microsoft access 2016, learn access 2016, access 2016 tips and tricks, learn access, access tutorial, access training, tips and tricks access 2016, access 2016 how to, free access 2016 training, access 2016 free tutorial, access 2016 tutorial free, free, how to use access 2016, e learning access 2016 video, Access Beginner

Id: 1XTXv1V3-ag

Length: 227min 45sec (13665 seconds)

Published: Fri Jan 31 2020