How to Group Records by Dates (Year, Month, etc.) in Microsoft Access Reports

welcome to another free microsoft access tip video brought to you by access learning zone.com my name is Richard Ross in this tutorial I'm going to teach you how to group records together by date in your access reports here is my customer database it's the database that I build in my regular access courses I have a customer list if I click on one of these customers like myself and click on open selected customer you'll see all the customers details now one of the things that I track in my database is something called a customer sense field this is when this customers account was created in the database I like to think of it as kind of like the customers birthdate as a customer well what I like to do from time to time is generate a list of my customers broken down by year so I can see okay these are the oldest customers who joined in let's say 1992 and these guys are in 93 and 94 and so on so that after five years I can send like these customers are present and then after ten years I can send these guys a slightly more expensive present and so on so I want to generate a list of all of my customers grouped by year and the easiest way to do that is with a Microsoft Access report with a sorting and grouping level let me show you how that's set up I'm going to click on create and then report design I like to work with a blank report yes there is a wizard to do this but once you learn how to work with blank reports it's much much better faster and easier than using live wizards I find when I use a wizard it takes me more time to undo the formatting that the wizard does because I don't like the way they look so it's faster and easier for me to just do it from scratch so that's how I teach it in my classes I'm going to click on report design there's a blank report right there first thing to do is specify the record source where is this report getting its data from so double click right there with a ruler bars meet and a little block you see the property sheet the first property is record source so come over here drop this down I'm looking for the customer table which in this database is customer T right there customer table now I can close the property sheet and come up here and pick add existing fields this is a list of all the fields that are in the at table or query you can use queries to I'm going to pick the fields that I want to see on this report so on first name then I'm going to hold the ctrl key down as I pick more fields ctrl last name company name and the customer sense field all right then I'm going to click and drag any one of them over here to put all four of those fields on my report now I can close the field that's because I'm done with it all right so there they are now I want these labels to appear once at the top of the page so I'll put them in the page header I'm going to select all these ctrl X to cut click inside the page header and ctrl V to paste now I can arrange these the way that I want the most put first name over here followed by last name followed by company name and we'll keep customer cents over here on the left now I'll get rid of all the same extra empty space like that right and line these guys up there's first name last name company name and we'll put customer cents over there let's get rid of all of this extra empty space by grabbing the bottom of the page footer and sliding it up just like this watch click and drag alright let's save our work so we don't lose it in case the power goes out alright ctrl s to save let's call this customer cents birthday report are all right customer cents birthday are let's see what it looks like let's go up here and click on print preview drop this box down print preview there we go now I hate these boxes see this is one of the things I'm talking about access puts all this formatting in here that I don't like so I'm gonna get rid of those boxes but look at what I've got data wise first name last name company name customers cents over here but I want the sort and group these by year so all the 2012 show up together for example alright how do I do that well we got to turn a grouping level on so let's close print preview come back into here I'm going to click over here and get rid of that formatting real quick I'm going to go to format shape outline and then transparent that will hide that so now if I print preview it again you see this looks much better ok let's turn that sorting and grouping level on come up top on the ribbon right there it says group and sort click on that now down on the bottom of the screen you'll see this thing appears it says group sort and total let's come over here and click on add a group what field do you want to group on I'm going to group on that customer sense field from oldest to newest is this sort that's fine if you want the most recent ones up top drop that down by quarter is how you want to break it up how you want to group it by the entire value so each unique value is grouped by day week month quarter by year is the one I'm looking for or you can go custom by every three months by every six months alright so click off of that my year should be in there notice now we have a customer sense header section I'll show you what that does in just a second alright now come up top here and print preview it look at that now it's the same basic report but all the records are grouped together by ear see there's the only one from 90 only one from 92 we have two from 98 grouped together alright scroll down they got three from 2012 to that so that's how you group them together by year now let's put something in that header section let's go back into design view got this empty outer section sitting right here watch this I'm going to copy this customer sense copy paste alright and I'm going to open up its properties by double clicking on it now I'm going to change the control source to this watch equals year the word year open parentheses go to the end close parentheses now it says equals the year of customer sense so only show me the year portion of customer sense like 2012 okay close that let's maybe format this guy a little bit let's go to format let's bold it and make a little bit bigger how about like 16 point make the box a little bit bigger alright save that and one more thing open it up again forgot about this take the short date format off because if if you if you leave it as short date it's going to be in the format of 1 / 1 / 2012 for example so we have to take the format off alright and then also let's left justify this left align it alright now let's save it and take a print preview and perfect there we go 1990 92 98 and so on so that's how you group your date values by any particular interval I pick the year you could do month if you do quarter that's how you utilize the group header and there's all kinds of stuff you can do with these grouping levels I cover sorting and grouping in much much more detail in my access expert level 12 class I teach you how to group customers by state here you can see a couple of different customers in New York for example we put a count down here a total of their credit limits over here you can see that if there is no state I show you how to put the word none in there because maybe some customers are missing data we also break it down by multiple grouping levels I show you state and then country as well so if you scroll down here there's my Canadians and I've got one Italian in there as well we do another report with currency grouping so you could take your sales for example break it down by sales rep and then show all the sales that fall on a particular range like zero to five hundred five hundred to a thousand and so on we also build an employee work log where you can enter in the employees information time in time out the system calculates hours worked you can track what activity they were doing if you want to you can specify a default employee or a default date and that automatically enters it in here so you could put in a me once for example and then just type in a bunch of dates one after the other and then we build a work log report which is grouped by employee and then grouped by week because there's a bunch of individual records in here right for this particular week beginning 728 and I teach you how to calculate that right if today's date is 7:30 what's the Sunday before that so you can figure out what the week began on in that particular case it's 728 so we group it by employee then we group it by week we calculate the week begin date then add up the hours worked so this goes right on your timesheet information so we cover all that plus all lotmore in access expert 12 that's a long class I class about two and a half hours long most of my class is usually about an hour long well that one went over double time so it's a real long class a lot of great information there I started going with grouping and sorting and I just I couldn't stop there was just so much good stuff to include in this lesson that I couldn't stop myself so for more information on this class or if you have any questions or comments please feel free to post them or contact me or visit my website access learning zone.com thanks for watching if you like this video please be sure to give it a thumbs up and comment below I've got tons of free microsoft access tips and tricks videos on my channel plus lessons and other subject like Microsoft Excel Word Visual Basic windows and lots more and don't forget to click to visit my website at access learning zone.com for even more free videos my access message forums and lots more hope to see you soon you

Channel: Computer Learning Zone

Views: 115,670

Rating: 4.8581562 out of 5

Keywords: Microsoft Access (Software), report, reports, grouping, group by, grouping level, sorting & grouping, sort & group, group by year, group by month, group by date, group header, group footer, headers and footers

Id: -ucMOofuIWA

Length: 9min 59sec (599 seconds)

Published: Tue Sep 03 2013