Sorting and Grouping in Microsoft Access Reports

welcome to another tip of the week video brought to you by access learning zone comm in this tip video I'm going to show you how to create report grouping levels in Access 2010 in this lesson I'm going to show you how to take this service log where we've got different service techs different service types on various dates I'm going to show you how to add all this information up and use grouping levels in a report to clean up the information and show each service tech followed by the type of service they provide him and how many times they provided that service between whatever two dates you specify we'll use report sorting and grouping levels to accomplish this goal the first thing I'm going to create on my supporting tables I'm going to be a technician table to store my service techs which is a tech ID my auto number and a tech name you can put other things in here like phone number and email address and mailing address but for now for this example we'll just keep it simple and I switch over to datasheet view and put a couple of records in Joe suebob like Tom and Mary now I've got my service tax I also want to track different types of service whether it's PC repair or software consulting or training so I have a service type tea table that'll include a service type ID Auto number and a service type text which is a description of the type of service and there are some sample service types now my main table it's going to bring everything together is my service log table I've got a service log ID which is the auto number for this table a tech ID so I know which service technician perform the call the service type ID what kind of service was it that links back to my service type table now I'm just going to track the number of service calls per type per day so I've got the service date which is the actual date that I'm tracking and how many service calls of this type did the service technician go on this day now I've opened up all three tables so I can see them now ideally we'll build a form to do all this data entry but for now for this simple example I'll just right in the tables let's say tech ID one Joe performed some PC repair on for one and he had two service calls all right see how this works tech ID let's say Bob performed training three on four one and he had five appointments okay and so on so I'll put a couple of different dates in for a couple different texts Joe had some software consulting on for two and he had two appointments alright so I put in some sample records okay there's a couple of different records couple different texts a few different dates now I want to show a report that's broken down by service type and tech ID so I can say between these two dates I don't wanna see for each technician the different types of services performed and the total counts so let's first create a query to bring together all the information that I need I'll go to query design I'm going to add in my service log table and at other tables for supporting information I rearranged ease a little bit so you can see the relationships here tech ID is related to tech ID up here and service type ID is related to service type ID here and the service table now I'm going to bring in the star from service log team and then the supporting information from these other tables so tech name and then service type I don't need these ID fields up there because that's just duplicate information I'm going to save this as service vlog one queue for query save that and when I run it you can see there's all my information from my service log plus but what are the right here I've got the supporting information Technium and service type now if you want to add a date criteria to this you can go back to design view we'll have to add service date separately and I'll click off the show button so it doesn't show up twice and down here criteria just say between and then inside of square brackets start dates and and date just like that that will prompt the user for two dates and use those as the beginning and end date for our query so if I run this now it'll say start date I'll say for one and date let's say for two and now I'm just seeing the for one and for two records let's save that ideally if it's a query or report that you're gonna be running often you could use a form so you could type in those criteria instead of having a little prompt window pop up there's tips to do that on my website now I'm going to create a report so I can group these by service tech then by service type so let's go over here I like the use report design I like building things from scratch I don't like the Wizards the first thing you have to do is tell the report where it's getting its data from so open up the report properties double click right here where the ruler bars meet that opens up this property window the record source will be our service log one query all right now it knows where its records are coming from now we can go up and click on add existing fields to see a list of fields that are in that query now what do I want to see on my report well I want to see the tech name I want to see the service type I want to see the number of service calls and the data is irrelevant because we're going to group by the month let's just put that there for now the service date all right and I'll close that window and I'll save this as my service log r4 report and let's view it and see what we get I'll click on the View button right here we are asked for a start date for one and an end date let's say five one and there we go we get tech name tech name and so on it's showing each record once but I want to group these and see all of Joe's together and then all Bob's together and so on instead of Joe Bob Joe Joe etc so let's go back to design view I'm going to add a grouping and sorting level click on this button right here it's on report design tools and then the design tab that'll turn on this bar way at the bottom down here it says group sort in total way at the bottom let's add a group we're going to group it on now we can group it on the tech name or the tech ID whichever one you prefer I'm gonna grab it on the tech ID just in case you have two Joe's or two Bill's the tech ID is always unique do you want to sort this sure from smallest to largest is fine now I also want the CA group header and footer so click on a little more over here and right over here it says with a header section okay make sure that's on you can also turn on the footer section if you want to that's up to you let's turn it on it's a footer section so now if you look up top you've got a tech ID header and a tech ID footer so I could take the tech name and slide it up here alright and then get rid of some of the extras empty space in the middle alright save your report and now run it and take a look at what you get start bate for one and see this is why you might want to use a form because you want to keep typing in these dates the form stays open in the background okay look we've got Joe do you see repair a piece of your pair software a PC repair then down here you got tech name Bob so they're all grouped together well would it be also nice if we can group together the service type well that's just another grouping level alright come back in here come down to the bottom add a group this time we're going to group on the service type ID from smallest to largest and I'm not gonna bother with a footer for this one we're just just going to use the header so come right back up top here take service type slide it up into the service type header I'm gonna take this stuff and put it over here just like that alright in fact we can probably get rid of these labels here let's give our the labels rid of that get rid of the service type the tech name labels right here's the name of the tech here's the service type here's the service date and the number of service calls all right save that and run the report start date and date and look at that there's Joe PC repair then there's his dates you might want to sort this to write software consulting is next then we've got Bob and all of his details see how they're grouped together and then this one's grouped under this one now here's the key if you only want to see totals for the period of dates that you put in there you don't want to necessarily see all of their individual dates you just want to see a total for that period you can hide the detail section watch this we'll go back to design view let's put up here a total for the number of service calls all right watch this I'm gonna copy this copy and then hit paste I've copied and pasted the number of service calls up into the service type ID header okay that's fine you can put it in the header too now double click on this guy to bring up its properties alright it's called text 4 that's fine change the control source equals the sum of the numbers calls that'll add all of those up in that detail section alright yeah you can give it a good name up here if you want like total service calls that's up to you you want to name the control I prefer naming my controls now when I run it look what I get four one five one we get a total of all the records below alright this 13 is the total of all the records in this section so 6 plus 5 is 11 plus 2 is 13 right this is the next total here the 2 for this guy okay you can put functions like that in the header or the footer of any group and now here's the beauty here's why I did that go back to design view open up the properties for the detail section change the visible property to know now everything that's in this detail section will be hidden save your report open it up again stuck a date and they in there we go now I've got just the tech and the sum of all of his PC repairs all of the software Consulting's and the next tech and so on and yes you can put totals in here for each tech if you want to all right go back to design view I'll just copy this cuz I've already got it here copy let's put it in the let's put in the tech header you can put in the tech header or the footer whichever you want to do maybe bold it save it and one five one there we go goes total for the period each group I might see how these sorting and grouping levels work and of course now you can format it and get rid of these border lines and arrange things the way you want them if you want to see the tech name next to each one of these and turn off that header that's also an option you can do that too alright I take the tech name and copy and paste it down into here like that and then just hide this tech ID header you don't have to see that visible no I'll leave the work that I've done in case I change my mind in the future though alright now when I run my report four one five one it looks like that say man you can reformat this and make it look better but there we go there's our sorting and grouping levels and you can stack multiple grouping and sorting levels on top of each other if you want to to get the desired effect thank you for watching this lesson I hope you enjoyed it and for more tips just like this one visit my website at access learning zone com

Channel: Computer Learning Zone

Views: 72,865

Rating: 4.6528301 out of 5

Keywords: microsoft, access, 2010, report, sorting, grouping, aggregate, sum, calculations, functions, header, footer, sections

Id: KwX0xovCUwo

Length: 13min 5sec (785 seconds)

Published: Mon Apr 25 2011