Office 2010 Class #45: Creating Queries In Access (16 Examples)

welcome to office 2010 video number 45 hey we're talking about access and I got a make a note here it's 12:40 for March 7th and we already downloaded this file right here these are the notes for this big access project we're working on wooden crafts but as of right now I changed all of the information about criteria so you need to go and download this again unless you're downloading it for the first time after 12:45 March 7th hey we are in access we have been working with our wooden crafts database for the last number of videos and here it is now let's remind ourselves of the definition of a database the database does two things stores raw data and then creates information from that raw data so if I open this product table that's the raw data that's in a table and in the last couple of videos we not only created the table but we also created a form and we use the form to enter data or search for data so part number one to a database stores raw data part number two create information from that raw data so let's look at this table for example you could ask the question and for us we're going to jump into the realm of queries now queries are when we ask a question of our database and then get some answer so for example we could say hey show me the records for just for s C the answer would be to eliminate all the records that are not SC and just have a small table with just SC that would be our answer that would be our information we'd use that to then analyze and do whatever it is we want to do with SC so ask a question get an answer that's what we're going to do with queries and that is the creating information from raw data part of data basing alright so where are we going to go we're going to go to create and right here in the create ribbon there's the query group and we're going to use query design mode so I'm going to click on this and it's automatically going to give us this show table dialog box it's just being polite it's an a which table do you want to ask a question up so I'm going to double click on this one click close now this is the field list I can't see them all so I'm going to point to the edge when I see that diagonal arrow I'm going to click and drag I'm going to come to the middle here and when I see that cursor I'm going to click and drag up here I'm going to drag down just a little bit so here's the field list and this is where we create our question now our first question we're just going to say hey database show us just SC and we only want some of the fields not all of them so I'm going to do product ID oh I just double click then it pop down there and then I'm going to do description so this is a field list anytime you double click or click and drag you're then starting to create your question right now we have the ability to ask questions of these two fields we want to see product a adn description how about on hand selling price and supplier code now right now this is a question we haven't put any criteria in yet but this is just a simple query saying hey just show me some of the fields not all of the fields let's go look at our answer by the way you see it says query 1 we haven't named it yet but we've been using this toggle throughout the this access section of the class to just click back and forth back and forth between where we design our objects and the result now here's where we asked our question and then we can click on this and there's a result oh look at that so this is just a simple query we said hey show us just some of the fields not all of the fields right because sometimes for example you want to see selling price information but not cost information so this is like question number one in our handout and in fact I'm going to go back underneath where before we add criteria here I'm actually going to save this and this actually has one extra field as compared to the description in the handout but no problem our goal was just to see that yeah we can ask a question here let's go ahead and save this control us by the way run is the same as clicking this datasheet here all right I'm going to control s and what do you think a good name for this remember we're going to use our naming conventions we've used throughout the whole class and in the textbook chapter 2 they have you name each query query 1 query to query 3 query for that that's not a good name because when you come back later you have no idea what the query is about so this is just show I'm just going to give it a real practical show all products on hand and sell price and then click OK and just like that we have a new group and there is I'm going to expand this and there's our query let's go ahead and close this let's go ahead and double click and open this oh there it is notice we're to double click it opens in datasheet view here I'm going to close this if you want to go straight to design view you can right click design and it goes straight to design view now let's go ahead and create our next query that's query number one query number two is when we're going to take this one step further and add some criteria so I'm going to go to query design I'm going to show my table close this pull the field list down pull this up a little bit alright this next one I want to show those same field so I'm going to double click double click double click skip over cost double click double click and now we're going to add some criteria notice it says criteria field table whether or not you want to sort that's as just as easy as you know ascending descending whether or not you show sometimes you have fields with criteria that are determining which records to show but you don't want to show it so you check and uncheck I don't we're not going to we're going to have show all on all of ours but criteria there it is so we can just come over here and type SC and that by the way little SC is the same as big s SC all right it's not case-sensitive let's go ahead and look at this well that is so cool we have in essence dud what we did over in Excel right when we did filter now it's just showing it's as if we filtered in on SC I'm going to click this design view the criteria though goes down here in this area here alright I'm going to ctrl s and I'm going to call this show SC selling data a selling price data or something like that something smart so really our question here you know I always try to name these like the same thing as our question was because the question we're asking with this query here is hey show me the selling price information for SC all right I'm going to go ahead and look at it one more time and admire okay let's close this all right now I would like to do a similar query to the one we just did show SC selling price but instead of hard-coding the SC and I want a dialog box that will ask us every time we run the query hey which supplier do you want to see data for now I'm going to use the same infrared in this query in turn in our next query and I don't want to have to recreate it so watch this with this selected I can use ctrl C and then with it still selected I can control V now it's going to be polite it's saying hey do you want a copy of this oh no I'm just going to give it the name I'm going to call it so supply our selling data and I'm going to put in parentheses the word parameter as this is called a parameter query as we'll see now right now that are the exactly the same now I'm going to just go right click design to jump straight to design view and instead of putting criteria here we're going to use square brackets now as we'll see later in this video square brackets are what you put around field names when you're doing formulas so I'm going to put a square bracket and I'm going to type the word or the phrase enter supplier code close square bracket what you're telling Excel with an exhale what you're telling access right now is this does not have any criteria but with the square bracket and this data a pop-up box will jump up and allow you to enter the criteria so let's try it I'm going to click view and you'll see immediately it's not going to let us because this is the pop-up message you get so now I can type watch B H click OK and there it shows B H so let's close this it's going to ask if you want to save yes indeed because I changed it I change the structure and now every time I open this double click it's going to ask me what who do you want to see data for so I'm going to type SC enter totally cool a parameter query all right now our fourth query is going to be just a simple one I'm going to go up to create create design immediately show the products close pull it down and I want to do a simple query but this time instead of selling price I want to look at cost so I'm going to double click product description cost and that's it I'm going to go run it then there it is it just shows me our cost so we we could see we can have criteria but we can also in essence have our criteria be just show me some of the fields not all the fields now I'm going to control s and I'm going to say cost and product or product and cost or something like that all right now I'm going to close this I'm going to go back up to create we'll go on to our fifth query now this one we're going to show our products table I'll race you here pull or pull this down pull it up in the middle we'll have to do that every time this time I want to show all the records and then we're going to add some special criteria but I want to show you a cool way to add all of these fields at one time if you click here and before you click on the last one can you remember from our studies of Excel PowerPoint Windows Explorer and Excel and word what is the key I have to hold before clicking on the last one in order to highlight everything in between shift and so I can use that trick we've been using once they're all selected I can simply click and drag and there they are now if I go out here this is the same thing as the table right but now I want to do what's called contains criteria what I would like is to search for all of the products that contain rail now we should have something like a rail railroad and a rail Rea bridge or something like that you know if you have lots of a wooden toys like this you probably have a bunch of railroad related stuff or maybe have a bunch of Thomas railroad related stuff but now we have to type the word rail now right now that would not work for our particular goal here because it would look just for the word rail but if you put an asterisk before and after it means search for anything that contains the word rail now the way this works is that a struct can mean zero or more characters so really what you're asking the database is hey in the description field show me rail and anything zero or more characters before or zero or more characters after so it could return the word just rail that would be zero before zero after it could return the word railroad that would be some characters after or whatever let's go ahead and see how this works I'm going to click on the view and there it is contains that is a very handy trick to know for example if you're searching through address data and you want to find businesses on a particular street or something like that alright I'm going to control s and I'm going to type contains rail all right let's do our next one the next one we're going to use some number criteria I'm going to go up create design show the table drag this down drag this up I'm going to show product ID description and supplier code and then for cost and I just simply want to do some criteria here now back in Excel we saw comparative operators less than greater than less than or equal to great and other comparative operators here we're just going to type less than 10 and that's it the small side of the comparative operator is pointing towards you know the entire column right so it says find all the ones that are smaller the big side of this is pointing towards the 10 so that means 10 has to be bigger than anything in that column cost column in order to show these four fields let's go look at our answer oh look one two three four five six alright I'm going to control us let's say something like less than Oh cost less than ten bucks now I'm going to I put a dollar sign here don't put any dollar signs in the criteria just got to keep the numbers without the dollar sign but here this is the name of it so that's perfectly alright click OK wow look at all of our cool queries now the beauty of these queries and we saw this in the very first video of course is if right now if I double-click this we could see all of these but now if I go change any of the source data the coal car let's say the price moves up to 15 right it won't show up in this query so I'm going to go to the products form I'm going to click here and ctrl F I'm going to type coal and I don't want a whole field I want any part because I'm searching for just a part of coal alright and then click find next and so now I found it I can close this if I change this to 10 oops 10 and then close it notice I'm not saving the raw data oops what did I do oh let's try that again that was the old clicking without paying attention all right I'm going to click right here ctrl F fine next I'm going to try this again 10 okay so that should work I'm going to close all right and now when I go to cost less than ten now the that particular item is not there anymore the coal car so these are totally dynamic that's why you name them smartly and you probably if you're running a business your entity or whatever you're probably running the queries to the you know often and so it's totally dynamic totally amazing all right I'm going to change this I'm going to go back over the products table since this is small I'm just going to come here and coal car I'm going to change to seven so you can go either in the table or the form I'm going to close that notice I didn't save the raw data now let's create our next query this one will be a greater than or equal to so I'm gonna go to create design show the products table drag this up and let's see product ID description and sell price so Boop sell price and I'm going to do greater than or equal to now as we talked about it back in Excel there is no single character for greater than or equal to see just have to type two characters the equal sign always comes second alright let's go and run this there it is so price greater than 20 I'm going to control s and what should we call this hey so price o greater than or equal to 20 bucks alright now we want to talk about and a and D and or oor criteria so I'm going to create I'm going to show the table pull this up alright now let's go ahead this is number eight in our handout I'm going to show all the records where sell price is less than less than fifteen bucks and on hand is equal to ten or is greater than or equal to ten so let's show it let's show all the fields here again I didn't say that right this is going to be a nan criteria there's going to be two things that have to be true in order for a record to show I'm going to drag this down here now the first one is we want to say please show me sell price less than fifteen and a second thing has to be true in order for this record to show I'm going to say on hand right here greater than or equal to ten now notice something very important and criteria has to be on the same line this is on the same line and the way I remember it as I always look over here to the side and there's a visual cue the Bill Gates and their pals put the word or here which means all of these are available for or we'll do an or criteria and in our next query but our criteria goes on different rows alright so if you want two things to both be true they go on the same row now there are some other ways to do this we could but the reason why I remember it as one row for and and more than one row for ORS because it's the same over in Excel if you're doing advanced filter' which we didn't do in this class but it's kind of nice that they work the same way in both places all right so there you go let's go ahead and try it every single time the record gets shown there's going to be a true for this which means the on hand is greater than or equal to ten and there's a true for this less than fifteen let's go ahead and and look at the results and there it is let's test it number two things have to be true both get a true true on hand less than or are greater than or equal to ten true sell price less than fifteen true same with here greater than or equal ten ding-ding-ding-ding true less than fifteen true alright let's save this control us and I'm going to type something like sell just SP less than fifteen and on hand greater than equal to ten okay and then click OK alright so that's an that is an and criteria now let's do an or I'm going to close this query go back up to create show the products table we're using the products table every time you can use any of those or any combination we will have one in just a few queries ahead where we do multiple tables at the same time alright now this one we're going to do or I'm going to show all fields again by the way you can show this button right here shows all the fields but then you can't do criteria on individual fields so I'm going to highlight all these drop it right here and I'm going to go over to supplier code and I want to show SC or b8 so I'm going to type s see you actually can type or right here those in but I'm going to I remember it this way because as you get more complex queries your or criteria is listed this way so I'm going to type BH so on different lines now let's go ahead and run this and there it is true true true each one we get a true so this one is different it asks it looks at every record in the original table it says is this equal to SC if it gets a true that immediately to pulled up if it's a false in this case it would have been is it equal to SC false is it equal to be H true and so that means it will extract it so in in terms of logic there's too long tests but only one of them has to come out to be true to show the record alright I'm going to control s and I'm going to say SC or BH or maybe something like supplier SC or BH click okay alright now oh now we get to do some formulas let's go to create design I'm going to show the products table I'm going to pull this up now we got to do our first formula now we're going to calculate inventory value so I'm going to show product ID in description and I'm going to make a calculation now watch this we can type it right here I'll zoom in when I do the video or you can just type your phone in here or you can right click and point to zoom and it just gives you a little bit more room here well let's go ahead and do it this way okay ready there's two syntax things we have to remember here we have a calculated column and it's going to have a new label which means it's a new field that's going to be called inventory value well there is no field called inventory value but we can type the words now in order to get it to recognize as a new label or field name calculate a field name you have to type a colon so the first syntax were learning is colon and the way it works in form is is everything before the colon is the new name of the field and everything after is the formula now think about it how do you calculate inventory value well there's lots of ways but for us it's how many we have on hand times our cost so really we're multiplying two fields now the second syntax I have to remember is square brackets if you use cost times on hand that will not work and the reason why I mean it's perfect field name x field name but it won't work unless you put them in square brackets that's just the way it is these square brackets also show up in Excel if you're ever doing formula table formula nomenclature a square bracket here in axis and sometimes over in Excel means this is the fields name alright let's go ahead and try this I'm going to click OK this is exciting our first formula I'm going to run big meaning there it is now this is an example of you a calculated field where we avoid redundancy right we already have the the raw data information which means on-hand units and costs stored in a table so you'd never take up space by you know typing in values like this you make a calculated field wow that's totally cool I'm going to control s and I'm going to give this a good name what's a good name for this oh yeah inventory value and we'll make a report out of this one later let's just go test it this is too cool our first formula and access I'm going to go change barnyard friends so I'm going to go to on the products products form barnyard friends I'm going to change the cost to $1 just temporarily close this now I'm going to come back to my inventory value double click and now there it is just totally awesome dynamic table later we'll see the real power of the database right we have some rod out of there this is a query inventory value there's the answer and we'll make a report of it right so you'll never have to do your calculations again you just automatically print out your report each end of each week or whatever and it's totally dynamic the query here and later the report obviously are connected to that raw data stored in the table I'm going to close this I'm going to come over to products and barnyard friends you know I don't remember what the price what the amount was can someone please tell me what the that was oh yeah 27 right thank you for whoever said that just kidding all right so 27 let's close this all right now let's do another formula create design DoubleClick's products pull this up let's let's calculate gross profit so we'll do something like product ad description and then gross profit right click zoom you don't have to you can type it out their gross gross profit : is the syntax that says everything before is a label everything after is a formula it is selling price and I'm going to type my square brackets that's something I got my square brackets selling price I hope I spelled that right there's actually a great feature you can actually build these you don't have to type these out but we're typing them out all right so it's a sell price - not x cost in square brackets now just to show you what happens lets misspell something I'm going to click OK and I'm going to click run it doesn't have any idea what's going on we've misspelled a field and remember how earlier we did a parameter query and we type some text that was not the name of a field into square bracket so that's what's happening here I'm going to click cancel and then I'm going to edit this I know that there's some trouble as soon as I see that I know I did not spell the field name correctly so I'm going to zoom and try cost click ok let's run it let's see if this works oh I still got it right selling price oh okay so I didn't pay attention earlier I'm going to click cancel I don't remember what I called it now in the handout it said sell price let's try this one there it it so I as we said earlier if you misspell something you have to misspell it everywhere else throughout the database because that's in essence what communicates with that table so I had both cost and sell price field names incorrect but now that they're correct I'm going to click this and there it is the gross profit so when we sell a barnyard friends there is just the difference between cost of goods sold and the sell price 23 bucks all right I'm going to control s and call this something smart like gross profit click OK click OK all right our next query or next question it's going to be hey please calculate the average selling price so I'm going to show that products table and watch this I'm going to just double click selling price once this is going to be an unusual query it's going to be on one field and we need to show the total rows there's a button up here for totals or you can right click totals and sure enough there's something called total you come here go to the drop-down and there's a bunch of functions here let's do a verage a V G and that's it when you click can you guess what the result will be since this is a field right and that's a single calculation when you calculate an average it gives you a single number so let's go ahead and click on this and that's it it just gives you a single number this is not something you're necessarily going to print out as a report but it is a useful query right as time goes on we can see whether our average prices going up or down now I'm going to control s average CEL price and then click ok so we see right there average selling price now I'm going to immediately this one's useful in that it gives us a single average but I'd like a verage for each particular supplier so I'm going to copy this and control-v and immediately I'm going to say average sell price or each supplier or something like that that's what that's our question we're going to ask of the database all right so now right-click design well let's see this gives us all of them what if we drop the supplier code over here by default it says group by and that's it it will group by this so it in essence will look through this field find out a unique list meaning give me one of each different thing that's in this column and it will calculate the average for it so I want to come over here there it is so we've bought from these three suppliers and the average sale price for each one there it is control s now let's do the same thing again but here let's copy this one control C control V and now what I'd like to do is do that parameter query again so I'm going to say average sale price for each supply on then in parentheses lted parameter and then click okay there it is right there right click design and now I'm going to come down here to that criteria row and the same thing we did before square brackets enter fire code now remember we've run into this a few time here we're using it to our advantage that thing spelled inside the square brackets doesn't exist as a field name but that's what we want here earlier when we did our formula early and had trouble it was not what we wanted all right let's go ahead and click this enter supplier code I'll say SC and there it is all right I'm going to close this it's immediately going to say do you want to save it yes all right now I'm going to actually do 16 now this is between criteria and then we'll do the 15th one which is the Joule query a between two tables all right so we're going to go to create design show the products table drag this up drink this down a little bit let's show product ID description and selling price and now we want to do between criteria we did contains criteria we did an or criteria we did an and criteria this is a between I'm just going to say between 15 and 25 and this is kind of cool here we get to mix words between 15 so that's there's a word and a number and 25 and it'll know what to do can't do this in Excel right I mean you can do between criteria but you have to do greater than or equal to 15 and less than 25 are less than or equal to or what however it is so let's click ctrl well I was let's run it and see what we get and there it is values between those two bookends control S how about sell price between 15 and 25 now notice look at all these queries and that is often the case right because all the data analysis done inside of a database is a lot more of that than there is you know raw data stored in tables so we got lots of queries there alright let's go ahead and close this now our last query we're just go to create query in design and let's show two tables products and suppliers no big deal I mean we only do one of these here but you can ask questions and multiple tables that's kind of a nice amazing thing and you know in our class we never got to you know the super advanced formulas where you do data extraction with formulas it's all pot all of this is possible over in Excel it's just a lot harder so learning a little bit of access like this to do queries across multiple tables like this is just awesome because it's so easy all right so now this one we want our question of the database is going to be please make a reorder phone list so I need some well how do you determine whether something is we need to reorder it well let's say on hand is less than five so that's an example of a field from here but guess what we need the information first name and telephone number from this table so lucky there luckily they're joined so let's go ahead and I'm going to do supplier code first name last name and telephone and then from over here we probably need product ID let's see if we got a product ID so we and the description I'm going to do on hand or cost next and then on hand notice we have a lot of fields here now we come over here and we're simply going to say well let's look at the answer right here right I notice there's lots of duplicates we will when we get to our report see they're individual products so we need to show each one of these but when we get to a report we'll group and it will show only the name and the telephone number one time and then all of the products underneath but there it is that's all of the records we need to exclude and only show the ones less than five or less than or equal to so I'm going to go back underneath and on hand I'm going to say less than five and now when I run this you can see ding-ding-ding there we go those are the ones it's totally dynamic right I want to reorder these will not be in this reorder phone list alright I'm going to control s and call this reorder phone list or something like that click ok alright so that was a lot about queries in our next video we'll our last video in axis we'll make two reports one from the inventory value and Riyadh or foreign list and then we actually have one final video that will kind of we'll do a mail merge between word access and Excel alright see you next video

Channel: ExcelIsFun

Views: 300,020

Rating: 4.8082457 out of 5

Keywords: Office, 2010, Access, Mike, Gel, Girvin, excelisfun, Highline, Community, College, Ron, Davidson, Computer, Information, Systems, Busn, 216, Applications, Class, Create, Queries, in, Simple, Query, Word, Criteria, Parameter, Contains, Less, Than, Greater, Or, Equal, To, AND, OR, Formula, For, Inventory, Value, Gross, Profit, Average, Group, by, Reorder, Phone, List, Between

Id: WQkI5QdojII

Length: 37min 53sec (2273 seconds)

Published: Mon Mar 07 2011