fter doing some basic cost accounting, you’ve calculated that it costs your company an average of $2.14 to respond to a customer’s order inquiry on the telephone but only 10 cents if the customer accesses the data via your Web site. Surely you don’t need any more research to convince you that the Internet is the way to go.

In “Do It on the Web,” JofA, Mar.01, page 43, we demonstrated how to create a basic Web site with Microsoft’s FrontPage. In this article we’ll go several steps further: To demonstrate a Web site’s flexibility, we’ll show you how to set up a site that lets customers determine whether what they want to buy is in your inventory, verify orders and change an order if need be, and convert an order’s format to Excel because some accounting data are best handled in a spreadsheet.

While this workshop focuses on enhancing customer service, with some adaptation, you can modify it for many other uses.

VIEWING ORDERS In order to track this tutorial, you’ll need to download the Access database and the FrontPage file we created in the earlier article. You’ll find them at http://ftp.aicpa.org/public/download/GardenDelights.exe and at http://ftp.aicpa.org/public/download/ExoticFoods.mdb. Once they’re downloaded, create a folder called webs7 in the C:\My Documents\My Webs folder (assuming C:\ is your root directory). Unzip GardenDelights.exe to C:\My Documents\My Webs\webs7, and now you’re ready to begin. Add a table to ExoticFoods.mdb by opening the database and double-clicking on Create table in Design view (see exhibit 1, at right).

Exhibit 1

Using the knowledge you obtained in the prior article, create five fields: OrderNo, CustNo, Password, ProdDesc and Quantity. OrderNo has been designated as the primary key (right-click on the OrderNo and select Primary Key from the shortcut menu). Save the table as Orders (see exhibit 2, below).

Exhibit 2

Exhibit 3 Add a few records to the table (select View, Datasheet View) as shown in exhibit 3, above, and close both the database and Access.

Now open FrontPage and the Garden Delights Web site and create a new page by doing the following: In Navigation View, click on the Home Page (Garden Delights) and select File, New and Page. Right-click on the new page, select Rename from the shortcut menu and change the page label to Orders. Press Enter and select View, Refresh to see the new file (orders.htm) in the Folders List. Right-click on order.htm, select Rename and change the file to orders.asp. To change the Page Title to Customer Orders, double-click on the orders.asp file, right-click on the page and select Page Properties, add Customer to the Title and click on OK (see exhibits 4 and 5, below).

Exhibit 4

Exhibit 5

Type in Check Your Order on the orders.asp page. We’ll use the Database wizard to modify the existing Food database connection to display the customer orders. Select Insert, Database, Results and click on Use an existing database connection. Make sure the Food database connection is selected by clicking on the down arrow until you find it (see exhibit 6, below).

Click on Next to find the Orders table (see exhibit 7, below). You may have to click on the down arrow to find it.

Exhibit 6

Exhibit 7

Click on Next to show the fields that will be displayed; there are only five fields in this example. You can scroll down to view all the fields (see exhibit 8, below).

Now we have to password-protect the information so only the company and the customer can access it. Since we don’t want the password to appear on the screen, click on Edit List and remove Password from Displayed Fields on the right by double-clicking to send it to the Available fields area (see exhibit 9, below). Click on OK.

Exhibit 8

Exhibit 9

Click on More Options, Criteria and then on Add to tell FrontPage how to restrict the records. In this example, the customer would enter its number in OrderNo to search for its orders. Note that Use this search form field is checked (see exhibit 10, below).

Click on OK (see exhibit 11, below).

Exhibit 10

Exhibit 11 Click on the Add button again and use the dropdown box to find Password. This will require users to enter their password (see exhibit 12, below). Click on OK.

Click on OK again to return to the More Options dialog box. Click on the Defaults button and add a default value for the OrderNo field by clicking on Edit. The default can be any value—just make sure it is not an actual order number so that a customer’s order doesn’t appear as the default. Click on OK. Repeat the process to add a default value for Password. Finally, you may want to modify the default message by suggesting customers reenter their order number (see exhibit 13, below).

Exhibit 12

