DeltaAccess –
a SQL interface to DELTA (Description Language for Taxonomy), implemented in Microsoft Access

Glossary of terms and procedures

The glossary contains short explanations which are used as popup topics in the Windows help file. This concept can not be simulated easily in a HTML file.

If you are reading the documentation sequentially, you should probably stop here. The following entries make little sense when used outside of their hypertext context.

Appendix 3: Table of Contents    Main table of contents

 

DeltaAccess database size limit: Access imposes a limit of 1 GB for the size of the mdb database file. This is equivalent to ca. 150 MB import file size. You would have to store your data on a SQL-server and link the files back into Microsoft Access if your database grows larger than this. (Also, that could mean 25 h import time: I hope nobody is using DeltaAccess with such large data sets yet. The largest data set tested so far had 2500 items in a 3 MB item description import file.)

 

SQL (Structured Query Language; pronounced 'sequel'): A standardized database language used by most modern database engines and servers. SQL consists of a Data definition language to manipulate the definition of tables, indices etc., and a Data manipulation language to retrieve or change data. The Microsoft Jet database engine SQL is generally ANSI-89 Level 1 compliant, although some differences exist. The most important difference is that more intuitive wildcard characters are used:
 

Microsoft Jet SQL

ANSI SQL

Any single character

?

_ (underscore)

Multiple characters

* (asterisk)

%

See the contents of the Microsoft Access help file, Microsoft Jet SQL Reference, Overview, Comparison with ANSI SQL for more information.

 

*** Terminology: Used and Unused characters are synonymous to Scored and Unscored characters, as used by Pankey. I consider the Pankey terminology preferable, and will change the documentation accordingly in a later edition. **** Pankey examples: "Report on missing data", "List the unscored characters for each taxon".

 

Compatibility of character definition:

Two character definitions are compatible if the characters, as identified by their number (CID), have compatible character types and identical character state numbers or codes. If only the character or state names have changed, no problem will occur, as long as the meaning of the respective names remained identical. Example:

Character definition 1

Type

  

Character definition 2

Type

1. First character

TE

  

1. First character with different name

TE

2. Second character
   1. First state
   2. Second state

OM

  

2. Second character
   1. First state with different name
   2. Second state

UM

3. Third character
   1. Small
   2. Intermediate
   3. Large

OM

  

3. Third character
   1. Small
   2. Large

OM

The characters 1 and 2 would be compatible, despite differing names and types (OM and UM are compatible). Naturally, the meaning of the characters or states where the name differs must be compatible. Due to character 3 the two definitions in the example are incompatible. It is possible to import data from definition 2 into definition 1, but not the other way round. Note that this would not make sense in the current example, since the meaning of state 2 differs (larger vs. intermediate).

 

 

The character type UM is defined in the DELTA standard as Unordered Multistate character. It is a categorical (= 'qualitative') character on the nominal scale. In contrast to ordered characters, each state can directly be transformed into any other state.

DELTA allows several character states in single character (= multistate). See also Overview of categorical character types.

 

 

The character type OM is defined in the DELTA standard as Ordered Multistate character. It is a categorical or qualitative character on the ordinal scale. The sequence of states is fixed: To reach state 4 from state 1 one must go through the states 2 and 3.

DELTA allows several character states in single character (= multistate). See also Overview of categorical character types.

Overview: unordered and ordered categorical character types

(See also overview of character types)

The normal case of an ordered character (type OM). In contrast to numeric types, only the sequence or order of states is known, but not the distance. Thus, states 1 and 2 and states 2 and 3 may have very different distances. The additional information that states are ordered is useful for identification and analytical purposes.

The states are ordered, but branching occurs. This type is rare, but may occur where the phylogeny of a character is know, e.g. through fossil evidence. Sometimes colors or shapes could be viewed as branched ordered states.

A special case of linear ordering. Most common example: months of a year. For phenological purposes (when does a plant flower, insect fly, etc.) it is important to know that January is next to December and February.

Implementation of this character type (as "CY") is planned for a future version of DELTA.

Unordered categorical character. See character type UM. Each state can directly be transformed into any other state. Most structures and shapes are treated as unordered characters.

Note that there is no real difference between ordered and unordered states if only two states are present.

 

