Save Same Calculated with airSlate SignNow

Save same calculated

[Music] hi this is the help video for access chapter 3 the prepare case we're on part c which is the last part of the prepare cases so i have the instructions on the left and the downloaded file on the right and it's the same scenario of for a database as we've done in part a and part b so i have downloaded it and opened it so i'm done with step a step number two if you need a quick snapshot or statistics for a table or query you can use the total row the total row is a special row that appears at the end of a data sheet that enables you to show aggregate functions from one or more fields so an aggregate function would be some kind of statistic across a range of records totals counts average of a set of records the manager would like to quickly find the total of all unpaid invoices and count the number of unpaid so total which is a sum and number which would be a count this is on page 197 and 198 of your textbook so the first thing we're going to do is create a new query in design view so let's just analyze what tables we need so we need invoice and customer based on the information that he wants so let's come over here and put those in there invoice and customer i'm going to close i'm going to do my housekeeping which is just kind of open up my and bring my grid up okay so now we're going to start putting fields down into our grid so invoice date invoice total and paid then customer first name customer last name so all i do is double click on each of those limit the results to only those records where the invoice has not been paid so i need to figure out how to do that invoice has not been paid so one of the ways that i start working with this is to actually run it and see what the paid field looks like so a paid field is a binary it's a yes no field checked means it has been paid unchecked or false means it hasn't so we can use that knowledge that we want to just show those where paid is false right come back here and we can type um false and run it and look we only have those unpaid you can also use the no the word no if that makes more sense to you but notice if i just do n o it's going to bring up that function now i need to hit the space bar and then i can run it so either false or no and your instructions have you using no so even though false would work in order to get graded correctly you want to probably want to use no in that page so there we can run it and we see that so we have not um we have the answer that we want but what he he wants if i run this is oh we got to go back i'm sorry i forgot to sort i got sort the results in descending order by invoice date okay after running the query run it add the total row so to add the total row when i'm looking at the data sheet i'm going to come up here on the home tab in the records group and click on total it's going to put a total row here and i can then sum or count or average so for invoice total i'm going to find that column total row click drop down sum so outstanding invoices total 274 dollars and 25 cents and then we're going to count first name so i'm going to click here come down here and count notice i don't have all those choices because this data type doesn't allow me to do anything other than count so we're going to do count so there's six invoice that haven't been paid so let's go ahead and save that qry unpaid invoices so now once that's created as the records get updated in the tables if you run this at any point you're going to get that updated most current results set or answers for that question of what invoices are out so then maybe you know you send out some notifications for this you get some payments it gets marked so this would shrink or new records get added that haven't been paid right so it's always changing when you're working with a query so we're done with that so we're going to do that three aggregate functions can be used in queries to perform calculations so remember that term aggregate means a statistic across a range of records so perform calculations on selected fields and records one advantage to using aggregate function rather than just a total row is that you can group criteria and then calculate the aggregate function for a group of records okay so let's talk about that so so if you had like customers from different cities or different divisions or departments and you wanted to get some statistic for that group the way that you would do it be an aggregate function on the actual query and not the answers all right so the manager would like to see how many products are offered the average product price and the minimum and maximum product prices we're going to use design a query that lists the price from table product four times apply the count average min and max function for each of these okay so what does that mean we're going to come up here to create query design and we're going to come here to query and we want table product so what i want to do is i want to put down this field of price and i'm going to do it four times each of these columns are going to turn into the aggregate function the first one count have min and max but right now i don't have a total row and so to get a total row on your grid in the query tools design there's a total once you click on this you now have a total row by default it says group by we're going to come to each of these and change that aggregate to count average min and max so now if i run it i get the count of products the average price of all the products the lowest selling price for products and the highest selling price for products so we're going to save that qry product stats good don't close the query so we're done with step three so edit the products dance query so the favorite kind of is being applied isn't it okay okay so we're not renaming it at all okay field names in aggregate queries are composites so look at this watch this count of average of min of so if i look at this it's this weird name right so one of the things that we can do is um we need to keep the original here but we want to display it as a different name so what we're going to this is on 200 and 201. edit the product query so the that where the price field is um it's number of products average okay so how we do this is up here in the field name this is how i do it i right click and i say zoom and then for here what you want to do is um we're going to call this what are we calling this number of products colon let's run it and see if it works there we go number of products product products gotta go back i'm going to zoom again and i'm going to put an s on here so we're going to do that process for the other three so i'm going to go to the field name where it says price right click zoom come in front of price and put the the kind of the rename so this is going to be average price colon so when access looks at this anything left of the colon is a name anything to the right of the colon is a calculation so we're going to do this next one right click zoom and then this one is lowest price colon okay right click zoom this one is highest price colon okay run it i just want to make sure all my fields show up number of products average price lowest price highest price use autofit which i did save and close so we are done with step number four all right step number five not only can you find statistical information for selected records using average function in a query for all your records or for all your records using the total row you can also calculate statistics for groups of records create a group to calculate statistics works the same way as aggregate query but must include the field to group by so this is what i was talking about so you can have a stat or you can group it by a field so um [Music] i have the default group by the spa manager this is what we want to do here the spa manager would like to see how the statistics calculated in the previous query for each product category okay so i don't want to redo this query from the last one so i'm going to copy it and so that is product stats so i'm going to right click on it do a copy come to this blank area paste and then it's going to be called qry product stats by category and then that's the one that i want to go to in design view right so we didn't have to start from scratch and redo all that rename all the fields blah blah blah okay so edit the query so the statistics are grouped by each category so what i need to do is i need to get category down here on the grid so i'm going to click hold and drag it down and then category is there right now notice it came in and it says group by so grouping by category it's going to do these statistics let's run it and see right so remember the categories bath body face foot house brand so now instead of when i run this one where i get all my statistics right i get the breakdown by that group right so it groups it by there so we can go ahead and save that and close it so it's just a modification of the one we already did so number five is done oh i forgot to put the page numbers so that was page 201 202. all right step six the manager tried to create an aggregate query to calculate the total of items an average number of items purchased by different methods phone online person the results made no sense and she's asked you to help her figure out why open the purchase methods so we're going to open that in design view and make the necessary corrections so the query displays the correct information so let's run it and see what we get right so in person in person in person online online phone so what she wants to do is average number of items purchased by different methods phone online and in person so this is page 202 and 203 and let's look at this so we're saying total sales volume quantity we're grouping by quantity that doesn't make sense because total number of items and the average so i think we need to change this group by to a count right because she wants the number of the number of so let's run that and see okay so look it's down to just one line for each purchase type instead of one line for each purchase type and quantity and now it's totaled up by that purchasing type and has done the average um open so run the query adjust the columns but don't save but do not close okay so save we're done with six all right number seven so number seven is really making it look nicer so if we run this look at this average and you get that a lot of times with the average so how would we fix that well first of all it's on page 203 of your book and we want um let's see we want to change the formatting of the average to be fixed with zero decimal places so i'm going to sit on that field and then i'm going to come up here to the property sheet and if i go into property sheets so i'm talking about field property so make sure it says field property because and make sure you're on the right field and format drop down fixed decimal places zero right so we change the property or the characteristics about this one field i can close that and run it and now look it's a whole number and we're good with that so we can save that we're done with number seven and we can close out of that so we have one more step number eight which is on page okay so this last query that we're going to do is creating a calculated field so we did a calculation but it was an aggregate calculation right so it was some kind of statistic across a range of of records this calculation that we're talking about is a new field in the table well in the query that would perform some kind of a calculation in each row then that calculation would exist so let's look at this we're going to do the spa manager would like a query to show her the total value of inventory using the quantity in stock and the price fields so if we took quantity times price we're going to get the total value in inventory so i need to create a query that perf that does that so we're going to create a query in design view so it's all from product right so we're going to come over here to tables product close do my housekeeping here and then we want product description category quantity in stock and price all right sort the results in ascending order by description okay so now what we want to do so if i run this i get how many we have and how much the price was is so in this new column the new the first column that has nothing in it i'm going to click and they want you to use the expression builder so we'll do that here first so right click build it's going to bring the expression builder up and what we're going to do is we're going to type total inventory colon and then from the down here we're going to look at tables and we want to look at product so we can get the fields from the product displaying right i could certainly type it but we if we're using the builder we might as well be able to pick it so i just navigated here to the database opened up tables and found the table that i am interested in using as part of my calculation and so the first thing would be quantity in stock times price then i always need to get rid of that expression up there so quantity in stock times price let's do okay and run it so now i have a new field that multiplies quantity in stock times price why does that not look right it doesn't look right because i put quantity in stock twice so i need to fix my error i need to get rid of that just gonna delete it and double click on price that's better now i'm gonna show you how i do it i'm gonna right click and zoom and i come to my zoom window and i do total inventory i'm going to call it 2 just to show you and then i'm just going to type and i can see the i can see the field names right here so i can just type it times price and for me that just seems easier right notice when i do this any field that you're referencing has to be inside square brackets that tells access that this is a field so if i run these they should come out to the exact same thing and they do so either way would work and really you don't know once you um once you once you build it you don't know how you actually did it so there we go total inventory all right so we are going to save this as total [Music] qry product inventory um we sorted let's run it just make sure all of our fields are good all right so we are done step number eight is done and we're done with the [Music] save that you can close out submit it for grade remember three times you can fix any errors all right thank you