Microsoft Access 2016 for Beginners: Creating a Database from Scratch

In this session we're going to create a database  from scratch and we are going to learn about some   of the various components of a Microsoft  Access 2016 so we can kind of understand   how the application works and where the different  components are. So once we open Microsoft Access   we click here on the blank database and like we  learned earlier the first thing that we need to   do after we click on the blank database we need to  give it a name and take note where you're saving   this database. Now as soon as you open the Access  database here what we have is very similar to   Microsoft Word and Excel and other applications  in office 2016. On the very top we have the   quick access toolbar with a bunch of commonly used  options we have the file tab here and then we have   these different tabs at home the most commonly  used functions very similar to other applications.   Then we have here the create tab this is to create  different components related to databases. For   example creating a table creating queries creating  forms and reports. Then we have the external data   tab. This is basically for us to get data from  other systems and import it and link it into   a database here in Microsoft Access and then  database tools this is another tab where we can   design the database and define the relationships  or define basically any tools related to the   database here for whether to repair the database  to create macros or other components. Then we have   here this new tab called table tools. So table  tools here this is very similar to the contextual   tools in Microsoft Word or Excel basically a  new tab that shows up in the context of what   we are doing. So right now we are creating a new  table and it's giving us options here for this new   table. Next to it here you have also tell me what  you want to do or that tell me feature. This is   new in office 2016 so basically if you wanted to  learn how to use query wizard or how to create a   new form or anything like that you simply type in  there how to do that. So for example query wizard   and you just basically takes you directly to that  option in order to learn how to perform a specific   task in Microsoft Access. And then notice on the  bottom here you have a couple of other options   and I'm not going to take the time to tinker with  those too much. It's basically the design view and   the normal view and we'll cover those shortly.  Now creating our first database here. Databases   as I mentioned earlier they are designed using  tables. Typically you have at least one or more   table. So now this is our first table that we are  working with and we'll give it a name shortly so   it will actually ask us to save and give a name to  this table in a moment. Typically the way tables   and databases work is that one of the fields by  the way these are referred to as that field so   you'd have the ID field the first name field and  a last name field and so on so the columns which   we refer to them where you'll hear the term field.  Then you'll also hear the term record so we'd say   this is record one or record two or record. The  record is think of it as the row here. So you   have more than one piece of information related to  a record so you have for example first name last   name address and so on relate it to that specific  customer. Here this would be the field name so   right now it says ID so you get to change that to  say customer ID. The type here for customer ID it   typically it needs to be a number so notice under  the data type this is an auto number that means   that when it goes to the next customer it'll go  so from customer one to number two number three   number four automatically. The data type for each  one of those fields typically has to be specified.   The next one it's asking us to what type do we  want to make this next field here. So the next   field here we are going to make it text and this  will be first name then the next one. We're going   to make this field type as well as short text here  and then we're going to call this last name and   then the third field we are going to make this  let's say the state address and this would be   text as well. The next field here it will be city  and then the next, state and then the next one zip   and the zip code we want that probably as a number  field. And then the next one you can pick whatever   other field that you're going to utilize but take  note here that it can be various other fields so   it could be for example a date field when they  signed up to be your customer and such or you can   have an attachment for this customer or you could  be able to post a hyperlink field as well. So in   this case we are going to create a field here  for attachment and that would be for example for   the picture for that customer or various other  fields. Basically this step it's referred to as   the designing this table so we are defining how  the fields are going to be formatted. If we are   done with the design at this point because simply  we could do a couple things at this stage. We   could either enter the data directly from this  table that we and by the way the data that you   enter from now on from this point on it has to  be matching the type that you defined a moment   ago for example the zip code has to be a number  it can't have letters in there and things of that   nature. The other thing to keep in mind is is that  data typically we for now we are going to enter   it here directly into table but typically it's  not entered from the table itself it's typically   entered from the form of the database and we'll  learn about this shortly as well. Let's enter just   one record here for the sake of testing and then  if we go to the next record you notice it entered   the customer ID automatically. So the concepts so  far that we covered in this session: keep in mind   when you define those fields you need to specify  the data type and it's very important to think   it through as to all the fields that you want  in a table when you're designing your database.   So then you want to make sure that all the fields  that you'd want in that particular table they are   included in there. You can add them later as well  but it creates it causes complications it's best   to think it over initially. The other thing is is  that you need to consider categorizing the data   accordingly in various categories and then these  categories they become your tables. So for example   you want to make sure that let's say customer  information anything related to the customer such   as the address the preferences and mailing address  and that type of thing you want to keep it in one   table. Then anything related to orders you want  to keep it on the order table. Anything related   to payments you would keep it in the payments  table. Anything that you want to keep related to   inventory you want to keep it in an inventory  table and even the inventory could have all   kinds of sub tables as well. The key there is to  categorize information in major categories. Those   categories become at least a table of some sort  and then you define the data type for each field   here and then you have to make sure that whatever  you enter in that field you want to make sure that   it matches that type of data. Also remember  as you design your database you need to have   kind of a key differentiator between the records  in your table. So for example if you had two   customers named as Hubert Sims and such you want  to make sure that how do they differ and the way   to differ from one customer from one record to  another is by assigning them something unique.   For example a customer idea, a unique customer ID  and those are typically referred to that becomes   your primary key. The primary key again that is  what will differentiate between two records. Once   we are done with designing our first table here  you'll click on close here on the top right of   this table and now it will ask us to give the  name to save the design for this table. It will   actually save the design along with the data that  we just entered. And now notice here under the   tables list here on the left hand side we have  customers information. Now to open this up you   simply double click on it and you'll be able to  view it and enter new records in there as well.   If you need it to change the design you could  simply click here to add additional fields or   another method to change the data and change the  design for this table is also by using the design   view. So notice here under the home tab we have  view and there are a couple of views. There's   a datasheet view what we currently are seeing  and utilizing and then you also have the design   view. Let's click here on design view and this  is a more sophisticated way. It's a little bit   more complicated if you're not used to working  with databases but yet it's actually a lot more   powerful and a lot more useful. So here what you  can do is basically you can modify the structure   of this table. On the left hand side you have the  field name which was the column for each column   in that table that we saw earlier so we could  change the names here we could change the data   type as well. If you have a lot of data in your  table and you go and tinker and manipulate the   data type you might the most likely get an error  message. So keep that in mind as you design your   tables to try to do it as best as possible in the  beginning whether it's the data type the layout of   the fields and such now. From here from the design  view like I mentioned earlier you can change the   data type so you can say OK under the state I want  that instead of 255 characters I want that to be   only the two-digit abbreviated version of it or  you can make it 40 characters long or whatever   the length of the field is you can define the  field. You can also change the format in the   mask and all kinds of default values and you can  control all kinds of additional settings here so   I'm not going to go into the more fancier options  here but for the big picture you can change the   steps from the datasheet view or you can change  it from the design view from here. Notice as well   that you can change the order of those fields by  holding down the mouse and you can move one field   above the other one as well and change the order  of those fields. To add new ones you can add them   here in the bottom and then you have to define  the data type as well. So for example this is a   field for comments so this would be a long text  so you want to make sure here that the user can   enter enough text. I believe that'll be 64,000  characters that it will accept in that field   when you start typing on it. Once you are done  with any of the design changes now we click on   close here and it's going to save the structure.  Typically this save option it takes place only   when we change the structure of it so one of  the concepts here is is that if you change the   structure of your table and the design of it then  it's going to ask you and prompt you to to save   it. However if you are simply entering data the  data is saved automatically into your database.   So that's another concept to understand when using  databases. The other thing is that databases are   designed to be used by multiple users at the same  time so once you have finalized your design you   can have 10 users, 15 users, 50 users or however  many users access and update the table at the   same time and work on the same file the same  database at the same time. So those are some   of the very basic concepts on getting started  with an Access database and access table. Now   this is not all. Next we are going to enter some  more data into this table and then we're going   to create a query we're going to create a form  and then we're going to create a quick report   with just one table. And then furthermore so stay  tuned we're going to create multiple tables and   we're going to link those tables together  and then we're going to utilize the more   intermediate functions in Access databases  and using Microsoft Access so stay tuned.

Channel: Kaceli TechTraining

Views: 459,713

Rating: 4.8084874 out of 5

Keywords: access 2016 tutorial, microsoft access tutorial, office 2016 tutorial, learn access tutorial, access for beginners tutorial, tutorial on access, creating a database in access, skaceli, sali kaceli, tutorial on microsoft access, learn access, designing tables in access, create database from scratch, data types, design view, modify table, structure table

Id: FfmNqu0wP00

Length: 14min 42sec (882 seconds)

Published: Fri Dec 16 2016