About : office furniture computer table
Title : office furniture computer table
office furniture computer table
welcome to office 2013class video number 48. hey, we're still studyingaccess, and in this video we want to talk about queries. now, in the lastvideo, we created tables, the heartof any database and we created formsto test our database and to enter datainto our tables. but in this video, we want tosee the power of access, which is not just to storeour data but to create
useful information fromthat raw data using queries. now, i'm going to goover to our handout. here's page 19 of 21and pages 19, 20, and 21 list our queries. now, queries-- we did lotsof excel in this class, and we did queries inexcel, things like a filter. but it all comes downto this-- data analysis, going from raw datainto useful information. we're always converting the rawdata into useful information.
so the definition ofa query-- well, query just means question--we're going to ask a questionof the raw data. the answer that we getis the useful information used for decision making. so here's our listfrom three to 15. you can read that if you want. otherwise, we're just goingto go straight over to access. now, let's justopen up our table.
double click, andthere's all our raw data. now, our first questionis a simple one. i just want to say, hey, showme just description and the sell price. see, here it is. we're a small kiosk in themall selling wood toys, and i just want aprintout of the names of the toys and the sell price. so, this is a verysimple question.
we don't want to seeall the raw data. we just want the name ofthe toy and the sell price. so i'm going toclose this table. now, where do we goto create queries? we go to the create tab. and in the query group, we'realways going to use design. so, query, design. so i'm going to-- boop. it's always going to askme which tables i want.
so i'm going to doubleclick the products table. later, we'll use bothtables for a single query. here, we're just goingto double click and show the products table. i'm going to close this. immediately, we knowwhat this is right here. we've seen it in excelwhen we did pivot tables. we've seen it here in access. it is a field list.
remember, fields arethe thing in access that connect all ofthe different objects. so here's a field list. and down below, we canpoint to the middle, and we see that cursor. we can click and drag up. here's where we createour question or our query. now, here's a field list, andi want to see descriptions. so i'm going to click anddrag 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 iwant to sort it or not. and later, we'll seehow to apply criteria. but remember, this one is, hey,show me all of the records, but i just want to seedescription and sell price. so i'm going to clickand drag down there. and there's an exampleof a simple query.
it's a question. show me a descriptionand sell price. now, i'm going tocome up to view. you can click run, but ialways just click view. remember, we have two views. we're going betweendesign and data sheet. so when i click datasheet, that's the answer. all right. now, let's control s. don't savequeries as query 1, query 2.
the textbook has you do that. that's silly. always give your objector 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 isdescription and sell price. click ok. all right, so wheni close this, now i have, oh, my first query--description and sell price. i can double click itand see the answer.
if i want to gostraight to design view, i simply say, rightclick, design. now, in design view,we can edit our query. i want to come downto the sell price. field, table, sort,show, and criteria. i want to use the sort. so i'm going tosay 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, anytimewe need to look it up, boom, we have it right there. now, when i close this objecthere, it's going to ask me, do i want to save. yes, remember? objects, anytime you change anyaspect or structural element in the object, youhave to save it. so i'm going to say yes.
it's only that raw datathat's automatically saved. all right, so that'sour first question, our first query,our first answer, our first task of creatinguseful information. now, our next question, if we goover, double click the products table, i'd like to show justthe records for sc supplier. so close this table, create. queries group, i'm goingto use query design. show tables, doubleclick the products.
close. pull the cornerof the field list so i see the whole field list. i might even drag this up. i'm going to closethis property sheet. and here, i want tosee all of the fields. so watch this. select the first one, andbefore clicking on the last one, we know we've seenthis keyboard and mouse
trick maybe 100times in this class. we hold shift and thenclick on the last one. whoops. click on the first one beforeclicking on the last one, hold shift. there we go. now, watch this. i can click and drag. now, don't drag it outhere in the middle.
be sure to drag it to thefirst column, and boom, instantly we haveall of the fields. now, right now, ifi click view, that shows me everythingin the table. i'm going to go back to design. i want to use the criteriarow for the first time. go over to supplier code,and who is it i want? sc. now when i clickview, beautiful.
there's my answer. there's my useful information. any time i want to see the toysfrom sc, i open up this query. now i'm going tocontrol s, and i'm going to call this somethinglike products from sc, and click ok. now, if i double clickand open this at any time, later when i have moretoys or fewer toys from sc, this will update and be dynamic.
now, notice we've hard-codedsc into the criteria area. there's a different querycalled a parameter query. and actually, from the list ofqueries, we have three to 15, this is actually 15. we're going to basicallydo the same exact query except for i'm goingto change the criteria. i'm going to come over andclick on the query, control c for copy, and thencontrol v to paste. so i've copied it.
ah, it's being polite. you can't have the same namefor two different queries, so i'm going to callthis records by supplier so now i have this query. i'm simply going to cometo it, right click, design. and i'm simply going tochange the criteria down here. now, remember what we said--we saw square brackets briefly in our last video. and back in excel, wesaw the table feature
use square brackets. but square bracketsmean field name. but check this out. if i use a square bracket hereand enter some text that's not a field name, i'm going tosay enter, enter supplier code. now this becomesa parameter query. so a dialog boxwill pop up and ask us to enter thesupplier code we want. by typing entersupplier code, that's
just the text that willappear 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 totype ap, because that's one of the suppliers,and click ok.
and just like that, we see ap. now, i'm going to controls on this and close it. now, anytime we comeover to our queries and double clickrecords by supplier, it's going to be polite. if i want to seesc-- and by the way, whether or not they'reupper 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, openit, next time i type ap, enter. and just like that,this parameter query is showing us all of therecords for whatever supplier code we type in. now, our next question, we'regoing to look at the raw data, i'd like to showon hand but only when on hand is greaterthan or equal to 10. so i'm a close this. go up to createqueries, query design,
show the products table,close this dialogue box, pull down the fieldlist, drag this up a bit. i want to show all fields. by the way, that asteriskwill show all the fields, but then you can't use criteria,and we have criteria here, so we want to show allthe fields this way. click and drag tothe first column. now, i want to comeover to on hand. and in the criteria row, i'mgoing to say greater than
or equal to 10. remember, we've seenthis a bunch of times in excel and in access. there's no such thingas a single greater than or equal to or less thanor equal to character, so you always have touse two characters. now let's run this. and just likethat, we can see we have two toys that have10 or more on hand.
that means number ofitems on the shelf. query 1 is not a good name. control s, i'm just goingto put on hand greater than or equal to 10, click ok. now, let's go backto design view. notice, we have asingle condition here. in our next query-- i'mgoing to close that, and we can see it's listedthere-- opening up the products table, let's say someonecomes in and says,
hey, i have a party. there's 10 people. but i don't want to spendmore than $10 on a toy, so i need to find a toy thathas on hand greater than or equal to 10 and sellprice less than 10. so i'm going to closethis, go over to create, query design, doubleclick products table, pull down thecorner, pull this up. now, in this case, i just wantto see description, sell price,
and on hand. now, criteria-- we havea criteria for sell price and for on hand. so for sell price, it has to beless than 10, and then on hand has to be greaterthan or equal to 10. so now, this iscalled and criteria. two conditions haveto be true in order for the part of therecord to be shown. and criteria alwaysgoes on the same row.
notice this criteriarow here, there's lots of other rowsbelow for or criteria, and we'll see an examplein just a moment. or criteria goes on differentrows or different lines. and criteria goes on asingle row or a single line. now later on when we go andstudy excel in business 214-- this class is a prerequisitefor that class-- the advanced filterfeature inside of excel will follow the sameexact convention.
so will database functions. so here in access, andcriteria goes on the same row. all right, when iclick 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 goback to design view, change the on hand togreater than or equal to 8, and now when i runit or click view,
there are two toys to choosefrom-- coal car and pets jigsaw, 10 and 8 on hand. now i'm going to controls and call this sell price less than $10 and on handgreater than or equal to 8. now, let's go backand look at our table. our next question is goingto be a type of and criteria, but it's calledbetween criteria. we would like to see sell price,but only between $5 and $10. so let's close this table.
our question is, show mesell 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 showall of the fields, so i'm going to hold shift,click on the first one then the last one, click anddrag to the first column. whoops, it didn't quite take. now i want to cometo sell price. and this is kind oflike our data validation
we did for cost when wewere building our table. we actually typed out thecriteria all in a single cell. so here's how it's going to go. between 5 and 10. now, when i clickview, boom, we can see all of the sellprices between 5 and 10. it looks like there'sthree products. now, i want to goback to design view. between 5 and 10, those aretwo conditions or criteria
that must be met. each price must be greater thanor equal to 5 and less than now, i'm going to save this. control s. we'll call it sellprice between $5 and $10. we can go ahead and close it. now, with those two, sellprice between 5 and 10 and then sell price less than10 and on hand greater than or equal to 10, thoseare and criteria. now we need to talkabout or criteria.
so i'm going togo over to create. actually, the question we'llask if we open up our products table, i want to seesupplier sc or ap. so if we were doing that overan excel, we would use filter, and we would check the check boxand 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, holdshift, click on the last one,
click and drag tothe first column. now, it's this suppliercode, criteria and or. so, the way i alwaysremember 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 needsto only get one true when it's applying the criteria. and in our last twoexamples, the and criteria
meant that both conditionshad to come out true. all right, so we're goingto type sc on one row and ap on the second row. that means it willask each record, are you sc, or are you ap. when i click view,just like that, i can see that i haveall of the sc and ap. that's or criteria. go back to design,different rows.
control s. so,suppliers sc or ap. i'm going to goahead and close this. all right, so we'veapplied some criteria. now we want to see how to dosome queries based on formulas. now, the first one is i justwant to open up the products table, and all iwant to do is figure out what the average sellprice is for all of our toys. now, in excel, we wouldhave the average function, and we'd highlight thiswhole range and do average.
but here's how we doit over in access. we go to create, query design. we'll show our products table. and watch this. i'm simply going todrag sell price down. and we need toshow the total row. now, totals you can come up andclick this totals button over here. you can right clickanywhere in our design grid
and point to totals. there's a total. we come to that particularcell 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 clickavg, and watch this. this is going tobe weird in terms of getting a result for a query.
when i come up toview, 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 reportyou're going to print out. this is just dynamic. at any time, youcan open this up. and no matter whattoys you have,
it'll tell you theaverage sell price. i'm going to control s.so something like average sell price. click close. now, i'd like tochange that query. i actually copy it andthen change the criteria. average sell price is fine. that's for all the products. but now i want to doaverage for each supplier.
so we'll do our same trick--copy this, control c, paste it, control v. the namecan't be the same, so now i'm going to say average sellprice by supplier, average sell price by supplier. and there's my averagesell price by supplier. right click, design. now, we're simply going to dragsupplier code, and we're done. what's happening here is now itsays group by in the total row. so it in essence will gothrough the supplier code
and get a uniquelist, one of each, and calculate the average. now i click on this viewbutton, and just like that. we have only boughttoys from three of our total of fivesuppliers, and there's the average for each one. control s, and then close. so both of these wereaggregate calculations. aggregate calculationsmeans we're just
calculating a single number. now we want to see how tocalculate some formulas, where we actually domath on the fields. now, let's lookat products table. we certainly would likea query and a report based on that query thatshows us cost times on hand. that's how this businesscalculates its inventory value. so i'm going to close this. we'll go to create, querydesign, show the products
table, drag itdown, drag this up. and here we simplywant to double click description todrop it down there. and that's the onlyone we're going to use. now we're going to come downand create a calculated field, and here's how we'regoing to do it. we're actually going to typethe new field name here. this is going to becalled inventory value. and as soon as wetype a colon, that
means everythingbefore the colon is going to be the new fieldname, and everything after the colon isgoing to be our formula. now, here's the deal. we're dealing with field names. so really, what i'd likeis cost times on hand. now, you can't justtype cost times on hand. the only thing youneed to add to that is to make sure that you putwhatever the field name is
that you're going to use inyour formula in square brackets. so let's try it. square bracket, cost, endsquare bracket, times, and then square bracket-- now remember,if you misspell it here, so be sure these haveto be spelled correctly. field names are what connecteverything in our database, right? so if you spell it wronghere, it won't work. so i'm going tospell it correctly,
close square bracket. so that's how we can build asimple formula taking the cost field and multiplyingby 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 expandthe column width so we see our new calculatedfield name, inventory value.
and for each one of the toys, itcalculated the inventory value. i'm going to goback to design view. again, everythingbefore the colon, that's going to be the new fieldname, calculated field name. everything after isgoing to be the formula. field names, got tobe in square brackets. control s, probably a goodname would be inventory value. and the beautiful thingabout queries, of course, is inventory value rightnow, skyscraper is 750.
but if i go to the formfor products-- now, i want to find skyscraper. i'm going to clickin this cell, and i'm going to use thekeyboard control f. now, control f is the same in wordand excel and powerpoint. it's find, control f. so you simply click in thefield 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 willfind all of the description fields that contain sky. so when i click find next,sure enough, there it found it. now, the cost isdefinitely not $125. it's actually 12.50. so check that out.
that was a typing error. but when we change itin the form-- remember, the beauty of adatabase in access is instantly from this form,it dumped it into the tables. so there's our correctskyscraper price. when i go over to my inventoryvalue, instantly it updates. so as soon as theraw data changes, everything in your databasethat's connected to it updates. now, let's do our next query.
i would like tocreate a formula that calculates gross profitfor each product. so i'm going toshow products table. that's the field list. pull it down. i'm going to showjust description, and now we're goingto come down here. if you don't liketo work right here, you can right click, zoom.
and it's not going toshow it to us any bigger, but it does give us a littlebit more room right here. so i'm going totype gross profit. remember, everythingbefore the colon becomes thecalculated field name, and everything after,hey, square bracket. it's sell price,close square bracket. capitalizationdoesn't matter here. i like to keep it the same.
minus-- now, let'sjust see what happens if i type this incorrectly. so i've misspelled it, so ofcourse it's not going to work. when i go to runit or click view, it's asking, remember, oh,this is a parameter query. so it would workto our advantage, like when we did oursearch for suppliers. but here, it'snot going to work. so i'm going to click cancel.
as soon as you see thatparameter query pop up, you know you've spelledthe field name right. so right click, zoom. and now i can come over here. let's make sureto spell it right. click view or run,and instantly, there's our gross profitper individual item. that's if we sell oneitem, our gross profit, sell price minus cost,23, 12.50, et cetera.
control s-- always givingsomething a smart name. all the way from thebeginning of this class, when we studied windowsexplorer, word, powerpoint, excel, and now in access,always naming things. even if you have justthat one teeny skill added to your toolkit of computerskills, that skill, it helps us to find thingsand understand things. so now over here, i havea bunch of smart names. i know exactly what allof these queries mean.
now, we have two morequeries we want to do. we want to do what'scalled a contains query. let's say we're lookingat the products table. and this is a smalldatabase, right? but maybe you want tosearch for railroad, all of the descriptionsthat contain railroad. in our case, i wantto search for all of the descriptions thatcontain the word rail. we're going to use a wildcard, an asterisk wild card.
all right, so i'mgoing to go to create, query design, showproducts, and i'm going to show allof the fields, i want to see the entire recordfor any toy description that contains rail. all right, so we're goingto come down to description. and although it willeventually show up differently, i'm going to type it inas if we were doing it in excel, because this wildcard works in excel and access.
the asterisk means zeroor more characters. so when i type rail orasterisk, rail, asterisk, what i'm telling the criteriais find the word rail with no charactersbefore or after, or any word that containsrail, with either one or more charactersbefore or after, so that when you usea wild card, it says zero or more othercharacters of any type. so it would find rail,railroad, railway bridge.
all right, so when iclick view, sure enough it found the descriptionsthat 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 ascriteria in whatever functions or formulas you're using. all right, i'm going to controls, rail query, click ok. that is a wild card. you can also usewild card if you
were searching for a particularstreet, everyone on birch or something like that. all right, i'mgoing to close this. we have one last query. let's look at ourproducts table. now, we would like what'scalled a reorder phone list. any time the number ofunits gets below five, we'd actually like to knowthe product id, description, on hand, sell price.
but we'd also like to knowfrom the supplier data table the person's name andtheir telephone number. and that's where having aconnection between tables is helpful, because now we canask a question of both supplier data and products table. if we remind ourself,database tools, relationships, we can see we have arelationship between these two tables. all right, solet's go to create,
query design, double clickproducts and supplier data. hey, look-- it looks likethat relationship window. now we're allowed tosimply take whichever fields we want from eachone 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 stufffrom different tables. but that's not what we want. we need a criteriaon on hand, so i'm going to go back to view. this toggle here is veryhelpful, back and forth, until you get your queryconstructed correctly and your answer looking good.
all right, let'sgo back underneath, and we're goingto go to on hand. and our criteriais less than five. so every friday, wego and print this out. and there are thepeople we should phone and the productswe should order. now, let's save this. control s, and somethinglike reorder phone list. so in this video, we saw alot about the amazing ability
to create queries in access,criteria, or conditions that then pull certaindata from certain tables and create useful information. now, in our nextvideo, we'll see how to create areport, all right? we'll see you next video.