The character types IN and RN are defined in the DELTA standard as Integer and Real Numeric characters. Integer numeric characters are typical for counts and only take values of 0,1,2, ... while real numeric characters can take fractional values like 2.463, or 1.3e-4 (which is the way in Access and most computer programs to enter the number 1.3 x 10-4). Other terms for real numeric are floating point numeric, or interval scale.

Note that DeltaAccess does not restrict IN character type data entry to integer numbers, because the mean, s.d., etc. of IN characters can be a floating-point number. See also explanation of character types.

 

To enter a real numeric or floating-point number into DeltaAccess, the character type must be defined as RN or IN. You enter fractional numbers using the decimal separator (usually "." or ",") defined on your computer (as set in Windows control, international). This documentation assumes that your decimal separator is a period (".").

If your number contains decimal exponents, use the letter "E" in the place of the 10. Thus, you would enter '1.45E-3' for '1.45 x 10-3'. The range of floating-point numbers in DeltaAccess is -1.79769E308 to -4.940656E-324 for negative values and 4.940656-324 to 1.79769E308 for positive values. An over- or underflow error occurs if numbers are larger or smaller than these values.

 

The character type TE is defined in the DELTA standard as text character. You can enter any kind of text, plus additional notes. Note that searching of the text information is possible, but relatively slow. Information relevant for identification should normally not be put into TE characters. See also explanation of character types.

 

The DELTA standard defines several special character states, also called pseudo-values. 'U' stands for unknown. It is usually entered to indicate explicitly that the character was checked in the present item, but is unknown to the best of current knowledge; as opposed to: character not yet checked (in which case nothing is entered). Thus, in contrast to most databases which support only a single Null value for missing observations, DELTA allows to separate Null values.

'V' stands for variable and is an alternative to entering all possible character states. It does not explicitly state that all states are present, but that so many states may be present that the character is considered to be uninformative in the present item.

The dash ('-') stands for not applicable and is used where this is not already obvious through the definition of inapplicable characters in the character definition.

DeltaAccess further uses the special character state 'TE' to allow the entry of text data in the place of a numerical or categorical character. While this is default in DELTA, DeltaAccess requires this special state to be explicitly enabled in the character definition. For text characters, this state is the only state defined allowed. The special state TE is used exclusively by DeltaAccess for its internal operation. It will not appear in an export to a DELTA coded file.

 

Combined character states are used when a combination of states has a distinct quality from the states it consists of. Examples are combinations of colors as in multicolored flowers ('red with white spots'), as opposed to flowers changing color ('white, later reddish') or flowers with alternative colors ('red' or 'white').

Combined states can simple be added as a new state, but to preserve the relation with the states it consists of, it is recommended to use codes of the form <state code1>&<state code1> (e. g. "1&2"). In standard DELTA, as implemented, e. g., by Confor, you can create combined states ad hoc in any item description. You do this each time you use the "&" operator instead of the usual "/" operator. DeltaAccess does not support this. It purposely requires the deliberate definition of such a combined state, to increase the structure of the data and to avoid unwanted combined states. Analyzing existing data sets, it was detected that the majority of combined states were due to errors, rather than deliberate decisions.

If you use the recommended "1&2" codes, the combined state are more appropriately exported to other DELTA programs, and in a compiled item description for interactive identification the constituent states (e. g. "1" and "2") are added.

 

Modifier Templates are stored directly in the hidden template table USys_DeltaTemplates_MOD. You can edit these entries after you have made this table visible using the Microsoft Access options. Your new entries will be added to any new descriptor project you create. This is especially useful because, since DELTA does not support modifiers, the import process can currently only identify modifiers already present in this table. I appreciate it if you send me your comments and additions to the default list.

 

Manually link the item part of a descriptor project with an independent physical character definition

Creating an item link together with the copy option for character data makes it impossible to enforce referential integrity in the new character definition. This means that you yourself are responsible for the consistency between character definition and item descriptions. If for any reason you want to take that responsibility, you can achieve this manually: Create one descriptor project with Link/Link, and another descriptor project with Copy char. definition. Delete the item tables of the second project, the character definition queries of the first project, and rename the character tables of the second project to the names, the previously deleted queries have had. Example: First project X, 2nd project Y. Delete tables Y_DESCR, Y_ITEM, Y_RSC, and queries X_CHAR, X_CS, X_DEP, X_MOD, etc. Rename table Y_CHAR to X_Char, etc.

 

