Learn how to construct a reporting tool that compares various data points and allows the end user to define the parameters of the report. By hooking several of Access’s objects together, see how tol create a dynamic tool for generating an infinite number of reports based on the needs of an end user.
- [Instructor] Business managers love looking at comparative data, whether it's different areas over the same time period. Or a year over year analysis of the same location. Comparing data points can quickly give some powerful insights into the health of an organization or the overall trends. In this chapter we're gonna construct a reporting tool that compares various data points and allows the end user to define the parameters of the report by hooking several of Access's objects together we can create a dynamic tool for generating an infinite number of reports based off of the needs of the end user. Let's take a quick look at how this completed system will work.
Here on the main menu you'll notice we have a lot of new elements here on this portion of the screen. I have a column of combo boxes where we can select some parameters for our report. For instance, I'll go ahead and choose January 2018 in the New England sub region. And we can compare these records to selections we make in this second column. Let's compare the same month and year, but a different region. I'll choose the mountain region. Once all my selections are made I can go ahead and press this report button at the bottom. And I'll get a customized analysis report that just contains that particular month, January 2018, and would take a look at all of the mountain states, and we can compare them to all of the New England states.
If I close the print preview window and make some different selections let's compare month over month, January to February of the New England region. I'll press the report button again, and I'll get an entirely different report that looks at those parameters. Now, these combo boxes over here on the main menu are being dynamically generated based off of a query. As our database grows and we get new records added into the databases these combo box selections will automatically grow right along with it. So there won't be any additional maintenance to make sure that this stays up to date along with our data.
So, we make selections and press the print report button. This seems pretty simple on the surface, but how is this really working? What's going on behind the scenes? To the end user things look pretty simple. You have some selections on a form, you press a button, and you get a report. But behind the scenes there's an entire chain of events that occurs to make that process appear seamless. When we press the button on the form the report only begins to open. During that process the report object looks to see where its data is coming from. The report's record source property links to a query. So, the query is called upon to start gathering the records from the database tables.
The query object then sees that it needs to get the filtering criteria from the original form object. So it retrieves those parameters from our form. With a criteria in hand the query can complete the task of gathering the records and passes the group back to the report. Finally, the report has everything that it needs to format the data and presents the finalized report layout back to you, the end user. So what appears to the end user to be a simple action, you press a button then you get a report, is really a series of steps that is handled internally by Access. Over the next several movies we'll build and hook all of these pieces together starting with the form.
- Creating an AutoExec macro
- Working with hidden objects: queries, tables, and more
- Splitting a database into back and front ends
- Creating a dynamic reporting tool
- Helping end users by providing shortcuts and context-sensitive help
- Writing custom functions in Visual Basic
- Packaging and distributing your Access application