Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The first step in creating a reporting tool is to build the form. Here is where the end user will input the parameters that they're interested in reviewing on the reports. Let's go ahead open up our Chapter 7 custom group and I'll open up the frm_Blank file that I've already started. This basically has some images and some color formatting already applied. Change this into Design View by clicking the Design View button here in the top, and we need to add a couple of drop- down menus for the user to select from. The first one will be the Geographic Division and the second one will be the Year.
We can add combo boxes using this button up here in the Controls section of the Design Ribbon. We'll select Combo Box and add one to our form. This will open up the Combo Box Wizard and it presents us with two options. Neither of these options are going to be exactly what we're looking for, but we'll step through them a few steps and we'll see why neither of these will work. The first option is I want the combo box to get values from another table or query. I'll go ahead and say Next. I'm looking for the Geographical Division. We can get that from the States table.
I'll double-click on States. We'll add Division to our Selected Fields. We'll say Next. Let's go ahead and sort these Ascending by DivisionName, Next, and Access presents us with a table or a sample of what that drop-down menu might look like. Because each division could have multiple states associated with it, the drop-down list that Access generates with this method will have multiple divisions. We can scroll through the list and see East North Central is represented many times, and the same for each of the other divisions.
So this isn't the way we want our drop-down menu to look. Let's go ahead and back up all the way to the beginning. The other option says I will type in the values that I want. I go ahead and say Next. Access presents us with a blank table where we could type in just the values that we want. We could type in the values here, but this won't be linked to our data table, and if our company were to expand, say open up some international locations, those locations would not show up on the drop-down list unless we modify this list manually.
So this isn't really a good option either. Let's just go ahead and say Cancel to get out of the wizard altogether. A better option is to use an embedded query to link this combo box to. We can go into our Property Sheet for this combo box. We'll go to the Data tab, and under Row Source click in the empty field. We can use this button on the far right with the three dots on it to open up a query design window. From here we'll add our States table and close the Show Table window.
We'll add our DivisionName and we'll turn on our Totals row to group everything. Let's go ahead and view the results by clicking Run. And we see a data table that will represent the drop-down menu for our form and this is exactly how we want to format it. Let's go ahead and close the query. Access will ask if we want to save the statement and we do. And you can see that that SELECT statement has been populated into this Row Source column. I can right-click on it and say Zoom to open it up in the zoom window if I want to investigate it.
Let's go ahead and close that. So that's the drop-down menu for our Divisions. Let's add another one for our Year. Once again, go up to Combo Box, click once in the form, and we'll press Cancel to get out of the wizard. In the Property Sheet, go to Row Source and click on the Build button. For the Years, we'll look to our Orders table. We'll add that and say Close. Now we're not looking for the Order Date, because this is going to be a day-specific feature.
What we want is just the years that are represented by the dates, and we can use the Year function that we saw previously to do that. In this first field, let's right-click and go to our Zoom box. We'll type in the Year function and extract the year from the OrderDate field. year([OrderDate]). Let's go ahead and say OK and we'll turn on our Totals column to aggregate all those values together.
Clicking Run will show us the results, and that looks like what I expected. So we can go ahead and close this query. We'll save the changes and that has now been populated into the Row Source for this drop-down menu. Now let's clean up our form a little bit. I'm going to move the labels on top of the fields. So I'll click on the gray box at the top left corner of each label and I'll drag it to the top. And I know my Divisions are going to be a little bit long, so I need to make this wider, and I'll drag its label to the top as well.
Let's go ahead and rename these. This first one is Geographic Region. And the second one, if I double-click on it, is Year. So let's test this out and see where we're at. Let's change into Form view by clicking the button here on the View menu and we can see we have a drop-down list for our Geographic Region and a drop-down list for Year. Okay, so we need to copy these and add two more versions for our comparison data. One more time back into Design view. We'll highlight these two boxes, going to right-click, Copy, right-click again, and Paste.
Let's go ahead and drag these into position and I don't need the labels with these because they're already labeled above. So I will click on the label and delete it. Click on the Year label and delete that. And now we have our four drop-down menus to select Region, Year, and then our Comparison Region and Comparison Year. The last step is to name each of these boxes so they have a unique identifier that we can find later. We'll go into our Design view, we'll click on each drop-down menu, and switch to the Other tab, and we'll give it a unique name.
This first box we'll call cbo_region. The first Year we're going to call cbo_year, the Comparison Region we'll call cbo_compregion, and the Comparison Year we'll call cbo_comparisonyear.
Now each of these boxes has a unique name and we can identify that when we build our query. Now that our form is built and we've named the data collection points, we can start building the query and handle the end user's requests.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.