Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In this course, author Adam Wilbert illustrates how to create and leverage real-world queries and turn raw data into usable information. The course covers setting up queries, performing calculations, using the built-in Access functions to further refine query results, and identifying top performers or areas for improvement based on a range of criteria.
Our three elements, the form, the query, and the report, are now complete and it's time to provide the final piece for our end user. Let's expand the Chapter 7 section and we could see our query, our form, and our report. Let's go back into our form for a moment. The last step is to revise some functionality to jump from the form to the report. In this completed version I have a Preview Report button. Let's take a look at how that was created. If I go into the View menu, we'll go back into the Design view for the form, and we can use our Button wizard here, Button, to add a new button to the Design View.
Underneath the Report Options we have an option to open the report or preview the report. The difference is the Open Report will open the report in Layout view, whereas Preview Report will open the report in the Print Preview mode. We'll go ahead and select Preview Report, say Next, choose what report we want to preview, rpt_SalesByDivision-complete, go ahead and say Next, and you can choose a picture or I prefer the text version for the button, and go ahead and say Next. Any name we'll do here and say Finish.
So now we've got a duplicate of the button that was created earlier. Let's go back into the Form view and let's test our results. We can choose any geographical region that we want. Let's take a look at the South Atlantic states in the year 2009 and compare those to the New England states with the same year. So once again, our form is going to collect the information from the end-user, these four variables. When I press the Preview Report button, those four variables are going to get fed into our query. Let me open our query in Design view again.
Underneath a year we're taking the Year combo box value and we actually right-click and go into the Zoom menu. So we're taking the Year Combo box and the CompYear box. I'll say OK. For the DivisionName, if I go into the Zoom box, we're loading the values from the Combo division and the Combo CompDivision boxes. Those are the names that we gave to these four elements. Once we have those values and we press Preview Report, the query gets its information, passes it to the report, the report formats it, and gives it back to you.
And there we go. We've got our final report. So that's it. Wee've got a flexible Reporting tool that will grow with our database over time. Using the same process with a few modifications to the underlying query we could easily generate a report that look to the performance of a specific month instead of year, or look at how our sales team ranks against others within the same state. The options are endless. I'll leave it up to you to decide which questions are most important as you apply queries to your own database.
There are currently no FAQs about Access 2010: Queries in Depth.
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.