Author: Cox, Patricia
Date published: February 1, 2012
Last month we created a delete query and a macro to run it. This month we will create a Reporting Period table to store the periods in our budget reporting time frame and to populate data into the Budget table.
Create a Reporting Period Table
We can make the Reporting Period table contain only quarterly and monthly periods-like the Excel Budget project created by Jason Porter and Teresa Stephenson-or it can be used to define reporting periods that relate to your organization. For consistency, this example will stick to quarters and months.
Create a table in design view and save it as Reporting Period. Include fields for ID, Reporting Year, Reporting Quarter, Quarter Begin Date, and Quarter End Date. Make sure that the data types for Reporting Year and Reporting Quarter are the same as for the Budget Year and Budget Quarter fields in the Budget table. The begin and end date fields may be useful if you have reporting periods that don't follow calendar quarters in your fiscal year. Once the fields are created, enter the data shown in Figure 1 into the table. This new data can be used to append reporting periods to the Budget table.
Back up Tables before Deleting Contents
Before we run the delete query or macro that we created last month, it would be prudent to back up the Budget table. As you do something for the first time in Access-particularly anything that involves deleting something-you may want to create backups. Select the Budget table. While holding down the CTRL button, drag the table up or down a little bit, and then release. A new table called Copy Of Budget will then appear. Now run the delete query or macro, and we're ready for the append query.
Create an Append Query
Let's begin with a query that will append values for the budget values of the Basic Bike. On the Create ribbon, click on Query Design. This will open a new query and display the Show Table dialog. Add the Reporting Period and Product tables, and then close the Show Table dialog. As you may have noticed, we aren't linking the tables in our query. Tables are linked when they have a relationship. When they aren't linked, the query will create a record for each item in the table. This will create a record for each quarter for each year.
On the Query Tools Design tab, change the query to an append query that appends to the Budget table. Now add the fields. From left to right, they are: Reporting Year; Reporting Quarter; BudgetType: "Sales"; Product Name; Budget Count; Amount; and Product Number.
Figure 2 shows the query. As you can see, there are some calculations in the Budget Count and Amount query fields. The Amount query is a simple calculation. It should read:
Amount: [Budget Count]*[Selling
The Budget Count field is somewhat complex. It takes the initial Sales Units from the Budget Assumptions form and multiplies it by an exponential factor of the growth so that the Budget Count increases each quarter. The exact entry in that field should be:
Budget Count: Int([Forms]![Budget
Assumptions Entry Form]![Sales
Assumptions Entry Form]![Growth
As you add the fields, make sure the Budget Assumptions Entry Form is open in Form View. This lets you reference items on that form to use in the calculations. As you can see in the Budget Count field, you can type in the name of the form in brackets, e.g., [Budget Assumptions Entry Form]. To indicate the text box field name from that form, you use follow the form name with an exclamation point and the field name in brackets, e.g., [Budget Assumptions Entry Form]![GrowthBasic]. The carat () is for exponents so that the value is increased each quarter. The ID field is an autonumber field from Reporting Period that sequentially numbers the quarters.
Use the Append To line to indicate which fields within the Budget table these fields should be added to, as shown in Figure 2. For the Product Number field, set the Criteria to 1 to specify the Basic Bike data. Save the append query with the name Append Budget Values for the Basic Bike.
This is a complex query with a number of sophisticated features coming together. It may take you several attempts to get it exactly right. It took me three attempts to work through all the details before I was satisfied. Don't give up!
Expand the Macro
Add an OpenForm action to the beginning of the Budget Process macro to open the Budget Assumptions form. At the end of the macro, add an Open- Query action to run our new append query. Save the macro. Then you can test your work by running the threeaction macro until you have corrected any typos or data-entry mistakes in your tables.
You can always download the database SF_FEB_2012.mdb from LinkUp IMA if you want to look at the completed query. As always, e-mail me if you have 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.