Microsoft Access 2016 Tutorial for the Workplace and Students: Part 2 of 2 for by Sali Kaceli

[Music] [Music] in this session I'm going to demonstrate how to create a compound form to input data between two or more tables in Access 2016 so once we have linked the tables and define the relationships in a database then you can do a lot of wonderful things and you can enjoy working with Microsoft Access and now we have linked those tables together and we have defined the relationships to basically create form forms they can be created either or not an individual table for example if I go here under create and like you'll under form wizard here and let's say I want customers and I want to create a new form for all my customers click on next and the next and then next again so there is my form for the customer so this is a simple form in Microsoft Access to create a compound form and by the way the form now has been saved here under forms customers go under create here go under form wizard and then we choose customers let's say I want everything from my customers table and then I want also data from our contracts table and here under the contracts I want to make sure I have the contract number I don't need the customer ID because it's going to be in the customers table then I want the contract amount and then the signing date and then the type as well and then click on next at this point it asks us how do you want to organize the data we want to see basically since our customers are going to have more than one contract so want too many relationship we want to organize it by customer so we have customer one and then you can see three or four contracts customer two one contract or five contracts or 15 contracts or whatever they have and then we leave everything else alone here and then click on next then here we click on next and then we give it a name so we could actually say contracts by customer and then click on finish by the way make sure you give a meaningful name there and now at this point notice what happens here so we have the customer on the top here their customer information but then right below this customer we also have the orders or the contracts you could go and add new contracts if you wanted here so you could have and such so now for for this customer we have a new contract created as well so basically at this point your assistant can use it to look up customers and their information or you can use it to update the new contracts or new orders related to that customer or your customers notice you have also these controls here so you can go from one customer to the next so you go next here and by the way you'd have to adjust this so if you click here on customer around the customer area then we can go from one customer to the next and so on if we want to go from one order to the other and add new orders you have also those controls for this subform this form here is a mechanism to enter new data or view data you have the main form which is contracts by customer but then right below it you have the subform which is pulling from the contracts table so we have the data from two tables being displayed at the same time and then you have the main controls for the main table over here and you can create new records as well if you need it by using this button right here and then in the bottom here you have the actual orders or the contracts for the customers to update data in here whether you want to change that your comments or whether you want to add new forms or new customers from here now remember all the data as we enter from here it actually goes and gets stored in the actual tables if we go here under customers we should have this you know customer that we just entered earlier so that's how compound forms work and how you create them and update them and use in the next session and we'll show you how the customer is the form in Microsoft Access 2016 or your in this tutorial I created a form called contacts by customer this is what it looks like so far and the purpose of this session is how to customize this form there are three different views for customizing anything in Microsoft Access here on the left hand side you have the layout view and then you have also the design view one thing is you can do definitely is you can customize this and make it smaller or bigger or however you want to resize this so let's say you want to make the text there format it's like it different and you have the formatting tools and basically change it however you want very similar to Microsoft Word now the next thing that you might want to do here is it that you don't really need this customer ID data so big and such so you can resize it and we are doing this by using the layout view the next one is go through each field here and customize this to your liking additionally you can change the formatting for this so it'll be a little bit easier to read you want to make sure that you're just only the stuff here on the left and not elsewhere and basically you're formatting this to whatever you want so you're customizing it to your liking since we are formatting there isn't changing the layout view notice that we have the form layout tools these are other contextual tools related to this form in Microsoft Access we have these different tabs here that you can change and tweak and such but one of the cool things here is is that you can apply themes instead of you spending all afternoon tweaking this and changing this probably the width of these fields customize them manually here as far as the colors inside what you could do is you can school and simply apply one of those themes so you pick from one of those themes here and notice it's going to change the design whether it's the font or whether it's other components related to this form so pick one of those themes and then further adjust it and then notice that you have also various color schemes here that you again apply didn't change too much but you get the idea and then you can apply also the different fonts as well so that's one way to customize this if you are a to tinker and insert images and such you can utilize this and you add logos and such and then you could click here under property feet and this tells us where this field is linked to or it controls the linking to the table itself so for example right here it says that it's linked to the comments field on a customer's table and then notice there are lots of controls here that you can change typically you don't want to tinker with this or to change this to something different because then it will break that link and then the data will not be updated now this is one way to customize this form the next way to customize it is going into the design view and this is another mechanism so I'm going to save these changes first this is a little bit more in-depth that shows the data where it's linked behind the scenes with what fields and you can line things exactly the way you want them appropriately and all that type of thing you notice that you can change here colors and alternate rows and background the image and all that type of thing and the fill and all that type of stuff that is more advanced that you can resize stuff move things around if you need it to but in a nutshell that's how you customize you customize it by going through these different views that we just went over here once you are all set with a design and such then you can close your design save the changes and then open it up again and it should have applied the changes that we made earlier you want to make this forms as nicely as you can so that your staff that is utilizing these forms is actually using them effectively and also it's pleasant and easy for them to enter the data keep in mind that you can create more than one form based on the data so if one of your assistants needs access to only a couple pieces of data here then you create a form specifically for them and for others you create a more complete form and such so hopefully that makes sense and that it was helpful in this session I'm going to briefly demonstrate how to search for specific records by a specific field via a form in a Microsoft Access database particularly in Access 2016 so supposedly this is our form here and we have contracts by customer and now your assistant is keeping track of your customers and also keeping track of orders and such and now a new customer called and how can she pull up the information for a new customer so you can pull up the information by using a variety of methods here by using any of these fields all that the assistant would have to do is click on a field let's say searching by customer ID she simply needs or he needs to simply click on the field here click on find and then type the customer number so for example 11 0 40 then click on find next and then though this we have that specific customer in here the other one the thing that you can do or she could do it or anyone could do here is searching that say by first name or by some other field so I'm going to go back here and let's say by first name you can click on the field there and then choose Michael fine and you can have this over here as well and there is Michael Ingram phone number and information related to that specific user then you can find additional ones if needed as well so notice there are two records with that you can also filter the records by a specific field so let's say you wanted all the records to be displayed the customers that start with the name first name Michael so you can select it here and go under filter and then you notice you have all the different options here so you can simply uncheck them all and then pick what you want here let's say Michael and unfortunately it's beyond the recording area here but you can get the idea so it'd be Michael or whatever but let's say Jessica here I'm not sure that there is more than one Jessica but let's say Jessica and John and then I'm gonna check Michael here now notice there are three filtered results that showed up here and we can go from one to the other to the other so there were two customers or the name John here keep in mind whenever you have filtered stuff it's going to display only what you've filtered by and then this field right here where it says filtered it's going to be highlighted there you can do that with any of those fields by either searching and finding the records from up here or by filtering from this option over here [Music] in this session I'm gonna demonstrate how to create queries from multiple tables in an Access database we'll create those queries using the query wizard and then in the next session we are going to do the advanced query method in order for us to create queries from multiple tables we need to first make sure that the tables have been linked via these relationships here so you can check that from the database tools and then relationships and you should see these links and notice here that we have the customers table which is linked to the contracts table so let's say that we want to create a listing or a query as the technical term is of the customer ID the first name last name street address and then the zip state and then we want to also list the contract information for these customers so here's how we do that we'll close this first and now we go here under create and then we go under query wizard and then click on OK and then we are going to go first to customers we're gonna pick customer ID first name last name street address city zips and then we go to the next table here we go under contracts and then we'll pick up the contract number and then the amount date and type then we click on next and then click on next again and then here we could have some kind of meaningful name and then finish you notice at this point we have the list where the data that we requested customer first name last name and such and the data has been pulled from both tables so that is creating a query using the query wizard you could do also the similar thing as well if we save this now we can go and create one for invoices so let's say we're gonna see the contracts and how many invoices remain to be paid so we go here under query wizard and then go under simple query wizard again and we can do this with two other tables so we can go under contracts we can get the contract number and then we can go under invoices and then pick the invoice number a date and whether it was paid or not and then next and then give it a meaningful name click on the finish and here we have a contract number the customer ID the contract amount date the type the invoice specific number the date that the invoice was issued and whether the invoice was paid so next we are going to learn how to utilize the advanced queries in a database in this session I'm going to briefly demonstrate how to utilize the query design in Access 2016 so far in our access tutorial we have learned how to use the query wizard in defining and designing a query but in most cases in Access 2016 and previous versions of access a more effective way to utilize queries and design queries is to use a query design the way that works is that instead of you going through step by step and adding specific fields in a query you can actually design this query using this method so to utilize the query design we click here on query design icon and then the big idea here is is that you pick the tables that you want to work with initially so in our case here the tables have been linked by using the relationship module that we saw earlier and now we'll pick the tables that we want to utilize and then click on OK typically the relationship looks like this so we have customers then we have contracts and we have invoices the way it works is that you have these tables here with all the different from each table and you pick specific fields from each table and you're creating a new query so for example we want here first-name lastname and let's say the invoice number and typically you can either double click on these fields that you want to add to the query down below or you can simply drag these fields down here like we did a moment ago if you double click on this asterisk sign it will insert all the fields that are part of that table now here we want for example the the invoice number the contract number and I'm double clicking at this point the item and the amount and then we want also whether it was paid or not now let's assume that these are the fields that we want in our case now you can run this and see what it looks like notice you have the first-name lastname actually they're kind of backwards you can readjust that we can go back and readjust it and we have invoices paid or not if we want to tweak this query again we go here under design mode and go into design view and then tweak this again so if we want it for its own first name to be first simply drag this to the left and once we move the fields the way we want or customize this view the way we want then we can run this again and now know this first name is in the beginning and then you have last name and so on so that's how you briefly you as the query design in Microsoft Access 2016 and an Access database notice as well if we go back here to the design view you can define the sorting order you can define specific criteria whether the criteria is either or or different various criteria by various fields here and that's what we'll learn next and before I finalize it's completely here notice also there are additional parameters that you can utilize here and we'll cover this shortly as well such as the query builder and the totals field and such in this session I'm gonna briefly demonstrate how to utilize criteria how to define the criteria within a query in Microsoft Access 2016 using a query design so here's how it works let's say we have this query here called invoices and contracts actually we have not named this yet it's query number one we go here under a query design view and now let's say that we wanted to see for example only the invoices that have not been paid if we go under the invoices table and notice here that this is a yes or no field to enter a criteria so we see only the invoices that have not been paid we go here under the design view and what we want to do is here under invoices paid notice there is an option for criteria there is a row here that we can put a criteria for this specific field so we want to display only the invoices that have not been paid in that case we want to display those that meet the criteria for no so we simply have to put no in there and now click somewhere else outside of this area and then around this query now notice it's displaying only those that have not been paid if we want it to display the text here whether yes or no or invoices paid earn with the wording no next to it just to be sure we make sure that this check mark and by the way that should be there automatically make sure that it has the check mark right here now if we go and run it again notice it says invoice paid no and it's displaying only those if we want to hide that field we go back to the design view and then just take out the check mark and then run it again and it will not display so that's how you insert a criteria within a query use the query design now you can have multiple criterias as well if we run this query right now notice that there are invoices ranging from a thousand dollars to more than thirty thousand here so let's say we want to see only the invoices that have not been paid of greater than ten thousand dollars so to insert that criterion we go back to the query design here and then under the amount for the specific criteria we want to put them so the both criterias would be met we say greater than equal ten thousand and then click anywhere outside of this field and then around this so we are saying we want the criteria all these fields plus the amount needs to be greater than or equal to ten thousand and then the invoice needs to be not paid we run this and now notice we have all these invoices display the other thing that we could do is we could sort this and we can go back here to the design view again and customize this further so under the sorting criteria we say we want to sort this in descending order when I see the largest amounts first followed by the smallest ones so notice we are doing three things so far click on run again and now notice the one hundred and five thousand dollar invoice comes first and then the rest are following that then if we go back to the design view we can even insert as many criterias as you want and I hope you get the idea so you could sort for example by a specific zip code or by a specific city and so on under city for example if I wanted to add an additional field and I want to insert it right there just simply drag it in there and then we could have various criteria so right now I don't have any criteria by city and if I run it no this is just going to display the city but let's say that I want the city Lansing or Holland so I don't display those two cities now in my case here I can go back to design review and I'll enter two criteria so one of them will be Lansing now you have to type that correctly and I can say or Holland so it could be either one of them and then run them know this it is displaying only the city Lansing or Holland but then notice that the criteria is not quite what we were expecting earlier notice that we have 2500 so it's no longer just 10,000 or more the reason for that is because we have here in our criteria stating that the criteria could be Lansing greater than 10,000 an invoice is not paid or anything from the city called Holland so we either have to move this up here or you wise the criteria either or that we are using earlier but then keep in mind that it's not going to apply so we have to put them like this with the or here and then the additional two criterias then we run it and notice these are the only clients that have not paid their invoices yet with greater than 10,000 balance and only Lansing or Holland just for those two cities so that's how the query design works with multiple criterias within the query [Music] in this session I'm going to demonstrate how to use the query design and also define calculated fields or have calculated fields as part of a query design let's assume that we have a bunch of customers they have not paid their invoices and we want to calculate a late fee so part of our query we want to display what the late fee calculation would be so here's how we do that we go here under the query design and then first thing we need to pick the table so we want to utilize so let's say we want customers and then we want contracts and then let's say we wanted the invoices so I'm just going to list those three tables that we are utilizing at this point but in reality I'm going to use only customers and invoices in this case so we want the first name I'm double clicking on them last name and then let's say you wanted to stay the address city state and zip and then we want also the invoice number the date the item the amount and then we want whether it was paid or not I'm gonna resize this I'm just dragging it up so we can see this a little bit easier and now if I go and run this query know this is gonna display the data that we picked however it's not filtering yet or it's not giving us only the unpaid invoices to fix that we go under design view again and then we go under invoices we say not paid so whether it was paid under the criteria now there's a criteria row here we say no now the next thing that we want to do and by the way if we run this again notice it'll the display now if we go back we want to create here a new field and we'll say late payment and then part of that field we wanted to calculate what the late would be so the way you do that is by clicking here on this option that it's called builder so we want to build a new calculated field we click on builder so first I selected the new field where we want to do this and I'm secondly we want to utilize the Builder function before we can utilize the Builder function it's best to save this query first so we could say just click on the X here on the top right and we'll say yes then it will call this late fees and then click OK now we go back here to the late fees and we run this query then we go under the design view by the way you could right-click on it and choose design view as well to get to it and then we scroll all the way to the MT new field here and then we want to click on builder on builder by the way it wants to save it notice we have all these fields right here under the expression categories that's basically telling us that these are all the fields that are being utilized as part of this query so all that we have to do is we click on the invoice amount and double click on it and notice it's putting it in brackets and such and then we do the asterisk which is the multiplication so we want to say the amount multiplied by some kind of percentage so you could say it's 5% late fee for any unpaid invoices past 30 days or whatever so then you do that by x zero point or as part of your database you could design another field in there or column they were to say late fee like how much the late fee would be and then in that way you don't have to enter the zero point five manually you can just multiply the late fee multiply it by the actual amount of the invoice in our case we're just gonna do it this slightly manually so we have the invoice amount times 0.5 and then we click on ok you could have simply selected another field there instead of 0.5 like I mentioned in a moment ago now we click OK and then notice now it enters all kinds of codes here now instead of you choosing to have that as expression 1 we could have that called late fees or late fee and then simply run this and now notice if you scroll to the right you have here the invoice that was not paid it's $12,000 and the late fee is $600 and it has calculated this by the way for all the amounts now if you don't want this invoice paid no you can just simply hide it like I'll show that in a moment let's say we want to format this in in currency in dollar amounts we can do that as well and we go back here under design view and then we want to choose to not display whether it was paid or not just that column we want to hide it and then the amount here if you click on the actual field and go under property sheet here on the top or you could right click and choose property and then under the format we want to click on the drop down here and I'm going to choose the currency and then you can even choose a decimal places that say two decimal places and then you can close the property sheet here now if you run this again notice that the late fee will be in currency and that's how you calculate the late fee using the query design in Microsoft Access 2016 now we save this and then at any point for any of the customers as you enter and change data this will be generated automatically [Music] in this video I'm going to demonstrate how to create reports and customized reports in Microsoft Access 2016 this process is going to start with the very basics and then we're going to move into some of the more advanced features customizing reports in Microsoft Access so in this case we have a database with three tables and if you wanted to learn more as to how to work with an Access database please refer to the previous videos on this tutorial so we have three tables we have a contracts table let's assume we have contracts for customers and we have a customers table and then we have an invoice is stable so the tables look like this now in our case we also have those tables linked together which is a common feature of an Access database or any of the relational databases out there so as such if we go here on their database tools and then we go on their relationships you'll see that we have those tables linked together and if you want to learn more about these you can check the previous tutorials as well and now we can create reports based on each one of those tables individually or we can create reports by pulling data from multiple tables for example if we wanted customer ID from the customers table and the first name and last name from the customers table then we can also pull for example the contact number we can pull also the amount and assigning date and the contact type because those two tables are linked together so let's learn how to do this to create a report we need to go here under create tab and then we can click here under reports and notice we have this whole section with so we can create using the design option the blank report start from scratch or we can use the report wizard if you're just starting with the databases I would suggest that you start with the report wizard as that is going to be the simplest way for you to learn how to do this so we go here under a report wizard and then you'd basically pick the first table that you want to pick the fields from so in our case we go to the customers table and let's say we want the first name and we want the last name and let's say we want the street address the city zip state and email so these are just some of the fields from that table now in our first instance here we are going to simply create a report just from one table so as we get the idea and then we are going to pull the data from multiple tables so we go here under next and then we choose how do we want to group those if we wanted to create groupings of those customers in our case for now we're just going to leave this alone and then we click on next and then here it's asking us do we want to sort those customers so we could technically choose to sort those by first name or by last name or by a zip or any of those fields here so we're going to just say we want to sort them by first name then you can also choose additional sorting criteria as well subsequent ones typically the first option here it's going to take priority then it's going to go to the second priority and so on so we click on next and then it's going to create the report here in the tabular format and then you can also choose whether you want the orientation to be portrait or landscape if it's a lot of fields that you're gonna have in your table it's best probably to choose landscape but for now with weather the number of fields that we have we just can't create it using the portrait view and then they give it a name here so just customer list we can call this whatever we want customer list and then it's going to give us a preview of this report we click on finish here and notice the preview has been created so if I close it here on the right hand side now notice under reports I have a new customer list report I double click on it you know this I can see all those customers here now you say well it's kind of cutting here the email field on the right hand side how can I change that how can I customize that we can customize any of those reports that we create a couple ways or you can do this in a couple ways you can either go here under view as soon as we have selected the report here we can click on View and you can change it the layout view here so that's one way to customize it and this is under the layout view we can go and simply resize the fields so that's one way to do it so and others I'm just readjusting the width of those fields now probably the address needs to be longer notice I'm going to the right here and such the state doesn't have to be as wide than the zip I can adjust this accordingly you know this you need to adjust the labels here on the top as well I'm pressing here control a to select everything and we can also go here under format and we can change for example the font for this to be a smaller size so you can control how this will display by utilizing or changing the font visual aspects of it that's one way for you to customize this if you want it by the way these fields here on the top the labels to be in a different format you notice I'm holding down the control key here and I'm selecting all those labels now under the format then I can go here under a format and then make this bold I can choose a different font size and all that type of stuff now there's also under the format we have to select all option that's I did earlier using the control a key on the keyboard if you wanted to change the top or the heading of this you can simply go here and double click on the heading and then customize this any way that you want so that's one easy way to customize this report that we just created here notice that there are different designs here that you can apply as well to this report so notice you have these new tabs here on the very top under and these are typically referred to as the contextual tools since we are tinkering and working here with the report module of the database and adjusting the layout and the design of it you know this we have this like design tab arrange tab format tab and even page setup so you can change the look in the field of this specific report you know this also under the design tab you also have these design themes so if I wanted to apply a different look and feel to this I can apply one of the themes now to go back to the normal view for this report we go here under view and then report view and notice it has changed it has been modified with the changes that we applied earlier another way to customize your report is also to utilize the design view and I'll go more in depth about this shortly here so you go here on the design view after we have selected our report and this is kind of more complex and again notice you have here again the same contextual tools at the very top however you can change here notice it says page header if I wanted to move this field a little bit farther to the left and adjust the width and such again these are the labels notice I can do them more precisely from here so you can adjust the width of those fields here and you get the idea at this point now these would be just the labels on the top and these would be the actual fields in the bottom notice you can also adjust the footer what it will show up as at the bottom of the report from here as well so let's assume that I didn't need the email field you can simply press delete from here and delete both the label and the actual field the bottom stuff here it's actually the field from the table that it's linking to the table and then we can close this report here on the top save the changes and now double click on the report again and notice we do not have that field for the email also if you wanted to see a preview of this you can click here on the top left and choose print preview and this is how a report will look at this point now this you also have the footer here the page footer we can close the preview from here and at this point let's also learn how to add a field to an existing report so let's assume that this is our report and then somehow we missed adding the email field to an existing report of of course we could create something from scratch however in certain cases your report might be fairly complicated and you want to add another field or a couple fields so here's how you add the other fields to it you can right click on on the report and choose design view or you can go up here under view under the Home tab and choose design view so you'll kind of want to do this from the design view now on the right-hand side or in the very top here notice how it says add existing fields so you can click here and add existing fields so notice we have the list of fields here from our table and we can move this a little bit and then we want to add here the email field so it's simply drag it but we need to drag it now this if this white spots here where the detail for the form is but we don't need to put it under page header we want to put it under the detail area this would be the content over our report we are kind of linking this field with the actual table from the customers table here so this is not the label it's actually the data from the table so we simply drag it and we just drop it where we want it then adjust the positioning of it on that report how you want it how wide you want it and all that stuff and then notice at this stage it's not giving us the option to put this the label for it because if I run this right now I save the design notice it's not going to have the label here on the top as to what this is we need to basically go under the design view again and and in this case the label is right there but notice it's kind of like if we move this a little bit you know there's the email label it's over here to the left what you can do is you can simply click on that email label the one that came in or the one that they brought in deleted reposition your data field here how you want it and how why do you want and then above it under the header area that's where you need to create a new label for this so to add the label on the header what you do is you go here under the label area here in the design notice there's this icon label here and then choose where you want that label and how wide you want it and then type in their email hit enter and then reposition it the way you want it realign it same thing with the one here as well and let's go under view here and choose report view notice we have this email field created if we wanted to change the formatting and adjust the formatting again you can simply go under view go under layout view and then under design apply a different theme or apply the theme that you have from before now this is in certain spots here we have this or for this we might still need to adjust the formatting notice this has a border alone around it this field so we might need to adjust the formatting for it and get rid of the border to change the border there are a couple ways to do that you can either right click here under the field and notice I'm under the layout view you can do this also from the design view and if I go here under properties for this object so once I go to the property sheet for this specific field now this here under border it says to use a border-style:solid and I can change that so that it's just a parent or nothing and then you can choose the border effect as well if you need it to if I go here under and close this now that border should not appear any more notice it's gone if I wanted to change the size of the font of course go back to layout view and for this field you can go under the format tab and then change the size to match the rest of the form and then if we go here under design tab again and if we go and view this report this is how it will look at this point instead of spending all this time to customize the look and feel of this you can also go back here and use what's called the format painter so you can copy the formatting of a specific cell and adjust it for other cells so if I go back here to my layout view and I will do an undo some of those things that I had done earlier so some of those changes now this right now if I go back to view here you notice I still have my border stuff again here go under format painter here from the layout view and I can copy the formatting of an existing cell and apply it to another one so you can go to any of these cells go under format here and then use this icon right here the format painter that copies the formatting of an object and applies it to another object so click on it and then go to the other object any of these here and apply it and now it's all changed automatically so that's the easier way to do this then close it click on Save Changes and now you can double click on it and this is your report and this is the print preview of this report so this is how you create a report this is how you customize the report and how you remove fields from a report and also how you add additional fields to the report if you have an exam one [Music] in this video I'll demonstrate how to create a report from multiple tables in Microsoft Access reports are one of the most commonly used features of an Access database as a user most of the time you're not going to be creating databases and you're not going to be designing them and you're not going to be creating your tables in a database even though you can do that however most of the time you're going to be creating queries in a database and you're going to be creating reports from the existing data from that database now the reports can come in handy whether you're in a corporate environment or you have a corporate database or whether you have an Access database but the concept it's going to be pretty much the same since the tables are linked typically and the linking of the tables you can see it here under the database tools and relationships we have customers customers have contracts and then for each contract there are invoices as well so it's kind of all this stuff it's linked together however now in our case we can create a report from fields from the customers table original first name last name and then contact information and also include the contract so you see who our customers are what contracts those customers have so that's what we're going to learn in this session so to create a report you can go here under create and then we want to go under this section here under the reports section and we want to look here under the reports section my suggestion if you're starting is to use the report wizard so we click on that and then we are going to pick some fields here from the customers table so we go for example if you wanted a customer ID you can pick it as well click on add customer ID first name last name and let's say we want just their telephone number and the email address notice you can pick only certain fields then you can also go here under the drop before we move to the next step and we can pick from the contracts table from another table you can pick fields from that table as well because those tables are linked with primary keys and foreign keys so we go here under table contracts and now let's say we want the contract amount and then in the contract type so that's all we want in our case then we click on next since we are using multiple tables the system is asking us do we want to group the report results by customer or by contracts so in this case if you have a customer that has multiple contacts you want all those contracts for that customer group together so in my case I'm going to choose customers and it's going to list me the customer and then a sub listing of all that customers contracts so that's what this is for so you have customer up here and then the contracts will be listed right below it then we click here next and then so what kind of levels do we want here so we could say we want a customer ID first name last name telephone email and then the contact information right below it so then we click on next and then how do you want to sort those so we could sort those records and then you could also choose here that the report summary options and here you could say you want to see what the average is or what the sum of all the contracts was and all that type of stuff for each customer you can just choose here sum and the system is going to give you the total of all their reports for each customer or all of their invoices or contracts for each customer so this option is going to be available if you have numbers in their number values or amounts in there then we click on next and then it's asking us do we want to have the orientation as portrait or landscape and you can tinker with that and check it out but in our case since we do not have as many fields it okay to have this Porter's then we click on next again and then we have to give a name for this report so in our case we'll say customers and contracts or you could say contacts by customer now you can change this later as well but it's important to give meaningful names as you start with a report then click on finish and this is how our report will look like so this is kind of a preview we need to kind of adjust it a little bit notice we have these amounts or dollar signs here and all this type of stuff so we'll need to customize this in a little bit so for now I'm going to close it here to customize this further we go under view and if you wanted a preview of it at this stage you can do a preview but that's not ready yet you know this is not quite ready with all this stuff yet we can go back here close the print preview option go under layout view and we can adjust the layout of this a little bit so notice how we have the amounts here we could actually move this farther to the left so these will be the amounts and then we can resize this field then we can go and get also the description what the contract is and then resize that as well how we want it to look then also resize other objects that you'd like here now notice we'll assume here we know what the contracts are and such but you know there's there the actual labels are here on the right-hand side I'm going to delete them for now just so that for simplicity because we kind of know the amount here and also we know what the contract was now this over here this is supposed to be the total for each contract because we have chosen the todos so this you might want to move it also right below the amounts area and then you know this you have here summary for customer ID and then 30 year records and such you could change the wording here now this is that some you can move that field closer and instead of just some you can say total and basically just the formatting any way you want remember you can also apply themes for this to customize it so it looks slightly fancier without you having to waste your afternoon with this and then if this is not very useful in your case you can simply delete that specific field here and then also remember that you can apply here the formatting so let's say I wanted the formatting to be something like the previous cells here to remove this border and notice you can go under format take the format painter apply it to another field and notice it's apply so that's an easier way to apply the formatting and again you can take the time to customize this and make it as fancy as you want of course you can also adjust the labels here in the very top you could the format that differently as well and let's assume that you wanted these labels here on the top you want them bold you can again format them any way you want let's say that you want it also the actual customer information just the customer data a certain font or color or whatever you could format it however you want and it would make it more visually pleasing so this is one way to customize this using the layout view so if I close this and save the changes by the way you have to save the changes in Microsoft Access only one year change in the design of something otherwise it will save the data automatically into your table so into your forms so now if I open this up again this is what it will look like if I go to the print preview this is what it'll look like again for the print preview not bad you have the customer 11 over 11:15 here whatever that number is and notice you have all their contracts and also the total for each contract here and you have also the footer for this page for this report now if you wanted to make this and make more changes in a granule format and such and go into more detail for tweaking the formatting of this you can also use under if I close here the Print Preview you can use the design view and under the design view you can adjust here additional components for example the contact amount here notice it's not fully lined up I could adjust the size of it that adjust how much space is between the amounts and contacts and the total here and format certain things a different way and let's say between each customer I wanted a little bit more space I could adjust the spacing after each record and so on now if I go and save this and run it again by double clicking on it you notice there is a little bit more space between each customer and the total and the prior customers information so that's one way that you can customize this and tweak this further as you are working with the reports and such and we learned about this a little bit in a previous video sometimes you might want to add a new field to the report so for example we have this report here but for whatever reason we want it to also know here what the actual contract ID is whatever reason is part of this report so instead of us recreating the report from scratch we could simply add one more field to this report to this existing report so to add the field you could either go here on layout view and then under this area right here add an existing field you could do it from here and you choose to add so right now it's showing us the fields from the customer table however we could choose all tables and we want it under the contract table and we want to let's say the contract number and we want that contract number at the end of the contract information area or wherever you need it so we could drag it from here it actually put it in the very beginning and place it basically wherever you need it wherever you want it I'm going to delete a little label for it on the top for now so that we keep our report slightly cleaner adjust the size of it accordingly how you want it change also the font for it and that's one way to display it in there using the layout view the other way to display this field in there to add this field and this is what you'll probably use in most business environments is by using the design view so if I go here to my report I'll close it for now open up the report again and go under view design view or I could right click on it and choose design view that's another way to do it now at this point you notice these are my existing fields I have the contract amount I have a contract type however let's assume that right the next contract type I want to add another field in this case I go here under add the existing fields and I could have the field the same way that I showed a moment ago in their ad contract number over here I want to delete the label for it unless you have a use for it at this point in this view and probably don't need the number to be that long and then that field at this point should have been added so if I close it and run it I should have the contact number at the end of each contract for each customer here so at this point let me explain how this data is actually to the and how you control the linking of each field to the actual table because the data from the report is actually pulled from the tables it's not really stored in the report it's actually just at any time you're under the report its querying it's looking up the data in the table and it's displaying whatever the criteria is that you have specified go here under the design view and if you go to any of these fields or the data fields here it's a contract type or contract amount and such you can select a field and then just go under the property sheet property sheet it's going to do is it's going to bring up the property details for this specific field that we just selected and then here it's telling us how it's going to format it and all that type of stuff and it's the contract amount that we have selected currently it's good we could select any of the fields here we'd want to from this report and we could customize here any of the details how we want this to be formatted basically so this is just a formatting currently for this item we are controlling how the formatting is going to display for that value so far however if we go here under the data tab and the control source is where is this linked to it's telling us what the source of the control to what table and what field is it picking so in this case it's actually going to the contract amount from the contracts table and if you click on the drop down if for some reason you need to link it to a different field that you have selected initially in your report you could just pick it from here or if you want to link it to a completely different field that does not show up over here under the list of fields for your current report you can click on these three dots and then go under your database that you have opened and you go to link it to a different field within one of your tables so freedom under contracts here if I wanted to link it to the date or to another one whatever it may be that I want it simply select it from here and then click ok and then run the report again and it's going to link it to a different control source from a different field on that table that you select so that's how we can create a report from multiple tables and also group the results and create a calculated field for the amounts within each order for each customer and group those orders together so that's one way to do that so if you want to further customize this report and add or change the order of those fields and such you can simply drag those fields by going here to the design view so if you're here under design view and let's say we wanted to change the order of those fields you can simply shuffle the fields around here so this will put the contract number in the beginning you can resize this how you want it the amount now it will be in the end also the total we'll put the total on the right-hand side at this point right below the amount and you can readjust the spacing between them and now if we close it save the changes run it again you know this we have the number in the beginning we have the actual contract information and then we have the amount on the right hand side if we wanted to further customize it in this case such as formatting and such we can go here under view layout view and tweak this a little bit more to use a format painter and click on the destination cell and we'll apply that formatting of the previous cell that you have selected now if we close it save the changes run it again now it should have been readjusted the layout of it if you want to see a preview of it click on print preview and at this point it will show up like this of course you can customize this stuff and make it as fancy as you would like you can make the reports very complex it's very customizable basically [Music]

Channel: Kaceli TechTraining

Views: 154,770

Keywords: Microsoft Access 2016 tutorial, access 2016 full tutorial, access tutorial easy, learn microsoft access 2016, skaceli, sali kaceli, access database tutorial, access 2016 forms, access 2016 queries, access 2016 reports, customizing reports in access 2016, creating queries in access 2016, creating forms in access 2016, calculated fields in access 2016, access 2016 part 2, kaceli tutorials, learn.kaceli.com

Id: bqQ-eKMxk6Q

Length: 67min 53sec (4073 seconds)

Published: Wed Dec 06 2017