Build the form

- [Voiceover] The first step in creating our reporting tool is to build the form. Here the end user will input the parameters that they are interested in reviewing on our report, specifically the year, month, and subregion of the values that they wanna see. Now we can get the subregion information out of our states table. We can see that we have a column here for subregion that corresponds to each of our states. The year and month details are gonna come out of the customer orders table. Go ahead and open up the orders table. We can see the order date here. We'll have to parse out the order dates in order to extract the specific year and month that the order occurred in. Let's go ahead and use this information and start building our combo boxes back here on the main menu form. I'll right-click on the tab and go into design view. Then on the design tab on the ribbon I'll grab a combo box control and come down here and click to add it in. This will start up the combo box wizard, which we're not gonna use, so go ahead and press the Escape key to get out of that. Then this data actually isn't going into an underlying data table, so we're gonna leave the control source property for these combo boxes blank. Let's turn our attention to the row source property though. This will define what appears in the drop down menu. I'll go ahead and click here and then click on the build button on the far right to start up the query editor. For the year data we're gonna pull that information out of the orders table. I'll double-click on it to add it and then close the show table window. Now if I were just to come over here and add in a copy of the order date that's gonna be the full date that the order was placed down to the day. All I wanna see is the specific year. So instead of just adding the order date here from the orders table I'm gonna come down here to the field and we're gonna calculate the year instead. Let's right-click on it and go into the zoom window, so I have some more room to type. First we'll start with a column alias. I'm gonna call this column year. Then we're gonna use a year function to extract the year from our order dates. I'll type in year and then open up a parenthesis, then I'll make a reference to the order date field by typing that inside of a square bracket. I'll finish with a closing square bracket and a closing parenthesis. Then I'll press the OK button and that gets added down here into my field. If I press the run button we'll see what this looks like and we'll get the extracted year of 2016 for each of my orders. Now I only want each year to appear a single time, so back in design view I'll click back here in the background to change my property sheet to the query properties, and then I'll go to unique values and change that from no to yes. The other thing that I wanna do is make sure that my years appear reverse chronologically, so I'll come down to the sort field here and change it to descending, that way the most current year appears at the top of the list and it goes descending back through time. I'll go ahead and press run and we'll see what that looks like. And we can see that we have orders just for the year of 2016. So essentially what we're doing right now is making sure that our query is future proof. That way in 2017 when we start having orders then that will automatically appear in this drop down list at the very top and then we'll see 2016 down below. So there's our first combo box query. Let's go ahead and go back into design view, I'll close out the query, and save the changes. Now let's add a combo box to define the month. We'll go back up here to the design tab, grab a new combo box, and come down here and click right below. Once again, I'll press Escape to get rid of the combo box wizard. And on the property sheet for the new combo box we'll turn our attention to this row source and click on the build button. This time we're gonna do essentially the same thing, we're gonna grab information out of the orders table, I'll go ahead and close the show table window, and instead of grabbing the order date what we want is just the month. Let's come down here into the field and I'll right-click and go back into our zoom window. I'll make a new column alias of month, and then we'll follow that up with the function called month, and we're gonna apply that to our order date. I'll go ahead and say OK to that. And then I'll come down here to this column and I wanna make sure that these data appear ascending, so that January will appear at the top of the list and December will appear at the bottom of the list. Once again, I'll click back here into my query to change my property sheet to query properties and we'll change unique values from no to yes. Let's go ahead and run this and we can see what that list looks like. Now in our data set right now we only have orders for the months of January through June, but again, we're making this future proof so that next month when we do have new orders that'll automatically get added to the bottom of the list here for July. Now what I'd also like to see is the full name of the month, not just the numerical representation. Let's go back into design view and we'll add one more column. I'll come over here to the second column, right-click, and go into my zoom box. The name of this column is gonna be month name. After the alias and the colon I'm gonna use a different function here, and this function is gonna be called format. I'll open up a parenthesis, and the data we wanna format is the order date, so inside of square brackets I'll type order date. I'll type in a comma. And the way that I wanna format this is using a code with four Ms. I'll type in a quotation mark, four Ms, and then a closing quotation mark, and a closing parenthesis. The format function when you tell it to use this four M letter code right here will essentially take our date and return the full name of the month from that date. Let's go ahead and say OK to that, that get's added down here to the second column. And we'll go ahead and press run and we can see the result of that here. Let's go back into design view, I'll close my query, and we'll save our changes. Now because this query has two columns being returned we actually need to make some additional changes here. The bound column is gonna stay one, that'll make sure that the numerical representation of our month is the value that gets chosen from the drop down list. If I switch over here to the format tab though the column count, I'm gonna change that from one to two, 'cause our query has two columns. And for the column widths I'm gonna type in zero inches comma one inch. That'll essentially hide the numerical representation of the month and only display the full name of the month. So even though we'll be seeing the full name of the month using this column widths here the bound column is defining the value that's actually being returned when we make a selection. So if I choose January from the list the value will be one. If I choose December from the list the value will be 12. So that takes care of our combo box here for the month. We need one more combo box to define the subregion. So once again I'll come up to the design tab, we'll grab a new combo box, and I'll come down here and click to add that. I'll press the Escape key, then on the data tab we'll come over here to the row source, I'll click, and click here to go into the builder. The subregion is gonna come out of our states table, so I'll go ahead and add that, and then we'll just double-click on subregion to add that column down here. Let's go ahead and start these alphabetically ascending. And once again, I'll click back here in the query editor and we'll change the unique values property to yes. We can run our query to see the results of that list and that's looking pretty good, so let's go ahead and go back into design view, I'll close the query, and save our changes. Now we just need to move things into position. I'm gonna select all three of these labels here just by dragging a box around them. On the format tab I'll change their font color to black, and I'll make them bold, so we can read them. Then I'll click off of them and double-click on this first one here, then I'll highlight it, and we'll call this year. The second one I'm gonna call month, and the third one I'm going to call subregion. I'm gonna select these three boxes here just by dragging a box around them, and using the arrow keys on my keyboard I'll kind of nudge them into position. Then I'll hold down the Shift key and press right to make them a little bit wider. Then I'm gonna make copies of all of these, so with them still selected I'll press Control + C on my keyboard and then Control + V to paste in the copy, then we'll move those into position over here just to the right. Then I'll click off of it to deselect everything and select these labels that appear on top of these other boxes right now. Just go ahead and click through all of them and hold down the Shift key to select all of them at once and press Delete on your keyboard to get rid of those. Then I wanna add in a label up here above all of these combo boxes. On the design table we'll grab a new label control and I'll just click up here to add that in. We'll call this sales analysis. On the format tab we can change its formatting. I'll make the text black, make it bold, and make it a little bit bigger. Then I'll adjust the size of the bounding box, so that we can see the full text here. And then using the arrow keys we'll nudge it into position. Finally, I need to name each of these six combo boxes, so that they can be used in the query that we're about to build in the next movie. So we can click on the first one and on the other tab in the property sheet we'll find the name property. I'm gonna call this first one CBO year one. And the second one over here I'm gonna call CBO year two. For the month row I'll call them CBO month one and CBO month two. And finally the subregions, we'll call those CBO subregion one and CBO subregion two. At this point it's a good time to save our form. I'll press Control + S on the keyboard to save it and we'll test it out. Go back into the design tab and switch our view into form view. Now I should have two combo boxes that allow me to choose a year from my data, as well as a month here based off of the month name. And then I also have a combo box that'll allow me to choose a various subregion from the data. So now that our form is built, we've named all of the data collection points, we can start building the query that'll handle the end user's request and we'll do that next.