Access 2016 - ADVANCED QUERIES, CROSS-TAB QUERY, REPORTS AND FORMS

okay on this next access chapter I want to explain some of the more difficult aspects of the chapter only because like I say that there's areas in that in the book that that really confuse a lot of people so first off what you need to do is to open the file that you started in the first chapter so CI looks like I'm in the wrong folder here I want to go to documents and my school folder and let's see I did notice an error in my previous uh my previous video I told you to save in unit 12 and I realized that was an Excel unit but in unit 13 here we are and I saved it there it is front last and associates that's the one we just created now now you need to modify this so come and do a save as now so go file save as and when you do the save as take it to unit 14 so you put it in your unit 14 folder and that's your prat last associates as well just do that right in the beginning and then you won't have too much trouble so I'm going to skip over some of the easy queries in this assignment and just demonstrate the more difficult ones okay the first thing is telling you to do is to use a different design before you use the query wizard now you're using the query design it's going to bring up a little bit different menu and the reason why is because you want to be able to choose which table you're using so make sure that you understand the table that you're using I believe this first one you're use in the account table now that the trick here so so once I've chosen the table I don't that's the only one I plan to use for this query so I'm going to close it and then I just need the account number and I think that you know that again the instructions are a little bit ambiguous here so really all you have to do is I went account number account name amount paid and the current due so I can grab all of these just by holding down my control key um let's see account paid and current do I've got them and now just pull them down Oh see it's not going to take it sorry let's try it this way double click no I guess I've just got to take it in the way they said that if I double click it it'll come in as well okay I'm not paying current do I thought I could drag them in but it didn't work properly now here you're just choosing the criteria and then this one yes JM 3 2 3 and so you go ahead and you run the query and that's the only one that you get and the next step is probably the trickiest part to this one and you just need to save it as M 0 to Q 0 1 and you're going to be making a bunch of queries here so so this is just to give you a list and you can see that there's your your first query ok this one we're going to learn we're going to create a new query now we can close this one off don't really need it anymore and we're going to now go to a new query design again we're using the account table and you can use account loops close this account number account name the amount paid and the current do now in this one we're using a wild card wild cards simply means this letter and the wildcard mean is the asterisk and that's any any letter after that one so that one's a common one again that we use and so it'll be anything with a K and that's your query they're going into this whole big save as thing that often time compute confuses people all you have to do is right click it click on save get the option and M 0 to Q 0 2 another tricky step that we can do is we can copy these queries and then keep using them because we're using the account queries several times so in this one all I would do is right click it click copy and paste and now I've got that one but that one's supposed to be M 0 2 Q 3 I believe ok so now I'm at that query and I just have to modify that query so I go into my design that was with a right-click again and Here I am and I'm going to take off the wild card and in this one we add the city and it's supposed to be Granger and run it there's that okay the last step to this one is just they don't want the city showing so all you have to do again is come back to the design and uncheck it it still is picking up the city of Granger but it doesn't show in the query ah I named that wrong that last one wrong so what I need to do is to close this off and it's supposed to be called let's rename it okay it's supposed to be called a count City so but if you notice all I did was right click to rename it and so now account City is the name of that query so I missed one step here it's again in the design view they're just telling you okay now you've tried the Granger deal now try to do one that you have to enter the city and that is just to do a bracket not that kind of racket this kind of bracket do you though and it's enter city and all that does is when you come in it gives you a dialog box when you get ready to do the city and now and it won't show the city but that as Wells was the first one and that's what you're doing is you're testing to see if you can make it work but you'd have to know what cities were in there in order to get it to to give you the information you want okay here's your third query coming up what you're doing again is you're copying this one and then paste it and this one's going to be called here and now we're modifying that again what we're going to do is play with the design a little bit again right-click design and they want you to you're taking off the criteria here and the first one you're just doing an amount do of zero running the query and you see what happens okay let's start query number four so let's copy again go to a number four and four and this one let's go down to our design view again and we're taking off the K and this time of the we're changing this is supposed to be a account of the account number oh wait a second let me see it maybe I using a different table okay true confession they switched tables on me so let's see what we've got here with the table names umm no I want to see the table names I want add a new table so I think probably the easiest way to do this would be to close out this one um say no there and let's go ahead and create a new one from the query design and use the accountmanager table and this time um let's save it under the correct name m0 - 2 + c q0 4 well that's cute okay there we go and yes we're replacing that one because i mix up the tables okay so we want the accountmanager number the last name the first name and the start date where is it right there okay ice double clicked on each one of those to get them down and then what we're doing is we're saying okay let's take a peek at this there's your start dates but we're going to start we're going to change it to the start date should be greater than 1 / 1 / 2015 ok so that's that's considering a comparison operator so I'm just saying that okay their start date has to be greater than that date so let's see what happens now and I get that the two folks okay so this next query is using the account table so I can go back and copy that previous table that I used it was for account and let's go back and use it again and it was this one copy again and paste and that one is number five okay let's open her up go to right-click design view take off my kay I think there's some fields I need to add so I have account number account name amount paid current due and account manager number there okay so now what we're doing is we're building an and condition and with an end condition they're on the same line okay you haven't you have different types of conditions you can build you know build an end condition or an or condition or would mean that both both things apply and condition means that that in order for it to work both of these things have to happen and so they're saying the current due has to be over 2500 and the account man sure has to be number 31 okay so if you run this ah you can't set criteria before you add a field up and for goodness sakes I see what I did I put this in the wrong area okay so it's 31 okay so now let's run it and let's see what that would it look like if I've done it as a or condition okay so I'm going to come over here and pull my 31 down below that's an or condition and see what it looks like see I get everything so that was over that dollar amount or the 31 okay see so I even got some that were at zero so it just has to do that that the end condition both of those conditions have to apply and they're on the same line so we're back to what it should look like and that's what it should look like well I guess I need to read ahead in the book because the next one is the or condition so let's just copy it and paste and just change the name to number six and here we go so now we're on number six right click design view and Oh for heaven's sakes I should have here better make sure that my five is correct because I thought that's the one I copy okay so let's go here and that should be the current do a account manager and we're doing oh I think it's pre testified wasn't saying that it did that okay so we're doing a greater than the 2500 and then we have the or condition which would be 31 okay let's run it alright now let's go back and save and make sure that all of this if I do it close all it will save everything yes yes yes yes now let's go back and look and make sure those conditions are in there yeah see there's the end condition and here's the or condition okay okay so this one is one of those that the instructions is just terrible or just terrible so we're just going to copy again this and we're going to come in in the paste and this is supposed to be query number seven and this is where the instructions get really weird okay so so I need to make sure that I close these off because these are already saved all right so it's saying okay let's go back to our design view and and what it's doing is it's telling you okay now now come in here and get rid of all these with this select thing going on what you can do I mean you just have to you just have to well it's just tricky to do this then hit delete okay so we're doing the city just pulling them all in and the first thing they want you to do of course is to view what you just did and then you're coming in and you're doing some changes to it of course so let's go back to design view okay so your city is going to be sorted in ascending order and over here you can get real um real specific and this is your is your property sheet and then here what you're doing is you're coming in and you're saying okay properties okay um if you look couple things if your property sheet isn't showing up over here it's because it's been closed right here and so what you need to do is just click on property sheet and it will come open notice that this is for the city field if I click in a blank field I get more options and this is where they want you to change this to a YES on the unique values and what that does is when you run your query see you only get just the just we don't get any duplicates in the names of the city and that's what they're trying to get you to do with that unique value option okay we're ready for a query number eight and we're still using the account query so let's just copy here and paste and call it number eight and then right click go to design all right so now let's see what we have we have going on here we want account number account name account manager number and amount paid so we want the current du over there let's just cut it and we take off the number and the amount paid supposed to be on the other side so I'm just going to highlight it and move it over and that was a click and a drag we want account manage your number in ascending order and account paid in ascending order as well so this one's ready to run and go so you can see that okay the numbers are in ascending order but the second criteria was the amount so that's the reason why thirty-five comes up with a zero in the 35 category I'm going to save these two queries um so that I make sure that I got them in my memory and close and then I'm going to make another copy because it's the next one so we're going to copy and paste and this again is number nine and okay all right so now I'm in query number nine and in this one I want to go back to my design view and I want the account number account name the account manager number and the amount paid so it's the same query but they've added some extra criteria and in that what you need to do is to come up into the design tab in this one right here and instead of return all you just do five now this one really throws people off and I've had multiple people out trouble with this one so let's look at see what happens with a regular run okay and we only want five so what we're going to do is come back to our design view again go in and choose the return is five of them and it's ascending so it'll be only the amount paid and I believe let's see um I don't I think we're supposed to change some of the criteria a little bit nope double checked it and it's still ascending ascending and there's my five so if I run it'll just be the top five numbers and we're good to go okay the next one is really um different for you so I just want to save and close this one and let's get started its work rating and we're going a query in the design we're going to use both of the tables and early on in the first chapter they men this about these these tables okay and why they set it up a certain way now I'm just resizing these so you can look at all the fields a little bit now notice that these two tables are related to each other and it's on the accountmanager field when we did the first time when we brought the accounts in we use the account number as a primary key and this one when we created it from scratch we use the account manager as the primary key now we have a relationship between the two and and if you look let's see if I can get into the relationship it's showing that all only will include rose from the join fields now this particular menu can get extremely complicated if depending on what you want to do in it in Access they're not doing extremely complicated in this textbook so you're just doing a basic join but I want you to understand how the join works okay because what you want to do is to make sure that you have one common field this in this case the common field is the accountmanager number field okay and remember it has unique numbers now the next part is just making sure you've got the right table for which area so right here we want to do from the accountmanager table we want to do the account number that was the first item then that's supposed to be ascending and then we're going to do the last name from the account manager there and the first name from the account manager then we're doing the account number from the account table and the account name from the account table right here there we go and the account number is supposed to be ascending as well now when we when we run it we find that it's working and if you wanted to expand this column of course you could expand it a little bit by just pulling out the side now this one has a different name so they like to mess with you a little bit here it's called the manager account query okay there you go now they're having you mess with the the join a little bit so you're coming back to your manager account query and you're going to the design and there's your join okay now remember if you double click that join it gives you this menu and they're saying okay now we want you to change this I want everything from the account manager wait should o left column is account manager number what they want you to change the option to include all records from account this should work and the other one we had the tables were just reversed but it should work just fine if you do number two and we come on okay and take a view see okay so this is a good example because when I did this remember my cables reversed from the ones in the book so I don't want all records from account and only those records from account manager where the fields are equal I want all message records from account manager and so and so that was a little bit different in the options so when I click OK now even though my join properties my left table is the account and my right is the account manager if I run it now I get the results so the textbooks asking you to get so that's a good example to show you how those join properties work next step is for us to get and create a report so we're doing a report and this one is we're using the report wizard so we're coming up and we're going on report wizard we got the query sage yes and it is on that one so good now we can tell it which fields we want to bring over for this report and I believe we're bringing over looks like we're bringing over all of them so might as well just click here okay and and we'll go on to the next one and next and do you want to add any grouping well let's see what it says on the grouping and the instruction it actually the only instruction that it says is to make sure it says that it's tabular and it looks like it is seeds that say okay so we're coming here and what goes in ascending order okay so all we need to do is that since the since it's already sorted in the in the query we don't need to put anything here and it's tabular and next it's portrait and this is the manager account query and we're previewing the report okay so it is coming up eight-and-a-half by eleven looks okay to me they're just trying to get you to go into the print preview if you want to take a look at it um I could just go to print and I don't want to print it right now I just want to do a preview this this is technically a preview right here oh I'm in it so I just need to click on close print preview okay we're good and then if I wanted to change anything it would maybe may make those margins out but there they look fine okay now we're making a form so we're coming in on the create option and we're going to the form wizard and again we're throwing over all the fields I believe yep and let's go next and this one is supposed to be okay I went to the form wizard and I should have gone to the easier one which is just going to form and it creates it for you and then you need to save it okay it's your last step let's go through and close all of these and what you want to do is export this so we're going to the external data and this time I'll we're going to the export to excel and we're just making sure that it's the manager account 1 and they want you to check this box with the formatting and layout and you should be good the only problem I see so far is a path here I want to make sure that it's going into my unit folder so I'm going into Cleves coming back to my school coming to questa and finding my unit folder units and this is unit 14 and there's my manager account query in Excel and go I don't think we need to save the export steps on this one so we're out so now that I want you to do something and then tell you not to do it this is always fun so you come here and go to design and you say okay well the following they want you to modify the manager account query so we've got manager name first name last name account number account name and amount paid so we're adding a field and there's the amount paid field and then we're coming in and we're going okay let's make this greater than 2,000 or 20,000 it is and then we're taking a peek okay and then it says now don't save that just run it click on no on this and then we're going to click on no okay for this next one it's a little complicated but I'm going to take this manager account query and I'm going to copy it and then I'm going to paste oh no I used the wrong query so I'm going to stop on a pause this again I need the I need the one that does not have the join so this one is the this is just I'm going to copy the query number nine and paste that one and rename it ten now this is one of the more complicated processes you need to do so so make sure you're paying close attention to this one so I'm going to coming back into my query design and what you want what you're going to be doing is adding a amount paid and the current do so so I need to click here on current do and you're going to be working with these two fields together to make another calculated field and this this is something that you have to do in the test for this so you you just need to make sure that you understand what you're doing and so the idea is you're actually what you're doing is you're creating a new field altogether so you're coming you're coming in this blank area and you're going to the zoom option and you're and you're creating the field now you can the book as you're typing this in but it's almost easier if you come here instead of going to zoom you go to build and then see you can say okay it's from the account manager let's see it was from this table the account table and I'm going to take the amount paid and I'm going to add with the plus the current do so much easier that is then to do it the way that they have you typing this in and so that's a little complicated then what happens when you come to this side you're supposed to give it a name for the field you can't do that in this box so when you click on okay it just comes up as an expr and that's what we need to change let me look see what we got okay there it is that's where we need to change it's under over expressions a second okay I see what caused me a problem I needed to save this first because it wasn't showing the current due because I added that one so when I come in here now and I go to the build see current do was there and I can just type in total amount and then add put in the amount paid up this should be without the expr in front of it right there total amount okay then the plus and the current do let's get it go now I should have the right result there you go that's what it is he it's a little tricky at that expr I had to take out but I liked it better than having to type all that in you you just get your a more accurate and so let me go over that again for you so first off what I did is I saved the query so we needed to make sure we saved it after we did the copy from dying because then it has the current do in it I come here and then I go to build and I can put in that field name I want total amount it is and then we're going to say okay now that's some the amount paid and where I put that in and I want to take out this expr because that's not what I want I don't want it to come out on my front they're not taking that out and I want to put in my plus and my current do get it over there come on current dude they go there's what I want it to be in that little box and I click on okay and now it works I just realized there was one other thing that was happening with this let's go back to the design view and remember we get a return equals five on that one so let's just do all and then he'll it'll be the right result okay let's go to query number eleven so copy and paste and this one is eleven so let's go back to what we're looking at and we don't uh oh we should have saved this one before we left but that's okay we don't need those totals anyway so over here what we need in this one is just the amount paid and the account manager numbers so we're going to go here and cut these and we just want the account manager number over on the other side and the current do is cut okay so now what we're doing is we're doing a little bit of a a extra feature and so we have the sort option here but we want to do is create a total well that one tricked me I couldn't get the total option to come up unless I created its court from scratch so the copy wouldn't work there so I'm just going to close this out and save over it ah no okay so now I'm creating a query from the account query and now I can do this option because I wanted that extra field in there tricky huh and so anyway so what we want is this is supposed to be amount paid that comes in first and I should be able to get the totals option now oh there it is it came in here I didn't see it and that's from some but it says group by actually they wanted the average so it's good average then we want the accountmanager field double click it there we go and it's grouped by and we're choosing the where option and then we're choosing the criteria is number 31 and then we're going to run the query so this should be saved as say that M 0 2 1 1 and replace the one that I had ok so let's take a peek so here we are and run that's so that's all you get on that one you so I've been flipping back and forth here this is what 11 supposed to look like now what I did is I copied 11 and I'm getting ready to do number 12 okay this one we're grouping by manager number so let's just get this out a little bit more so we're grouping by manager number no criteria and the amount paid is the average and then we just need to run the query oh these are supposed to be on the other side so I should take amount paid and put it over on the other side click on that you click on the title and just move it there you go and for heaven's sakes design view I forgot to click on the manager number okay here we go run there now we have the averages okay we're going to do that crosstab query so we're going to create a new one oh let's close these out what we're doing save and close so save and close and close yes and now we're doing a cross tab query and this time we get to use the weird The Wizard okay so here we are and we're doing cross tab query okay we're choosing the table account we want the state field [Music] I'm going to start this one again it took a little bit of playing around with to get it right so again query wizard crosstab query click ok um and then we're going to the next and choosing the state and then we choose next again and this time you go to Account Manager go Next again and this is where you go amount paid and you choose sum so it gets pretty tricky here and then ah it's not amount paid its current do and then some and then we're ready for the next again and this is supposed to be called state manager cross tab ok and finish pretty tricky okay there you go that's that one with this last one what you're trying to do is just get some different views of the tables and so you can you can customize your views I like this one it's it's to each is own on that and you just practice what they say in the book okay so um let's close out this make sure that everything's saved before you close and this query this would be ready to submit so don't forget to submit the excel file with it because you do have to do an export so this is a pretty tough one and I think I've got some extra things in the one after this the lab two that you'll need to have for that particular assignment in your notes ok

Channel: Marilyne Cleeves

Views: 4,018

Rating: 3.4615386 out of 5

Keywords: Cross-Tab Query, Simples Queries, Queries with parameters

Id: YBOvFJvjqH0

Length: 44min 48sec (2688 seconds)

Published: Sat Jun 24 2017