Master budget project: data vs. information.
In the March 2010 Excel Budget article by Jason Porter and Teresa Stephenson, there is a Production Budget. The budget is shown with quarters across the columns and budgeted items as row descriptions. This is an example of how using cells and the matrix structure in Excel can show data as well as other information. In Access, data and information are stored in separate objects so that data is stored only once but used for multiple purposes. With one data source, you can create a lot of consistency and reduce errors that might occur with multiple spreadsheets displaying the same data in different places. The data stored in tables in Access is used to develop queries and then is professionally presented in reports. Thus, our process needs to separate the data into tables and use queries to bring the information together and feed reports.
To store the data in Access, we structure t in a way that allows us to use it for multiple purposes. The budget data is stored in a table named Budget that contains the following fields: Year, Quarter, Budget Type, Budget Item, Count, Amount (see Figure 1). As an example, let's consider the Production Budget. Ultimately, we would add all the budget items into this table to use for calculations. This same table can be used to add a variety of budget items that can feed queries to calculate the Pro Forma Balance Sheet line items.
To create the Production Budget Report, we can use a crosstab query to gather the data and then create a report to present the information, adding headers and footers for context. Crosstab queries are a way to summarize the data like Pivot tables in Excel. With a crosstab query, we determine which items will be row descriptions, column descriptions, and the values we summarize in the matrix.
To create a crosstab query, go to the Create ribbon and click the Query Design button. From the Show Table dialog, add the Budget table to the layout. Close the dialog. Double-click the Budget Type, Budget Item, Budget Quarter, and Amount fields. This adds them to the Query Design grid. Be sure to add them in that order (see Figure 2). In the Query Design Tools ribbon, click the Crosstab button. This adds Total and Crosstab rows to the design grid. The Total for each field will default to Group By. For the Amount field, change it to Sum. On the Crosstab row, the Budget Type and Budget Item fields should be set to Row Heading. The Budget Quarter field should be set to Column Heading, and Amount should be set to Value.
Save the query and run it. Try using the Report Wizard to quickly create a report using this query. The database file for this month, SF_Nov_20011.mdb, has a sample report I created. I used the Wizard to get a close approximation of the design I wanted, then deleted some items and moved others around in Design View until I was satisfied (see Figure 3). You can format it so many different ways. Try using the Wizard to come up with your own version of this report. Be sure to check the Properties Sheet if you want to change things such as the lines around the numbers. Properties are very useful when customizing controls on reports.
Following the August column on Balance Sheet Design, a reader asked me how to stop the Group 3 control from repeating. When you have an issue like this, a good place to start is the Properties Sheet. A control's properties are the best options for customizing output from defaults. In this case, I used a property named Hide Duplicates. The default for this is set to "No." Changing it to "Yes" stops the control from repeating.
This month we have added an additional file to the LinkUp IMA Access page. It contains a list of the columns over the last three years and a brief summary of the topics and concepts covered. As I work on the column each month, I often refer to this list to see what has already been covered. I hope this file will be helpful if something comes up in a column that was discussed previously and you are looking for a refresher on it.
As I review all the things that I am thankful for this November, I include readers on my list. Thanks for writing me with your questions over the last three years and letting me know what you found interesting or challenging or what you would like to see in an upcoming issue. And thanks for traveling with me on this journey into Access. As always, feel free to e-mail me should you have any questions.
Patricia Cox has taught Excel and Access to management accounting students and other college majors and has consulted with local area businesses to create database reporting systems since 1998. She is also a member of IMA's Greater Milwaukee Chapter. To send Patricia a question to address in the Access column, e-mail her at email@example.com.
|Printer friendly Cite/link Email Feedback|
|Date:||Nov 1, 2011|
|Previous Article:||Saving custom functions in an Add-In Workbook.|
|Next Article:||Apple 4S.|