Access 2010: Advanced queries and creating a calculated field

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 a 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 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 table 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 a 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 this 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 lastname 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 those 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 is paid or no now remember from before when we chose the customer city we ended for enter 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 will 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 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 times x is the asteroid let's say we are going to charge 5% 0.05 and then we click okay now notice it says here e XP 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 notice we just created another another column that we added so for this customer that has a thirty thousand dollar bill the 5% 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 it 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 the design view and then what you do is there is a property sheet over here an icon 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 and 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 to 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 they 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 it'll earn 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 say 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 modify 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 I go and change that to $1 as soon as I update this invoices amount because again the query was running from the invoices before closed 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 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

Channel: Kaceli TechTraining

Views: 37,160

Rating: 4.9041915 out of 5

Keywords: Microsoft Office, Office 2010, Word 2010, Excel 2010, Computer concepts, advanced queries, calculated fields, hiding a field in a query

Id: a7TLiNeeYiM

Length: 12min 6sec (726 seconds)

Published: Thu Apr 11 2013