How to Do Grouping and Sorting of Report Data in Microsoft Access

i'm your host sean mckenzie thank you for joining me once again on my channel on data engineering and data analytics in this episode we're returning to our microsoft access playlist we're going to talk about report groupings and specifically how to take a bunch of data that fits into one group and make sure that that shows up that way in our report um so that we can have multiple groupings and and all the data showing up the way that we want to now this is very handy and if you write a lot of reports in your work you're going to use report groupings all the time so without further ado let's get to our report groupings in microsoft access okay so to get started here i'm using the the same file that we use for some other examples and as you can see here this is a climate change data thanks to the world bank for providing this data on their website and i've imported it into access and as you can see there's a bunch of countries and they've got some measures through the years here some some entries don't have data and some do and as you can see if i scroll down there's there's all these different kinds of measures that are included as part of this data set and so you can see here this particular measure the serial yield it does have a lot of entries in it so we'll we'll be sure to see those in our report and so what we're going to do first is i'm going to show you first how to do grouping when you create the report using the wizard and then i'm going to show you how to create the report or create the grouping i should say in an existing report where you you just never had any grouping before and so the first thing i'm going to do is i don't want to our report to spit out all 13 000 rows or whatever of that table so i'm just going to take i'm creating a query here and i'm just basically going to create a simple query that selects everything from the table all of the columns but i'm only going to take canada i don't know say norway and and spain so um i'll take three countries and then you'll be able to see um in this query you know just the records for for those countries so i in order to start this query you can just go to create and then new query design and i'm going to save it here i just save it as climate sample and as you can see it's the same data but i only have 174 records as opposed to 13 000 which is a little bit easier for us to work with so you can see this is the table itself and it's got like thirteen thousand five hundred twelve records uh which is going to give us a giant um printed printout when we do our print preview on our on our report there but yeah if i go back to our climate sample here you can see it's 174 records and it has all those fields so and we only have there's spain we only have canada spain and norway and uh and then we've got all of the measures that were also included for the other countries and it's going to give us a nice easy report uh to create so we'll go to our create ribbon there and we'll zip over to the report wizard which is the easiest way to make a report in microsoft access we've selected our query and i'm just going to take the country name and then i'll take the series name and and so you can select your fields and then move them into the report just as you can see here and then i'll just select the last three years just for for for interest sake here and so you can hit next and then on the next sort of page of the wizard here you'll see that now it's asking you know do you want to add any grouping levels and that's exactly what we want to do so we could choose country name and or series name as a grouping or both but we're just going to take country name this time you'll see that it creates it at the top and it sort of gives a preview of what it might look like when it's rendered and then you can see it asks for you know a sorting and the sorting is what will be sorted inside of your grouping and it asks you for a layout and the step layout is very nice for for group reports with groupings on them and and then it asks for a name on the last step so you can just say you know climate sample report and then i'll put wizard because we created this one with the wizard and and then i'll just hit print preview in order to view it and as you can see the wizard sort of you know approximates where the fields should go and what it should look like and it's a bit of a mess when it first comes out and you can go back and you can clean up a lot of this but it does give you the report and there's your grouping so canada is the first one in the list and if i sort of view it at a high level you can see there's norway so you can see the stepped means that it's it stepped the data down from the from the grouped uh part of it so all of the records for canada are together all of the records for norway are together and again you can see there's spain on page four and all of the records for spain have been grouped below you know below where we've we put spain and so you can right click on the tab and you can go to design view to quickly switch back and forth as you can see here in the report design you can see it's added country name header as a section of the report and that is the the part where the grouping resides and so you can expand that and you can change it and put underlines or change the colors of that in order to really show it on the printed page on the printed page where this section starts for this country or for whatever you know different measure that you choose and so if i expand that out [Music] you can see it put it up there but it right justified it so we'll go back and we'll we'll just left justify that to make it look nicer and and so we'll click on our where we put our country name we'll go to format and we'll just put left justify in order to make it stick to the left there and so if i i can also expand the series name which we'll do here in a minute but just to show just to show that you can do that here's canada is now you can see now it's very obvious that canada is a group there because it's got bigger text and and so now all of those records grouped together you're sure that those are inside of the canada group and you can use the extra real estate there um and you can you know expand your series name to to to look a little bit nicer something like that and you you could go through you could do all kinds of formatting here i won't get into that now um and we'll just look at it and there you go so so now you can sort of see a little bit more clearly if you look at the top there that country is at the top it's very obvious that it it steps down into the data for that country and there's some you know number entries on the on the right there uh for each of the years you know the it's sorted by in it's sorted inside of the group by the field that we chose and you can see that's where norway starts on page two and uh and so you can see canada ends and then norway starts and that's sort of a nice easy way of doing that part now if i close my print preview what i might want to show here is you know we might want to do something like you know add a page break because maybe you want your group to start on a new page every time so what we can do there is it's also going to show us how how the groupings get added so if i click group and sort that button there that's going to take us into the into the group and sort section and you can see the it's the section where you you can sort of administer or take care of all of these you know different headers because you might have one inside of another inside of another and as you can see it's created a group on country name and it's got with a on top so it's you know sorting and it's you know it's got all these different things in here that you can change it's got with a header section because we can see that in our report there but it doesn't have a footer section but the footer is something that you can add to your group and that's where you can do things like you could add a sum to you know if if you had numeric columns there you could add a sum for and then you'd have sums for each group you could add all kinds of things in there but in our case what we're going to do is we're just going to create a page break in here so that a new page starts so it does the whole the whole group in this case country and when it gets to the end of the data for that country it looks at the footer what's in the footer and it goes oh there's a page break in there so that's gonna you know create a new page for the next group and that's really handy so you can you can play with that footer you can put sub reports in the footer to have just data that's related that you can do all kinds of stuff with the footer and the header but in this case we're going to go to our controls and we're just going to select that page break there there it is the the page break so we're going to click on that and then we'll click it into our report sort of where we want it and you can see that little that little graphic there is the is the page break and so now we have a footer in our group that just has a page break in it and that's really going to help us to to just show the data that we want to the way that we want to show it so you could have departments and you print everything off and or and you might want to just give the pages for one department and so you could do that because here's where the page break is at the end of that data for canada and then you can see on a new page norway starts and and if you go to the next page it goes all the way until norway data ends and so that's a very handy way of of separating the data and getting a page break on you know after each group and so um there's our last last the last country there so we had the three we have spain norway and and canada and their their data is all grouped and it's sorted within the group and we've got a page break after that and so that gives you an example of how to do this report with the wizard so i'm going to close that now so you might be asking yourself you know okay well that's great but what if i have a report already that i need to add a group into and so we're going to do that now so the first thing uh that we're going to do is we're going to create a report with no groups in it so we're going to pick the same data that we picked last time and we're going to pick the same years of of data but of course in this example we have no grouping so we picked our our fields we're going to go to the next screen here and we're not going to choose any grouping so we're going to say hey this is a report that already exists it has no groups in it but we want to add a group so we'll just sort it by whatever country name and then you'll see here on the last page we don't have the the stepped option we just have tabular and so we'll take that for now and then we'll call our report climate sample report and we'll leave off the wizard because uh off the end of the name because we're not using we're not going to use the wizard to put the groups in and so if i open this report we got our six pages kind of similar to before but the data is just like all it's just i mean it's sorted you can see norway together kind of sort of there and um and you know it's kind of all mishmashed and and your boss tells you hey put a put a group around these you know around this so this is what we're going to do here so we did country name last time but this time what we'll do is we're going to add we'll add a series name and as a group instead of country names so we've already sorted it by country name and we can add a grouping but what i'll do is i'll add the group first and then i'll show you how it's easy to screw this up so what you can do is if you just choose your group here then then it will create a group but it's already sorted by country name so you know you might think that you've got your group set but we're going to need to move that up and uh so first things first you don't need to have the series name in the detail anymore because that's going to be in the in the the the header and so we can remove that and we can use that real estate that's left there and then we'll add into our series name header we'll add our our you know nice big letters to show the series name and then uh and then you know you could expand that out because those those names looked really long for the for the different series and um what you could do is you could type the field in here but i'll just go to the i'll go to the data tab and i'll just choose it from here so we've got series and then name and then tab off of that and that's going to make that text box you know belong it's going to bind it to that field and so so now we've got the series name in our header which is what we want and we've got our everything you know set the way that we want so if i go print preview um it comes out and it looks kind of kind of funky it's not quite what we want um and so if i you know go in here and i look at it it's like okay well i did group it but because we we left that sword in there that already existed so you might need to look for that when you create if you're adding a group into an existing report that somebody else made you need to be careful of the groups and sorts that are already there so in this case here's the one we made and we're just going to go to the right and we're going to move the importance of that one up to a higher higher level and it's going to put the sort below it and then when we choose print preview now you can see okay that's what i wanted to see access to electricity and then countries data inside of that and that's sort of the way that you can organize that if i scroll through i can see where the fields had good data you know i can see that and you know we can analyze that or look at it however however we like and and that's something that's really nice i don't like the box you know the border around that box there so i'm just going to go and change that to transparent and then hit my print preview again and now it looks a bit nicer you know so you could go through and you can start formatting and doing all kinds of cool stuff there and and that's going to make your make you a report that will be really great for your team and that's how you do grouping on microsoft access reports hope you enjoyed today's discussion on how to do report groupings in microsoft access if you like what you saw today please give the video a thumbs up make sure to subscribe to the channel if you have not subscribed yet and uh click the bell when you see the bell and uh if you have any questions or comments about what you saw today make sure to leave those in the comment section below have a great day have a safe day and i'll catch you next time

Channel: Sean MacKenzie Data Engineering

Views: 227

Rating: 5 out of 5

Keywords: ms access group sort, ms access group, access group and sort, access reports, access report wizard, sean mackenzie data analytics and data engineering, access reports advanced

Id: vCSOkY1IzKQ

Length: 17min 36sec (1056 seconds)

Published: Tue May 25 2021