Integrating HTML forms or natural-language-description documents into other applications

If you want to integrate HTML form documents or natural language description documents created by DeltaAccess into other applications, e.g. into a web interface for interactive identification, you must know the rules by which the filenames of the HTML documents are formed.

The simplest solution is to use the item IDs (IID) as filenames. The IID attribute can be accessed directly from the ITEM table. If you want to use filenames based on item names, the integration is more complicated. Rules for filenames based on item names explains how the filenames are build. Instead of porting the functions used to create filenames based on item names, you can create a new query in Access, switch to SQL view, and insert the following query (replace 'PRX' with the name of your descriptor project):

SELECT IID, ItemName, ItemFileName("X.HTML", 3, [IID], [ItemName]) AS Filename FROM PRX_ITEM ORDER BY ItemName;

You can either use this query dynamically to look up the appropriate file name (use a where clause like: "Where IID=87"), or you can create a static table use a Insert-Into query, for example:

SELECT IID, ItemName, ItemFileName("X.HTML", 3, [IID], [ItemName]) AS Filename INTO Item2FilenameTranslation FROM PRX_ITEM;

Please contact me if you have any problems with this. Also note, that the procedure contains a method to deal with duplicate item names, which is not present in the query above. Thus, if you have duplicate item names in your descriptor project, you should rather base the filenames on the IID.

 

Rules for filenames based on item names (HTML forms or Natural language description documents)

Several problems exist, if you choose to use the item name as filename. The item name may be too long, it may contain characters not compatible with a file name, and it may not be unique.

To create filenames based on item names,

After these replacements have been made, the item name is truncated to 40 characters. Should this item name not be unique, the IID number is appended to the second and following items. See also Integrating HTML forms or natural-language-description documents into other applications.

 

Action: Various management options are available, like create new, delete, restore, etc. Click on to pick an option from a list.

 

Select All button: Selects all entries in the associated multiple selection list box

 

Deselect All button: Deselects all entries in the associated multiple selection list box

 

Complement button: Reverses the selection of all entries in the associated multiple selection list box: selected entries are deselected, not selected entries are selected

 

Select-Condition-button: Select entries in the associated multiple selection list box using a condition entered by the user. Examples how to specify the condition will be given in the dialog asking for the condition.

 

Edit Char ... button: Open the selected character(s) in the standard character editor (CharEdit). Useful to look up additional information about a character as well as to change part of the character definition if required.

 

Edit Items ... button: Open the selected item(s) in the standard item editor (ItemEdit). Useful to look up additional information on an item, as well as to change the item definition or item description.

 

Edit Headings ... button: Open the selected heading(s) in the standard heading editor (EditHeadings), where you can edit headings, create and delete headings, and change the association between headings and characters.

 

 

 Move button (Reorganize character states):

Move the selected character state definition (and all item description data where this definition was used) from the character on the left side to the character selected on the right side.

 

 Join button (Reorganize character states):

Join the definition and item description data of the character state selected on the left side with the character state of the identical code in the character selected on the right side. You can not select a character state on the right side. To join state 1 on the left side with state 2 selected on the right side, select 1 on the left side and enter '2' in the Rename CS field at the bottom of the left.

 

 Copy button (Reorganize character states):

Copy the definition of the character state selected on the left side to the character on the right side. Item descriptions using this character state are not copied!

 

 Delete button (Reorganize character states):

Delete the character state selected on the left side and all item description data using this character state.

 

Rename character state (Reorganize character states):

The character state selected on the left side is renamed while copied or moved to the character on the right side. Enter the code (usually number) to which the character state shall be renamed. To rename or join a state within a character, you can select the same character on the left and right side.

 

Select Used Items button: Select all items, which contain the character state (or states) currently selected in the multiple selection list on the left side of the dialog box. This can be a different state than the one you want to insert. See MultiItemEdit for further information.

 

