Writing a Formula and Exporting the Table to Access

Customer Question: Please can you tell me how to update a column with a value. The value is based on a formula applied on another field in the table. If I use a formula, and I examine the formula using Access then the value in the field is the formula not the value

(The customer appears to be opening the .mdb file within the Manifold .mfd/.mdb map pair directly. Another possibility is that he is writing the formula into the Table View without using either the "Edit Formulas" button or using the "=" equal sign short cut. Tech Support decides to send a collection of screen shots so all is made clear.)

Technical Support Replies: Manifold imports the database into the Manifold .mfd/.mdb map pair. This allows much faster processing when working with complex geometric data than linking an exterior database file. We're not sure what you are doing that the formula appears and not the value. Perhaps you are entering the formula as a string instead of entering the formula for computation. Here is a step by step procedure that sets forth the method.

We use the Counties98_demog sample data set. The screens show a Table View where all the fields have been hidden except one, a median income field that has been renamed "med" to keep the display simple. In addition, we have created a new integer field called median1, which is initially empty for all records. There are over 3000 records in this database.

	01.jpg Press highlight all to highlight all the rows.
	02.jpg Push IN the Edit Formulas button to switch the display to showing formulas instead of values.
	03.jpg CRTL-double-click into the top box of median1 and enter the formula [med] * 2 in the box. Since this is a simple example, we will do a simple formula that takes the contents of the "med" field and multiplies it by two. Press ENTER
	04.jpg The formula is propagated through the entire table (this is the effect of the standard Windows "power move" of using a CTRL-double-click to edit a box in a grid where all the rows are highlighted). This may take a few tens of seconds on a slow machine since there are over 3000 records in this example.
	05.jpg Push OUT the Edit Formulas button and we see the computed value of the formula instead of the formula text.
	06.jpg Click on the Export Table button to export this to Access via ODBC.
	07.jpg We are now in the standard Microsoft ODBC dialog. We'll export to Access 97. Choose the MS Access 97 DSN and press OK.
	08.jpg A nuance: ODBC exports only to databases that are already created. We can add a new table to an existing database, but if we don't have a database already created, launch Access in a separate window and create a new, blank database to be used. Here, we will add a table to the NewDB97.mdb database. We will save it to a table called "median."
	09.jpg Here we have opened Access [this is actually Access2000....] and have opened the median table in the NewDB97.mdb database. Note that the Export Table command in Manifold's table view exports only those fields that Table View has been told to display. Note also that the values computed by the formula have been exported and not the formula string.

Visit the Manifold.net Online Store anytime to purchase Manifold products. Details regarding shipping and purchasing may be found on the Online Store's information pages as well as on the Miscellaneous Info page. Email sales@manifold.net at any time, or call 800-556-5919 during business hours if you have any questions regarding products, purchasing, or shipping information.