Queries per second

Transcription

Welcome to Office 2013 class video number 48. Hey, we're still studying Access, and in this video we want to talk about queries. Now, in the last video, we created tables, the heart of any database and we created forms to test our database and to enter data into our tables. But in this video, we want to see the power of Access, which is not just to store our data but to create useful information from that raw data using queries. Now, I'm going to go over to our handout. Here's page 19 of 21 and pages 19, 20, and 21 list our queries. Now, queries-- we did lots of Excel in this class, and we did queries in Excel, things like a filter. But it all comes down to this-- data analysis, going from raw data into useful information. We're always converting the raw data into useful information. So the definition of a query-- well, query just means question-- we're going to ask a question of the raw data. The answer that we get is the useful information used for decision making. So here's our list from three to 15. You can read that if you want. Otherwise, we're just going to go straight over to Access. Now, let's just open up our table. Double click, and there's all our raw data. Now, our first question is a simple one. I just want to say, hey, show me just Description and the Sell Price. See, here it is. We're a small kiosk in the mall selling wood toys, and I just want a printout of the names of the toys and the sell price. So, this is a very simple question. We don't want to see all the raw data. We just want the name of the toy and the sell price. So I'm going to close this table. Now, where do we go to create queries? We go to the Create tab. And in the Query group, we're always going to use Design. So, Query, Design. So I'm going to-- boop. It's always going to ask me which tables I want. So I'm going to double click the Products table. Later, we'll use both tables for a single query. Here, we're just going to double click and show the Products table. I'm going to close this. Immediately, we know what this is right here. We've seen it in Excel when we did pivot tables. We've seen it here in access. It is a field list. Remember, fields are the thing in Access that connect all of the different objects. So here's a field list. And down below, we can point to the middle, and we see that cursor. We can click and drag up. Here's where we create our question or our query. Now, here's a field list, and I want to see descriptions. So I'm going to click and drag down to the first column, and I'm going to drop it. Immediately, I can see, hey, there's the field, there's the name of the table. I can decide whether I want to sort it or not. And later, we'll see how to apply criteria. But remember, this one is, hey, show me all of the records, but I just want to see Description and Sell Price. So I'm going to click and drag down there. And there's an example of a simple query. It's a question. Show me a description and sell price. Now, I'm going to come up to View. You can click Run, but I always just click View. Remember, we have two views. We're going between Design and Data Sheet. So when I click Data Sheet, that's the answer. All right. Now, let's Control S. Don't save queries as query 1, query 2. The textbook has you do that. That's silly. Always give your object or your file a good name. In this case, this is an object, so I'm going to Control S. And the name of the query is description and sell price. Click OK. All right, so when I close this, now I have, oh, my first query-- description and sell price. I can double click it and see the answer. If I want to go straight to Design view, I simply say, right click, Design. Now, in Design view, we can edit our query. I want to come down to the sell price. Field, Table, Sort, Show, and Criteria. I want to use the Sort. So I'm going to say Sort Ascending. Now when I click View, there's our answer. This is our useful information, so we can print this out, use it as our kiosk. Name of the toys, price, anytime we need to look it up, boom, we have it right there. Now, when I close this object here, it's going to ask me, do I want to save. Yes, remember? Objects, anytime you change any aspect or structural element in the object, you have to save it. So I'm going to say yes. It's only that raw data that's automatically saved. All right, so that's our first question, our first query, our first answer, our first task of creating useful information. Now, our next question, if we go over, double click the Products table, I'd like to show just the records for SC Supplier. So close this table, Create. Queries group, I'm going to use Query Design. Show Tables, double click the Products. Close. Pull the corner of the field list so I see the whole field list. I might even drag this up. I'm going to close this property sheet. And here, I want to see all of the fields. So watch this. Select the first one, and before clicking on the last one, we know we've seen this keyboard and mouse trick maybe 100 times in this class. We hold Shift and then click on the last one. Whoops. Click on the first one before clicking on the last one, hold Shift. There we go. Now, watch this. I can click and drag. Now, don't drag it out here in the middle. Be sure to drag it to the first column, and boom, instantly we have all of the fields. Now, right now, if I click View, that shows me everything in the table. I'm going to go back to design. I want to use the Criteria row for the first time. Go over to Supplier Code, and who is it I want? SC. Now when I click View, beautiful. There's my answer. There's my useful information. Any time I want to see the toys from SC, I open up this query. Now I'm going to Control S, and I'm going to call this something like products from SC, and click OK. Now, if I double click and open this at any time, later when I have more toys or fewer toys from SC, this will update and be dynamic. Now, notice we've hard-coded SC into the criteria area. There's a different query called a Parameter query. And actually, from the list of queries, we have three to 15, this is actually 15. We're going to basically do the same exact query except for I'm going to change the criteria. So watch this. I'm going to come over and click on the query, Control C for copy, and then Control V to paste. So I've copied it. Ah, it's being polite. You can't have the same name for two different queries, so I'm going to call this records by supplier and click OK. So now I have this query. I'm simply going to come to it, right click, Design. And I'm simply going to change the criteria down here. Now, remember what we said-- we saw square brackets briefly in our last video. And back in Excel, we saw the table feature use square brackets. But square brackets mean field name. But check this out. If I use a square bracket here and enter some text that's not a field name, I'm going to say Enter, Enter Supplier Code. Now this becomes a Parameter query. So a dialog box will pop up and ask us to enter the supplier code we want. By typing enter supplier code, that's just the text that will appear in the dialogue box. So let's try and run this. I'm going to click View or Run. Either one will work. And there it is. Isn't that cool? It says, Enter Supplier Code. And I'm simply going to type AP, because that's one of the suppliers, and click OK. And just like that, we see AP. Now, I'm going to Control S on this and close it. Now, anytime we come over to our queries and double click Records by Supplier, it's going to be polite. If I want to see SC-- and by the way, whether or not they're upper case or lowercase doesn't matter here-- instantly I see SC. If I close this, I've looked at it and seen what I wanted, open it, next time I type AP, Enter. And just like that, this Parameter query is showing us all of the records for whatever supplier code we type in. Click OK. All right. Now, our next question, we're going to look at the raw data, I'd like to show on hand but only when on hand is greater than or equal to 10. So I'm a close this. Go up to Create Queries, Query Design, show the products table, close this dialogue box, pull down the field list, drag this up a bit. I want to show all fields. By the way, that asterisk will show all the fields, but then you can't use criteria, and we have criteria here, so we want to show all the fields this way. Click and drag to the first column. Now, I want to come over to on hand. And in the criteria row, I'm going to say greater than or equal to 10. Remember, we've seen this a bunch of times in Excel and in Access. There's no such thing as a single greater than or equal to or less than or equal to character, so you always have to use two characters. Now let's run this. And just like that, we can see we have two toys that have 10 or more on hand. That means number of items on the shelf. Query 1 is not a good name. Control S, I'm just going to put on hand greater than or equal to 10, click OK. Now, let's go back to Design view. Notice, we have a single condition here. In our next query-- I'm going to close that, and we can see it's listed there-- opening up the Products table, let's say someone comes in and says, hey, I have a party. There's 10 people. But I don't want to spend more than $10 on a toy, so I need to find a toy that has on hand greater than or equal to 10 and sell price less than 10. So I'm going to close this, go over to Create, Query Design, double click Products Table, pull down the corner, pull this up. Now, in this case, I just want to see Description, Sell Price, and On Hand. Now, criteria-- we have a criteria for sell price and for on hand. So for sell price, it has to be less than 10, and then on hand has to be greater than or equal to 10. So now, this is called And criteria. Two conditions have to be true in order for the part of the record to be shown. And criteria always goes on the same row. Notice this criteria row here, there's lots of other rows below for Or criteria, and we'll see an example in just a moment. Or criteria goes on different rows or different lines. And criteria goes on a single row or a single line. Now later on when we go and study Excel in business 214-- this class is a prerequisite for that class-- the Advanced Filter feature inside of Excel will follow the same exact convention. So will database functions. So here in Access, And criteria goes on the same row. All right, when I click View, just like that, there's only one toy. Now, the person says, hey, wait a second. Actually, I have eight people. So I'm going to go back to Design view, change the on hand to greater than or equal to 8, and now when I run it or click View, there are two toys to choose from-- Coal Car and Pets Jigsaw, 10 and 8 on hand. Now I'm going to Control S and call this sell price less than $10 and on hand greater than or equal to 8. Click OK. Now, let's go back and look at our table. Our next question is going to be a type of And criteria, but it's called Between criteria. We would like to see sell price, but only between $5 and $10. So let's close this table. Our question is, show me sell price between 5 and 10. I go to Create, Query Design, show the products table, pull down the field list. And I'd like to show all of the fields, so I'm going to hold Shift, click on the first one then the last one, click and drag to the first column. Whoops, it didn't quite take. Now I want to come to sell price. And this is kind of like our data validation we did for cost when we were building our table. We actually typed out the criteria all in a single cell. So here's how it's going to go. Between 5 and 10. Now, when I click View, boom, we can see all of the sell prices between 5 and 10. It looks like there's three products. Now, I want to go back to Design view. Between 5 and 10, those are two conditions or criteria that must be met. Each price must be greater than or equal to 5 and less than or equal to 10. Now, I'm going to save this. Query 1 is not a good name. Control S. We'll call it sell price between $5 and $10. Click OK. We can go ahead and close it. Now, with those two, sell price between 5 and 10 and then sell price less than 10 and on hand greater than or equal to 10, those are And criteria. Now we need to talk about Or criteria. So I'm going to go over to Create. Actually, the question we'll ask if we open up our Products table, I want to see supplier SC or AP. So if we were doing that over an Excel, we would use filter, and we would check the check box and the filter for SC and AP. But over here in Access, Create, Query, Design, show that Products table. I want to Show All Fields. Click on the first one, hold Shift, click on the last one, click and drag to the first column. Now, it's this supplier code, Criteria and Or. So, the way I always remember it is that Or is there to remind you, that when doing Or criteria, you go down to different rows. Now, Or criteria, unlike And criteria, Or criteria needs to only get one true when it's applying the criteria. And in our last two examples, the And criteria meant that both conditions had to come out true. All right, so we're going to type SC on one row and AP on the second row. That means it will ask each record, are you SC, or are you AP. When I click View, just like that, I can see that I have all of the SC and AP. That's Or criteria. Go back to Design, Different Rows. Control S. So, suppliers SC or AP. Click OK. I'm going to go ahead and close this. All right, so we've applied some criteria. Now we want to see how to do some queries based on formulas. Now, the first one is I just want to open up the products table, and all I want to do is figure out what the average sell price is for all of our toys. Now, in Excel, we would have the average function, and we'd highlight this whole range and do average. But here's how we do it over in Access. We go to Create, Query Design. We'll show our Products table. And watch this. I'm simply going to drag Sell Price down. And we need to Show the Total Row. Now, totals you can come up and click this Totals button over here. You can right click anywhere in our design grid and point to totals. And there it is. There's a total. We come to that particular cell in the drop down. Now we can see AVG. Notice there's Min, Max, Count, Standard Deviation, and a number of others. I'm going to click AVG, and watch this. This is going to be weird in terms of getting a result for a query. When I come up to View, it's going to give me a single answer? Well, yeah. That's the question we asked. Show me the average sell price. So this is not like a report you're going to print out. This is just dynamic. At any time, you can open this up. And no matter what toys you have, it'll tell you the average sell price. I'm going to Control S. So something like average sell price. There we go. Click Close. Now, I'd like to change that query. I actually copy it and then change the criteria. Average sell price is fine. That's for all the products. But now I want to do average for each supplier. So we'll do our same trick-- copy this, Control C, paste it, Control V. The name can't be the same, so now I'm going to say average sell price by supplier, average sell price by supplier. And there's my average sell price by supplier. Right click, Design. Now, we're simply going to drag Supplier Code, and we're done. What's happening here is now it says Group By in the total row. So it in essence will go through the supplier code and get a unique list, one of each, and calculate the average. Now I click on this View button, and just like that. We have only bought toys from three of our total of five suppliers, and there's the average for each one. Control S, and then Close. So both of these were aggregate calculations. Aggregate calculations means we're just calculating a single number. Now we want to see how to calculate some formulas, where we actually do math on the fields. Now, let's look at Products Table. We certainly would like a query and a report based on that query that shows us cost times on hand. That's how this business calculates its inventory value. So I'm going to close this. We'll go to Create, Query Design, show the products table, drag it down, drag this up. And here we simply want to double click Description to drop it down there. And that's the only one we're going to use. Now we're going to come down and create a calculated field, and here's how we're going to do it. We're actually going to type the new field name here. This is going to be called Inventory Value. And as soon as we type a colon, that means everything before the colon is going to be the new field name, and everything after the colon is going to be our formula. Now, here's the deal. We're dealing with field names. So really, what I'd like is cost times on hand. Now, you can't just type cost times on hand. The only thing you need to add to that is to make sure that you put whatever the field name is that you're going to use in your formula in square brackets. All right. So let's try it. Square bracket, cost, end square bracket, times, and then square bracket-- now remember, if you misspell it here, so be sure these have to be spelled correctly. Field names are what connect everything in our database, right? So if you spell it wrong here, it won't work. So I'm going to spell it correctly, close square bracket. So that's how we can build a simple formula taking the cost field and multiplying by the on hand field. It will do it for each record. All right, let's click View. Just like that. Is that not beautiful? And we can even expand the column width so we see our new calculated field name, Inventory Value. And for each one of the toys, it calculated the inventory value. I'm going to go back to Design view. Again, everything before the colon, that's going to be the new field name, calculated field name. Everything after is going to be the formula. Field names, got to be in square brackets. Control S, probably a good name would be Inventory Value. Click OK. Click Close. And the beautiful thing about queries, of course, is inventory value right now, Skyscraper is 750. But if I go to the form for Products-- now, I want to find Skyscraper. I'm going to click in this cell, and I'm going to use the keyboard Control F. Now, Control F is the same in Word and Excel and PowerPoint. It's Find, Control F. So you simply click in the field you want in the form. Find what? I want to say Sky. Now, Skyscraper, that would require that the match be a whole field. I want to search for Any Part. Use Match Any Part, and it will find all of the description fields that contain Sky. So when I click Find Next, sure enough, there it found it. Now, the cost is definitely not $125. It's actually 12.50. So check that out. That was a typing error. But when we change it in the form-- remember, the beauty of a database in Access is instantly from this form, it dumped it into the tables. So there's our correct Skyscraper price. When I go over to my inventory value, instantly it updates. So as soon as the raw data changes, everything in your database that's connected to it updates. Now, let's do our next query. I would like to create a formula that calculates gross profit for each product. So I'm going to Show Products Table. That's the field list. Pull it down. I'm going to show just description, and now we're going to come down here. If you don't like to work right here, you can right click, Zoom. And it's not going to show it to us any bigger, but it does give us a little bit more room right here. So I'm going to type Gross Profit. Remember, everything before the colon becomes the calculated field name, and everything after, hey, square bracket. It's Sell Price, close square bracket. Capitalization doesn't matter here. I like to keep it the same. Minus-- now, let's just see what happens if I type this incorrectly. So I've misspelled it, so of course it's not going to work. When I go to run it or click View, it's asking, remember, oh, this is a parameter query. So it would work to our advantage, like when we did our search for suppliers. But here, it's not going to work. So I'm going to click Cancel. As soon as you see that parameter query pop up, you know you've spelled the field name right. So right click, Zoom. And now I can come over here. Let's make sure to spell it right. Click OK. Click View or Run, and instantly, there's our gross profit per individual item. That's if we sell one item, our gross profit, sell price minus cost, 23, 12.50, et cetera. Control S-- always giving something a smart name. All the way from the beginning of this class, when we studied Windows Explorer, Word, PowerPoint, Excel, and now in Access, always naming things. Even if you have just that one teeny skill added to your toolkit of computer skills, that skill, it helps us to find things and understand things. Click OK. So now over here, I have a bunch of smart names. I know exactly what all of these queries mean. Click Close. All right. Now, we have two more queries we want to do. We want to do what's called a Contains query. Let's say we're looking at the products table. And this is a small database, right? But maybe you want to search for Railroad, all of the descriptions that contain Railroad. In our case, I want to search for all of the descriptions that contain the word Rail. We're going to use a wild card, an asterisk wild card. All right, so I'm going to go to Create, Query Design, Show Products, and I'm going to show all of the fields, I want to see the entire record for any toy description that contains Rail. All right, so we're going to come down to description. And although it will eventually show up differently, I'm going to type it in as if we were doing it in Excel, because this wild card works in Excel and Access. The asterisk means zero or more characters. So when I type rail or asterisk, rail, asterisk, what I'm telling the criteria is find the word Rail with no characters before or after, or any word that contains Rail, with either one or more characters before or after, so that when you use a wild card, it says zero or more other characters of any type. So it would find Rail, Railroad, Railway Bridge. All right, so when I click View, sure enough it found the descriptions that contain the word Rail. When I go back underneath, it changes it to Like. Now, over in Excel, you can actually use that exact asterisk as criteria in whatever functions or formulas you're using. All right, I'm going to Control S, rail query, click OK. That is a wild card. You can also use wild card if you were searching for a particular street, everyone on Birch or something like that. All right, I'm going to close this. We have one last query. Let's look at our Products table. Now, we would like what's called a reorder phone list. Any time the number of units gets below five, we'd actually like to know the product ID, description, on hand, sell price. But we'd also like to know from the supplier data table the person's name and their telephone number. And that's where having a connection between tables is helpful, because now we can ask a question of both supplier data and products table. If we remind ourself, Database Tools, Relationships, we can see we have a relationship between these two tables. All right, so let's go to Create, Query Design, double click Products and Supplier Data. Hey, look-- it looks like that relationship window. Now we're allowed to simply take whichever fields we want from each one of these tables. So I'm going to say, Description, Control Z, Product ID, Description, On Hand, Sell Price, Supplier Code, First Name, and Telephone. Let's check this out. Oh, wait a second. That's everybody. Well, that's pretty cool. We could just combine stuff from different tables. But that's not what we want. We need a criteria on On Hand, so I'm going to go back to View. This toggle here is very helpful, back and forth, until you get your query constructed correctly and your answer looking good. All right, let's go back underneath, and we're going to go to On Hand. And our criteria is less than five. So every Friday, we go and print this out. And there are the people we should phone and the products we should order. Now, let's save this. Control S, and something like reorder phone list. Click OK. All right. So in this video, we saw a lot about the amazing ability to create queries in Access, Criteria, or Conditions that then pull certain data from certain tables and create useful information. Now, in our next video, we'll see how to create a report, all right? We'll see you next video.