Microsoft Access 2016 Reports: Advanced Formatting

I have some advanced formatting features or options I want to show you that you can apply to your report in fact some of them we already covered when we apply formatting to our forms as you recall there's a lot of similarities when it comes to designing our forms and reports but before we make any design changes here let me introduce you to this report it's my customer details we've got fields here and let me click and drag the scroll bar over more over here let's go ahead and go back first thing I want to do is let's go ahead and change the title of our report to do that let's right click in a blank area and go to the design view now if you don't have a title and you want to add a title to the report you can do it one of a couple of ways you can either come up here on the design tab and go to the controls group and I have to click on the drop down arrow for controls so I can see my controls and here you want to select Alcoholics Anonymous well I don't mean to label the label but that's what a a reminds me of any case go ahead and click on it and then hover over in the report header section and either click and drag your box the label box or just go ahead and click but if you just click you get a teeny tiny little label box and you won't see anything until you start typing something and there you go now if you want to apply formatting to it you can't do it while you're in edit mode or while the cursor is flashing in the label so if you come up here click on the format tab see you can't apply anything bagel 0 so what you want to do is you want to hit the enter key to get out of the edit mode with the labels still selected and then you can go ahead and make a bold italics well whatever you want the font group there but I'm not going to do it that way with the selected I'm going to hit the Delete key on the keyboard because the other way that I did it and that you can do it too is come up here click on the design tab go to the header footer group and just click on title it adds a label down below for you and you can go ahead once it adds it make well if you don't like the default text within it make changes to it and the difference between the two well once you add it by using the title way you can always go back to it in an instant by clicking on it so if you're out here gallivanting in the report making changes you're like ooh I need to go back to the title well you can come up here click on it once but if you need to make changes to it you have to after you select it click on it again and then you're in edit mode but like I said if you're out and about here and you come up here and you click on title takes you not only right to it but in edit mode who makes it all worthwhile doesn't it well any case while I'm in edit mode let me go ahead and delete details hit enter and so it's up to you do the label thing or the title just keep in mind that when you add a label already here you're like yeah I want to do the title you probably want to delete the label because if you click on title it puts the title on top of your label so choose one or the other any case let's go ahead and resize this and I can do that by hovering over the right middle resizing handle until I can see arrows pointing in opposite directions then I can click and drag in or go out or double click really fast to do a best fit that works and the next how about selecting a text box or label and when you select one if you notice dashed line that goes around all the others including the one that you selected to remember what that means as you recall in an earlier training video it means that that well in this case the selected label is part of a table and so to find out any other labels or text boxes that are part of the table especially if you really can't focus on that thin dotted line just hover in the upper left hand corner I don't know if you can see it the little tag there well you could probably see the four-way arrow click on it and it selects everything within the table the labels and the corresponding text boxes were those things that are part of the table now if you want to be able to move like let me click and just select the let me click off and select the customer name text box independently around within the detail area or section I can't do that as long as it's part of the board or the table so I have to break up the table or remove it and you can do that by well when you click on the tag to select everything in the design view come up here click on the arrange tab and go to the table group and remove the layout but I'm not going to do that but we learned about that in an earlier training video and it's only available here not in the layout view because if you go to the layout view you just get these three options well let me show you hit birds with one stone because if you can't see a little tag in the upper left-hand corner the layout view you'll be able to see it so let's right-click on the tab go to the layout view and well there's the tag and you'll only see that when you select any one of these cells here because if you click off of it you don't get the tags so go ahead and select a cell and then you see it click on it and it selects all the cells within the table so like I said when you come up here click on the arrange tab but in the layout view over in the table group you don't get the remove layout option you just get well you can change the layout from stack to tabular but we won't do that how about gridlines so remember when it comes to working with your table these items that are within the table group you want to select the table because if you just select one of these cells within the customer name field it'll only apply to that field want to click on the tag you know you're working with the table with these items in the table group so let's go ahead and click on gridlines click on the drop-down arrow and I can add horizontal vertical top bottom or whoo let's do both select that click off and there's my thin little grey gridlines between top bottom left and right let's see us go ahead and select a cell click on the tag click on gridlines what other options do we get lose some color oh let's spice it up with something maybe red and click off there's the red click on it click on the tag click on gridlines what else we can change the width make it something horrendously large six point you get my point let's go down to border something from Solid 2 ooh dashing select that and then click off and go oh that's horrifying undo undo let's go ahead and undo all that and I'll go back to just the thin gray grid line here for my table and the next how about if we change the background for our column titles so instead of this kind of burgundy what we can do is go ahead and click and select the first cell in that row and then hover over on the left hand side until you can see a black arrow pointing to the right click on it and it selects everything over in that title row so once we have it selected we can come up here to click on the format tab go to the control formatting group and let's click on shape fill and let's do something like ooh a dark purple hover over it it says purple accent for darker 25% oh that's a fun name select that and not too bad and speaking of colors here we got our alternating row colors so it goes from white to green white to green and why does it do that these banded rows help us keep us within the focus of the row that we're looking at so fall cleaning if I go up I hit white or if I go down I hit white and so I want to keep in that green row so if you want to change that so it's not green like maybe something complementary to the purple here well green kind of is but we can change that in any case go ahead and change it you need to click out in this gray area here and then come up here on the format tab go to the background group and you get the alternate row color you don't get it if you have something selected within it and I mean literally and figuratively you don't get it you gotta watch my video or if you click on the tag you still don't get it what did I say there you go go ahead and click off in that little white area and then you can go ahead and get the alternate ro color let's go ahead and click on the drop-down arrow and we can do now you can try light purple and see that and then go ahead and click off and do you select that there we go cuz the orange borders was throwing me and that was the selection I'm like I don't like that but hey that's alright I like that color okay next how about some alignment options for like our customer ID column here this is aligned left so if I go ahead and click down below to make it aligned like this one I think that nope that's a mine left too in any case if I want to align both of them then I have to select both so click on the fields down below hold down the shift key and click on the title up above and then you can see you've got both selected and then to go ahead and change it to middle alignment come up here on the format tab go to the font group and there is middle you can do it that way or you can bring up the property sheet by right-clicking on the border and going down to properties and then over here in the properties sheet for well it says multiple selection for what we have here you can see down below where it says text align click in there and double click to change it to right right double click distribute whoo that really breaks it up want to double click to toggle through all the options you can click on the drop-down arrow and see what's available we'll go back to Center and then go ahead and close out let's do some more fun things like how about I want to go ahead and collapse the customer name I want to try to squeeze everybody in here and not have them so well spaced out because this is a report and it's gonna cut it off and I don't want these cut-offs so let's go ahead and hover over the right-hand side of the customer name fields - I can see arrows pointing in opposite directions and click and drag to pull it in pull it in come on we got to fit everybody so we got to keep squeezing it in but the problem that I run into is that it cuts off the name I'm like Ghost Hunters who is that instead of having a stretched-out well what we can do is we can go ahead and have these fields do a text wrap and so we'll do the can grow feature that says you can grow not horizontally but vertically so when it comes over and wants to cut it off it'll take that and wrap it underneath so we can see what Ghost Hunters a is to do that let's go ahead and bring up the property sheet for this and we can let's right-click on a border or just right-click anywhere and there we go there's the properties and let's scroll all the way down to the bottom and I'm on the alt tab and there you go can grow no double click yes and then you see it automatically takes that and wraps around so Ghost Hunters Oh America that's nice let's go ahead and close out ok next let's do some conditional formatting so if you have a column that has a bunch of numbers and you want to focus just on a few or apply some formatting or highlights to range of values like let's say I want to format values between or the customer ID whose numbers are between let's see 3 1 3 6 5 and 3 2 4 1 1 so with my numbers data type field here to go ahead and highlight those well between those ranges of numbers with that selected just any cell here in that column come up here on the format tab go to the control formatting group and click on conditional formatting and it says show formatting rules for the customer ID well that's where we're at if we were somewhere else we can go ahead and change it and short for the other column but we're right here let's go ahead and create a new for the customer ID click on new rule and we want to check values in the current record or use an expression so down below it says format only cells where the field value is between two numbers and what are those numbers so if I type in three one three six five at the low end and the high end is three to four one one so it's like anything greater than or equal to three one three six five and then anything less than or equal to three two four one one so between and then what format do you want to apply to it we'll make it bold italics we'll just use everything that we can in the book and we use a fill color we'll make it the purple let's see the font color maybe we'll go ahead and make it white there's the preview does it work for you okay I'll go ahead and click okey-dokey and then click well you can apply it but clicking okay is like applying it but in any case there's a rule so if you're like I want to make changes to it go ahead and edit it let me click cancel and then click okey-dokey and it applies to it now where's the field color shouldn't there be field color well you won't see it here in the layout view the only place you'll see the back field color of the purple is in the report view you won't even see in print preview which means if you don't see it in there it's not gonna come out of your printer but we get all the other formats which well it doesn't work for me here because white against white oh that's terrible let's go ahead and right click so I can show you the only view that it actually works in the report view there you go when you actually click inside the cell where it pops so you may not want to use that back color here or fill color for those cells because I didn't doing it for me and like I said right click go to print preview what you see there is what's coming out of your printer so undo edit let's right-click and go back to our report view and let's right-click and go back to our layout view and then to make changes to it well come up here to click on the format tab conditional formatting and let's edit the rules and then of course the fill color isn't the issue really it's that I selected the font color that's white and yuck we can go back to automatic make it black click okie dokie okie dokie and well that's better so I don't have to worry about that bleaching in the white background and then of course to go ahead and remove it conditional formatting and delete the rule click okey-dokey and we're back to where we started now how about counting up how many records we have in our report let's begin one two three four no I'm kidding what we can do is we can use the count function to be able to count this up and have the result down at the bottom of the report to do that let us come up here click on the design tab go to the grouping and totals group click on totals and we won't do count you want to count our records go ahead and select that and then scroll down to the bottom and it adds it right there so when I right click and go to the print preview because what you see in there is gonna come out of your printer down at the bottom well you can see that the four and one is shaved off just a little bit there so let's go ahead and right-click go to well design view or layout view it doesn't matter here let's go down at the bottom and there we go just hover over the bottom and click and drag that oh you see how when I did that it actually shot across here well that looks like it's part of the Borg the collective the table here when I click on it see that's included that's not gonna work for me well maybe you want to go ahead and have the number here moved over to the right or just you know move it independently the rest of the cells here within the table or the column here and to do that I've got to remove the layout but before we do that let's go ahead and add a label here that tells us what this number is about instead of just 14 so to add a label well let's go right click to the design view and there we go there's the count you can see the dashed line that goes around it that encapsulates that let's come up here click on the design tab go to the controls click on control let's add the label AAA click and then just go ahead and click here and then we'll type in records are you gonna put total number of records whatever works for you hit enter and then let's go ahead and hover over the border and click and drag that and you see it doesn't let me do that because it's part of the board the table everything has to be just perfect when it comes to being part of the table so to go ahead and remove the records so I can resize the records the label independently from the rest here and also move them about let's go ahead and select the label here and hold down the shift key and select the function the count function in its own text box there and then come up here click on the arrange tab go to the table group and remove it from the layout and whoo that was close let's go ahead and hover over the bottom of the grid click and drag and stretch that open just a little bit and then we can click and drag and move these guys around you know put them wherever we want now the results of the count function it looks like it's centered so let me click off so I want to left align so I can put that as close to the label as possible and the label well see that's got all that extra space so let's hover over the right little resizing handle double click really fast allows us to do it because it's not part of the collective the table here and click and drag that move it right they'll close use the arrow keys and let's go up just a little bit and then the well what's that little tag here it says it's a new label and it's not associated with the control oh it's got nobody to label let's go ahead and click on the drop down arrow and say ok go ahead and associate yourself with somebody or you can ignore it let's associate and then it selects the adjacent guy here and says is this the one of course it is click okie dokie and he's happy you don't get that little warning that's nice so they're buddies now and then for the text box remember it's center lines so come up here on the format tab and color the font group its Center and let's do left so we can move the results closer over to our label here records and then let's go ahead and take it for a test drive right click go to the print preview oh well it's getting there right of course you know how to tweet that and be able to nudge it closer and get it just looking so let's go ahead and close out of the print preview close out of here I'll save it and the other formats that I want to show you let's do it in another report let's do it the sales profit report double click and let's go ahead and scroll over and I'm looking at the well quantity sold so we got it from smaller numbers to larger numbers and instead of focusing on it numerically how about if we do it graphically I want to add data bars in these cells here that as the number gets larger that bar gets larger so to do that lets right-click and go to the layout view and then select one of these cells here within the quantity sold column and then just come up here click on the format tab go to the control formatting group and let's do conditional formatting let's do a new rule and let's go ahead and select compare to other records and so there you go we got the shortest bar is gonna be lowest value the longest bar is gonna be a highest value and if you want the bar color to be different well choose something else but I'm okie-dokie with that let's go ahead and click okey-dokey click OK again and there we go so that's 63 that's bigger than the other what's the biggest one 306 is getting up there it's at the largest one Oh No 500 oh he's pushing that bar there in any case you can get your data bars or graphical representation of your numbers here through the conditional formatting and let's go ahead and scroll over here leave that alone let's scroll up to the top and I'm looking at the part number and the book title and there's a lot of duplicates here so can't we just go ahead and group these duplicates by customer here so we can have all these customers grouped under one part number book title basic medical billing so to do that let's go ahead and click for the part number and hold down the shift key and select also the book title since they're gonna be the same in duplicate because the part number represents the book title so they'll match up and then to go ahead and hide the duplicates let's bring up the property sheet by well just right clicking and going to properties and then on the alt tab and my scroll Deeley is almost all the way to the bottom here you can go up just a little bit but I'm looking just slightly up from the bottom the high duplicates it's set to no double click to yes and then close out and there you go let me click off so all the customers who purchase the same book that has the same associating part number grouped under both of those and we don't have it duplicated over and over again so that helps clean up our report now let's go ahead and go to the design view right click design view I think we have yeah we do we got a page number here and the page footer with the selected I'm going to delete it pretend that we don't have a page number for our report or a page numbering system but let's come up here click on the design tab go to a header or footer group and click on page numbers so I can show you how to insert one if you don't have one you got the page format I like the page n of M so if I had 10 pages of my report it would be like page 1 of 10 2 of 10 3 of 10 that's nice because when I print them off and somebody says hey I got 5 pages and they're thinking that that's just it because with page n you'd think well that's it but with of something more like out of 10 and you're on page 5 of 10 you'd be like hey I need something more here so page and of M the position top of the page let's do the bottom page in the footer alignment can be Center or somewhere over to the right click okey-dokey and I'll scroll over to the far right and there it is of course you can go ahead and select that come up here on the format tab and do all types of fun formats and trebuchet - let's do something comical like woo Comic Sans let's go ahead with it partially typed in but highlighted hit the enter key on the keyboard and okay let's go ahead and take a look at that right click on it and go to print preview and then click on it to zoom out and do you see anything at the bottom of there that page of course not because why because my report is a pig it's got so many columns it's going while bleeding over on to the second page so until I go to the second page I won't be able to see it so there we go page 103 so the first two pages is considered to be page one because again I don't have my columns all nice and you see I got a lot of space here I got to go ahead and well right-click and go to the layout view and crunch these you know go over it crunch it and click on that crunch that in and then of course as you recall we talked about earlier that if it doesn't automatically wrap around within the column you can use the can grow features so go ahead and rewind the video to watch that again if you need to thanks for watching hey as a quick reminder if you liked my video please give it a thumbs up you can also click on me and subscribe to my channel to get notified with the latest videos and four great specials on my products please see the description below this video

Channel: Kirt Kershaw

Views: 21,809

Rating: 4.8974357 out of 5

Keywords: microsoft Access 2016, Access 2016, Access, 2016, microsoft, report, format, advanced, can grow, hide, duplicates, conditional, formatting, data bars

Id: 7HPtP8EQKJo

Length: 22min 35sec (1355 seconds)

Published: Fri Jan 25 2019