Select Unused Items button: Select all items, which do not contain the character state (or states) currently selected in the multiple selection list on the left side of the dialog box (i.e. the state is absent, or "not scored" in these items). The character and state used for selection can be different from the one you want to insert. See MultiItemEdit for further information.

 

Identify button: Use the Identify dialog box to select items based on complex criteria. See MultiItemEdit for further information.

 

Cumulative checkbox (below the buttons on the right side of the item list): If this box is unchecked, all items are deselected before a new selection through the Used, Unused, and Identify buttons is performed.

If it is checked, the results of multiple selections through the Used, Unused, and Identify buttons will be added to the items already selected. The item set created is the union set of all selection steps done. This is equivalent to a combination of the selection steps with the OR operator. See MultiItemEdit for further information.

 

 Insert button: Insert the character states selected in the list above the button into the items selected in the list on the right side. If only a single character state is selected, you may optionally insert additional Modifier or Notes information.

For text characters you can insert text information entered in a field that appears in the place of the character state list above the button. See MultiItemEdit for further information.

 

 Delete button: Delete the character states selected in the list above the button from the items selected in the list on the right side. Additional Modifier or Notes information is ignored. If, e. g., a Note with a text other than the one currently set in the Notes field on the dialog box is attached to the selected character state in one of the selected items, the description is removed, including the Note. For text characters, any text information is removed from the selected items. See MultiItemEdit for further information.

 

 Move Down button: Move the selected entries down to change their relative sequence

 

 Move Up button: Move the selected entries upwards to change their relative sequence

 

Move Steps: Moving entries can be comparatively slow. If you want to move an entry, e. g., 6 steps up, you can set this field to 6, and the move buttons will move all selected entries 6 steps in a single operation

 

 Insert between button: Insert a new character, heading, or item above the first character, heading, or item selected. See Reorganize definition for further information.

 

 Delete button: Delete the selected characters, headings, or items. If you delete a character, the associated character states, and all item descriptions using this character are deleted as well. See Reorganize definition for further information.

 

 Copy to project button: Copy the selected characters, headings, or items to a second descriptor project. You can select the project in the pick list below the button. If the ID number already exists in the destination project, the definition is inserted under a new ID number. However, since character and heading names are required to be unique, the copy action will fail if another character or heading with the same name already exists in the destination project. See Reorganize definition for further information.

 

You can enter a character number or name. If you click on the -button, you can pick from a list of available character numbers or names. If you select a number, the name will be filled automatically and vice versa.

 

Button to edit list of ID numbers: If you click on the button , a dialog opens in which you can select one or several items, characters, or character headings. See extended multiple selection for more information on selecting multiple items in a list box.

 

Condition 1 AND condition 2 generates the intersection set: of the result sets returned through each condition alone.

 

Condition 1 OR condition 2 generates the union set: of the result sets returned through each condition alone.

 

Microsoft Access 95 has several bugs, which are removed in Microsoft Access 97. One thing you may stumble upon is that modifiers can not be added in the CharEdit form. This is a documented bug in Access 95: checkboxes are incompatible with lookup queries. The form works fine in Microsoft Access 97, which has many other advantages as well: For example it is faster and less memory demanding than Access 95. I will no longer support Access 95 beyond the 1.0 beta version of DeltaAccess, which should be sufficient to give you an idea whether it is worth upgrading to Access 97.

 

Checkbox report, development note: Unfortunately it is not easy to produce an editing form in Microsoft Access which would be a simile to this printed report for entering your corrections. It may be possible using Visual Basic itself, but it would be a lot of work, which I currently cannot find the time to do. Also, although there are ways to generate reports with continuous text, you can not, to my knowledge, integrate checkboxes in such a text. Technical note: The checkboxes in the report are simulated using characters from the standard Windows font Wingdings. Access checkboxes would not be exported to RTF/Word in Access 97.

 

Context menu: The floating menu which appears on the screen in many Windows programs if you click with the right mouse button is called the "context menu". It is usually context specific to the object on which you have clicked. Since only the relevant menu options are available, it can be a fast method to perform certain actions. The usefulness depends strongly on the currently selected object: Selecting an axis in an Excel Graph will result in a highly useful context menu, selecting any text in a Word document will result in a barely useful context menu.

 

