Access making a table from scratch in design view - access tutorial for beginners 2010 2013 2016

okay in this video I'm going to show you uh a way to make a table from scratch and uh here we're going to use access to make the table and what I would highly recommend to you is that you write down your Fields before you make your table maybe you can put them in Excel spreadsheet or put them in a Word document or just write them down and you can see on the screen uh here's what I mean we we would actually write down the fields this does a couple things for you make sure you don't forget anything for first of all then uh it makes the creating of the table that much easier because you can just go from the list and then it also helps with your documentation so I would really highly recommend that you would write down your Fields before you uh make your table here we're going to make a table that's going to be a customer list and I have some sample Fields like first name and last name and uh different fields like that so now I'm going to minimize my field list here and we're going to go into Microsoft Access ACC in the previous versions of access uh over here this is called the database window now it's called the navigation Pane and that's where all of your objects for that database will be listed this is a brand new database so we don't have any objects yet but when you do have objects they're going to be listed over here uh in either the database window or what we call the navigation pane now in the previous versions you would have a tables Tab and then right about here on the screen it would say create table um you know by in design view in this version to make anything you're going to pick on the create menu or the create tab up here and then notice there's a whole section that's called tables uh and also under the create tab we can make forms and reports and and queries we'll find uh other videos for that in this series so let's make a table uh in table design view and now once we're in design view this is going to look very similar to uh the way it looked in previous versions so notice how you have three columns you have the field name the data type and the description the field name is the name of the field like first name last name whatever you might have there then the data type we're going to click on that pull down there's actually another video in the series that describes the different data types all right so uh when you're writing down your field list I would recommend that you would also include the data type and I'll give you some examples of those the description field is optional however if you use that it does have a nice purpose whatever you put there is going to wind up down here on the status bar when the user does data entry into that screen so it's a great place to put uh instructions for that field or any kind of specifications let me uh let me uh repeat that if you put something in the description field whatever you put there will wind up down here on the status bar when the user does data entry into that field so I would use that for field instructions or specifications let's build our table so I'm going to put in first name if I look back at my field list that I showed you I have a field that's called uh first name as we can see now you can put a space or not uh if you if you want to separate your words you can either separate them by a space or an an underscore you might want to use an underscore rather than a space because sometimes in other databases they may not like a space in your field name but axis doesn't really care if you put a space in the field name or not now what about Case Case actually doesn't matter in other words I couldn't have another field that was called first name even if that was all upper case so what I would say about case is be consistent if you made the the first letter of that field um Capital make all of your Fields like that just be consistent within your own database otherwise case actually doesn't matter then you're going to tab over and you're going to pick on the data type so like I said in another video uh it describes the data types for you so I'm going to pick on text text is just a normal field and it can be up to 256 characters long and it can contain numbers and letters and special characters so what what you would probably want to do is uh down here you have your Field properties and uh You' probably want to change that to a different field size in other words a first name doesn't have to be 255 characters long so let's make that uh maybe 20 now that that could be an important setting for you because it's going to allocate that much space for each record that you have in your table now if you if you only have a small table it's not going to make a difference but if you have a very large table may maybe over 10,000 records then if you allocate too much space for that field it's going to waste a lot of hard uh a lot of space in your hard drive so the field size can be important uh and then uh in other exercises we'll talk about some of these other properties now if we come over here to description again that's optional but let's say I'm going to type in something like uh this is the first name and then you're going to see where that lands a few minutes when we're in uh when we're doing data entry into our table so then I'm going to go to my next field and we'll call this one last name and again uh I'm getting that from my field list that I that I showed you earlier so yeah you want to keep on referencing that of course this one is also going to be a text field we'll have other data types uh shortly within this table let's hit the last name we're going to make that 30 characters long cuz sometimes the last name gets a little bit bigger and then you don't really need to put a description on each field uh in fact in this table I'm not going to do that but you could if you wanted to It's just in the interest of time I'm not going to put the description on each field uh so then next we'll have an address field and the address is also going to be a text now the address of course gets a little bit longer so maybe we'll make that 60 characters long if you wanted a second line of address then you would have a second address field here so maybe we'll put in address to again some people would need that and some people don't need that but uh you know you would want to put that as a separate field if you want to include that it will make that one also 60 characters long so you can see it's really not that difficult to make your table and if you have your field list like I showed you before then that scream becomes fairly easy uh next we're going to have a City Field uh I like good uh simple field names like City and address you know but if your company has naming standards for your field names make sure you follow that standard otherwise I just like nice simple field names uh the maybe the city might be 30 characters long uh next we'll have a State field now the State field is also going to be text but maybe it's going to be just a two character state so we make the field size two what that's going to do is make sure that the user can't type in more than two characters that's what the field that's one thing that the field size does uh then we'll have a zip code now sometimes the ZIP code uh starts with a zero or sometimes in in different countries there's letters and there's zip codes so I would actually make that a text field if you leave that as a number field even though all of the the zip codes United States are numeric if it starts with a zero it would actually chop off the leading zero so that's why you actually want to leave the ZIP code into as a text field so let's say we're going to have uh 10 here nine for the number and one for the dash maybe maybe 10 years ago you would just have five characters for your ZIP code now we'll have 10 one for the number I mean one for the dash and nine for the number uh so let's actually put a phone number into our table now and I'm going it called phone uncore number now a phone number would probably also be a text field because sometimes a phone number has dashes or parentheses or sometimes even a phone number still has letters in there so anytime a field has any non-numeric characters then you have to make that a text field uh later on we'll change something else about the phone number now let's go back into uh that field list and see what's next after the phone number uh so we have a fax I'm actually going to add the fax in the next example uh then we have an email address so let's go back into access and then we'll have an email and we can just leave that as a text field then uh let's say we want to start date in other words when did the customer start doing business with your company if it's going to be a date time then you really want to make that a date and time data type so you're going to tab over to your data type and click on the pull down and then you see your different data types we're going to make that a date and time field there's reasons for that and it has some really nice benefits which we'll talk about in uh other videos now let's say you're going to track if we go back to our field list here after oh we want to see if they're active or not so I skipped that field but that's okay we can put it right there so I'm going to come over to the access table and we'll call the field active now the customer is either active or not so we're going to make that a yes no field yes no's actually come up quite a bit but that would be like for an on or off or a yes or no or a true false type of situation uh then let's have a if we look back at our field list then we have an account balance and an account history so in this case I'm going to go back and um go back into the table maybe instead of account balance we're going to say account limit you know what is their limit that they can spend maybe they have a credit with your company so that would that's going to be a currency field you know we're talking about dollars and cents there if it was just a regular number then I would make it a number but since that is a dollar figure we're going to talk about currency and then we're also going to type in uh notes let's say every time I speak to that customer I want to keep track of what we've talked about so this field might get very very long and for that reason I would make it into a memo field so that kind of gives you a a a useful or we used a lot of the the different data types um so we used a text a memo a number quite frankly is very similar to a currency we use the date and time we use the currency later on we're going to use an auto number field a yes or no we did use the Olay object I really wouldn't use in newer tables uh that's going to be where you include a whole file into your into your record I think a better way is to make a hyperlink a hyperlink would just be the file's name then when you click on that it'll open up that file uh in the other program or maybe the hyperlink is going to be an email address or a website that could be a clickable field now here's a brand new data type it's called attachment so what that means is when you send this database out uh to somebody else maybe outside of your company then you can include the file as an attachment and then they can still get to that file uh so it would be two separate files you would have your database file and then whatever you attached and then later on there'll be another video about the Lookup Wizard so now you see we have our our uh T our fields in our table now one table can have up to 256 Fields per table now you should never really have that many even a table that has over 100 Fields probably has too many fields but you could go up to 256 if you wanted to but you could have as as little as one field in a table but it could go up to 256 we can always add or change Fields later on there'll be another video about that so I'm going to close this window and it's going to ask us to save that we're going to pick on yes and I'm going to give the table a name I'm going to call this one uh this is clients and we're going to click on okay now you see a new screen here it says there is no primary key defined so a new term has come up now a primary key a primary key is going to be a unique value uh in that field for every record in the table maybe it might be a social security number maybe it might be a part number okay so think about a field back at work or a piece of information that's different for each customer or for each order maybe that's the part number the customer number the order number things like that that would be the primary key now uh another reason uh well one reason that you have the primary key is so you can look up that record and get to that exact record let's say for example that you have a customer table and you have two customers that are named John Smith well if you try to look up John Smith you you know it doesn't it may not know which record that you want to go to however if if John Smith number one let's say his customer ID is 123 and John Smith number two let's say his customer ID is uh 677 then just by typing in the customer number that I can go to the exact John Smith that I wanted to so that's the primary key it's going to be unique for each record now here it's telling us although a primary key isn't required it's highly recommended a table must have a primary key for you to define a relationship between this table and other table so that's another reason that you have a primary key we're going to talk about relationships in a different uh in a different video do you want to create a primary key now in this case we do we're going to pick on yes and then we'll see more about that uh in the upcoming videos but now that gave you an idea of how to make a table from scratch and hopefully you saw how we would uh add their fields in there