Microsoft Access 2013 Tutorial Level 1 Part 05 of 12 - Customer Table, Part 2

welcome to Microsoft Access 2013 beginner level one brought to you by access learning zone.com here on YouTube this video is lesson 5 of 12 plus an introduction if this is the first video you're watching in the series click on the link shown to start this course in the beginning otherwise we'll start lesson 5 right now in lesson 5 we're continuing to work with the customer table ok so we've seen how to add text fields let's add some non text fields to our table I'm going to scroll down just a little bit you can add a whole bunch of different fields to a single table I would say that most tables that I build personally end up having between 10 and 30 fields or so you can have a lot more if you want but if you find yourself starting to have more than 100 fields in a table you probably are running into a situation where some of that data should be in a second table now we talked about multiple tables working together in the Access expert classes yes there is an upper limit to the number of fields you can have in a table but realistically in a real world database you shouldn't ever hit it let's put an email address just emails fine I'm going to make this a hyperlink field now you can either drop this box down or you can just start typing in hyperlink H and there it is hyperlink tab tab this way I can put their email address in and then when I click on it in my database it'll launch my email program and I can send them an email right from there similarly website make that a hyperlink okay how about the person's phone number or just phone now you'd think you might want a number field for this because it's usually a number but I'm going to go with short text same thing with postal code up here in the United States the postal code or the zip code is simply a five digit number why wouldn't I use a number field instead of a text field there are several things to take into consideration when trying to decide whether to use a text or a number field if you ever need to perform calculations on the field use a number otherwise use a text field if you ever need to add two of them together for example or find the sum or average then use a number do you ever need to add together a group of zip codes now probably not do you ever need to find the average of a phone number list nope use text might you want to find a customer with the greatest number of children yeah you might so for that use a number field another factor to consider the sort order numbers are sorted like 1 2 3 4 5 and so on 10 11 and 12 come after 9 texts on the other hand sorts by the character so 1 is followed by 1 0 is followed by 1 1 then 1 2 then 2 and so on so text is sorted alpha numerically whereas numbers are sorted numerically does the field possibly contain a leading 0 social security numbers for example the word number is right in the field name social security number that's a number for those of you who are outside the u.s. that we use to track individuals sometimes they can start with a 0 so forgetting about the dashes because you can store numbers and then format them later with dashes but if you just want to store the characters you might have a leading 0 to consider a zip code for example can start with a 0 so that's why you want to use a text field number fields will drop those leading zeros so 0 2 3 4 1 becomes just 2341 that's another consideration for when you'd want to use a text field another thing to consider is the value always a number or could there possibly be text if you get a phone number like 1-800 druidia you have to find a phone and then say ok d is this or is that and put those numbers into your table otherwise just let the user type in the text now obviously if you have some kind of an automated system like fax numbers and the database has to be able to send that directly to your fax software then yeah you might want to force only numbers but generally if it's just calling them from time to time text is fine so once again with this new information at hand I'm going to stick with short text for my phone number field next I'd like to keep track of how many employees each of my customers has now I'm building my database to be company centric in other words each customer is a company let's say PC resale is primarily a business-to-business organization so I want to know how many employees this company has so I'm going to put in num employees now you can put in number of employees or just employees I find that num employees is short enough so that it's not a really long name like number of employees and if I just put in employees that might not tell me enough information so num employees for me at least is good enough to say that's the number of employees for this company you can type it in however you want again just try to maintain some consistency this is going to be a number field because I may want to run a report later that says show me a list of all of my customers with more than 50 employees I couldn't do that with a text field or let's say I want to sort my list of customers based on the number of employees from high to low you can't do that with a text field so I'll go number now when it comes to number fields you can specify what kind of number you want down here under field size you'll see it says long integer let's click in here then notice another drop-down box appears click that drop-down box and you'll see all the different types of numbers that are available to you the default type is long integer counting numbers basically 0 1 2 3 and their negatives negative 1 negative and so on now sometimes you want to add decimal places to your number values long integers can't handle decimal places for example you might want to track the customers discount rate maybe each customer has their own different discount so that'll be a number field but down here you can't use long integer a long integer won't let you store a value like five point five percent now there are a bunch of different types of numbers in here this byte integer single double replication ID decimal for today all I want you to worry about is long integer for counting type numbers and double for any numbers that require a decimal value so for our discount rate we're going to store that as double so I can put a value like 5.5 in there I will explain all the differences and the intricacies between these and when you want to use the different types of numbers in future classes there are some pretty strange differences decimal for example seems like it's the one you want to use but trust me for now use double next I'd like to track how long each customer has been with me so I can send a three-year discount coupon for example so I'm going to put customer cents in the next field of customer cents this is going to be a date/time field just press d for date/time field remember a date/time field can store a date a time or both let's keep track of each customer's credit limit that will be a currency value currency is a special type of number value that is optimized for dealing with dollars and cents or whatever your local currency happens to be how about that value that I briefly mentioned earlier is active this will be a yes/no value again does that mean the customer likes to exercise no that means the customer is on our mailing list for example let's add in a notes field this will be a long text field formerly memo it's good to have notes fields in most of your tables they don't use a lot of space if you don't put data in them so for most things like customers or products or vendors or orders even I generally tend to have a notes field in there somewhere where you can type in extra notes about that thing now we're going to skip some of these types for now like Oh Ellie object for example that's good if you want to put let's say a customer's picture in the database that way you can print it on his nametag or you can use an attachment field to store his resume as a word document right in the employees record we'll talk about calculated fields and lookup Wizards in future classes but there is still one bit of data that we're missing we still don't have a way to uniquely identify each customer and that is what the auto number field is for auto number remember is a number that access keeps track of it will start at one and automatically increment them for each record that you add to the table you don't have to worry about it it happens in the background so let's create an auto number field and I'm going to call this my customer ID customer identifier almost every table that I design is going to have an ID field in it there are very few exceptions Auto numbers are very important when it comes to relating information from one table to another for example each customer might have multiple orders how do you know which customer each order belongs to you're going to put the customer ID in the order record now we're not going to cover relationships until expert series later on but just keep that in mind for now we need that ID field to uniquely identify each record in the table the other fields in here aren't really good at identifying customers I could have a hundred John's and even six John Smith's phone numbers can change from time to time even values like social secur renumber aren't good so when it comes to setting up relationships in your database trust me and stick with auto numbers you can have other fields if you want to let's say you've got an old paper system or an old system you set up in Excel and you've assigned IDs to your customers that's fine you can add that as a second field for your own purposes but for the database for access to keep track of those unique customers set up an auto number field auto numbers never change and access maintains the list for you auto numbers never get reused so if you delete customer for you never have to worry about another customer for appearing and then accidentally assuming customer four is orders that could never happen your users can't edit auto numbers they can't be changed so it's the perfect field to keep track of unique customers with now you don't ever have to even worry about these auto numbers if you want to see them on your forms and reports if you want to print a customer ID you can that's fine but you don't have to you can leave them completely in the background properly build each table in your database will have its own ID field so the customer table has customer ID the order table will have an order ID the product table will have a product ID and so on there are very few exceptions to this rule most tables that I build will get an ID field now as a matter of personal preference I like to keep the ID fields up at the top of the list of fields usually when I start building a table the first thing that I do is put the auto number in but I showed it to you this way so I can teach you how to move fields around I'm tricky like that so here's what I'm going to do I'm going to click on this little gray box over here on the left hand side of the field that selects or highlights the entire row now let your mouse go so it's not clicked any more then with the arrow here click and drag to move this field up to the top of the list way up the top right above first name and then let it go that's all that's how you can move these fields around if you decide you want email up top just click on it and drag it up where you want it like right on top of first name I'll put it back where it was click and then drag down here right there you can move these fields in any order that you want and it really doesn't matter to access what order these fields are in I just like having the IDS up top okay so now we're done building our table for now let's save this table to the database now nothing we've done yet is saved so if we were to somehow lose the power all of our work is gone so let's hit the Save button right up here any quick access toolbar notice the tooltip that pops up says you can also press ctrl s on your keyboard get in the habit of doing that that also works in Word and in Excel hit ctrl s whenever you think about it to save your document ya word in Excel have that autorecover feature where it saves automatically for you but I still like to get in the habit of hitting ctrl s it goes back to the days before autorecover so I'll hit save you're asked for a table name now table 1 is an awful name it doesn't tell you anything about the data in the table so I'm going to call mine customer T with a capital T on the end again no spaces I like to end all of my table names in T my queries with a queue my forms with an F my reports with an R again this is just a personal naming convention and I've been doing it this way for many many years now going back to the early versions of access when you build a report or a form access lets you base that former report on a table or query where are you getting the data from well the older versions of access didn't use to tell you whether it was a table or a query we just show you one big long list that had all the tables and queries together in it so if you had a customer table and a customer query you didn't know which one was which she had to guess so I started putting customer T putting T's and all the end of my tables and queues and all the end of my queries you may also see this in some books TBL customer same thing it's that Hungarian notation again table customer just tells you it's the customer table personally I prefer right this way you can do it whatever way you want here's a hint though if you're planning on watching a lot of my courses stick with my naming convention otherwise you might get confused later on also I tend to keep my customer table names singular so that later on if I'm writing a macro we're doing some VB programming I don't have to think to myself always a customer table or customers table so I try to avoid plural table names again just another tip and I'm mentioning this stuff now when to get you in the habit of doing it because changing all your table names later is a bit of a pain so I've got customer T in the save as box I'm going to hit OK and now I get this warning message it says there is no primary key defined what does that mean it says although a primary key isn't required it's highly recommended a table must have a primary key for you to define relationships between this table and other tables in the database do you want to create a primary key now essentially the primary key is that one unique value that each table has that lets you uniquely identify each record now we put that in there right we made that ID field that auto number but we didn't tell access that it was the primary key if you look there's a button right up here on the ribbon that says primary key I almost never remember to click that button before I save the table so access yells at me and it says hey you forgot to define your primary key do you want to create the primary key now just say yes now what happens is access looks at your table and it says does he have an auto number if so access makes that the primary key for you see little key symbol right there next to customer ID access took care of us if you didn't have an auto number already in your table access adds one for you and just calls it ID in any case I showed it to you this way because that always happens to me I build a table I hit save and then I get the error message oh there's no primary key to find do you want to make one now yeah sure you can make it yourself by simply clicking on the primary key button but again I almost never do that now the primary key forces this value to never have duplicates and it sets up something called indexing which speeds up searches and sorts and we'll talk a lot more about indexing in future lessons you can index other fields too like first name or last name that way if you run a lot of reports for example customer lists sorted by last name if you index the last name it makes that report run much much faster indexing is a topic for a whole nother lesson will cover that soon okay so now I'm done designing this table so I'm going to close it by clicking on the little X here in the upper right corner that's the close button for the table now you can see over on the left here there's the customer T in the navigation pane if you want to make more design changes to it right click on it and select design view that will open it back up in design view again I'll close it when you're ready to put data in the table double-click on it and that will open the table up into datasheet view in the next lesson we'll start putting data into our table thanks for watching if you like this video be sure to give it a thumbs up and comment below I post new videos here on YouTube all the time so be sure to subscribe to my channel for updates click to begin lesson 6 now and also be sure to visit my website at access learning zone comm for more free videos and to sign up for the entire level 2 series for just $1 you

Channel: Computer Learning Zone

Views: 413,990

Rating: 4.9671121 out of 5

Keywords: access 2013 tutorial, microsoft access, ms access, microsoft access 2013, ms access 2013, access 2013, access tutorial, access training, Double, Save your table, Table naming conventions, Primary Key, text, number, long integer, id field, move a field

Id: hwBCdmpDTVs

Length: 19min 44sec (1184 seconds)

Published: Wed Aug 21 2013