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.
The final step in creating our reporting tool is to link our query dynaset to a report. Let's expand the Chapter 7 custom group and we'll open up the report rpt_ SalesByDivision-complete. I'll double-click to open it and Access is going to ask us for the parameters. Basically it's looking at the query to get its information. We don't have anything open right now so that's why we're getting this parameter request. Let's go ahead and say Cancel to this and we'll get our query setup. In order to do that though, if you remember, we might need our form.
So let's open up our form, we'll populate these values. I'll select New England 2008 compared to the Pacific region 2008. At this point I can run my query but we'll use the form as input to generate the dynaset. Now I can go ahead and open my report and see the formatted query results. So on our report we can see we have New England, the year 2008, and the states within New England. We've got the summarized total for the entire year of 2008 for each state.
We also have an aggregated function that's generating the division sales for the entire division over the year and the average division sale from each state. We can go ahead and scroll down and select the Pacific region, we can see the same information. The year 2008, states within the Pacific region, their total for the year, the division total for the year, and the division sverage for the year.
Finally on the bottom we have a Grand Total section that takes the total division sales from the Pacific and the New England states and adds those together, and we have a function over here that's generating the current date that the report was run. Let's take a look at the Design view to see how some of this is put together. We'll change our view to Design view, and I'll go ahead and open up our Property Sheet either by pressing F4 or clicking the button on the ribbon. Let's take a look at the report properties first. I'm going to go ahead and expand this a little bit so we can see everything.
And we'll see that the Record Source for our report is based off this qry_SalesByDivision-complete query. If we click on each of these boxes, for instance StateName, the control source for that is the StateName field that came from the query. Let's scroll across here is our SumOfPrice. The control source for that is the SumOfPrice from the query, so this column here. Go back to the report. The Division Sales and the Division Average are calculated fields and you might recognize this. This is the Sum function that we've seen previously and it's taking the sum of the SumOfPrice field from the query.
Same thing with the division average. It's an average function that we've seen, so the average of the SumOfPrice field. Let's go ahead and scroll down. This box here has that Now function that we saw. So this is the exact same syntax to generate the current date. So this is how the form was put together. It's basing its results off of the query and it's taking the query dynaset and formatting it for a printed page. So now that all of our pieces are together, the last step is to return to the beginning and finalize the form with a button to jump straight from the form to the report.
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.