Exhibit 13

Click on OK, Next, Next and Finish. Save the page.

To customize the Submit button on the search form, right-click on the button and select Form Field Properties from the shortcut menu. Type in Check your order in the Value/label textbox and click on OK (see exhibit 14, below). The Reset button is not helpful in this application; click on it once to select and delete it. Save the page.

Finally, we’ll change the properties of the Password field in the search function so that asterisks (****) fill the textbox. To do that, right-click on the Password textbox, select Form Field Properties and change the Password field from No to Yes. Click on OK and save the page (see exhibit 15, below). Be aware that each time you tweak the Web page and regenerate the database, the field defaults back to No—so you have to change it back each time.

Exhibit 14

Exhibit 15

CUSTOMER OPTIONS
Now we want to give customers an opportunity to either confirm their orders or, if necessary, change them. Although this requires a bit of coding, it’s worth the effort and you can streamline the process by using FrontPage’s Replace feature. The activity requires three pages: a catalog order page, a confirmation page and a thank-you page.

Start by creating the catalog order page. In Navigation View, click on the Home Page (Garden Delights) and select File, New, Page. At the top of the page, enter a heading: Garden Catalog, Complete the form to receive the newest 75-page catalog. Select Insert, Form, One-line textbox. Press Shift-Enter to create a line break. Repeat until there are seven textboxes, one for each data item to be collected (see exhibit 16, at right). We’ll add the textbox labels shortly. Now we’ll create a two-column, seven-row table inside the form. To do that, make sure your cursor is inside the form and click on Table, Insert, Table and then set seven rows and two columns. Change the border size to 0 to make the table invisible. Drag the textboxes one at a time into the second column. Place the corresponding labels in the first column of the table.

Exhibit 16

To name a textbox, right-click on it and select Form Field Properties. Replace the default name, such as T1, with a meaningful name: Name for the Name box, Address for the Address box, and so on. You will need to click on OK after each entry. Save the catalog order page as catalog.asp and change the page title to Catalog Orders by right-clicking on the page and selecting Page Properties.

We’ll make a copy of this page as the foundation for the confirmation page, which we’ll prepare now. Save the catalog.asp page under a new name—confirm.asp. Change the heading to Confirm your shipping information. Delete the Reset button by selecting it and pressing Delete. Enter user instructions next to the Submit button: Need to make changes? Click your Browser’s BACK button (see exhibit 17, at right). Save the page. We’ll return later to this screen to make more changes. Reopen catalog.asp and click the HTML tab at the bottom of the screen. We’ll make two minor changes. First, change <form method=”POST” action=”—WEBBOT-SELF—”> to <form method=”POST” action=”confirm.asp”>.

Exhibit 17

Then delete the short paragraph that refers to the webbot. It is easy to distinguish because the type is light gray, whereas the remainder of the HTML code is usually black, brown or blue. Highlight the light gray text and press Delete. Save it.

Thus far, we have directed FrontPage to collect information in catalog.asp and display it in confirm.asp. Users can go back to make changes to their order or submit it to the database after we finish confirm.asp. FrontPage has a default confirmation page that will appear automatically. For that reason we will replace it with our own thank-you page. On a new page, enter Thank you for your order. You may want to create a link back to the home page. Save the page as thankyou.htm (see exhibit 18, at right).

Exhibit 18

Now reopen confirm.asp. Right-click inside the form and select Form Properties from the shortcut menu. Select Send to database and click on the Options button. Click on the Create Database button. An Access database will be created with the same name as the file name (confirm.mdb). Click on OK. Access will name the newly created table Results. Enter thankyou.htm in the box labeled URL of confirmation page. Click on OK twice. If you skip this step, FrontPage’s default confirmation page will appear.

To make the user entries appear on this confirm.asp page, select the HTML tab at the bottom of the screen. Each textbox has its own line in HTML code. In order for the user’s entry (from the catalog.asp page) to appear, it’s necessary to make one change to each textbox: To add the information that’s collected, insert value=”<%=Request.Form (“textboxname”)%>” between size=”20” and the final > of the line.

