Hello. My name is Sali Kaceli .The following is a comprehensive tutorial on using Microsoft access 2016 during this tutorial I will cover basic concepts related to databases and how to design and use a basic database using access 2016 we will start with the very basics of the Application and of databases [overall] and then we’ll get moving into working with Data in a database the tutorial is designed to be concepts based and Simple to understand the best way to learn is to follow the video hands-on on your computer So let’s get started first Some general concepts related to databases and how databases work? first we’re going to start with the definition of a database a database is a collection of data that is stored in a computer system a Database allows their users to enter access and [update] and analyze their data quickly and easily They are a powerful tool that you see them all the time when you go to the doctor’s office when you go to the grocery store When you go online to purchase something all that information is usually stored in what’s called a database the easiest way to Understand the database is to think of it as a collection of lists So think of it for example if you’re running a business you have [a] list of customers you have [a] list of orders from those customers you have a list of items that you are selling to your customers or services that you are providing to your customers, and then you might have a list of invoices and other Purchases from vendors and things alike but when you put all those lists together in a database those lists are linked together and information is related from One table to another and that’s partially what is referred to as a relational database [so] where does access fit into this? Microsoft access is a relational database software program or database management system dBMs That runs on windows operating system It is used to manage data that is organized into lists such as information about customers Products vendors Employees Projects, so basically Microsoft access is a relational database management system created by Microsoft to store organize [and] Manipulate Data as well as to select and report on it So some of the components [just] [to] summarize them briefly are tables queries forms and reports Tables that’s where the data is stored within the database then the queries are three specific data from the Tables or other queries and Displays only the Data that you specify Queries allow you to ask questions about the data in your tables for example I want just the first name last name and Email address and the form is used to enter new records in a table That’s the data entry component of it and to edit or delete existing records Into the table usually the Data is not entered directly into the table manually, but it is done through the forms and then the reports or the fourth component here And those are the [very] [symbols] of the output they summarize the fields or records From a table or a query in an easy-To-read format for example that will be Report that you print out something that you print out to give to your manager for the sales of that day now What does all of this look like in the national database? Here’s a really simple Database that we have we have a database called the customer sales here, and we have a bunch of tables in this case We have the customer table We have the contact table And then we have invoices so think again about these as separate lists So the customer table of course it will have the list of customers We have here the customer Id company first name last name telephone number State address City state and so on so those will be [very] similar to what we have seen in excel You can sort this [data]. You filter it [and] things of that nature I think of it just as a list now
[besides] the customers if you’re running a business, [then] you probably have different contacts or different sales So in this case. This is another example of a table of contacts table if we double [click] on it We have a contact number customer Id Contact amount a date and in the type and then a notice we have another table here called invoices and the invoice table has the date number and the item what it was for the amounts and What is your [spAdes] or not? So those are the tables again? This is where the data is stored? Into a database think of it here tables storage The next [components] in the database I have mentioned earlier It is the queries, so if we go here the queries for example are like we have the table customers those who have a whole bunch of fields here the fields are those names of the columns here and let’s say [that] we wanted to generate a list of only the first name last Name, and then they say email address and some other pieces of information here We create what’s called a query and we learn how to create queries momentarily here So notice I have another entity here under the customer table And this is a query that we have run that I defined earlier if I double-click on Notice I have only the first name last name telephone [number] you [lanice] So that think of it the processing of a data based on a criteria And this is an example of a form the form is basically instead of you going to the customer table scrolling all the way to the bottom and Entering the Data manually here Which is not recommended [just] [go] under the customer data entry form for example and form similar or nicer than this and Enter the records right here and You just basically fill in the data in real business. This [is] what? the Assistant was utilize for entering the data into the into the database and Then the final components as I mentioned earlier it is the reports and reports are [very] similar to queries, but they are just Designed so they can be printed out and they look nicer So these were some of the basic components of a database the tables are where the data is stored? The queries are how the data is? manipulated Then the forms are how the data is updated and added on to and then that? Reports how the Data is printed out or output Once you are in Microsoft access [you] will know this is very similar to Microsoft word excel and other applications in office 2016 on the left hand side You’ll have the recent documents or the recent Databases are files that you have been working with then below you have here the option to go ahead and open a pre-existing Database and then on the right hand side here we have the option to create a blank database that will be used utilizing in a moment, and then further down here we have different templates that Are [available] in Microsoft access? 2016 these are pre-configured [access] databases that you can utilize We’re going to utilize one of those templates very briefly just to learn a couple of the concepts But we are actually in the next session here We’re going to learn how to design a database from scratch So that [we] understand how databases work how to use Microsoft access So in our case [here]. I’m going to very briefly Utilize here this database called students. This is new in access 2016 now here is a concept that you might want to keep in mind when dealing with Microsoft access databases Unlike word documents, and excel where you open a [document], and you create the document And then [you] manipulate in a lot type of thing in Microsoft access [or] as soon as you choose to create a database You have to give it a name and your the first thing that you do is you save that database? So in our case [here] We have to give it a name note the location where it’s going to be saved and then you click on create In this case we are utilizing a template So, it’s pretty defined with all the components and such So it’s slightly different or creating a database from scratch
so first thing you are presented here is a Tutorial on how to use this not everyone [of] them is [going] to give you this option so we are going to simply Get started here to utilize it now a couple of things here [just] briefly so that we get a couple of the concepts and I will demonstrate these Much more as we get started in the next module Typically a database has four components Just like in a computer that you have the four basic functions of the computer where you have the input? that the computer accepts input it stores the data by storing it in the hard drive it processes the [data] and Then it outputs the data in access databases You have a similar concept as well you have the tables which serve to store the data. That’s where the data is actually stored you have the queries which are very similar to the Processing of the data on the [computer] and for now just keep those in the back of your mind And then you have the forms in an access database as well That serve as a mechanism to enter the data into that database And then finally you have the fourth component here the reports that Serve for displaying the data in your database. It’s very similar to the output in your computer, so in our case here We have this form so if we want to add and [your] students We simply click on your student and then we fill in the information there you can add the picture you can add [additional] information And click on save and then new and then you add the next student and so on now this window what we are using right now, this is referred [to] as the forms so this serves as the input for this database now that data is not really stored in the form the Data is actually stored in the table here on the Students table, and we’ll learn more about this in the next session so now if we look here This is how it will be stored. So it’s very similar to like an excel spreadsheet but it’s just a bunch of tables [and] Typically in a microsoft access database or in any database you can have two tables three tables Hundreds of tables or even thousands of tables and those tables are typically linked together Via What’s called primary keys and foreign keys and learn about those as well? They are kind of related one table is related [to] the other table via those keys And that’s where you hear about the term Relational databases because the tables are linked the other [component] here is the queries we don’t have any of those for now that we could Utilize the next one would be the forms that we just used a moment ago And then if you need to create a report of all the students, you simply click on all Students, and it will display More visually pleasant report for you to print it out So those are some of the basic concepts using a template so now stay tuned for the next session that [will] learn how to design our database from scratch? We’ll create the first stable and then start building a couple additional tables 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 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 a quick access toolbar with a bunch of commonly used options. We have a file tab here And then we have these different tabs of 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 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 the 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 lab 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 so 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 the field so you 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 also hear the term Records so we’d say this is records one or record two or records So 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 personal 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 it could change that to say customer Id there’s a 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 [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 to say the [state] address and this will 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 is you can pick whatever other field is your [go] you eyes, 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 if it 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 free zone for the picture for that customer or various other fields Basically this step is 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 [at] this point on it has to [be] matching the Type that you defined a moment ago for some of this the zip code has to be a number It can have letters in there and things of that nature The other thing to keep in mind is is that the data typically we for now
We are going to enter it here directly into the table, but typically it’s not entered from the table itself It’s typically enters from the form of the database and we’ll learn [about] this shortly as well Let’s enter just one [record] here for a sake of testing and then if we go to the next record You notice it enters [a] customer id Automatically so the concepts so far that we covered in this session Keep in mind when you define those fields as 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 use 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 day 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 preference [asian] Mailing address and that type of thing you want to keep it in one table Then anything related to order you want to keep it on the order table Anything related [to] payments you keep it in the payments table Anything that you want to keep related to inventory you want to keep it 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 some sort and then You define the data type for each field here And then you have to make sure that whatever your 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 some kind of a key differentiator between the records in your table so for example if you had two customers Name the super 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 id 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 you’ll 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 summers 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 [Left-hand] side you have the field name which is the column for each column in that table that we saw earlier So we could change the names here Or we could change the data type as well. If you have a lot of data [in] your table and you go [into] 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 okay under the state. I want that [instead] [of] 255 characters
I want that to be only the two digits 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 stops 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 one 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 you 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’re simply entering data the data 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 a way they’ll 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 In this brief video we are going to learn how to add additional fields to a table in access database and then we’re going to learn how to create queries how to create forms and how to create a report based on a table in our database Stay tuned we are going to also learn later how to create multiple tables in a database and how to link those tables together and utilize additional functions in Microsoft access 2016 so we have our table that we designed earlier and Now we want to add let’s say to make a small change as I had mentioned earlier is don’t want to make too many changes After Data has been entered in the table But this is just for [demonstration] purposes [so] to enter a new field because do it a couple of ways here We could either click [here] on click to add and then choose a type of field that we want to add or we can go here under view and Then choose the design view [so] design view it’s this next one now We scroll all the way to the bottom here and then Let’s say we want to state whether this is a new customer or not so it’s going to be a yes or no field So we say new customer, and then we have to change this on the drop down here to be yes Or no, well the system is going to do there is going to allow us to put a check mark basically have a check box Once we have to tweak that you can add additional fields in there by the way you have here Under description Design view you have an area where you can actually? Put notes design notes for you as a design [out] of this database you could say
additionally you can go up further for any of those fields and Such as we cover the very briefly in the last session Each table needs to have a primary key and the primary key can [be] a number field it can be anything but it has to be something typically it’s a number in an other number something that increases or Some kind of code and it is what differentiates? One record from another so if you have two people the same name in the same address Then in order to differentiate them the best way would be to simply Assign them a new Id or to have different ids So the primary key typically in the database design here since we are in this view it’s represented with this Little Golden key if you wanted to change it to [a] different field and make that the primary key you can However, it has to be the right kind of field if you change it to this one Then that means that there can be no two people with [the] same name [onto] that table So you want to make sure that you set the primary key on the correct field and that’s why? It’s an Id Or something a number that is Generated by either the system with us showing it manually, but that number has to be unique So [I’m] going to make this back as a primary key and now let’s suppose that we [are] done with a design here We can click on close save the design changes and now let’s learn [how] to create a form forms typically are utilized for entering Data you could enter the data in here, but if you have a lot of Fields here, or those columns and you have a lot of data first It would be very [dangerous] that to delete records that you’re not supposed to delete But secondly it’s not the most use friendly interface to enter your data So therefore what we can do here is we can go under the create tab and you want to create? What’s called a form the forms you can create them from scratch? You can design them from here, or you can use The Form Wizard and as recommend that you utilize the form Wizard it’s much simpler much easier to do this go under Form Wizard here and Basically the way, it works is if you tell the system. Which table you want to use in our case we have [only] one table here, so Then you pick here What fields you want to include in that Form by the way you can design? Multiple forms based on the same table. You’re basically saying I want to feed data only for first name last name and address and you give access to a specific individual to just utilize that form While other individuals may have access to update more areas of your database so here we are just picking the Fields that we [want] you can pick them one at a time Or you [can] simply add all the fields in one shot here by clicking on this double arrow icon And then click on next then you can choose How you want your form to be displayed and organized and you can play with this on your own but typically columnar? Basically go up and down in the sequence basically then click on next and then you give it a name and then [it] says open the form to view or enter the information or open to modify the design even further So in our case here. We are simply going to click on finish Notice it has designed our form for us to enter the data. So notice it has Displaying the first record that we have entered already in our table now you might say well [that] this field file1 File2 File 3 this is because we chose one of the fields to have attachments and These are just the attachment fields here Also, keep in mind that you can design this form and altering about between the design of forms in that or later as well But for now, we are just learning about the basic concept of how forms work in a database And how they relate the tables so stay tuned for the other Functionality there so in our case here the form here serves to display information That is already stored in the tables or it also serves For us to enter new records and create add new customers here So if I click here add a new blank record, we leave the customer. Id alone here and Then we just fill in the information and notice that this it’s it’s not quite
lined up properly and all that type of thing I’ll show that the moment how to readjust the size of those and Then [you] basically just fill in the information if we go to the next record. It’s basically it’s stored What we just entered? And then you can just create a new one and keep on doing this so typically It’s the forms that your assistant is going to use to enter data and lookup information typically your users Do not really need to touch the tables and entering Data and such Because the system will enter the data using the forms So hopefully you get the idea there now if I have to go here to my table [by] the way you need to close any of those other things that you might have [open] here in the main area and We open the table here notice [that] I have now Jim. Smith my new customer that I entered manually, what did I enter through the form so again the forms are very similar to air the input function in the Computer and you utilize them to view what’s stored in the table and to store new Data in the table? Now to modify [this] form just very briefly here. You can click here under view And then choose layout view and you can kind of Spin. It. Just simply resize this stuff if you need it to So this is one way to resize it or to adjust the design it It’s somewhat simpler The other [methods] to modify the view of this form is By clicking here [on] view just like we use the design view to modify The design of the table we can use a design view to modify the design of our form here so if we click on the design of view now it becomes a little bit more complicated and such that’s why I said that the other Method was slightly simpler here is where you can change in a more precise way The design of this so here you could actually go and say instead of first name without the space there you could simply go ahead and change it put the space in there you can format that to be in gold and You could change the design of those fields and such Additionally from this view and it’s not time [for] us to learn about it But if you needed to its these second fields here the ones in white that are basically Connecting so you don’t want to change this word in here to put it a space. That’s the code That [it’s] links to the table because basically it’s saying it’s going to pull from that first name field on the actual table so you don’t want to change these if you [right-click] in [here] and Unfortunately not displayed here for you to view it but if you go to properties in the bottom because of my recording the way I have the recording of the screen and Notice is the control source. It says here, so it’s pulling the data From the customer information table, and it’s pulling this from the field First name, so this is more complicated Just for your information, you don’t want to change this second area here, then once you’re all set here We can close this save the changes to it and if we go back [to] our form Know this first name here. It’s in Bold and such So hopefully this makes sense on how the forms relate to the tables in an access database now let’s create a quick query here and let’s see how queries work in a very basic way, so if we go here to our table, and [we] click on create and now we go here under the Query of [Wizard] and the way queries work is basically you can connect to a table and you are saying List all the customers from that table or list only the customers from a specific zip code Or a specific city or a specific this state, [we] are putting criteria within That query or that question so bizzy’s are simply asking a question Give me a limited information based on this specific Criteria. So here Let’s say we want the [first] name last name and then the street address in the city and zip code then we click on next click on next and then it says customer information query click on finish and notice we got only what we Requested so we have first name It’s listing all the customers by first name by Last name Street, address City And zip the concept to keep in mind here is is that there is no data stored?
within this query itself [as] the Data is still stored in the table the query just stores the parameters of What our criteria? Is so to run this query all you have to do is double [click] here [on] the query and it pulls the data right away If we were to go and change specific data here for a specific customer Let’s say we had a fourth customer here And I’m entering this using the form now if I go and run this query Notice I have customer  listed here So the idea is is that you can create as many queries as you want whatever Options that you want. You know the manager might say I need a query I need to know how many sales We did today, so you could say okay. Give me all the customers Give me all the items that were sold, but based on a specific day The week or and such so that’s very briefly the queries if you need to change and we learn how to modify the query so just check next tutorial on customizing and working with queries in a database Now next in this session [we’re] going to learn how to create a report a reports in a database are a key element as well as very similar to outputting information you want to print it out particular leads to Organize the Data in a certain way it’s very similar to queries, but the queries are displaying the data in a very excel like worksheet here But the reports are designed to look nicer so you can print them out and hand them over to somebody so to create a report again you under the create option here or the create tab and then you want to go on the Reports and my suggestion would be that you utilize the reports wizard. So here you’re picking What table you want to pull the report from notice that you can pull a report from queries as well such Reports are built from tables or from queries, so whenever you build a report on the query if the report is actually going To that query and pulling the data so for now, we’re going to use it directly from the table And then we say I want the customer. Id let’s say the first name last name and the [state] address and Whatever else that you want here zip code and such and then we click on next now [this] stage we have the option Next and we can choose how to sort those customers. Let’s say by first name I by his last name or by customer Id and you can put different Searching options here or sorting options click on next click on finish and now Notice here. We have the report called customer information notice it’s slightly more visually pleasing here and You can also adjust the design of this very similar to how we did earlier for other components here so we can go to just the design by going to the layout view and Then resize those fields accordingly Now typically the numbers here this number of sign that means that it does not fit in that field so [you] need to still resize this or the other method here to Adjust the design of this is by going here under view and I go under design view [so] [again] the concept is whether you want to change the design of the tables the forms or the queries or reports You have the different views that you can change the design of them and particularly you want to use the design view For designing any of those components of the database now when we are done with this report We close it since we [tinker] through the design here Just click on save to save it close the other elements as well, and then go here under customer information this is our report and that’s the Data that it’s pulling from the table here, so hopefully all this makes sense in how a database are a very basic database with one table works and How the various elements of the database particularly those key? Four components there are other components that you add as well such as macros and things of that nature but these are the main Key components of a database let’s use utilize typically Now in this session. We have not learned how to create
additional tables in our Microsoft access database Typically an access database or any database how they will have more than one table And you can have hundreds and thousands of tables depending on how big your database is for this tutorial And you should have access to a link to download the working files the working file is a [sip] file here and what you need to do is you need to Double click on it after you have downloaded it and then you want to click on extract all once you accept them all take note where it’s going to extract this stuff click on extract here and Then you’ll see three files in there So we’ll utilize those three files to build an access database or to basically build three Additional tables by importing the data from those files in order for us to get a better understanding [of] how microsoft access works if we have to enter the data manually it will take us a very long time So we’re going to build three tables one of them is going to be based on customers like the contact information for our customers the next one is listing of the Contracts for those customers and then and a third table is going to be the invoices so stay tuned how we do this With the next session here keep in mind that this data. It’s two of those species of Data are in Microsoft excel So it looks like this, and you can have that data in [Microsoft] excel already in a system of some sort so basically have the customer the company first name last name and the Information related to the customer notice a customer. Id here is a unique number Then the next one the contract list notice you have a contact number the customer Id You have [a] contact amount. It’s been our excel spreadsheet [and] Then you also have this invoices list, and I have this on purpose here as a text file because you can import data from a CSV file or at a comma separated values file and this is what that would look like so you have the different values here the invoice and All the Fields are separated by commas That’s why CSV because of the comma separated values and then notice that each value here is in quotes so we’ll learn about how [to] Import this into Microsoft access database and create three tables from these so in this session, we’re going to learn how [to] Create tables in an access database by importing the [data] from another system will import the actual data and also the design For the tables in one shop from those systems to save us time you can Import the Data into an existing table in Microsoft access as long as the fields these crowns and also the datatype Matches from the external file with your current design in Microsoft access Since we are starting here understanding the concepts here using microsoft access We’re going to bring both the design because I hope you understand how to design the table and the fields and such By what we have covered so far in our tutorial if you wanted to do this on your own and such What you’d have to do? Basically design the table if you need to bring it into an existing design You’d have to take [let’s] say the customer is and when you design your table in Microsoft access [when] you go to Those Fields here in the design view Those fields have to match so you have customer. Id here from your excel file or external system It needs to match along with the data type And you’d have to have another field in here called company, and you have to create that then first name Last name telephone number state address all those have to be in exactly the same order if you [are] going to use an existing Table in Microsoft access in our case. We are not going to use the existing table So we are going to create a new table by importing the data from an external data file So if you how it works we go here under External Data, and then since we know that our data file was an excel file. We can go here under Excel and then [we] go and locate that file that we extracted earlier from the working files
so we go here under browse go under the working file and Notice we have here customer list click on open And I here we want to import the source data Into a new table in our current database if we wanted to add [just] the data Then we’ll choose the append option here to just simply add it to the existing table. You can also link it to an external Table or external data there as well, but that’s beyond the scope of this tutorial for now So we are going to click [on] import Data to create a new table click ok here, [and] then it says show the worksheet So this is our worksheet. This is what what’s in [that]? Table, then we click on next [and] then this is very important This is where a lot [of] users get lost so we also choose here the first row contains The headings so the first row in our [excel] spreadsheet Has personal the field names customer Id? Company field name last name, so this is just that label That’s what we are telling [the] system that that’s what that is Then we click on next and then here we say you can now specify about each item that your import is select the fields below and If you want to allow duplicates, yes, or no Typically in a database for example for the customer Id we don’t want to allow duplicates So [you] say no duplicates in our case We’re [just] bringing this to play with so minimizing the issues, and we’ll just leave it [alone] [now] [the] data type here It said it’s going to be short text. Typically you want to make sure that your custom Id it’s actually a number field so we can change that at this point and say a number to be Typically, it’ll be a double here that you choose the data type and such for sake keeping it simple I’m going [to] give this a short text then we click on the next now It says do you want to choose the primary key notice it’s starting to create a new one for you You could create this manually or automatically here the system will create it for you, or you can go and say no I’ll use my own key because I already have the customer Id however keep in mind it cannot have two records with the same customer ids You need to be sure that the data that you are bringing in from an external system to create this new database It actually does not have any duplicates in it, then we click on next here So our [name] for this table [record] customers, and then simply click on the finish Notice we can save the steps if we want it, but we don’t really need to save at this point We click on close and now if you have another table here And if you double click [on] it you have all of those customers there are 38 of them that it brought from that table notice that the table now it has the field names in the very top here [and] It also has the various fields such as the email field and a lot type of thing if you needed to create additional Fields, then you simply can go here under the home tab and you could go and add in your field So if you wanted for example a field called common, it’s going to add it and then you choose the type here so we are going to make it a comments field to be a long text here, and then we close the design and now if we go back to customers You’ll notice that you [have] another field called comments So that’s how you create a table by importing the [data] From an excel file let’s create another table of content at table so again We go here under external data. We go under excel, and then we choose the file that we want to import We’re actually going to get the contact list We’re going to create in your table along with the structure for that table From your external data file from our excel file, and then we click ok go next here We tell is the first row has the actual information next Then the contact number right now we are choosing to allow duplicates But typically you don’t want to because this is going to be our primary key the unique identifier for each record here And we’re going to leave it alone for now, then we click on next Then you want to make sure that you’re choosing your own primary key And you’re making a contact number as the primary key for here Then we click on next and then we just say this is going to be called a [contact] table click on finish click on close and now if we close this [we’ll] have contracts and customers and notice there are [sixty-five] contracts at this point And then we also have the customers now this next session We’re going [to] learn how to import data how to create a table by importing data from the CSV file
this session We’re going to learn how to create a third table including the structure from a CSV file in Microsoft access So we have here this invoices list we want to create a table called invoices and that data we import it from some kind of system out there that comma Separated values, so the creates a new table you. Go here under external data and well to import the data from A text file, so [we] click on next here Locate wherever our file is and notice It’s under my working files, and this will be also in your working files if you expanded it We choose invoices list we click on open Then click [ok] and then we want to tell the system here that this is a delimited type of data file which the values are Separated by a comma and you’ll know that when you receive the file from whatever system there It will typically be [a] CSV file And if you click on next and then we want to tell the system that this is a comma separated values so the values here for each column They are separated by [a] comma I’ll go next again so choose comma and notice here It says first row Containing the field names so make [sure] you select this line if you skipped it go back and try to follow it so in comma first row contains the field names click on next and then We leave this the way they are for now Click on next then make [sure] you choose your own primary key and the invoice [number] is going to be the primary Key Next and then this is going to be invoices and then click on finish So now we have an additional table here And this no table is called invoices notice one key aspect here if we go to customers. We have your customer Id this will be our primary key for this table? So even if we went over here on the design view though this customer Id has this golden key here? So that’s how our unique identifier for this table now if you go to another table here So if we go for example [to] contrast and open this up and go under design view notice that contract number is The unique identifier for this table anytime you are designing a new table that Table has to have some kind [of] unique identifier or primary Key now notice as well here that this table also has Another field called customer Id and the customer Id here We saw it that it was a primary key for the customers table that we saw earlier This was on this one what that means is [that] for the table contracts the primary key is contract number That’s the main differentiator and now customer Id is What’s called a foreign key basically it will give us the option to link customer Id from this table to the customers table with customer Id field [so] the concept there is is that in a database? In a relational database the tables are linked together One to another and such using primary and foreign keys The foreign Key is the common field Between two tables [if] that field that is the secondary it’s not the primary Key on that second table, so for example here We are saying we have a contract with this number But then this contact is associated with a customer of a specific number so if we go here under for example customer 1105 and We go here under contracts and we see 7W5 that’s saying that this contact  Belongs to Customer 11 double V the concept to remember is when you’re designing this you need to design also for foreign keys the common field that connect one table to the other and we’ll learn how to Connect those tables in a moment here now even if you went here under invoices you’ll notice that the invoices has an invoice number That’s [the] primary Key But then you have a contract number which will eventually connect to the contact number here from the contacts table and in the invoices table So the primary key is this one, but then this is the foreign key for this table
so primary keys and foreign keys are two very important concepts that play an important [role] when you’re designing the Database because they’ll need to be linked together, and that’s what we’ll learn in the next session here We’ve learned how to link the tables using the primary keys and foreign keys In this session, I [will] demonstrate how [to] link tables in Microsoft access database in access 2016 or how to define the relationships between tables in access? 2016 so a relational database like Microsoft access 2016 it requires that the tables are linked and Typically the key component [for] linking tables in a database is The proper design of the tables to start with and typically what you want to do Is that you want to have the primary keys and the foreign keys? Properly defined in the tables before [I] further explain this I’m going to clean up our database here So I’m going to first delete this table that has nothing to do with what we are planning to do in the next couple of minutes here I’m going to delete these queries as well by the way you don’t want to do this on a real database And this you’re sure that [you’re] the delete is and at this point. We have big tables. So we have the customers table We have invoices table in the contracts table to learn how we did this please refer to the previous tutorial ously We have the primary key for customer. Id this is the primary key for this table and then under contracts we have the primary key being the contract number and Then the foreign key is the customer id which is supposed to match with the customer? Id which is the primary key on the customers table, and then the same way under invoices we have the invoice number? Which is the primary key for the invoices table? but then the contract number which is a foreign key in this case goes to match with a primary key of [the] Contracts table so as it designed that there’s a database You need to factor in a plan on what the Florida keys are going to be and how the tables are going to be linking? With one another when it comes time to link them and this has to be done during the design process now at this stage We need to close the tables and then the next thing that we need to do here is we need to go here under Table tools, and then we are going to define the relationships, so we go here on the relations And then we choose to add the customers table Which is to add [a] contacts table, and then they invoice this table and then close this Now notice you can even organize them any way you want here you want to organize them Fairly logically if you can so as you see the structure depending on how you organize your database to link those Tables together notice we have here customer Id from the customers table, and we have [the] foreign key here [on] the contracts for the contracts table So what you do is you click on customer Id from the main table? And you drag it and you hold the mouse directly on customer Id on the contacts table here and then you let it go now this point You know this is saying it’s going to use the customer stable Linking to customer Id in the contacts table It’s going to be a relationship of one-to-many what that means is that you can have one customer? With multiple contacts and that’s hopefully what you want if you have a small business you want multiple contracts from the same customer You could also Enable here, what’s called a referential integrity? I’m going to check it for now to keep this simple, but that means that if you deleted the customer you’re probably never going to have that customer again, then you want to delete also their Contracts that’s what their referential integrity is going to do a cascade update and cascade delete if you remove the main source [going] to remove the items that follow with it if a student drops from College you want to drop [also] the courses and things of that nature Then here we click on create and notice that you have a line between the two The next thing that you want to do then is notice now We have the [contacts] primary Key here could link will The foreign key from the invoices table, so we want to say from invoices here
We want to drag this to the foreign key releases table again the same idea Contact number to contract number want to many relationships that means that you can have multiple Invoices for the same contract that’s what that means and by the way You can move this however it makes most sense to you If for [some] reason you chose the wrong thing notice that they [have] to match your customer Id has to be under customer? Id it can be linked to a different field if for some reason you have it improperly matched there You can right click or select [this] once and then you can choose either edit relationship or simply? delete and then if you want to delete it you say yes, and Then now it’s disconnected and then you can redo it again. So you just drag your customer Id to customer id and then click on create again, and now you have it the way you wanted it Once you’re all set with this you need to click on close, and that’s where the system is going to save these links between the tables because unless these tables are linked together we cannot get Data from one table to the other and when we link these tables through this relationship that we can actually go and say I Want for example a query here or a report of some sort that I want the first name last name And then I want the contract number and then I want the amount [and] a date and the contact type that’s when you can kind of cross and pull the data from multiple systems, then I click here on close and Then it’s going to ask us to save the relationship here We say yes, and now the relationship has been defined and now we can actually create queries We can create reports we [can] create forms to pull data from multiple tables here so this is where the beauty of Microsoft access actually takes place let’s very briefly learn just for the sake of testing at this point how to Create a quick query on pulling data from two or more tables Just before I finish this section, so you get an understanding of how the relationship Connection here between the tables what the advantage is so if we were to look here at customers now Let’s have a [customers] table, but I don’t really have any contact information for this customers table If I go to contract, I have only contact information, but I don’t really have any customer information Other than this id so now what we want to do is we want to pull the customer [information] and the contact information and have it displayed a certain way, so here’s how it works [you] go here under create and Then you go under a query wizard And then we click on [ok] and then we want to pull here first from the table Customers notice that we have three choices where we can choose from so we choose customers? And then now it’s just a matter of whatever we want here So we say I want the first name the [last] name and then let’s say I’m interested now from that table. I’m Interested on only these two fields the next thing that I want is I’m going to go under Contract, and then I want to pull here the contract number I want to pull the contract amount I don’t care for the customer Id because I know I have their first and last name already And then I want to see the date and the type of the contract so notice it’s about six Fields here from two tables now. I click on next and then Click on next again, and now give it a meaningful name So whenever you’re defining the queries you want to define it with a meaningful name contracts by first and last name click on finish and now notice that we have a new query created and now we have the data first name and last name and a contract number and Then the amounts the date and the type and notice so we pull the data from two tables from here you can create all kinds Of other things whether it’s reports or new queries or forms and we’ll cover those in next few sessions One other thing before I finalize is I should have mentioned this earlier is that in order for you to link? those tables correctly you need to make [sure] that the fields that are going to be linked through the Relationship are of the same Data type and this is where a lot of Users get frustrated because this does not work What that means is is [that] on the customers table when you go to design your table? You need to make sure if that [is] a number field if it’s numbers you need to make [sure] that this customer
Id over here on the next table [in] the foreign key? It’s actually a number field as well It’s the same type of field again here The contract number on this table and contracts table that needs to match exactly of the same Data type formatting otherwise They will not link and you’ll get errors on your linking and more concretely You can see that in my case. I go here to design view for the customers table. [I] have it under customer Id I have it a short text field typically it’s not going to be a short text you want that to be a number and Autom number typically I’m not going to change it right now, but typically it’s an auto number and needs to match number and Order number they can be used as one type of data So that’s fine, but you just need to be assured they match so in my case I have short text here, and if I go to my next table under contracts if you look for my Customer Id here it’s going to be Short text as well. That’s why [I] didn’t run into any problems So those can be numbers or all the numbers that they have to match on both corresponding tables Remember that in your design, and it will save you [a] lot of frustration So that’s how to defining the relationships in an access database works. It’s one of the key aspects of designing a database successfully