Access 2010 Tutorial: A Comprehensive Guide to Access - Access Made Easy

in this session I'm going to talk about some of the basic database concepts 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 when you go to search something on a library system 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're providing to your customers and then you might have a list of invoices and other purchases from vendors and things like when you put all those lists together in a database those lists are linked together and the information is related from one table to another and that's partially what it's referred to as a relational database so where does access fit into this Microsoft Access 2010 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 again 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 question is what are some of the database components the components just to summarize them briefly our tables queries forms and reports tables that's where the data is stored within the database then the queries retrieve 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 it's very similar to the other function in the computer like that processing that takes place in the computer if you were to take it as an analogy then 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 in the form it think of it the input of the data the entering of the information into the system and then the reports or the fourth component here and those would be very similar to the output they summarize the fields or records from a table or a query in an easy-to-read format for example that would be a 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 a natural 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 a 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 prism of the customer ID company first name last name telephone number street 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 can manage it in different filter it and things of that nature but think of it just as as a list now besides the customers if you're running a business then you probably have different contracts or different sales so in this case this is another example of a table the contacts table if we double click on it we have a contact number the customer ID contact amount a date and then the type and then notice we have another table here called invoices and the invoice table has the date number and the item what it was for the amount and whether it was paid or not so those are the tables again this is where the data is stored into a database think of it when you hear tables storage the next component in a 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 notice we have a whole bunch of fields here the fields are those names of the columns here and let's say that we want you to generate a list of only the first name last name and then let's say email address and some other pieces of information here so what we do is what we create what's called a query and we learn how to create queries momentarily here so notice I have another entry here under the customer table and this is a query that we have run that I defined earlier if I double click on it notice I have only the first name last name telephone number and email address so that think of it the processing of 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 you would go under the customer data entry form for example in a forms similar than this and enter the new records right here and you just basically fill in the data the real business this is what the assistant would utilize for entering the data into the into the database and then the final component 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 of each database has various components 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 in this session I'm going to cover a couple of the basic concepts on working and getting started with Microsoft Access 2010 as soon as you open Access 2010 this is what you'll be presented with you have the options to use available templates so you can create a blank database or you can use some sample databases for example a predefined ones that have been designed by the program and also you can go and download from office com different templates that are provided by Microsoft in our case here what we were going to do is we are going to create one from scratch so we're going to make sure we click on blank database and then we are going to give it a name I'm going to name this just using access notice what's different here is that unlike Microsoft Word or you type a document or in Excel you type a document or a spreadsheet and then you save it later here you have to create the database you save it and then you enter the Hertz and update it as you go it's also important to keep in mind that as you enter records and data into the Microsoft Access database the data is saved automatically just tell the system where you want to save it so I'm going to save it under users here in my name and then documents and it's going to be saved under the name using Access 2010 I click on create and at this point we have just started with a blank database before we actually get started with creating a blank database here a couple things as far as the advantages of using Microsoft Access as a database management system one of the things is that duplicate data is minimized and then secondly is that information is more accurate reliable consistent because duplicate data is minimized so in other words you have one customer with multiple orders but you don't have the same customer listed multiple time in those times in those lists also data entry is easier and faster using Microsoft Access forms and then the information can be viewed and sort it in any way that you prefer using queries forms and reports also information is more secure using Microsoft Access because you can limit access to the database by using passwords and different levels of security another thing to keep in mind is that multiple users can use the same database at the same time so notice here we have a table called table 1 and at this point we can just earth define the fields the fields if you remember from Microsoft Excel are those headers on the very top of the row here so we have the customer ID we can add the other fields for this table now notice we'll click here on click to add and the first thing that it asks us is that this new field that you want to add what type of field is it going to be is it going to be a text number current date or any of those so what we do here is it this is going to be text for example so we'll say first name and then just hit enter then the next one it's going to be last name and that's going to be also a text field the next one it's going to be the address let's say so we click on text again and then go to the next one text keep in mind that every piece of data with have it in separate fields so here we'd not put the address all into one field we'd actually have it as separate because the nice thing is that we can utilize it later for example to filter by only certain customers within a zip code or a certain customers within a specific state or city and so on and then if we're going to put a number here for example for a telephone number we just choose it's a number field now we are ready to enter the actual data in here in this table for now we enter it directly through the table our data needs to be entered by using the form or that's the most commonly used way of entering the data so at this point we put here the names of the customers and I'm just pressing tab to move from one field to the other couple other things here to keep in mind with tables is that basically you have the different columns and the different rows very similar to Microsoft Excel that we are used to using now each one of those is referred to as a cell very similar to like Microsoft Excel as well these are the different records so this is record number one and that includes all these pieces of data about this customer and that will be record number two each table has what's referred to as a primary key and this is a very important concept the primary key in a table is field that is marked as you need or that makes each record unique within that table now notice here under ID that record has been marked as unique and that's what differs to customers so because you could have another customer here Hubert Sims and then he could also live at 200 Manor Avenue with all these other pieces of data however what makes those two records different is this field here notice that the record one is different from record three because of this unique ID that has been assigned to each customer so even though all the other pieces of data are different they still are different because of this primary key there are actually two ways to create to enter data into a table either by the right here like we did so far or you can use the design view so this design view is actually the easier one so let me close this one and we'll save this and we'll call this customers again we are saving at this point we have created a database from before now we are just creating a table within the database so here is a table customers now if we wanted to go back here and notice under the Home tab we have this view and then a drop down under view on the drop down we can choose different views for that specific table we can click on design view and that will give you an opportunity here to change the design or the layout of this table and this is the preferred way of creating the tables actually so notice at this point we have the fields that we defined earlier now this is when we entered first name we have alt text field and when we entered the telephone number that was a number field now if we wanted to add additional fields we can simply add them right here present we just put the name email and then we can define what type of field it is what type of data is going to be in that field you can have it as text or any of those in here the email in this case it would be text and then you can also limit the field size and those would be the field properties here for email and then you could add additional data for example notes and the notes can be instead of being just plain text it could be a memo field which usually can take up to 64,000 characters so now that we have this table designed with additional fields we'll click on close here we'll save the design because you are always on everything or the design you have to save the design of it however if you add new records here you don't have to save them they're saved automatically now if you remember a moment ago we added beyond telephone number we added additional fields such as email and notes and here they are so that's how you create a table and we'll go into the rest of the components in a moment here just before we move any further here's a couple of things as far as the application itself the way it works I was supposed to actually show you this first but better late than never notice you have the Home tab you have the ribbon very similar to Microsoft Word Excel and the other applications we have used under the Home tab we have all kinds of common functions here some of them are similar but some of them are very particular to Microsoft Access then we have a create tab and this is where you create and add new components to a database think of it very similar to lead to Microsoft Word and Excel where you create adding different components like charts in Excel or word art or other objects in Microsoft Word and then you have also external data this is where you can bring in or link to other systems out there like to excel other access databases other system databases text files XML files and so on and then under database tools this is where you can define the relationship between tables or between those lists and these are the different contextual tools or the tabs that shows up depending on what we are doing very similar to what we did in Microsoft Word and in Excel then here on the Left we have this navigation or the datasheet view this gives us a listing of all the objects within that database because think of the database with multiple pieces and components to it then on the right hand side this is where we'll see the actual data for that specific table sometimes it's best to just close them and so you create something new and that is pretty much to avoid problems and issues that you'll see later in this session I'm going to show how you can create a table from scratch again we covered this also earlier but I just go very briefly and also I'll show you how you can create a table by importing data from a list that you might have in Excel or in a text file like in a CSV formatted file so to create a table if we needed to create a new table here with basically just go under from the Home tab would go into the create tab and then we'll choose table notice it's very similar to what we have before now at this point what I'd recommend is that you go under the drop-down here under this view and then choose design view we'll give you the name and then notice we start with the primary key this will be the invoice ID then we have order number and so on so that is the best way to create a table now at this point we click on close Save Changes and here we have the invoices table with invoice number customer number amount and so on so now we want to bring the data from an Excel spreadsheet so let's say we have this list the customer list and instead of us typing all the names of the customers again in Microsoft Access we only just simply import this so here's the list in Excel now how do we bring this into Microsoft Access it's actually very easy if we go here under external data we're going to go and get the data from a different system in this case we're going to get the data from Microsoft Excel so we click on Excel here and then we go and look where the data is so that's where I have my data here is customer list and it's going to say import the source data into a new table in the current database or you could append it to an existing table or you could link it to another data source out there the easiest and the less problematic one is the first option here because it's going to create a table based on the fields that exist in the excel table here so with all these fields on the very top so all we have to do in this case is just click OK so we selected the file we selected what we want to do with it we click OK and now it's going to ask us how this data is going to look so you simply click on next the first always say it has the headings so you want a computer to pick what was in Excel put in the first name last name and so on click on next again and then it's asking us which one is going to be your list of customers or the primary key here actually it's asking us to rename those fields if you want to adjust the names of these columns we don't have to do that and then you can also specify a primary key so the primary key you can either have the system create a new one or we already have different IDs here and we'll say the customer ID it's going to be the primary key now you have to make sure that there are no duplicates on to the excel file otherwise it's going to error out so you click on next and then we're going to call this customers and then we'll click on finish click on close notice we have a new table called customers those two are from before we actually can delete those to just clear out confusions at this time now this point we have the table called customers and if we open it up notice it has all the customers that we had from Microsoft Excel so that's one way to bring in the data from another system into Microsoft Access into a table now let's go ahead and bring also the invoices let's say we kept track all that data as well as the Vasari typing it we can simply go into an external system and we pick Microsoft Excel we go and browse for the file and now we are going to get the contacts list for example so the contact list we choose the first option to create a new table we click OK again and then it asks us that it's going to create a new one the first row contains the headings contact number customer ID and so on press next and then here we could get a chance to rename the field names however we don't need to click on next and then you'll choose the primary key so we click on next here and then we're going to call this contacts and then click on finish so notice at this point we have two tables and then we're going to create another table because supposedly we are running up a small business and you have the customers you have different contacts with them and then you also have invoices those would be different lists or different tables or different ways that you are categorizing your information now to bring the data in this case we are going to actually do this slightly different notice different I have invoices list the invoices list this is an example of what's called a CSV file or a comma separated values file so you have for example each field and record are separated by commas actually the records themselves are separated by a new line here so now we want to bring all this data into another table in Microsoft Access and here the concept is that how do you bring data from a text file or a comma separated file into Microsoft Access by the way this is a common format in businesses out there from major large systems pretty much any system can create lists and export the data into a CSV file and then you can import that data into Excel into Microsoft Access and so on so let's go under the external systems and we're going to choose here text file under text file we are going to pick the file here and this is the text 1 invoices list and then we'll click on open at this point we're going to choose first option to import the data into a new table that's going to be the easiest and then it's asking us a couple additional questions here compared to what we were getting before this is a delimited file comma delimited score comma separated so you click on next and then we know some of them they could be tab separated but in this case we know it's a csv file or a comma separated so we choose comma so notice it separates each item here or each field correctly and now notice we mark this here as first row contains the field names then we click on next and then similar to what we did earlier we have the invoice numbered we can change the names here of these fields in the very top and we can assign a new primary key or we can choose an existing key and we click on next and then we're going to call this invoices and click on close now notice we have three tables so basically as the concept is that you can have a database with multiple tables more than one table you could have hundreds of tables 10 50 tables depending on what you're doing and how large the database is and what functions it's performing however with the knowledge that you are getting from this course you should be able to actually design a basic database and by the way that's going to show up in your final exam as well you should be able to design a basic database and run a small business without having to pay somebody hundreds of dollars to design it for you so so far to just briefly summarize we did create three tables and those tables we created actually the first one we did was from scratch then we created new tables by importing the data from different systems by using the external data tab here we use Excel but similarly you because use data from another Access database and so on so now that we have a couple of the tables created by importing the data or by creating the data from scratch at this point I'm going to talk a little bit about adding data to those tables formatting them and then I'm going to get into linking those tables together so that we can perform queries and also run reports and get the data from various tables for example one piece for example get the customer first name and last name from customers table then go and get through some of the contracts for each customer from the contacts table and then invoices as well from the invoices table so in order for us to perform that we have to link the tables before we can do these queries so first we have here both table customers and to add a new record even though this is not recommended in a business environment but if you had to this is how you add a new record you go to the very bottom or anywhere here and notice you can go from one record to the other to the other by clicking on this next icon you can also go to the very end the last record by clicking here or creating a new record by clicking on this icon here and then simply type in there you can search by simply typing a name and it will bring up wherever that specific name shows up there are a couple ways to do this differently as well that we'll cover in a little bit now I mentioned earlier one of the key components here in an Access database is the ability to link those tables together so in order to link tables and establish a relationship between the various tables you need to have established first those primary keys and foreign keys that's where planning comes into play and it becomes very important so you have for example the customers table if we go under the Home tab and we click here or right click on the customers table and choose design view notice the primary key is customer ID for the table customers if we go for it under contracts notice if we go into the design view I'm right clicking here and choosing design view we have contact number that is the primary key however notice there is also a customer ID what that is saying is that when we are recording the contracts we are not necessarily recording the first name last name of each customer we are just putting the customer ID from the customers table and that is in the customers table a customer ID is the primary key as we saw a moment ago now here the primary key here now there's this golden key here on the left for this table it's a contact number so the customer ID in this case it's actually what's referred to as a foreign key the foreign key is basically a common field between two tables or two lists in this case it's a common field but it's not the primary key on the second table if in other words you have customers that have contracts and each customer can have more than one contract same idea is for the invoices as well if we right-click and choose design view here we have the invoice number which is the primary key but then we have the contract number which is linked to to the contacts table to establish a relationship this is a very important concept whenever you're designing databases and we are creating more than one table is that you have to link those tables together and this is where most of the students fail in their project one thing before I actually go ahead and link those tables it's important that whatever data type that the primarykey uses on the first table the same thing needs to be on the foreign key needs to match so basically the foreign key here is customer ID needs to be the same data type as the primary key of the corresponding table so now let's go ahead and link those tables to link them go under database tools and then you click on relationships notice it says relationships it defines how the data in tables is related such as the ID in a field or the name and fields how they match so I'm going to pick all the tables so we just click on each one of those contacts customers and then invoices we add them all to the board basically now notice we have customers here and the customers customer ID it's the same field under the contacts table so what we do is we drag the customer ID under customer ID under the contacts table now when we drag it it's a one-to-many relationship that means that one customer can have many contacts now there are ways as well to enforce what's called a referential integrity and I'm going to confuse it too much with this but that means basically that if you go ahead and remove a customer for some reason then it should drop all the contacts as well in the case of the students if a student has signed up for courses for a semester however they leave the university then the system should also drop the courses that they have signed up for basically that you update one record or you delete one record it should delete the other ones to cascade down for now for simplicity I'm going to keep that off we click on create and notice now we have this line it's telling us that customer ID here it's a primary key and this is the foreign key on the next table now under invoices notice a common field here we don't have your customer ID so summers is not related to invoices directly what we have is that from each contract we generate invoices so what we do here is we have contract number and contract number notice that's the primary key that's the foreign key here so what we do is we drag the contract number to contract number down here so make sure you drag it directly on the name of the foreign key and then notice it's going to be a one-to-many relationship so it means it's one contact with many invoices and then we click on create and then you can kind of move this either way however you want to arrange it and then the key here is to click on close and then save this relationship now these tables are linked together what that means is that if I wanted to pull for example the names of the customers first name last name and then the actual contracts I can do that at this point once the tables have been linked at this point let me show you how you can actually create a query go under the create tab and then we click on the query wizard that's the easiest one I'd recommend for whatever you do when you're using these databases and you're learning some of the basic concepts use the wizard component so click on query wizard and then what you do is choose simple and notice we have the table customers so the way it works is that you can pick and choose so you say I want the first name you pick the next field you add it to the right-hand side and let's say I want just their email so that's from the customers table now let's say I want to go to the contracts table and let's say I want the contract number amount and let's say I want the type so that's all I want the query is basically a set of criteria that you want so now I click on next and then we click on next again and then we give it a name then I click on finish' and notice the system-generated notice you can actually resize disease the system generated based on our criteria created the new list and this is a query based on those tables if you remember each of these tables has only pieces of this information but it's because they are linked together through that relationship with established earlier and through the primary keys and foreign keys that we can generate a query here with data from two tables and you can do this with more than two tables if they are linked correctly about creating a form where we can enter the data into a table so to create the form we simply go on to create and then we can choose the form wizard so let's say I want from the customers table I want to add all of these fields you can do the molding once by this double arrow here and then I want to also add the invoices let's say until you add the invoice number the date amount then I click on next click on next again next and this is our form so basically here what we are doing is we are entering so let me close it first so once we close it we save this and notice we have customers form there's also a subform for that that's because we link to a sub table if we open the customers form notice we have the customer ID the name and also all the invoices for that customer so for example Owen here with this address has these invoices and this is pretty much how most databases work now notice we have 39 customers and you can scroll from one customer to the other by this navigation bar here in the bottom and also you can create a new customer by clicking on this new or here in the bottom so when you create a new customer you can also create a new invoice for them so that's what's referred to as a compound form again the concept here is that you can create forms once the tables have been linked together so to create a report you can again use here the wizard so the report wizard under create and then report wizard and then let's say we want customers let's say we want to customer first name last name then we want to go under the contracts and then we want to get the amount and the type so let's say that's all we need for our report we click on next next again next here and then finish under the summary options you can actually define as well the amount so you can get the sum of the amount there or the minimum maximum and so on so if you wanted that the way I did that was by clicking on summary options here and then choosing just the sum you can also sort the data by amount and then we click on next then next again and then let's say we wanted customer report the names here you would make them meaningful names so that you when you see it 50 items in there you'll know what they mean so we click on finish and notice we have for owen here he has these contracts and the total comes to seven thousand seven hundred fifty so you can format these reports to look even nicer than this but again notice it looks nicer than the query the query looked something like this just state data and the report or it's designed so it can be printed out with a date and all that that stuff welcome back to Microsoft Access 2010 in this session I'm going to talk a little bit about database queries we're going to start with simple queries using the query wizard on particular tables then we're going to focus a little bit more into the design view and creating complex queries from multiple tables then we are going to sort the data using a query by certain fields within the query selection then we're going to modify existing queries customize them so that we learn how you can reuse the same query and customize only certain fields within it and then we're going to apply certain criteria and finally we're going to create some calculated fields based on selected fields for a query so the question is what is a query in the database well a query allows you to select a subset of fields and records from one or more tables and then present the selected data as a single data sheet so think of it this way if we have multiple tables here notice we have the customers table and that has a bunch of fields here the fields are those on the top of the columns basically and then we have another table for example contracts that has additional fields of its own now what we may want at certain times is that we don't care for what company this record belongs to we might be interested in only having the first name the last name and let's say the email address of that just this data from this table then on the contracts table we might be interested in knowing the amount the date that they signed up for and then also the type of contract or what the contact was about so that's basically what a query is it's selecting only specific fields that you are interested in from one table or multiple tables whenever you go to Wikipedia or whenever you go to Google or a library system and you search for something you're actually running a query on the database you're searching for specific fields especially when you're running an advanced search you're searching for specific fields within that database first we're going to learn how to create a simple query so let's say we want to go under the customers table and to create a new query we go under create here and then we click on query wizard under the query wizard we can select any of those at this point here but we'll start with a simple one and then we'll click OK so we can choose from one of those tables notice that you can actually query within other queries but for now we're going to select the table customers and then from the table customers let's say we are interested in having only the first name the last name and then let's say that we want it only the email address by the way if you use the double arrows here it will add all those fields on the left to the right hand side and if we don't want one of those fields that we added to the right we can choose to send it back by using the back arrow and now we'll click on next and then we'll give it a name and then click on finish notice we have only the data that we requested at this point we can go ahead and close this query save it because that had to do with the design of it and I'm going to close all these other tables as well now that we have here customer query by first and last name if we add a new customer here somehow in the very end here we are going to add a new customer that's customer name and then we add more data here if we want it to now notice I didn't have to save this I can close as it's that data is already saved automatically now if I go and rerun this query and this is the concept here is that the data is entered into the tables and the query all that the query is doing is just asking for specific fields within that table so notice when I rerun this it's going to bring up my name that I have just created now you'd say why is this showing up as just first name under Caselli if we go back to the table here and this is where you direct the data notice if I scroll all the way to the bottom I put my first name on their company so we don't want that there and we want it under first name and last name once we close it rerun the query again notice that it will show up correctly so that's a simple query based on one specific table now what about if you wanted to run queries on multiple tables so here's how you do that to run queries in multiple tables you need to make sure that those tables are related or linked together and this we did it in the previous videos and if you don't understand that part of it please go back and follow the video again that is done through the use of primary and foreign keys so if we click here on the relationships notice that we have the contact the customers table the customer table is linked let me do it this way it's probably more meaningful the customer table this is the primary key it's linked to the foreign key in the contacts table then the contact number it's linked to the foreign key under the invoices table the concept here is it's only when the tables are linked correctly through the primary and the foreign keys that you can run a query across multiple tables and this is what I mean so if I close this saving the relationship because I rearrange this layout here you say yes if I go back and create in your query here so I choose a query wizard choose simple query and let's say under-the-table customers I go and pick the first name last name that's the only two pieces of data that I want from the customers table now if I go under the table contracts I can go and get the contract amount the date and the type and then I go ahead and click on next give it a name here and then click on finish at least at this point as soon as I hit enter I clicked on the query here it picked the first name last name and pretty much the fields that I had to request it if I go ahead and close this go back here and double click on it notice that's the right one here and I have the data that I want so that's how you run a query using the query wizard if I wanted to modify this query that I have created already what you can do is and we'll learn this in the next video here you can go under the View tab and then choose design view and that's where you can modify further things as far as the query if you don't want the field if n I'll cover this in the next video that using the design view for queries in this session I'm going to talk about item two here using complex queries by utilizing the design view in Microsoft Access databases so let's go back here to access earlier we did simple queries using the query wizard through one table and multiple tables so now let's say we have three tables here we have the customers table you have the contacts and they invoice this table now if we wanted to create a query here we go on to create and we're going to use a query design the query design notice it's going to slightly different from the wizard that we learned earlier we click on it and now at this point it's asking us what tables we want to utilize for this query so let's say we wanted a table called customers and then the table called contracts and then we'll click close we can choose invoices as well if we wanted but at this point we don't need it I'll click OK now notice it shows us the relation between those two tables and also it shows the fields now what happens is that notice in the bottom here we have this area where we can actually add the different fields now if you double click on this star here on the top left that means it's going to add all the fields of that table it's going to execute a query for all the fields and by the way this is more visual and this is more useful and this is what you'll use most of the time if you are designing new queries in business or for personal use and so on so in this case we are going to click on or double click here on first name so let's say we want the first name in our query notice I'm double clicking on it and then last name and let's say I want the telephone number and then the email address let's say also I wanted the contract type notice I'm picking this from another table and that's because they are linked together from earlier let's say I want to see what the results will be now what I can do is you can click here on this icon there icon or you could save it and then double click on the saved query here but for now we are just going to run it to see how it works and whether it works so click on run and notice it has posted the data that we requested in that query now if we want to go back and modify it again and add another field what we do is we go under the view here we are in the Home tab and then the drop down here under view and then we'll choose design view under design view then we can pick something else like one of those other fields or if we don't want one of those fields we could simply delete it from here by pressing delete select in the field and press Delete and then if we rerun it notice that field is not no longer going to be executed or included in the query now let's go back here and go to the design view and we're going to add for example the amount and the date and then the contract type and notice at this point we have two four six seven fields that we have selected if we go and run it to test it we go ahead and click on run and there is our data if we are happy with this query what we do is we click on close here on the top right and then it will ask us to give it a name so now again we will give it a meaningful name so we'll call it customer info and then click OK there might be times where you might want to modify this existing query so what you do is of course we can run it and see what it does but you can also go here to the home tab there are a couple ways to do this so once you select the query you can right click on it and choose design view on the design view this is where you can add more fields or delete more fields like we did before so the concept that I'm trying to show you here is that how can you go back and modify a query again it is by right-clicking choosing design and then you and add more fields and delete other fields so let's say right here I wanted to add for example the state address city state zip I'm double clicking on them now notice we it actually added them farther down in our area here so what you can do is select what you want select those fields and then drag them farther to wherever you want them so notice I just was able to move the order of them so now if I rerun it notice I have the first name last name their address city state zip and so on then if we are happy with the design of the new query here we close it and then we click on to save the changes for the design again in Microsoft Access we just save changes for the design the data is automatically saved as soon as you complete entering one specific cell or the data into one of the cells so the next session I'm going to show you how you can actually add criterias to the queries in this session I'm going to talk about item number three here sorting data via a query and then also we're going to apply some criterias to those queries in an Access database so let's go back here to access and let's say I have a query as we talked about this also in the previous video so we have a query that we design from earlier and this query has the specific fields it has the first name last name street address city state and zip and telephone and email those fields are from the customers table but then we were able to also go and get the contact amount and the date on the contact was signed in the contract type all this data we got it from the contacts table so notice we are pulling data from two tables here and by now you should know why we are able to data from two tables let's go ahead and modify this query so what we can do is we can click here under view on the drop-down and choose design view or the easiest then this is a better way of doing it probably is to right-click on the item and then choose what you want to do so in this case I want to choose the design view so I click on design view and now we are going to change a couple things here let's say we wanted to order the customers or that list that query we want it to order it by last name so all you have to do is to sort it all you have to do is you click on the drop down here under the sort row and let's say we want ascending order by last name if I click on run notice that all the customers will be ordered automatically by last name now let's say I changed my mind and I want to order them by first name so what you do is you go under the view here or design view or right click and choose design view again and then change it so your order by first name in ascending order and then take the order out for this field there are ways to order by two or more fields here as well and keep in mind that the first column here takes precedence so it's going to order by this first and then aside the other one and so on let's go ahead and run this and notice now we have all the a s and so on pre-order that's how the ordering goes now let's go ahead and specify some criterias to our queries so let's say we wanted to see only the customers that are within a zip code notice before we are talking about separating in one of the previous videos I was talking about when you enter the data into a table make sure that you select or you enter individual pieces of that data in separate fields for example not the whole address in one field you separate the cities state zip and so on so that you can actually or did later to sort the customers later so notice in let's say we want to see all the customers that are living in the city of Holland for example so what you do is you right-click on the query here and then choose design view and then under city you notice right here it says criteria we just type the word Holland and then we run this query notice it gave us all the other fields along with just the customers that are in the city found let's say we are not interested in the field here for telephone number and all that type of stuff if we don't want that to show up what we do is we go back to the design view we could either delete the field completely for example for the back here the telephone and email or we can uncheck it in this check means that it's going to show or not show that field in the datasheet view one will you run this query so that's how you hide and show the different fields so if I click on show notice at this point the telephone number will not display anymore now what about if we wanted to go back to our query and if you remember from earlier if you remember from earlier we had customers from other cities as well we had the let let me display all of them so if I take this criteria out rerun it notice we have customers from Holland here and also from the city of Lansing and other ones as well so let's say I wanted to display only the customers from Holland or Lansing so what you do is you go back to the design view and then right here under the city we add criteria Holland and then down here we type also Lansing and then run the query and notice at this point we have the custom display from just these two cities so this is a type of criteria filtering it by two items within the same field now here's another thing that you can do here notice that these by city here they are not ordered together in certain order like by Holland first and then Lansing next and so on to change that or to fix that what you do is you right-click you go on the design view and this is what we learned earlier and let's say we want to order this in ascending order by city so we rerun this and you notice now we have all the actually we don't have them ordered correctly at this point because if we go back to the design view notice if you scroll to the left we are sorting this stuff also by the first field so that's why I was saying earlier that the first left hand side field takes precedence if I take that out this sword in front by first name now notice these will be sorted correctly by a city so we have all the home customers and then all the Lansing customers now you could go and modify this even further so let's say I wanted to get customers from Holland and Lansing but with incomes or contract amounts more than five thousand five thousand or more so notice we have some here with less than five thousand so the way you change that with adding a third criteria to this query is by going back here and then we we have those two criterias right here Holland and Lansing and then we are going to add the third one and the third criteria it's going to be by amount so if we go under contract amount and then we add here notice criteria on this same row on the right spot here we add greater equal five thousand and then run this notice it's going to display only the customers with greater or equal to five thousand actually we have here one with 300 and the reason for that is because we are telling the system notice the reason why it's giving us the 300 is because we have asked the system to show our lamb sing so it's got to be a criteria of holland and greater than five thousand or lansing so we could either take out lansing here or add also four so we'd have to add either the second criteria here under or which is not very common or just take lansing out and then adjust the criteria just so we see the results so this point we have the criteria is city hounds and then all the greater than five thousand if I run it notice it will have the right numbers if I go back under design view and add Lansing that I need to probably put here Prater than five thousand for Lansing as well and then run it and then it will give me only the numbers greater than five thousand so that's how you run it with multiple criterias now this is limitless basically you can have any type of combination here you can add other types of fields from these tables you can add even more than two tables if they are linked together correctly just keep in mind that queries are pretty much almost limitless depending on how many fields you have and how many combinations of those fields that you want to put together in this session I'm going to show you how you can run a criteria from multiple tables in this case three tables we have the contacts table the customers table and the invoices table they are all linked together and I want to pull let's say the first name last name those are from the customers table then the contract number and that will be under contracts and then the invoice date invoice item invoice amount and whether it was paid or not it is from the invoices table now how we can pull a criteria like this how we can run a criteria like this and then also do some more things with this whether the invoice was paid or not and even create a calculated field so that if the customer did not pay this invoice you charge them a late fee for it so let's go ahead and design the query so I'm going to close this and not save the changes here so here's how you create the query from the thate abel's we know that the tables are linked now we go under the query design here and then we add the tables so the first stable we can add the tables in any order really doesn't matter so I'll add the first one go to the second and then the third table then we click on close now let's say that the first field that we want in a query is the first name then we choose last name so I could double click on it and then let's say I want the contact number or whatever reason to it as reference I guess and then we can choose here the invoice date the item on the invoice the amount and then finally if it was paid or not by the way you can make these bigger by just dragging it down then double click on paid or not and then we can go ahead and run and see what this looks like click on run and notice we have pretty much what we had before so we have first name last name number date item and then the amount and whether it is paid or not now of course you we could go and arrange this in order for example whether it was paid or not and then we'll go here under design view either by right-clicking on it or just view here then design view and then we need to scroll to move this actually up and then we can go ahead and order this in ascending order now if I run it now this they're not in order now most likely it's they are not in order because we have another field that is doing the ordering actually chose the amount here so let's go back and we want it whether it was paid or not so choose ascending run it and now notice we have all the knows now what about if we want it to display only the customers that have not paid because these are the ones that we want to notify and eventually we want to even send them a letter tell them that there is a penalty or a late fee for the payment that the invoice was not paid so first let's learn how we can actually limit only those that have not paid so if we go back here under view and then choose design view we can go here whether it was paid or no now remember from before when we chose the customer city we ended for entered for example Lansing or Holland now in this case we have a yes or no so the invoice was either paid yes or not paid no so if before we enter the city name Lansing or Holland to limit it by in this case if we wanted those that have not paid that criteria would be here what we put in here would be just simply the word no because we want to display these fields with customers that have a criteria of the invoice not paid or with a status of no so now we run this and know this we have here just those customers if I scroll down there are no customers that have a paid invoice and what about in this list we do not even want to see the word no here or these fields no but we want to display only the customers that have not paid their invoices what you can do again we can hide this field and if you remember earlier we could go here under design view and by the way I'm not right-clicking on it because we have not saved this query yet so if I go ahead and choose to save this so invoices we give the name now we can right click on it and choose design view so that's why I was not clicking on design view there if I don't want to see the field no I can just uncheck it here it's still gonna run it in that criteria but it's just not going to display it so notice we don't have that field you'd say well why do I want this list this would come in very handy for customers that have late payments and usually as most companies do nowadays those that have not paid their 30,000 bill or more or less here you want to charge them a percentage fee so now in the next section here we are going to learn how to calculate or to add another field here that will calculate the late fee so here's how that is done so we go into design view right click design view or view and then design view on the top and then we're going to add a new field over here so to add a new field what you'll need to do is you need to click on this builder icon here on the top and then on here what we are going to do is the amount so the invoice amount double click on it and then x times is the asteroid let's say we are going to charge 5% 0.05 and then we click okay now notice it says here exp R and it has all kinds of other stuff behind it what we do here is we can type there just leave everything alone and right here where it's a exp r1 just that section we want to change that to late fee and leave the rest alone so if we go to the right here notice it's doing the late fee it's calculated the amount times 0.05 so then if we run this query know this we just created another another column that we added so for this customer that has a thirty thousand dollar bill the five percent comes to $1,500 so now the question would be how can we format this and by the way that is how you create a calculated field in Microsoft Access and you can look this part again in the video just rewind it and see again how it was done so now we are going to learn how we can add here the dollar amount or make or format this field as a currency field so we'll go back here under the query design and go into design view and then what you do is there is a property sheet over here nikon on the top right click on it go to the field that we want to modify the formatting for it and notice it's giving us the properties for the late fee field or for the invoice paid field now notice for these other ones like let's go to the amount here the amount was formatted earlier in the table into the dollar amount so now we are going to go here into the late fee and we're going to format this click on the drop down and we're going to format that into currency so the dollar amount and then the decimal places you can specify those as well so you can mark it at two decimal points so now we can close this a property sheet rerun the query and notice the late fee has been formatted as currency at this point we could save this query we could actually even click on save up here and that has been saved at this point because we have not saved it yet earlier and if I close didn't notice it doesn't ask us to save it again now if I go here under and run this query quickly notice I have all these late fees what about if I wanted to get the total here on the very top for these late fees and this is what we're going to learn next getting the total for different fields so let's go ahead and run this query again notice we have these late fees here and now let's see do we want it to see the totals in this query so in the very bottom here we want the total so what you do is you click on it and then go to the Home tab and then there is this option here for totals so we click on it and then it's going to give us one more row in the very end for us to specify wire what totals we want so let's say for the late fees we want the sum here and then also for the invoices we click on the drop-down and we want the sum of those invoices so then we save it and whenever we rerun it and those numbers change those will be updated automatically so if I go here under contracts and I modified notice that this was the total here ended with 850 1.3 million and 850 actually I needed to modify the invoices because that's where the invoices are pulled from so the amount here for example it says 70,000 I go and change that to $1 as soon as I update this invoice is amount because again the query was running from the invoices before close this run the query again notice that has been changed so now it's 1.39 $851 compared to what it was before so those are some of the queries that's a limitless like of what you can do and how you can run those queries I'll say play with them but this is a general idea of creating new fields calculated fields and by the way you can go ahead and change the design here and let's say that you only charge them 7% late fee you'll notice it from 65,000 it's going to change when you modify that so if we go back here to our design and we go to our calculation so instead of 0.25 we make it point 7 percent and then run the query again notice that from 65,000 now we have 91 thousand in late fees so check those out and it's a lot of fun actually playing with the queries and generating all these types of data in previous videos we have talked about different components of a database that we have tables queries then we have forms and reports in this session I'm going to particularly focus on forms forms are very similar to the function of input in a computer and forms are utilized to primarily enter and view records in a database so your assistant in your office would actually be utilizing the forms for looking up records and modifying content of those records and also entering new ones it's not recommended that the data be entered directly into the tables it's usually done through the forms so the question is how do we create a form so the forms can be created either based on a table and you can have more than one form per table or multiple tables and you can customize those forms as long as the tables are linked together as well if you are utilizing data from multiple tables so let's create a form here for just the customers first to do that we can utilize the form wizard so we click on form wizard and then let's say under customers we want customer ID company first name last name and basically all the fields the reason why you might want all of them is because you might want to update all the information about a particular customer however if you don't want your assistant to modify certain fields then you can create a form with just the fields to modify so now we click on next and then we click on next again and then just give it a name then we click on finish and this is the form at this point notice you can move from customer to customer by the way let's close it first and here it is customers simple so that's the current form you can navigate from customer to customer you can also filter or search by a specific name so let's say I click on first name here and I type Sally now this will pull up the records for this specific name and you can create new records by clicking on this last icon here you can make certain field required in a form or in a database or table but for now we're not going to mess with them because they're just learning basic concepts so now once that has been entered if we go back here on customer 42 that has been updated that record has been updated now how do we make this form a little bit nicer so what you can do here is that you can click on the Home tab and then under the Home tab you can click on view and then you can change it to the layout view the layout view allows you to modify those specific fields and basically format that form to what you may like or what you may want there are also predefined themes that you can pick and choose here and it will change the color theme you can change the fonts and other components as well and you can also add new fields however it is not recommended if you add new fields directly from the form the design needs to happen usually in the tables that's why when you're designing a database you would think and look at all the different angles as to how your table is going to be designed what fields are needing to be included and so on because it makes it so much simpler to create the forms to create the queries after you have a good structural design of your tables and of course you can resize those fields if you need it to as far as the formatting you can change the formatting here you can add a logo you can change the title of it change it so this customer simple change it to something else and so on so it can be modified quite a bit you can also move those fields around so if you make the form look as nice as you want it so it's a matter of that I can draw up here there is also the design view here if you click on the drop-down and choose design view that is where you can kind of thinker more and adjust those fields more precisely in different linear locations and so on and notice you can drag the whole data here so if I go ahead and close this save it then open it up again notice it has been adjusted of course it doesn't look great at this point but you can modify it so it looks better than this again the tick here is going under home choosing view and then going between those different views now this is a simple form this is just for one table now if the tables are linked just like in queries you can also create a compound form so the form here what you do t'lie is is use again choose the wizard for the forms so let's say we want the customers we want customer ID because the company first name last name that will phone stayed address and so on then we want let's say the contacts table we want let's say the amount actually the date and type if you remember when we link the tables together those tables add a one-to-many relationship that means that one customer with multiple contracts so in this case what when we click on next here notice that we can arrange this by customer so let's say you have Hubert Sims and then he would have all his contracts under him and not the other way around even though you can modify it to look like that but the design of the table is so that you have one customer with multiple contracts or one student with multiple courses that they have taken so we'll click on next here so basically it's going to look something like this customer and the amounts and contact amount here click on next next again and then we give it a name and then click on finish at this point notice we have the customers their email address and so on and then right below we also have the contacts for that specific customer so if we switch from one customer to the other notice it's going to change their contacts or it's going to list the contacts that those specific customers have so now you could either add a new customer or you could just add a new contact for this specific customer for example for a mall or however you say the name here you would add a new contact let's say 70,000 so one thing to remember here when you're dealing with these designs of the new forms and so on is to make sure you close the form at one point and then reopen it so it saves the design before you go and add any new contacts this is one of the things that will probably confuse you if you forget to remember to do that part of it so now let's say for example for a mall here we want to add a new contact so the contact let's say it will be the contact number 45 43 and that's the customer number so the customer number it's basically is going to match the customer number up here 11 0 14 so that's why it's pretty populating it and then let's say we add a new contacted the $7000 and then you can put the date in there and then what type of contract it is and then move on to the next customer basically so this has been registered at this point and it's all good to go so that's how you use the forms to add so it's basically the idea here is that you use the forms to add data retrieve data from different customers and update their records and at this point if you were to go back to those queries that we discussed earlier you would be able to see those contracts reflected so first of all customers of contracts if you were to search for a mall notice there is a 7,000 and landscaping that we created earlier so that's forms we'll have reports in the next session as I've mentioned in Prior videos there are primarily four basic components in a database there are the tables the queries the forms and the reports in this session I'm going to focus a little bit on the reports so how you create a report and how you modify those reports we have three tables here and a bunch of queries you can run a report either on the tables themselves multiple tables if they are linked together or you can run the reports based on different queries so let's try first running a query and running a report from a table so you go under create and then you choose the report wizard that'll be the easiest and then you pick here let's say customers let's say I want the list of customers by first name last name street address city state zip let's say I wanted to do a mail merge for these and of course if you wanted to do an email merge you could utilize the email field as well say click on next and then next again and then you can order those customers by first name last name or however you want them in a different order and then just choose how you want those fields laid out so just customers I'll name it for now then I'll click on finish and notice the customer have been listed and it looks a little bit nicer because you should be able to print this out it's designed to be printed out so think of the forms as the output in a computer but in this case it's in databases so you have the reports so now if you wanted to modify this form of show this to action in a moment by once I create a form a report that actually links to more than one table so if I'll close this close this other thing as well now let's go back and create another report we've got on the report wizard we go under let's say customers again it's the same idea that we have been doing here with queries as well same thing and let's say we want it first name last name and then we go under contracts and let's say we want the contact number amount date and type let's say we want next and let's say we want to order this by customers click on next it's very similar to the form that we saw earlier and then we are going to order this you could order it any order a priority here click on next let's say we want it by contact amount and you could even posts on the report let's say the total for the contracts then we click ok next so basically play with the settings here what you see on the screen next and let's say give it a name to the report then we click on finish now notice we have here the report with some summaries and a bunch of numbers now notice it doesn't look too great because I should probably have picked the landscape formatting so to change the formatting here you can click on landscape under page layout and let's take a look again and it still does not look that great now the next thing that you can do here to improve the looks and the feel of this is to go under close the preview screen here and then what you do is you go either under the layout view or the design view so that's the report view you go down under view here choose layout that'll change the layout we can tinker with the layout a little bit so notice now we have to adjust these columns and whenever you see those number signs that means that the field here it's not long enough so you can modify this to how you want basically notice so we can go here and make this much wider can go I change the formatting of these here to be a different theme and then you can also if you go here under view and choose design view you can also choose to modify those even further under properties notice we want to let say transparent and here we're kind of getting into more complicated stuff just keep in mind that you can go to different views and customize different components at this point so now we click on view your the report notice it doesn't have those lines and it's a little cleaner than before if you don't want a certain field if you go back to the layout view and you don't want certain component here just delete it and it will clean it out if you want let's say the some field to be showing up somewhere else just drag it and it will show up if you want to change the name you can do that as well format those go into the home click on that click on the color and notice now the report has been modified so that's how you modify the report how you create a report from the fields from different tables and of course you could make this look a lot nicer than we did in this section so far now one last thing I'll show you regarding reports here is that you can actually create reports based on queries if you remember earlier we had customers that have not paid their invoices yet and that also that they have a late fee let's say the boss wants to see a nice report of this of course you could print this out and so on but what you can do is it's a nicer thing to do it will be to create a report based on this query so what we do is click on create then go on the report wizard and then we choose the query invoice paid no that's the query that we have clicked last by the way that's the trick if you want that to show up first now we want to add let's say all the fields here then we click on next then it's going to order them by let's say customers next next and so on you could modify this further if you want it and then name it and here is our report so basically what it did is that it took each customer it shows us the contract number first and then the invoice is for each contact and and so on for example for an CEM we have this contract and this one has two invoices notice these numbers again like we learned earlier you can go ahead and tinker with them click on close the preview go under view change the layout and make these fields a little longer or nicer so you can actually read the content it's a matter of playing with it at this point and adjusting those numbers and the widths of the columns so that's how the reports work and again to keep in mind is to output the data in a nice format there's a lot that can be done with Microsoft to access and what we have covered in the previous videos is just scratching the surface for those of you that have not watched the previous videos I'd recommend that you go and check them out at this point I'm going to show a couple more things here one of them is exporting data into Microsoft Excel so if we go here under external data we can go ahead and actually send something so let's say we have this invoices paid yes or no notice that's this data you can actually choose to send it to excel or to a text file or email and so on you can also right-click on this stuff and then choose export and then choose Excel so either way it will work so if I choose the first method here select the query click on Excel then it's going to save it to a specific location and you can even choose to preserve the layout let's say we want to preserve that and then it will open the destination file when it's completed now we'll just click OK and the process is complete so now in Excel you what you could do is you could take this data and sort it out and adjust it and work with it any way you want like you used Excel before create charts and things of that nature so that's one of the options another option is to create a mail merge from a query or a table that you have in Microsoft Access a couple ways of creating the mail merge you can either go into words first and then link to the database and then run the mail merge from there so that's one of the ways the second way and it's a little bit easier actually is to go into access select what table you or what query you want to utilize for example I want here the invoice is paid yes or no and to make things a little bit easier here I could actually go and add the addresses if I wanted to so if I wanted to choose design view and I want to go and add the addresses I could if I was going to actually send an actual mail letter basically so if we go here to the right they'll be in the very end and it doesn't matter the order at this point so let's say I'm happy with it save this if I run it it will also have the addresses so now let's go ahead and create a mail merge from this one so we have this query and we go under external data and then we choose to export this data into a mail merge because you remember this was the import before the beginning of Microsoft Access what we did and then here we are going to choose a mail merge double-click on it it asks us do we want to link it to an existing Word document that means that if we have an existing document form letter like a letter that we are writing to our customers and so on we could utilize an existing one or we could go and create a new one from scratch so whether they use the second option here we'll click OK and then it opens Microsoft Word at this point it's just asking us it's kind of simplifying the process the linking has been done it's asking us whether you want letters or email messages or labels or what do we want so I'll choose letters then I'll click on start the document then I'll tell the system that I'm going to use the current document even though it's blank then we'll choose the next step select the recipients the recipients are already selected it's kind of linked to Microsoft Access because we started the process from Microsoft Access and that part is complete usually you have to pick it if you started from Microsoft Word so for now we are all set with the current list here no no this invoice is paid no that's the query in Access then we click on write your letter then at this point it's asking us basically to insert the fields so if I wanted to insert the fields I can go ahead here under insert mail merge field and let's say I want the first name space last name then I want the address now this doesn't matter the order and then I want the city comma state and then zip and we could say tear and then put the first name of the customer and you could have let's say the amount here and we want to put a dollar sign in front of it so it's formatted correctly and let's say we want the late fee posted as well and then you could put it in word invoice number we actually don't have that listed here but we could choose contact number for now now at this point with your company logo you could format this and make it nice or a different font and so on and adjust it the way you want it you could preview your letters and they're going to look similar to this if you wanted to format this nicer notice I forgot to put the dollar amount there or a dollar sign you could do that and you're ready to you could exclude certain recipients then you could complete the mail merge so click on next step complete it you could print them out or edit the individual letters I recommend that you choose edit to just look at them first and now notice this is how it's going to look so that's how you create a mail merge from access using Microsoft Word and one more final thing before we finish here with Microsoft Access let's go back in Microsoft Access from time to time it's worthwhile that you actually do compact and repair the database what that means is that it goes and checks it and verifies the data and so on the other thing that you need to do is that you need to make a copy of the database so that you have a backup of it because if you are collecting so much data and you are also spending so much time on it you want to have good backups of the database so basically you need to have multiple copies of it it's important also to understand that when using a Microsoft Access database you can have multiple users use the same file update modify and view the data from the same file unlike Microsoft Word or Microsoft Excel so that's it when Microsoft Access hopefully it was beneficial feel free to refer to the other video tutorials on learning these different features that we have covered so far in Microsoft Access

Channel: Kaceli TechTraining

Views: 80,435

Rating: 4.9024391 out of 5

Keywords: Microsoft Access 2010 tutorial, microsoft access tutorial free, full tutorial microsoft access, access tutorial, office 2010 tutorial, microsoft access easy, skaceli, office 2010 tutorials, microsoft office 2010 tutorials, access 2010 tutorial for students, queries, tables, forms, creating a database from scratch, learn how to use microsoft access, access tutorial for workplace, reports in microsoft access, linking tables in access, database table relationships, database concepts

Id: p100t0UtQu4

Length: 101min 10sec (6070 seconds)

Published: Mon Jan 20 2014