Loading

Everything HP200LX: Knowledge, Products, Service 

HOW TO USE Database Part IIThis article discusses how to use 123 as a sophisticated database program that can produce reports with amounts calculated from database fields using criteria you specify. This article builds on the database techniques presented in the November/December 1995 issueBy Carl MerkleThe November/December 1995 issue reviewed some basics of how to use 123 as a sophisticated database program. Specifically, we created a sales database listing the sales person, customer, month, part number, quantity, unit price and totals (see screen graphic, this page). The reason 123's database capability is important is that the builtin DataBase program, while useful for capturing data and printing lists, does not provide the ability to create reports with calculated fields and calculated group subtotals. Thus 123 can be used to enhance the Palmtop's builtin DataBase program.The November/December issue demonstrated how easy it is to create a 123 database and calculate group subtotals within a database using a subtotals formula that we created. We also developed an example showing how to link two database tables together using the @vlookup formula, thus obtaining capabilities similar to those found in "relational databases." The techniques employed, while useful, turn out not to be the most efficient and flexible way to create reports containing only results of the subtotals formulas. In this issue we'll take the database linking and subtotals concept a step further and discuss how to create a database lookup table in 123 with tabulated results using 123's @DSUM formula. Then we'll discuss how to use 123's @vlookup formula to grab results from the lookup table for inclusion in a report section elsewhere in the spreadsheet. In this context we'll discuss an error trapping technique you may want to consider when using 123's lookup formula. Sales Database with monthly sales amounts calculated using subtotals formula (from November/December 1995 issue). Introduction to 123's database summing formula We'll use the 123 database spreadsheet displayed below as a starting place for this tutorial. This was created based on the instructions given on pages 5456 of the November/December 1995 issue. (Note that there were errors in the spreadsheet displayed in that issue which have been corrected.) This time we will use another approach to construct a subtotals by month formula. To do this, we'll use 123's @DSUM formula, a powerful Database Statistical Function that lets you add up amounts in a field (column) of a database only if certain records (rows) in the database meet certain criteria that you specify. The syntax for the formula is (on a single line): @DSUM(Database_Range, Field_Column_No, Criterion_Range) Below is an explanation of the above formula: Database_Range  The range of our worksheet containing data. In our example above the database range is in cells B3 through I13. Notice that this range includes eight fields (columns B through I) and also includes the field names (Empl, Cust, Mo, etc.). Field_Column_No  We want to be able to create subtotals of the data in the Sales field, which has a field column number of 6. (The first field in the Database Range is Empl and has a field column number of 0. The second, Cust, has a field column number of 1, third, Mo, has a field column number of 2, etc.). Criterion_Range  This specifies where @DSUM will look for the selection criteria (i.e., which amounts in the Sales column 123 should add). The top row of a criterion range must contain one or more of the field names from the database. The second and subsequent rows of the criterion range hold the criteria themselves. For our example, we'll construct a criterion range in cells D16 through D17. We'll enter the label Mo in cell D16 and the number 1 in cell D17. To complete this example enter the following formula in cell H17: @DSUM(B3..I13,6,D16..D17) This formula says the following: For the range of cells between B3 and I13, add up all the data in cells in the Sales field (field column number 6), but only include the data if the number associated with it in the Month column is 1 (i.e., sales figures from the first month). The result of the formula should be $1,137,500. If the number 2 is entered into D17 the result should change to $812,500. The only problem with using this method is that to create multiple subtotals you need multiple criterion ranges, each with a different month. These are a pain to create. However, there are at least two ways around this that I am aware of. One involves the use of the data table command. The other uses a macro to simulate the data table command. The data table method is presented in this article. A future article will cover the macro method. Calculating multiple subtotals using the Data Table command 123's Data Table command lets you build a kind of whatif table in the worksheet. In this example, we want to vary the month and determine the sales subtotals (i.e., what if the month was 1, what if the month was 2, etc.). The first thing we'll do is to create a data table to vary the month number as an input into the @DSUM formula. In cell G18 enter 1, in G19 enter 2, and in G20 enter 3, in G21 enter 5. We'll calculate the subtotals of each months sales using the Data Table feature as follows:
Sales spreadsheet displaying subtotals in the bottom left, using the @DSUM formula in the data table (G17 through H21) The use of the Data Table command was discussed in the January/February 1996 issue. What it just did was replace the amount in D17 with each number in cells G18 through G21. As it changed each number, it calculated the result of the @DSUM formula in cell H17 and placed the result of each calculation in cells H18 through H21. As you can see 123 returned summary results in the table yielding answers that we expected (i.e., they match the monthly subtotals in column I of the Database Range). You can easily change the selection criteria upon which Sales are being subtotaled by changing the field name in the criterion range and the record labels in cells G18 through G21. For example, let's say you wanted to look at a sales subtotal for a particular part numbers.
Sales subtotals report based on Part Number instead of Month. Wrap up this portion of the tutorial by giving the sales subtotals lookup table (the range from G18 to I21) a short name like LTB as an abbreviation for Lookup Table. To do this issue the command (MENU) Range Name Create. When it prompts you to Enter Name: type LTB and hit (ENTER). When it prompts you to Enter range: you can either highlight the cells in the range from G18 to I21 or type in G18..I21 and hit (ENTER). Displaying the results in a report If you use the @DSUM approach on larger databases and with longer lists, you may occasionally want to subtotal the subtotals, or have them rearranged in a way that is easer to read. For example, in my profession I frequently need to take a client's general ledger database and summarize all the amounts in it into key financial statement categories. I then present the subtotal categories in an easytoread financial statement. To create worksheets with great reporting flexibility, I use three areas in my worksheets:
The @vlookup feature lets you keep a constant value (like the unit price of an item), or a longer description of the item, or the subtotals like those just calculated in a separate part of the spreadsheet. You can then use the @VLOOKUP formula in a report located elsewhere to access amounts and descriptions from the Lookup Table. In the above example, I could use a @VLOOKUP formula to display the subtotals calculated with the Data Table command in another part of the spreadsheet. (In the above example we're working on, we will create a report range later at C24 through H32.) Undesired results with @VLOOKUP When creating any report from results and data contained in a lookup table you need to be very careful when using the @vlookup formula because a @vlookup formula has features which can produce undesired results. These undesired results are mainly produced in the report range when you ask 123 to lookup a number or text that does not exist in the index column of the lookup table. In the example we have been working on, we created the first data table which had month numbers. If we had tried to lookup the results for month 4 (which doesn't exist in the lookup table), we would have gotten an answer of 1,512,500. To understand how these undesired results can be produced, let's review again how the @vlookup formula works. The syntax of the @vlookup formula is (on a single line): @vlookup(x,lookup_table_range, column_offset) @VLOOKUP compares the variable x above to each cell in the first (leftmost) column of the lookup table range. This column is called the index column. When 123 locates a cell in that column that contains x, it moves across that row the number of columns specified by the columnoffset and returns the contents of that cell as the answer. If @VLOOKUP can't find a cell that contains x exactly, it will find and return the value closest to, but not larger than x. Thus the result 123 returns is located in the lookup table at the intersection of the row specified by x and the column specified by column_offset. X can be either a value (number) or label (text). In addition, it can be the address or name of a cell that contains a value, label, or a formula that returns a value or text. If the index column of the lookup table contains values, then x must be values. If the index column of the lookup table contains text, then x must be text. If values are used in the index column, they must be in ascending order. If text is used in the index column they can be in any order. Entries in the index column of the lookup table should be unique  no duplicates. For values, if x is smaller than the first value in a range, @VLOOKUP returns ERR (error). If x is larger than the last value in the index column of the lookup table, @LOOKUP stops at the last cell in the column specified by columnoffset and returns the contents of that cell as the answer. If the value being looked up is not in the lookup table, 123 returns the results of the lesser of two lookup table index rows (i.e., if x is 24 and the index column of the lookup table contains 20 and 25 on adjacent rows, 123 will select the information on the row specified by the 20). For text, if x does not exactly match the contents of a cell in the first column of range, @VLOOKUP returns ERR. The problem I try to overcome when working with the @vlookup formula for report writing purposes is the fact that if x is a number, @vlookup can return a result even when x is not present in the index column of the lookup table. In addition, if x is text, @vlookup returns ERR when x is not present in the in the index column, and a number of ERRs in a report is confusing. To overcome this problem, I often use a conditional version of the lookup formula. There are two forms of the conditional lookup formula one for values and one for text. The conditional lookup formulas work as follows:
Let's create a report range in C24 through H32 and compose a conditional lookup formula for that range. Key in the following labels and formulas in the report range: Cell Enter this C24 REPORT RANGE C26 Part C27 P03 C28 P02 C29 P01 C30 P04 D26 Description H26 Sales H31 \ H32 @SUM(H31..H26) D25 2 H25 1 The entries in cells D25 and H25 will tell 123 which column in the lookup table contains data to be presented. The entries in cells C27 to C30 tell 123 which rows in the lookup table contain the data to be presented. Thus 123 has the appropriate column and row coordinates necessary to prepare a report. Because column or row coordinates can easily be changed in your report range, you can easily modify the report as you wish. Since the lookup table (G18 to I21) has label entries in the index column (i.e., P01, P02 etc are text rather than numbers) our report range also uses labels in cells C27 through C30. Because labels are used the conditional lookup formula should test for errors. The following formula should be entered in cell H27 and copied down to H30. Also copy the formula in H27 to D27 through D30. @IF(@ISERR(@VLOOKUP($C27,$LTB,1)),0,@VLOOKUP($C27, $LTB,H$25)) Your results should appear show at the top of this page. Sales spreadsheet Report Range displaying sales figures based on part numbers. Try experimenting on your own You might want to try creating a monthly subtotals report based on the month number (value) instead of the part number (label). To do this, you'll first have to recalculate the subtotals of each months sales using the first Data Table example shown on page 48 of this article. Change the label in D16 from Part to Mo, change G18 through G21 to 1,2,3 and 5, and rerun the Data Table command as described on page 48. Then in the REPORT RANGE you'll replace the Part (C26) with Mo and replace the part numbers with month numbers (C27 = 3, C28 = 2, C29 = 1, C30 = 4). Then change the textbased conditional formula in H27 to a valuebased conditional lookup formula, and copy it down to H30 and across from D27 through D30. H27: @IF($c27=@VLOOKUP ($C27,$LTB,0),@VLOOKUP ($C27,$LTB,H$25),0) Report Range displaying subtotals report based on months instead of part numbers. "Mo" replaces Part in C26. Month numbers replace part numbers. This formula first checks for the existence of the value being looked up (C27) in the index column of the lookup table (the range we named LTB) by checking for equality (i.e., is the value in C27 equal to value found in the index column). If this is true the next lookup formula is performed in which 123 is told the appropriate columnoffset to use. If it is not true (i.e., the value in C27 does not match a value in the index column of the lookup table) 123 is instructed to return a zero. Conclusion I have found the techniques presented in this and the November/ December 95 column to be valuable in my line of work in that they let me rapidly create useful analysis reports in 123 from databases. I usually obtain the databases in some other format and import them into 123. (A future article will be devoted to importing databases from other programs and exporting 123 data to other programs.) By experimenting with the techniques presented in this and the November/December 1995 column, you will find that your Palmtop PC can be used as a powerful front end reporting tool for databases that you others create in a traditional database program and import into 123. Unfortunately, since 123 is a memory hog and conducts all its work in RAM, the size of databases you can work with is limited both by available RAM and the 8,192 row limitation of 123. In the next issue, we will explore a an extremely useful 123 addin program called @BASE, which has recently been modified to run on the Palmtop. @BASE lets you work in 123 on huge databases that are in the dBase III format (most database programs can save their data files in this format). The size of database you can work on from within 123 using @BASE is limited only by the amount of space you have available on your flash card. Thus, with a 20 MB flash card, you could conceivably work with a monstrous dBase III file approaching 20 megabytes in size. [123DB.WK1 is the completed spreadsheet example used in this article .] 

Copyright © 2010 Thaddeus Computing Inc