Working with databases.
As accountants broaden their financial careers and become more analytical, they turn increasingly to database applications to convert raw data into useful information. Earlier, we presented a workshop on how to create a database (see "Building a Database From Scratch," JofA, Nov. 99, page 63). In this follow-up, we use the Microsoft Access database from the previous article to show how the program can display relationships and how to create data-entry forms.
If you wish to follow along with the tutorial, open Access on your computer and call up the file created in the earlier article, "Cust_Track," or create a new file using the data from that article. If you do not have Office 2000, use Access 97.
If you've upgraded to Office 2000 since building the Cust_Track database, you must first upgrade the database version by following these steps: When you attempt to open the file in Access 2000, the software will recognize the older file and prompt you with the Convert/Open Database screen (see exhibit 1), inviting you to convert the file. Click on Convert Database. After the conversion is complete, save the file as "Cust_Track_2000."
[Exhibit 1 ILLUSTRATION OMITTED]
The Cust_Track_2000 file contains two tables, Customers and Products. In order to track the products that customers order, you must create two additional tables, Orders and Order Details. To do this, double-click on the menu item Create table by using wizard and the Table Wizard (see exhibit 2) will appear.
[Exhibit 2 ILLUSTRATION OMITTED]
Create the Orders table first, adding the following attributes from the Sample Fields column to it: OrderID, CustomerID and OrderDate, as shown in exhibit 2. After selecting those fields, click on Next.
When the next screen appears, leave the table name as Orders and let the computer set the primary key by clicking on Next.
At this point, the wizard will prompt you to specify the relationships between the new Orders table and the two other tables. Click on Relationships, and the wizard will ask how the Orders table is related to the Customers table. Select One record in the Customers table will match many records in the Orders table and click OK. Now click on Finish and the Orders table will appear. Close the table for now.
To create the Order Details table, use the Table Wizard as before. For this table, add OrderDetailID, OrderID, ProductID, Quantity and SalePrice, as shown in exhibit 3.
[Exhibit 3 ILLUSTRATION OMITTED]
Again, keep the table name and let the wizard set the primary key. And again, set the relationship from the Order Details table to the Orders and Products tables. In setting the relationship, recognize that one order can have many details (line items). So highlight related to `Orders' and click Relationships (see exhibit 4). Then, as shown in exhibit 5), select the third option, One record in the `Orders' table ... and click OK.
[Exhibits 4-5 ILLUSTRATION OMITTED]
Similarly, one product in the Products table can belong in many Order Details tables (many customers may purchase a specific item). So highlight related to `Products' (see exhibit 4) and click Relationships. Then select the third option, One record in the `Products' table and click OK. Click Finish and the Order Details table will appear. This table can be closed for now.
BUILDING ON RELATIONSHIPS
With two tables created and their relationships specified, we now will design a data-input form to make it easy to add new data. Forms that include more than one table generally are built from queries, so select the Queries tab, highlight Create query in Design view and click on Open (see exhibit 6).
[Exhibit 6 ILLUSTRATION OMITTED]
Now you can select the tables you want to include in the query. As shown in exhibit 7, highlight and add the following tables: Customers, Orders, Order Details and Products. Although the order in which tables are added isn't critical, it's easier to visualize the relationships if you add the tables as specified in the exhibit. Select the Close option of the Show Table screen. If you accidentally get the tables out of order, you can move them by clicking and holding the left mouse button while dragging the table to the desired location.
[Exhibit 7 ILLUSTRATION OMITTED]
Next select the attributes for inputting data. In this simplified example, you can include all fields by double-clicking on the * (asterisk) item in each of the tables in exhibit 8.
[Exhibit 8 ILLUSTRATION OMITTED]
Close the query and save it as Query1 by clicking OK. If you wish, you can select a more meaningful name for the query.
Now you're ready to create the actual data-input form. Select the Forms tab and highlight Create form by using the form wizard. To access Query1, click on the down arrow button in the Tables/Queries dropdown box and highlight Query: Query1 (see exhibit 9).
[Exhibit 9 ILLUSTRATION OMITTED]
Now move all the fields listed to the Selected Fields option in exhibit 9 by clicking on the move all button. If you wish to be more selective, highlight the field you want and click on the single left-facing arrow. Then select Next to display a preview of the fields you want available in the form (see exhibit 10).
[Exhibit 10 ILLUSTRATION OMITTED]
Select Finish to get the first draft of the data-input form (see exhibit 11).
[Exhibit 11 ILLUSTRATION OMITTED]
If you wish to modify the form, click on the design view button. (If the design view button isn't displayed, select View, Toolbars and Form View (see exhibit 12).
[Exhibit 12 ILLUSTRATION OMITTED]
For example, since the spaces for the postal code and phone number are not large enough to display complete information (see exhibit 11), you can enlarge those boxes by holding down the shift key, clicking in each of the boxes, then releasing the shift key and moving the cursor to the right side of the box until you see the darkened double-sided arrow; at this point, make the boxes wider by left-clicking and pulling the boxes to the desired width.
To see how your form has changed, click on the form view button. You can move and adjust other boxes by returning to the design view. For example, you should widen the Order Details subform box in exhibit 11 by clicking once on the box and then moving the cursor to the right edge of the box to get the double-sided arrow. Again, stretch the box by left-clicking and pulling the box wider. You can see how your form has changed by clicking on the form view button. While in the form view, adjust the Orders column widths (see exhibit 13) by placing the cursor between each of the field names and double-clicking.
[Exhibit 13 ILLUSTRATION OMITTED]
After adjusting each of the column widths, go to the design view by clicking the design view button and then close the form to save the changes. Similarly, open the Customers form again and adjust the column widths of the Order Details. If you are using Access 2000, you must close the form after adjusting the Orders and then reopen the form and adjust the Order Details separately or the changes will not be saved--a Microsoft "improvement."
The form you created allows you to update and enter information for all the tables at once with minimal effort. Due to the way the database has been designed, CustomerID in the Orders section of the form has a relationship with CustomerID in the main part of the form and automatically prefills with the corresponding number when you fill in the Order-Date information. Similarly, OrderID has been set to automatically number in increments of 1 (AutoNumber), so all you need to fill in for the Orders section is the date of the order. To make this form more efficient for the user, move the order date column to the left. Open the Customers form and highlight the OrderDate information by clicking on it (see exhibit 14). Move OrderDate by clicking and holding the left mouse button down and dragging the highlighted OrderDate to the first column, as shown on the bottom of exhibit 14. When finished, go to the design view and close the form to save the changes. Once again, you must close the form after changing Orders and then reopen and change the Order Details column to save the desired changes.
[Exhibit 14 ILLUSTRATION OMITTED]
The Order Details information needs to be adjusted in a similar fashion. Go back into the form by clicking to open. All that needs to be filled in is the Order Details_ProductID, Quantity and SalePrice. So highlight and move the information to match exhibit 15.
[Exhibit 15 ILLUSTRATION OMITTED]
When you finish moving these columns, close the form to save the changes.
TAKE IT FOR A TEST DRIVE
It's always a good idea to test a database. To do this, we will enter a sale. Open the Customers form again and note you are on CustomerID 1--Fly-By-Night Clothes. Press the tab key on the keyboard until you get to the OrderDate box, or just click in the OrderDate box. Enter the date "112099" and tab once. Notice that OrderID has prenumbered your invoice for you (1). Click in the Order Details_Product ID box, which corresponds to your Products table and ProductID item. For example, since a limited number of items are offered, you know that ProductID 1 refers to jeans. So place a 1 in the Order Details_ProductID and press the tab key. Notice that the database uses the relationships to pull up the corresponding information for jeans (see exhibit 16).
[Exhibit 16 ILLUSTRATION OMITTED]
Now add the quantity sold--10--and tab over to Sale Price and enter $40.00. Similarly, add items to the order to match exhibit 17.
[Exhibit 17 ILLUSTRATION OMITTED]
You can add new customers easily by using the navigation buttons on the bottom left corner of the screen,
Use the new record button and input the information in exhibit 18, for the new customer (start with Company, and the program will automatically add a number for the ID).
[Exhibit 18 ILLUSTRATION OMITTED]
Congratulations. You have
just created tables, relationships and a form. Only a few years ago you had to be skilled in database technology and programming to construct a database from scratch; now with the user-friendly database programs, little special training is needed.
Now that you've gone through this exercise, be sure to look for opportunities to develop and use databases. Only through practice will you become comfortable with this new tool and find yourself using it often and flexibly. Mastery will lead you to many handy ways to make a database work for you, doing jobs you otherwise would have avoided as too difficult.
If you have a special how-to technology topic you would like the JofA to consider for inclusion in this series, or an application shortcut you devised and would like to share with other professionals, contact Senior Editor Stanley Zarowin. His e-mail address is firstname.lastname@example.org.
* AS ACCOUNTANTS BROADEN THEIR FINANCIAL careers and become more analytical, they turn increasingly to database applications to convert raw data into useful business information.
* IN THIS FOLLOW-UP to an earlier article on setting up a database, we show how the application can display relationships and how to create data-entry forms.
* ONCE YOU'VE DONE THE EXERCISE, seek opportunities to develop and use databases. Only through practice win you become comfortable with this new tool and find yourself using it often and flexibly.
* MASTERY WILL LEAD YOU to many handy ways to make a database work for you, doing jobs you otherwise would have avoided as too difficult.
DAVID G. HAYES, CPA, is an accounting doctoral student at the University of South Florida, Tampa. His e-mail address is email@example.com. JAMES E. HUNTON, CPA, Phi), is the Quinn Eminent Scholar of Accounting Information Systems at the same university. His e-mail address is firstname.lastname@example.org.
|Printer friendly Cite/link Email Feedback|
|Author:||Hunton, James E.|
|Publication:||Journal of Accountancy|
|Date:||May 1, 2000|
|Previous Article:||Start your own firm.|
|Next Article:||Let's reassess accounting standards.|