The browse buttons below the menu in the Windows Help program () can be used to read the help topics sequentially. Note that some pages may not be included in the browse sequence and will not be seen when you browse through the documentation.

 

A hypergraphic is an illustration that contains links to other information. When you move the mouse over an area for which additional information is available (e. g. a button depicted on the illustration), the mouse cursor changes to a hand symbol (). The area associated with a link is called a "hotspot".

When you click on a hotspot you will sometimes see a popup window on top of the illustration, and sometimes you will jump to another topic in the help file. The mouse cursor is identical in both cases.

 

The following contents topics directly access the main Microsoft Access help file. The copyright to these entries belongs to Microsoft Corp. You can only use them if you have a properly installed full version of Microsoft Access (i. e. not if you only have a run-time version).

 

To see the contents of the Access 97 help file, either click on the button labeled "Access Help" in the DeltaAccess help file, or select from the Access menu: Help, Microsoft Access Help or Contents and Index. The first tab ("Contents") of the help dialog box contains a list of all help topics. The symbols on the left side indicate whether the entry is a heading (book symbol) or a topic (leaf symbol). Double-clicking on a book symbol opens the book and displays the entries it contains.

 

By the use of a modified DELTA symbol, I want to express my reverence to the developers of the existing DELTA programs. At the same time I try to indicate that DeltaAccess tries to open up the DELTA standard, making it "accessible" to mainstream relational database systems. Most existing DELTA software is a closed system, which interacts with other software through import/export procedures alone. In contrast, DeltaAccess was designed to enable live data interchange with standard PC and WWW applications like SQL databases and webservers, using industry standard software interfaces (SQL, ODBC, DCOM), includes the source code and can be programmed in Visual Basic and other OLE capable software development tools.

 

Source code: To reduce the size of this application, I remove comments and formatting from the source code before distributing it. Thus, the normal version does contain the full source code, but is quite difficult to read. If you want to take a serious look at the source code or enhance some functionality, you can download a second version of DeltaAccess with full, commented and formatted source code. This version also contains additional forms, tables, etc. which are under development, may or may not work, and are not documented. Look for a ZIP file starting with SRC97_ followed by the version number (e. g. SRC97_151.ZIP for version 1.51) in the same directory from which you have downloaded the normal DeltaAccess distribution. Please contact me in case of problems.

 

 DeltaAccess

Copyright Gregor Hagedorn © 1995-1999. All rights reserved.
Institute for Plant Virology, Microbiology and Biosafety,
BBA, Federal Biological Research Center
Königin-Luise-Str. 19, 14195 Berlin, Germany.
email: G.Hagedorn@bba.de

See also Conditions of use

 

Printing the documentation:

Both the help version and the HTML internet version are somewhat difficult to print as a whole.

The Windows help function has only limited support to print parts of a help file. You can print each topic which you see on the screen. In the special Contents tree view of the help program (i.e. not in the tables of contents pages, which are displayed as help topics) you can also print a group of topics by selecting a first level entry ("book symbol", can be expanded to see the topic "sheets"). Still, each topic is printed on a separate page, which will waste a lot of paper. Also, many sub-topics of main topics are not included in the table of contents.

The HTML documentation is currently split into 22 files, each of which must be printed separately in the browser. Still, this is faster and wastes less paper than printing all topics of the help file. If you are reading this in a help file, point your web browser to the location from where you have downloaded DeltaAccess (www.bgbm.fu-berlin.de /projects /DeltaAccess/), select the link to "read the documentation directly in your browser", and go from the table of contents to the last entry ("Glossary", which contains this paragraph at the end). In your web browser, you can click on each of the following numbers and print each file: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24.


Some care has been taken to keep the documentation readable in printed form: internet URLs are added as visible text to hyperlinks, cross references often quote the title of the referred chapter. Still, some links to minor items or cross-references within the text might not be immediately obvious. Also, the width of some tables is optimized for screen display and may be too wide to print on your local paper format.

(Version for printing)

To make printing easier, a copy of the documentation in RTF format can be provided, through which the whole documentation can be printed as a single piece. (CURRENTLY NOT AVAILABLE, PLEASE REQUEST IT BY EMAIL)

 

Appendix 3: Table of Contents    Main table of contents