For example, the textbox that collects the user’s name would change from <input type=”text” name=”Name” size=”20”> to <input type=”text” name=”Name” size=”20” value=”<%=Request.Form(“Name”)%>” >.

The easiest way to do this is to use FrontPage’s Replace feature: Select Edit, Replace and replace size=”20” with size=”20” value=”<%=Request.Form(“Name”)%>”. Click on Replace All and close the dialog box (see exhibit 19, at right).

Exhibit 19

Complete the process by manually changing Name to Address, City, ST, Zip, Phone and E-Mail so that it resembles exhibit 20, below. (The formatting code has been removed from this exhibit for clarity.) Save the page. After the page is published to a Web server, the user can enter shipping information, check it and make corrections if necessary. The final Submit button sends the data to your database and a thank-you to the user.

Exhibit 20

THE EXCEL CONNECTION
Since many businesses keep their financial records in spreadsheet files, we’ll show you how to save those files as a Web page and display them on your site. You can use inventory. xls downloaded with the zip file to follow along. Select File, Save as Web Page—being sure to change the default folder location to the one that contains your Web site. Use the file name web_inventory.

Caveat: A reminder about HTML file names: Some older browsers don’t like spaces in file names so use an underline (_) to represent a space. Also, use all lower case letters for the same reason.

Change the selection from Entire Workbook to Selection: Sheet. The file name may change from web_inventory.htm to page.htm. If it does, click on the down arrow next to the file name box to change it back. Set the page title by clicking on the Page Title button and type in Gardening Delights Inventory and click on OK (see exhibit 21, at right). This title will appear in the blue title bar of browsers and will be picked up by search engines that scan the Internet for new material. Finally, to complete the conversion from a workbook file (.xls) to an HTML file (.htm) that can be viewed on the Internet, click on Save.

Exhibit 21

For a quick preview, open your Internet browser and select File, Open and Browse to C:\My Documents\My Webs\webs7. Select the file and click on Open.

Want to limit access so only some of the fields appear? It takes only a few extra steps. The trick is to avoid using spaces in the column header names in the Excel file. In this example, a column header such as Product Name wouldn’t work; instead, use ProdName.

To do it, open Access, click on File, New, Database, OK. Change the folder location to C:\My Documents\My Webs\webs7\fpdb and change the file name from the default name of db1.mdb to cust_orders.mdb and click on Create. If you downloaded orders.xls located in the CustOrders folder, your screen should match exhibit 22, below when you select File, Get External Data, Import and change the file type to Microsoft Excel (.xls).

The Import wizard will appear. Click on Next, check the box First Row Contains Column Headings and click on Next (see exhibit 23, below).

Exhibit 22

Exhibit 23

Store your data in a new table (see exhibit 24, below) and click on Next, Next.

Select Choose My Own Primary Key and select OrderNo. Click on Next (see exhibit 25, below). The default table name will be the same as the original file name (Orders). Click on Finish and OK.

Exhibit 24

Exhibit 25

GRAPH THE DATA
Microsoft Access is a great tool for collecting data via the Web. However, to create graphs for further analyses or presentations, it must be converted to Excel. You can easily do that in two steps. First make sure all numbers are saved as numerical data and then use the Office Link feature. Here’s how:

In the Design View of the Orders table we just created, check the data type of Quantity. Change from Text to Number if necessary (see exhibit 26, below). Save it.

Select Tools, Office Links, Analyze It with MS Excel (see exhibit 27, below). Save the file as Orders Analysis.xls. You can now create graphs in the usual manner. If you get blank graphs, return to Access and make sure the data type has been changed from text to numbers.

Exhibit 26

Exhibit 27

The new tools you learned in this workshop can help make a big difference in your customer, supplier and client relations. While creating a site looks complicated because it requires so many steps, once you’ve done it a few times, you’ll find that it doesn’t take long to do. And the practical applications are almost limitless.

TERRYANN GLANDON, CPA, PhD, is an assistant professor of accounting at the University of Texas at El Paso. Her e-mail address is tglandon@utep.edu.