Copyright: Database Desktop for Windows Help, v.1.xx
Citation: Copyright 1993 Borland International.


Help contents

Click a topic below to get information about that topic. Read Essentials if you are new to the Database Desktop.

	Essentials	Concepts and skills you need to work with Database Desktop
	Tasks	Step-by-step directions for using Database Desktop
	Menu Commands	Menu commands and dialog boxes
	SpeedBar	How to use buttons in the SpeedBar
	Keyboard	Keyboard shortcuts for Database Desktop menu commands


Essentials overview
Essentials

Before working with Database Desktop, read the following topics in this Help system. If you have no experience with databases, you should do the tutorial in the Database Desktop Guide before proceeding.

Using database data

Using tables

Relational tables

Indexes

Querying tables

Query-by-example (QBE)

Using DDE links

Database configuration


Using database data

Database Desktop lets you easily use data from external databases in your application. There are several ways to use database data. You can:

use Copy and Paste commands to copy data from a database and paste it into your application

use Edit|Paste Link to link database data to corresponding application data so that the application is updated whenever data in the database changes

See Also

Edit|Copy

Edit|Paste

Edit|Paste Link


Using tables

To set up a database in your application, you arrange the data into columns (fields) and rows (records). External database files (called tables), created by database applications such as Paradox or dBASE, are also set up this way.

Each row of an external database table contains all available information about a particular item; this is called a record. Each column contains one category of the information that makes up a record; this is called a field.

Relational tables let you easily extract or combine data from several tables to get exactly the information you need.

Indexes determine the order in which Database Desktop accesses the records in a table.


Relational tables

Relational database programs such as dBASE and Paradox give you a way to define a relationship (called a link) between information stored in separate tables. The data in a relational database is the information stored in all the related tables. The advantage of a relational database is that you can easily extract or combine data from several tables to get exactly the information you need. Also, a few small and discrete tables are more convenient to use and maintain.

Keys

A primary key (usually called a key) is a field (or group of fields) that contains data that uniquely identifies each record of a table. A key requires a unique value for each record (row) of a table. This ensures that you won't have duplicate records in the table. Tables that have keys are keyed tables.

A primary key can be defined on a single field or group of fields. When a group of fields is specified as a table's key, that group is called a composite key. With composite keys, duplicate values are allowed in individual fields of the key, as long as values are not duplicated across all fields of the key.

When you enter a new record, it may jump out of sight. This is because when a table is keyed, Database Desktop repositions new records according to the sort order.

Note:	Database Desktop is designed to help you use information that already exists in external database tables. You can't create a table using Database Desktop, nor can you define a key. To perform these operations, use dBASE or Paradox.

See Also

Using tables


Indexes

dBASE and Paradox use indexes, which are files that determine the order in which Database Desktop accesses the records in a table. Indexes organize records so that data can be found more quickly. Indexes work differently in Paradox and dBASE.

Paradox

Paradox organizes the records of a keyed table according to the values in the key field(s). This is its primary index. By default, all indexes organize and access data in ascending order (A to Z, or 0 to 9).

When a composite key is defined for a table, Paradox creates a primary composite index, which organizes the records by the first field of the key (according to the table's structure), then the next, and so on.

In Paradox tables, a secondary index defines an alternate view order to temporarily change the display order of the records. The physical location of the records in the table does not change.

dBASE

When working with dBASE tables, Database Desktop uses an index to organize the records in a table according to the values in one or more fields.

When a dBASE table is indexed, Database Desktop creates a file that contains the indexed field's values and their corresponding record numbers. Database Desktop refers to this index file when locating and displaying the records in a table.

See Also

Using tables


Querying tables

A query is a question you ask about the information in database tables. It can be a simple question about information in one table, or a complex question about information in several tables.

You can use queries to:

find or select information from a table

combine information from multiple tables

perform calculations on data in a table

insert or delete data in a table

change values in a table

define groups and sets of information on which to perform calculations and comparisons

Database Desktop uses a technique called query-by-example (QBE) to extract and manipulate data in external database tables.

See Also

Using advanced query-by-example (QBE)

Using query-by-example (QBE)


Query-by-example (QBE)

Database Desktop uses a technique called query by example (QBE) to extract and manipulate data in external database tables. For details on performing a query using QBE, see the Help topic called Creating a query.

Database Desktop actually accesses external database tables so you can edit, add, delete, or change the values in external database tables, without having to worry about network conflicts or old data. You can also search multiple relational tables at the same time.

To perform a query, you choose which tables to ask questions about. Then you enter an example of the information you want and run the query. Database Desktop determines the best way to arrive at the result, which then appears in an Answer table.

Answer table

The result of a query is a temporary table called Answer. The Answer table is overwritten each time a query is run. You save the Answer table data by renaming the table with Properties|Answer Table.

Other temporary tables can be created when a query is run, such as Changed, Deleted, Inserted, Errchng, Errdel, and Errins.

See Also

Querying tables


Using DDE links

Database Desktop uses Dynamic Data Exchange (DDE) to link data in one application to corresponding data in another application. To set up DDE links, you use the Edit|Paste Link command. DDE links are "live;" when data in one application changes, those changes automatically reflect in the other application.

For example, if you create a link between a database of customer names and the same data your application, then add a new name to the database, that name is also added to your application.

The application that is the source of the data to be exchanged is called the DDE server. The application that receives the exchanged data is the DDE client. Database Desktop can be a DDE client, server, or both.

See Also

Exchanging data


Database configuration

Database Desktop uses the Paradox Engine when it shares data with Paradox, dBASE, and other Engine-supported applications. For example, Paradox Engine is used when you open or save Paradox or dBASE files.

Database Desktop uses ODAPI (Object Database Application Programming Interface) when it accesses external database tables. The ODAPI Configuration Utility (ODAPICFG.EXE) lets you change access and format settings, which are saved to the ODAPI.INI configuration file. ODAPICFG.EXE is located in your Windows System directory. See ODAPI Help for directions on using ODAPI.

Running Database Desktop on a network

To work with external database tables located on a network, you need to set up the network drive using the Paradox Engine Configuration Utility.

In the Paradox Engine Configuration Utility dialog box, there are two options for the locking protocol, the mechanism that governs concurrent access of tables and determines what files an Engine-supported application can access:

3.5 Locking Protocol is the only protocol used by Paradox version 3.5..

4.0 Locking Protocol can be used for both Paradox versions 3.5 and 4.0 tables. Database Desktop and Paradox 4.0 use only this version of the protocol.

Caution: These locking protocols are not compatible. If a user opens tables located on the network and their locking protocol is set to 3.5, users with locking protocol set to 4.0 are locked out of the entire directory. Network administrators must be sure that all users are set up to use the same locking protocol.

To minimize potential conflicts, consider moving all Paradox 3.5 files to a separate directory.

Customizing the Database Desktop

During installation, ODAPICFG.EXE is copied to your Windows system directory. To start this utility, choose File|Run in the Program Manager, then enter C:\Windirectory\SYSTEM\ODAPICFG.EXE, where Windirectory is the name of your Windows directory.

In the ODAPI Configuration Utility, you must specify a Network Control File Directory to access tables on a network.

Note:	If you are using a network version of Windows, the Windows system directory is on the server. See your network administrator for details.


SQL drivers

The SQL drivers are an optional feature of the Database Desktop.If you want to order the SQL drivers, see the SQL literature and coupons inside the Quattro Pro box.


Database Desktop Error

If you see forward and backward browse buttons in the message box, Database Desktop has a stack of messages there, explaining more about the problem.

To find out more, click the forward browse button in the error message box.

To go back to a previous message, click the backward browse button.

If you see no browse buttons in the message box, there are no more messages.


Paradox for Windows Error

If you see forward and backward browse buttons in the message box, Paradox has a stack of messages there, explaining more about the problem.

To find out more, click the forward browse button in the error message box.

To go back to a previous message, click the backward browse button.

If you see no browse buttons in the message box, there are no more messages.

If you are testing an undelivered form, you can click the Design button to open the form in its Design window.


Menu commands

Here are the Database Desktop menu commands. The first column in the table displays the menu command. The second column indicates whether the command is available in the Query window, the Table window, or in both windows.

Command	Window

Edit	Query and Table

File	Query and Table

Help	Query and Table

Properties	Query

Query	Query

Record	Table

Table	Table

Window	Query and Table


Query window

The Query window is where you create a new query or open an existing one. If you're creating a new query and haven't yet saved it, the title bar displays "<Untitled>." If you've opened an existing query, the title bar displays the query name.

Opening the Query window

To open the Query window:

click the New Query Speedbar button

choose File|New Query

choose File|Open|Query

Query window SpeedBar

When the Query window opens, the SpeedBar changes to display the buttons used for queries. Click a button on the SpeedBar below to see what it does.

See Also

Creating a query

Defining the query

Running the query

Saving the query


Table window

The Table window is where you view and manipulate tables, one of the fundamental types of files Database Desktop works with. You use the Table window to move between records in the table, customize the view of the table, insert new records, and delete or modify existing records.

Opening the Table window

To open a table:

click the Open Table button

choose File|Open|Table

Table window SpeedBar

When the Table window opens, the SpeedBar changes to display the buttons used for tables. Click a button on the SpeedBar below to see what it does.

See Also

Viewing tables

Using edit mode


Open Table button

Opens an existing table.


New Query button

Left-click to create a new query, or right-click it to open a menu. Choose File|Open|Query to open an existing query.


Cut, Copy, and Paste buttons

Cut, copy, and paste data and objects to and from the Windows Clipboard. These buttons are shortcuts for the corresponding commands in the Edit menu.


Run Query button

Runs the active query.


Add Table button

Displays the Select File dialog box so you can add one or more tables to the Query window.


Remove Table button

Displays the Remove Table dialog box so you can remove a table from the Query window.


Join Tables button

Turns on example element placement mode. Example elements are typically used to join tables that are to be included in a multiple-table query.


Field View button

In Edit mode, the Field View button enters field view, where you can move the insertion point within a field value to correct mistakes. When viewing a table, field view lets you scroll within a field to see entries that are too large to display in the field.


Edit Data button

Puts Database Desktop into Edit mode.


Table Navigation buttons

|<	moves to the first record in the table

<<	moves to the previous full screen of records; like PgUp

<	moves to the previous record

>	moves to the next record

>>	moves to the next full screen of records; like PgDn

>|	moves to the last record in the table


SpeedBar

Database Desktop has a different SpeedBar for each of its three windows.

Click any SpeedBar button below for information about it.

Desktop (with no Query or Table windows open)

Query window

Table window

See Also

Query window

Table window


Tasks overview
Tasks

Click a topic for information on using Database Desktop.

Working in the application window

Entering and editing data

Exchanging data

Using query-by-example (QBE)

Using advanced query-by-example (QBE)


Working in the application window

Here are some of the tasks you'll do frequently in Database Desktop. Click a topic for more information.

Opening files

Saving files

Setting up a working directory

Setting up a private directory

Creating an alias

Changing an alias definition

Removing an alias


Entering and editing data

When you work with tables, you can:

customize your view of the table

work in Edit mode

cut, copy, and paste data

insert, delete, and lock records

work with validity checks and table lookup

Click a topic for more information:

Viewing tables

Using edit mode

Field types

Locking records

Fields with validity checks

Looking up table values


Using query-by-example

Database Desktop uses a technique called query by example (QBE) to extract and manipulate data in external database tables.

Here's a list of the things you tell Database Desktop when you construct a basic query:

what tables contain the information you want to ask about

what fields you want to see in the answer to the query

what records you want included in the answer

what calculations (if any) you want to perform

Click a topic for more information.

Creating a query

Defining the query

Creating query statements

Using checkmarks

Running the query

Saving the query

Working with the Answer table


Using advanced query-by-example

You can use query by example (QBE) to perform advanced tasks, including:

inserting new records into a table

deleting records from a table

changing the values of fields in a table

finding records in a table

defining groups and sets of information and perform comparisons and calculations on those groups and sets

Click a topic for more information.

Using example elements

Performing table operations with reserved words

Performing queries on groups of records

Calculating group statistics

Using sets

Using inclusive links

QBE file syntax


Exchanging data

Database Desktop can pass data to or get data from other Windows applications that support Dynamic Data Exchange (DDE).

The application that is the source of the data to be exchanged is called the DDE server. The application that receives the exchanged data is the DDE client. Database Desktop can be a DDE client, a DDE server, or both at the same time.

Click a topic for more information.

Database Desktop as a DDE server

Database Desktop as a DDE client

Database Desktop as both DDE client and server


Glossary of terms
Glossary

-A-  -B-  -C-  -D-  -E-  -F-  -G-  -I-  -K-  -L-  -O-  -P-  -Q-  -S-  -T-  -V-  -W-

   - A -

active

alias

alphanumeric field

Answer table

arithmetic operators

ascending order

asymmetrical outer join

   - B -

bind

blank field

Browser

   - C -

Check

CheckDescending

checkmark

CheckPlus

client

concatenate

   - D -

DDE

descending order

   - E -

example element

exclusive link

   - F -

field type

field value

field view

   - G -

GroupBy

   - I -

inclusion operator

inclusive link

index

   - K -

key

   - L -

link

logical operator

lookup table

   - O -

operator

outer join

   - P -

persistent field view

private directory

   - Q -

query

query-by-example

query statement

Query window

   - S -

server

set

set comparison operator

summary operator

   - T -

.TV or .TVF file

table view

Table window

   - V -

validity check

   - W -

working directory


active

describes the object or window to which the next keystroke will apply


 alias

a name you can assign as a shortcut to a directory


alphanumeric field

a field containing letters, numbers, or a combination of both. Some products call it a text or character field


Answer table

a temporary table used to store the results of queries


arithmetic operators

the  +,  -,  *,  /, and ( ) operators used to construct arithmetic expressions in queries


ascending order

a sort order: A to Z, uppercase, then a to z, lowercase, in alphanumeric fields; low to high in number fields; earliest to latest in date fields


asymmetrical outer join

a query in which an inclusive link is specified for only one of the tables involved


bind

to establish the relationship between associated tables. You bind tables by linking corresponding fields.


blank field

a field that does not contain a value


Browser

uses icons and text to identify types of files. You can use the Browser to list files in different directories.


Check

a checkmark symbol that displays all unique values for the checked field, excluding duplicates, in the Answer table. The values are displayed in A to Z (ascending) order.


CheckPlus

a checkmark symbol that shows all values in a field, including duplicates. Because sorting removes duplicates, the Answer table appears unsorted.


CheckDescending

a checkmark symbol that shows unique values sorted in Z to A (descending) order in the Answer table.


checkmark

a symbol used in query statements to indicate that a field is to be displayed in the Answer table


client

the application that starts a DDE conversation and usually receives data from the other application, called the server


concatenate

to combine two or more alphanumeric values using the + operator


DDE

Dynamic Data Exchange. A means for two or more applications to share data.


descending order

a sort order: z to a, lowercase, then Z to A, uppercase, in alphanumeric fields; high to low in numeric fields; latest to earliest in date fields


example element

in a query, a character or group of characters that represent any value or subset of values in a field


exclusive link

in a query, the use of an example element to retrieve from one table only those records which match the records in another table


field type

the definition of what kind of data a field can contain


field value

the data contained in one field of a record in a table. If there is no data present, the field is considered blank.


field view

a mode that lets you move the cursor through a field character-by-character. It is used to view field values that are too large to be displayed in the current field width or to edit a field value.


GroupBy check

in a Set query, the operator (indicated by checkmark G) used to tell Database Desktop to group records by that field without displaying its values in the Answer table


inclusion operator

the ! symbol used to include a complete set of records in Answer, whether or not they match records in another table


inclusive link

a query whose answer includes all the values in a field of one table, whether or not there are matching values in the linked field of another table


index

a file Database Desktop uses to locate records more quickly. The key field for a table establishes its primary index.


key

a field or group of fields in a Database Desktop table used to order records or ensure referential integrity. A key has three effects: The table is prevented from containing duplicate records, the records are maintained in sorted order based on the key fields, and a primary index is created for the table.


link (join)

a relationship between two or more tables defined by placing example elements in common fields


logical operator

one of three operators--AND(,), OR, and NOT--that can be used in queries


lookup table

In a validity check, a lookup table assures that a value entered in one table is a legitimate value in another table. In this way, data integrity is assured.


operator

a reserved character or characters that define an operation to be performed on a value or values, such as + for addition or * for multiplication


outer join

a type of query that uses the inclusion operator (!) to retrieve all of the records in a table, whether or not they match records in another table


persistent field view

a mode that lets you move among fields without leaving field view


private directory

where Database Desktop stores your temporary files


query

a question you ask about information in your tables. It can be a simple question about the information in a single table or a complex question about information in several tables.


query-by-example (QBE)

a non-procedural query language that lets you ask questions about data by providing examples of the answers you are looking for


query statement

one or more filled-out query forms. It determines which records are included in the result of a query.


Query window

the window you use to create a new query or open an existing one


server

the application that responds to the calling application, or client, in a DDE conversation. The server usually sends data to the client.


set

in a Database Desktop query, a specific group of records about which you intend to ask questions


set comparison operator

any one of the reserved words (ONLY, NO, EVERY, or EXACTLY) used to compare a defined set of records to other records


summary operator

any one of the operators (AVERAGE, COUNT, MAX, MIN, SUM, ALL, or UNIQUE) that answer questions about groups of records in queries


.TV or .TVF file

the file that holds table view properties--all changes you make to a table in the Table window are saved to this file. It has the same name as the table, but the file-name extension is either .TV (for a Paradox table) or .TVF (for a dBASE table).


table view

the representation of a table in the Table window, in row and column format


Table window

the window you use to view a database table or an Answer table. You also use this window to modify, insert, and delete records.


validity check

a constraint or check on the values you can enter in a field during data entry


working directory

where Database Desktop looks first for your files. Controls what files are listed in File|Open and File|Save dialog boxes.


Keyboard shortcuts

There are keyboard equivalents to most mouse operations. These keyboard commands usually have an abbreviated series of keystrokes called shortcuts. Click a topic below for more information.

Moving among records and fields in a table

Rotating columns in a table

Selecting multiple fields

Function key actions in tables

Function key actions in queries

Edit menu shortcuts

Table shortcut keys in the Table window

Keypad combinations


Moving among records and fields in a table

To move among the records of a table, you can either:

use the arrow keys, and PgUp and PgDn

use the Record menu

use the SpeedBar

To perform the operations shown in this table, make sure Num Lock is off before you press the keys on the numeric keypad.

Cursor movement keys

Combination	Non-field view	Field view
PgUp	Up one set of records	Up one set of records
Ctrl+PgUp	Left one screen	Left one screen
PgDn	Down one set of records	Down one set of records
Ctrl+PgDn	Right one screen	Right one screen
Home	Leftmost field of current record	Beginning of field
Shift+Home	Select to first field of record	Select to beginning of field
Ctrl+Home	Leftmost field of first record of table	First field of first record
Alt+Home	First field of current record	First field of current record
End	Rightmost field of record	End of field
Shift+End	Select to last field of current record	Select to end of field
Ctrl+End	Rightmost field of last record of table	Last field of last record
Alt+End	Last field of record	Last field of record
Left Arrow	Left one field	Left one character
Shift+Left Arrow	Select left one field	Select left one character within field
Ctrl+Left Arrow	(No effect)	Left one word
Alt+Left Arrow	Left one field	Left one field
Shift+Ctrl+Left Arrow	Select to leftmost field of current record	Select left one word
Right Arrow	Right one field	Right one character
Shift+Right Arrow	Select right one field	Select right one character within field
Ctrl+Right Arrow	(No effect)	Right one word
Alt+Right Arrow	Right one field	Right one field
Up Arrow	Up one line within field	Up one line
Shift+Up Arrow	Select up one line within field	Select up one line within field
Alt+Up Arrow	Up one field	Up one field
Ctrl+Up Arrow	Same field in first record	Same field in first record
Down Arrow	Down one line within field	Down one line within field
Shift+Down Arrow	Select down one line within field	Select down one line within field
Alt+Down Arrow	Down one field	Down one field
Editing keys

(only Backspace differs in the two views)

Combination	Non-field view	Field view
Backspace	Delete highlighted text (in Edit mode)	Delete character to left
Ctrl+Backspace	Delete highlighted text or word to left (in Edit mode)
Alt+Backspace	Undo record edit
Ins	Insert record (in Edit mode)
Ctrl+V	Paste (in Edit mode)
Ctrl+C	Copy
Del	Delete selected text (in Edit mode)
Ctrl+X	Cut (in Edit mode)
Ctrl+Del	Delete current record (in Edit mode)
Esc	Undo field edit
Tab	Commit value & move to next field
Shift+Tab	Commit value & move to previous field
Enter	Commit value & move to next field


Rotating columns in a table

To rotate the order of columns in a table with the keyboard,

1.	Select the column you want to move.

2.	Press Ctrl+R. This moves the selected column to the last place on the right of the table.


Selecting multiple fields

You can select multiple fields across rows and columns, simply by drawing a box around the ones you want. Fields selected this way must be adjoining.

To select a group of fields using the keyboard,

1.	Select the field where you want to begin (don't enter field view).

2.	Hold down Shift while using the arrow keys to place a box around the fields you want.

To select all fields in the table, choose Edit|Select All. A box surrounds the table.


Function key actions in tables

This table shows what function keys do when you're working with tables:

Key	Action

F1	Help

F2	field view

Ctrl+F2	persistent field view

F3	super backtab

F4	super tab

F5	lock record

Shift+F5	commit record

Ctrl+F5	post/keep locked

F6	lookup help

F9	enter/exit Edit mode

F10	menu

F11	previous record

Shift+F11	previous set

Ctrl+F11	first record

F12	next record

Shift+F12	next set

Ctrl+F12	last record


Function key actions in queries

This table shows what function keys do when you're creating a query:

Key	Action

F1	Help

F2	field view

Ctrl+F2	persistent field view

F3	up image

F4	down image

F5	make example element

F6	toggle checkmark

Shift+F6	cycle through [image], [image], [image], [image],

F8	run query

F10	menu

F11	move up one line in query form

F12	move down one line in query form


Edit menu shortcuts

This table shows the shortcut keys for the Edit menu commands.

Key combination	Menu command

Alt+Backspace	Edit|Undo

Ctrl+X	Edit|Cut

Ctrl+C	Edit|Copy

Ctrl+V	Edit|Paste

Del	Edit|Delete


Table shortcut keys in Table window
Table shortcut keys in the Table window

This table shows shortcut keys in the Table window:

Key combination	Action

Ctrl+D	ditto (repeats value in field above)

Ctrl+F	field view

Ctrl+L	lock record

Shift+Ctrl+L	commit record

Ctrl+R	rotate columns

Ctrl+Spacebar	lookup

Shift+Ctrl+Spacebar	move help


Keypad combinations

This table shows the operations Database Desktop performs when you press the keys on the numeric keypad. Make sure Num Lock is off whenever you use any keypad key for navigation.

Cursor movement keys

Combination	Non-field view	Field view
PgUp	Up one set of records	Up one set of records
Ctrl+PgUp	Left one screen	Left one screen
PgDn	Down one set of records	Down one set of records
Ctrl+PgDn	Right one screen	Right one screen
Home	Leftmost field of current record	Beginning of field
Shift+Home	Select to first field of record	Select to beginning of field
Ctrl+Home	Leftmost field of first record of table	First field of first record
Alt+Home	First field of current record	First field of current record
End	Rightmost field of record	End of field
Shift+End	Select to last field of current record	Select to end of field
Ctrl+End	Rightmost field of last record of table	Last field of last record
Alt+End	Last field of record	Last field of record
Left Arrow	Left one field	Left one character
Shift+Left Arrow	Select left one field	Select left one character within field
Ctrl+Left Arrow	(No effect)	Left one word
Alt+Left Arrow	Left one field	Left one field
Shift+Ctrl+Left Arrow	Select to leftmost field of current record	Select left one word
Right Arrow	Right one field	Right one character
Shift+Right Arrow	Select right one field	Select right one character within field
Ctrl+Right Arrow	(No effect)	Right one word
Alt+Right Arrow	Right one field	Right one field
Up Arrow	Up one line within field	Up one line
Shift+Up Arrow	Select up one line within field	Select up one line within field
Alt+Up Arrow	Up one field	Up one field
Ctrl+Up Arrow	Same field in first record	Same field in first record
Down Arrow	Down one line within field	Down one line within field
Shift+Down Arrow	Select down one field	Select down one line within field
Alt+Down Arrow	Down one field	Down one field
Editing keys

Ins	Insert record (in Edit mode)	Insert record (in Edit mode)
Ctrl+V	Paste (in Edit mode)	Paste (in Edit mode)
Ctrl+C	Copy	Copy
Del	Delete selected text (in Edit mode)	Delete selected text (in Edit mode)
Ctrl+X	Cut (in Edit mode)	Cut (in Edit mode)
Ctrl+Del	Delete current record (in Edit mode)	Delete current record (in Edit mode)
Backspace	Delete selected text	Delete character to left
Ctrl+Backspace	Delete highlighted text or word to left (in Edit mode)	Delete highlighted text or word to left (in Edit mode)
Alt+Backspace	Undo record edit	Undo record edit
Esc	Undo field edit	Undo field edit
Tab	Commit value & move to next field	Commit value & move to next field
Shift+Tab	Commit value & move to previous field	Commit value & move to previous field
Enter	Commit value & move to next field	Commit value & move to next field


File menu overview
File menu

Use the File menu to open queries and tables, save queries, assign working and private directories, and assign aliases. You also use this menu to exit Database Desktop.

The File menu is available in both the Query window and the Table window.

Here are the File menu commands:

New Query

Open

Save

Save As

Working Directory

Private Directory

Aliases

Exit


File | New Query

Choose File|New Query to create a new query. An empty Query window is displayed. You also see the Select File dialog box, where you select the name of the table you want to add to the Query window.


File | Open

File|Open displays another menu of commands. You use these commands to open a query or table.

File|Open|Query displays the Select File dialog box, where you open a query file.

File|Open|Table displays the Open a Table dialog box, where you open a table file.


Select File dialog box

You use the Select File dialog box to select and open the query you want. Display this dialog box with:

File|New Query

File|Open|Query

Query|Add Table

To select contiguous files in a list, you can drag the mouse or hold down Ctrl as you click to select non-contiguous files.

File Name

The name of the query you want to open. If the file is in the current directory, you can choose the name from the list.

Path

The full path for the selected alias.

Type

The type of file you can open.

Browse

Displays the Browser, where you can choose other files, paths, and file types to list.

See Also

Aliases

Opening files

Queries

Types of files


Open a Table dialog box

You use the Open a Table dialog box to open a table. Display this dialog box with File|Open|Table. To select contiguous files in a list, you can drag the mouse or hold down Ctrl as you click to select non-contiguous files.

File Name

The name of the table you want to open. If the file is in the current directory, you can choose the name from the Files list.

Path

Shows the full path for the selected alias.

Type

Shows the type of file you can open.

Browse

Displays the Browser, where you can choose other files, paths, and file types to list.

See Also

Opening files

Types of files


Browser

You use the Browser to access files in different directories. To get to this dialog box, press the Browser button.

Aliases

Chooses an alias or a directory. The lower left half of the Browser reflects the directory of the alias you've chosen. The lower right half of the Browser displays the contents of that directory.

Type

Shows the type of file you're browsing for.

Note: This option is only available if you are searching for a file and not a directory.

Filters

Shows the file extension of the type of file you're browsing for. Although the Type list gives you a certain degree of filtering, it doesn't distinguish between Paradox and dBASE files. Choose *.db for Paradox tables or *.dbf for dBASE tables.

Note: This option is only available if you are searching for a file and not a directory.

See Also

Aliases

Types of files


File | Save

Use File|Save periodically to save the active query to disk. The query must be syntactically correct before it can be saved.

The first time you choose File|Save, the Save File As dialog box appears, where you can save the file name and path.

Once you've named the file, Database Desktop does not prompt you for a file name.

Note:	File|Save is unavailable in the Table window because:

Database Desktop automatically saves the data you enter as soon as you leave each record.

You save a table's properties by choosing Properties|Save from the Table window.

See Also

Saving files

Types of files


File | Save As

File|Save As saves your changed query in a new file, without overwriting the original file. The query must be syntactically correct before it can be saved.

This dialog box displays when you choose:

File|Save for the first time

File|Save As to save a file under another name

the Save As button in the Alias Manager dialog box

It also opens when you try to close a file or exit Database Desktop without saving.

Dialog Box Options

Existing File Names

Lists the existing files in the selected path of the specified type.

New File Name

Enter a new file name or select one from the Existing File Names list and edit it. To save a file in another directory, either:

type the full path in New File Name

use the Path box to choose a different aliased directory

press Browse to choose an unaliased directory

Path

Shows the full path for the selected alias.

Type

Shows the type of file you can save.

Browse

Displays the Browser, where you can choose other files, paths, and file types to list.

Note:	File|Save As is unavailable in the Table window because:

Database Desktop automatically saves the data you enter as soon as you leave each record.

You save a table's properties by choosing Properties|Save from the Table window.

See Also

Aliases

Saving files

Types of files


File | Working Directory

File|Working Directory changes your working directory.

Dialog Box Options

Working Directory

Location (the full path) of your working directory.

Browse

Displays the Browser, where you select another directory.

Aliases

Displays an alias for the working directory.

See Also

Aliases

Setting up a working directory


File | Private Directory

Choose File|Private Directory to identify a directory to use as your private directory.

The private directory stores any temporary tables you create. This avoids conflicts with any other network user's temporary tables. Every person who uses Database Desktop on a network must have a private directory.

If you don't specify a private directory, Database Desktop creates one on your local hard disk. If you have no local hard disk, the network home directory on the file server should be used as the private directory.

The contents of your private directory are displayed in any Open or Save As type of dialog box. You can choose :PRIV: (the alias for your private directory) from any Path list in a dialog box or the Browser.

Dialog Box Options

Private Directory

Location (the full path) of your private directory.

Browse

Displays the Browser, where you select another directory.

See Also

Setting up a private directory


File | Aliases

Choose File|Aliases to view, change, or add aliases. Aliases let you give logical names to directories.

Creating aliases lets you give logical names to directories and is strongly encouraged, since it frees you from absolute path names, making your files more portable.

The settings for this dialog box reflect the information stored in your ODAPI.CFG file.

Dialog Box Options

Database Alias

Lists alias names.

Driver Type

Lists the driver type. STANDARD is the default driver type.

Path

Shows the full path for the selected alias.

New

Clears the Database Alias field so you can enter a new alias.

Remove

Deletes the selected alias from the list. When you're adding a new alias to the list, Remove works like Cancel and lets you exit without keeping the new alias.

Save As

Displays the Save File As dialog box, where you save alias changes to ODAPI.CFG.

OK

Saves any changes you've made for the current session. All Windows applications currently running are affected by any changes.

Cancel

Cancels the changes you've entered in input fields.

SQL Connections

If you have SQL drivers installed on your machine, you will see the following items on the Alias Manager dialog box when you choose a SQL based alias.

Server Name

Type the full path of the database, including the name of the server.

User Name

Type the name of the user recognized by the database server.

Net Protocol

Type the name of the network transport protocol recognized by the server.

Open Mode

The Database Desktop displays the type of file access mode available to you.

Schema Cache

Type the number of closed tables for which you want to cache metadata. The minimum setting is 0 (no caching of metadata) and the maximum setting is 32 tables. The default setting is 8 tables.

Langdriver

Enter the Language Driver to control the data on your server.

Password

Type the password needed to connect to the server. Asterisks (*) represent the characters you type.

Connect

Choose Connect to establish a connection to the server listed above.

Disconnect

Choose Disconnect to break the connection to the server listed above.

See Also

Aliases

Creating an alias

Changing an alias definition

Removing an alias

SQL drivers


File | Exit

Choose File|Exit to leave Database Desktop and close the application.

If you have a Query window open that has been changed but not saved, Database Desktop displays a dialog box asking if you want to save it. Choose:

Yes to save the file.

No to abandon the file and leave without saving it.

Cancel to close the dialog box and go back to what you were doing in Database Desktop.


Enter Password dialog box
Enter Password(s) dialog box

The Enter Password(s) dialog box appears when you attempt to load or run a query on a table that has been password protected. You must enter the password to open the table or run the query.

The password you enter is then added to Database Desktop's password list. The password list contains all passwords that have been entered in the current Database Desktop session. Once you enter a specific password, you can gain access to any table that recognizes that password until you exit Database Desktop. To disallow passwords you have entered, you must exit Database Desktop.

The Add, Remove, and Remove All buttons are intended for users who work with large numbers of passwords:

Add

Adds a password to the password list. When you press Add, the dialog box remains on screen so you can enter an additional password for a table that you intend to open later in the session. Press Add again to add another password.

Remove

Removes the password from the password list. This is useful when you want to open a new table but have exceeded the maximum number of passwords per session (if this occurs, you will be notified with an error message).

Remove All

Removes all passwords entered in the password list.


Database Information Dialog Box
SQL Information Dialog Box

Use the Database Information Dialog box to view or modify the connection parameters you set for accessing remote servers. You need to modify these parameters when

You connect to a server for the first time in a session

You change connections to access data in a different location.

The Database Desktop displays the parameter settings you entered in the Alias Manager dialog box. In most cases, all you will need to add or modify is the user name and password.

Dialog Box Options

Database Alias

The Database Desktop displays the alias name you entered in the Alias Manager dialog box.

Server Name

Type the full path of the database, including the name of the server.

User Name

Type the name of the user recognized by the database server.

Net Protocol

The Database Desktop displays the name of the network transport protocol recognized by the server.

Open Mode

The Database Desktop displays the type of file access mode available to you.

Schema Cache

Type the number of closed tables for which you want to cache metadata. The minimum setting is 0 (no caching of metadata) and the maximum setting is 32 tables. The default setting is 8 tables.

Password

Type the password needed to connect to the server. Asterisks (*) represent the characters you type.


Edit menu overview
Edit menu

The Edit menu provides commands to cut, copy, and paste text, paste DDE links, and select fields in tables. The Edit menu is available in both the Query window and the Table window.

These Edit menu commands are available in both windows:

Cut

Copy

Paste

Paste Link

Delete

These commands are only available in the Table window:

Undo

Select All


Edit | Undo

Edit|Undo inserts any characters you deleted, deletes any characters you inserted, replaces any characters you overwrote, and moves the cursor back to a prior position.

In Edit mode, edits are posted (saved in the table) as soon as you move off of a record. To discard any edits and restore the original record, choose Edit|Undo before moving off of the record. To discard changes to a single field value and restore the original field entry, press Esc before you move off of the field.

Note:	Because Database Desktop saves data immediately when it's entered, Undo doesn't undo conventional data entry.


Edit | Cut

Edit|Cut removes the selected text and places it in the Clipboard. You can then choose Edit|Paste to paste the cut text into another file, or somewhere else in the same file.

The cut text remains in the Clipboard, so you can paste it as many times as you want.

To cut selected text without affecting the Clipboard contents, press Del.


Edit | Copy

Choose Edit|Copy to copy the selected text to the Clipboard and leave the text intact in the file.

To paste the copied text into any other document, choose Edit|Paste.

The copied text remains in the Clipboard. You can paste it as many times as you want, until you cut or copy something else.


Edit | Paste

Edit|Paste inserts information previously cut or copied to the Clipboard.

Edit|Paste inserts the contents of the Clipboard into the current window at the cursor position.

The information remains in the Clipboard. You can paste it as many times as you want, until you cut or copy something else.


Edit | Paste Link

Use Edit|Paste Link when you're using Dynamic Data Exchange (DDE). When you use DDE, you can maintain a DDE link from your application to your query. Database Desktop uses DDE to monitor changes you make to application, then it updates the copy in your query.

Paste Link is the fastest way to create a DDE link. You must be running both the client and the server to do this.

To create the link

1.	Highlight the item in the server, then copy it to the Clipboard. Most servers use Edit|Copy to place a copy of the object on the Clipboard.

2.	Return to Database Desktop (the client).

3.	In the query form, select the field to receive its value from the server.

4.	Choose Edit|Paste Link.

5.	To rerun the query every time the value in the server changes, choose Query|Wait for DDE.

Note:	If you're creating and linking two applications with Paste Link, the server application must be saved before it can be linked. If you try to link an unsaved (Untitled) server document, Database Desktop doesn't have a document name and can't create the link.

Paste and Paste Link compared

Paste and Paste Link are different:

Choose	When you want

Edit|Paste	a copy of the data that behaves independently of its original

Edit|Paste Link	a DDE link to the data, so that changes made to the source are automatically made to the query


Edit | Delete

Choose Edit|Delete to delete all or part of a field without affecting the Clipboard contents, if any.

To remove a single field entry in Edit mode, select a field and then choose Edit|Delete. If multiple fields are selected, the command is unavailable. If field view is also active, you can select specific text, then choose Edit|Delete to remove it.

You can also select an entire record and clear it (remove data and empty the record) with Edit|Delete. Choose Record|Delete to remove the entire record (both data and space).

You can use Edit|Delete only on records (rows) or single field entries of the table. You can't delete a field (column) from a table. To work with only specific fields in a table, you can create a query and use the Answer table that results.


Edit | Select All

Choose Select All to select all fields of a table. Database Desktop surrounds the whole table with a box.

You can then choose Edit|Copy to copy the values to the Clipboard.


Window menu overview
Window menu

Use the Window menu to manipulate windows, arrange icons, and close windows. The Window menu is available in both the Query window and the Table window.

Here are the Window menu commands:

Tile

Cascade

Arrange Icons

Close All


Window | Tile

Choose Window|Tile to fit all open windows in the Database Desktop application window without overlapping.

The titles of all open windows are displayed on the Window menu. Choose a title to activate the window.

See Also

Window|Cascade


Window | Cascade

Choose Window|Cascade to overlap all open windows in the Database Desktop application window so only the title bars of inactive windows show.

The titles of all open windows are displayed on the Window menu. When you choose a title to activate the window, it moves to the top of the stack.

See Also

Window|Tile


Window | Arrange Icons

Choose Window|Arrange Icons to evenly arrange the icons in the Database Desktop application window.

Windows arranges the icons across the bottom of the application window in a straight line, maintaining the same order it found them in, left to right.


Window | Close All

Choose Window|Close All to close all open Database Desktop windows. Database Desktop prompts you to save any changes before closing each window.


Window title list

The titles of all open windows are displayed on the Window menu. Choose a title to activate the window.


Help menu overview
Help menu

Use the Help menu to get online Help for Database Desktop. The Help menu is available in both the Query window and the Table window.

Contents	Lists available Database Desktop Help topics; also accessible with the Contents button in the Help window.

SpeedBar	Displays pictures of Database Desktop SpeedBars with clickable hotspots for each SpeedBar button.

Keyboard	Lists keyboard shortcuts.

About...	Gives the copyright date and version number.


Query menu overview
Query menu

Use the Query menu to specify restart options, add and remove tables, switch to field view, run the query, and run the query every time the DDE value changes. The Query menu is only available in the Query window.

Here are the Query menu commands:

Restart Options

Add Table

Remove Table

Field View

Run

Wait for DDE

See Also

Using query-by-example (QBE)

Using advanced query-by-example (QBE)


Query | Restart Options

Query|Restart Options controls how Database Desktop behaves in a multiuser environment, when someone else changes data while you're running a query.

Dialog Box Options

Restart Query on Changes

Starts the query over if someone else changes data.

Lock All Tables to Prevent Changes

Locks all other users out of the tables needed while the query is running. If a lock cannot be put on a table, the query is stopped.

Ignore Source Changes

Continues running the query even if someone changes the data while it's running. This is the fastest option and works well if up-to-the-minute accuracy is not of primary importance.


Query | Add Table

Use Query|Add Table to add a table to the active Query window. You see the Select File dialog box, where you select the tables you want to add to the Query window.

See Also

Creating a query


Query | Remove Table

Use Query|Remove Table to remove a table from the Query window. Select the table you want to remove and click OK.

See Also

Creating a query


Query | Field View

Use Query|Field View to toggle in and out of field view.

When you're in field view, the insertion point blinks on and off, and you can edit query statements by typing, backspacing, and deleting characters.

You enter field view by:

pressing the Field View button on the SpeedBar

choosing Query|Field View

pressing F2

clicking in the field

To leave field view: either press F2, click another field, or click the Field View button on the SpeedBar.

See Also

Editing query statements


Query | Run

Choose Query|Run to run the query. A status window tells you the status of the query. If there is a problem with the query, Database Desktop displays an error message, prompting you to correct it. If additional information about the error is available, you can click >> or << to see it.

If there are no problems with the query, you'll see the message Query status after Database Desktop checks the query. Depending on the complexity of the query and the capabilities of your system, Database Desktop can often produce an Answer table in just a few seconds.

See Also

Running a query


Query | Wait for DDE

Query|Wait for DDE reruns the query every time the DDE value changes. If you don't choose this menu option, you have to explicitly tell Database Desktop when to run the query. It will use the current DDE value.

See Also

Exchanging data


Properties menu overview
Properties menu

Use the Properties menu to create and manipulate the Answer table. The Properties menu is available in both the Query window and the Table window.

Here are the Properties menu commands available in the Query window:

Answer Table

Sort Answer

Tile Tables

Cascade Tables

These commands are available in the Table window:

Save

Restore

Delete


Properties | Answer Table

Use Properties|Answer Table to display the Answer Table dialog box, where you rename the Answer table and specify its type.

By default, the Answer table is overwritten when the next query is run. When you rename the Answer table, it is saved until the same query is rerun.

Dialog Box Options

Answer Table

The new name of the Answer table. Enter the full path name.

Answer Table Type

Choose Paradox to create the Answer table in Paradox format. Choose dBASE to create the table in dBASE format.

See Also

Answer table properties


Properties | Sort Answer

Choose Properties|Sort Answer to sort the Answer table before you run a query. This overrides the default sort order

When you choose OK, you tell Database Desktop to sort the Answer table according to the Sort By list. The next query you run will use this new sort order.

Dialog Box Options

Available Fields

Shows the fields you can sort on.

Left-Facing Arrow button

Moves the selected field from the Available Fields list to the Sort By list. Add the fields in the order in which you want the Answer table sorted.

Right-Facing Arrow button

Removes the selected field from the Sort By list.

Sort By

Shows the fields the Answer table will sort on. Fields are listed in sort order.

Up and Down Arrow buttons

Move the selected field up or down in the Sort By list, changing the order of sort fields.

Note:	If the Sort Answer dialog box doesn't appear after you choose Properties|Sort Answer, check that the query is syntactically correct.

See Also

Creating a query

Sorting the Answer table


Properties | Tile Tables

Choose Properties|Tile Tables to tile multiple tables in the Query window. The tables in the Query window appear one below the other (the default setting).


Properties|Cascase Tables
Properties | Cascade Tables

Choose  Properties|Cascade Tables to cascade multiple tables across the Query window. The tables in the Query window appear as individual, stacked windows.


Properties | Save

Properties|Save saves the properties of the Answer table to a file with the same name as the table and .TV (for Paradox) or .TVF (for dBASE) file-name extension. If the query is untitled, the properties file is called ANSWER.

The properties saved are column arrangement and width, row height, scroll locks, and position of the table title.

Note:	If you change one or more table properties -- for example, column width -- and close the table without using Properties|Save, Database Desktop displays a save prompt. Choose No to close the table without saving properties, Yes to save the properties, or Cancel to leave the table open.

See Also

Answer table properties

Customizing a table view


Properties | Restore

Use Properties|Restore to erase any unsaved changes you've made to a table view and restore the last-saved view. Database Desktop reinstates the table view properties stored in the associated .TV or .TVF file. If there is no table view properties file, the default view of the table is reinstated.

See Also

Answer table properties

Customizing a table view


Properties | Delete

If you've changed and saved the view of the active table, but now want to return to the default view, choose Properties|Delete. Properties|Delete deletes the .TV or .TVF file associated with the active table.

If, after you run a query, the resulting Answer table looks different than expected (for example, if the columns are in a different order than they should be), try choosing Properties|Delete, and then run the query again. There may be an old ANSWER.TV or ANSWER.TVF file that Database Desktop is applying to your new Answer table.

See Also

Answer table properties

Customizing a table view


Table menu overview
Table menu

Use the Table menu to edit data in tables, disconnect DDE links, and limit the characters that Database Desktop accepts.

The Table menu is available in the Table window. Here are the Table menu commands:

Field View

Edit Data

End Data Entry

Notify On

Strict Translation


Table | Field View

Use Table|Field View to change only part of a field's entry in Edit mode, rather than replace the whole entry.

When Field View is checked, your typing is inserted at the cursor and does not overwrite the rest of the field.

Choose Table|Field View again to turn off Field View.

Persistent Field View

Ctrl+F2 gets you into Persistent Field View, where you can move from field to field without leaving Field View.

In Persistent Field View, press Tab, Enter, or Alt plus an arrow key to move from field to field. Press arrow keys to move character-by-character within a field.

Press Ctrl+F2 again to leave Persistent Field View and return to Table View.

See Also

Field view


Table | Edit Data

Choose Table|Edit Data to enter Edit mode, and enter or edit data in a table.

In Edit mode, you can select any field and begin typing to replace the existing entry. When you enter Edit mode, the Edit Data button remains pressed in and the status line tells you Edit mode is active. To position the insertion point within the entry so you can change a spelling or typing error, use field view.

The record is locked as soon as you change a character in a field. This prevents other users who might be viewing the table at the same time from changing it until you're finished.

In Edit mode, changes are saved automatically every time you move to another record.

Choose Table|End Data Entry or press F9 to end Edit mode and return to table view.

See Also

Moving among records and fields in a table


Table | End Data Entry

Choose Table|End Data Entry to end data entry and return to table view.


Table | Notify On

After a DDE link is pasted into a notebook, the Table|Notify On command is activated. While this command is active, it's checked in the Table menu, and the link is "live." For example, when you select another record in the linked table (in Database Desktop), the new value is delivered to your application.

To disconnect a link, uncheck Table|Notify On in Database Desktop. While this command is inactive, no changes are delivered to your application. To reconnect the link at any time, choose Table|Notify On.

When you create a DDE link to an entire table, Table|Notify On works similarly. When any record in the linked table changes, the entire table is refreshed in the notebook. Changes are posted in the table whenever the person editing the table moves off of the record.

See Also

Using DDE links

Edit|Paste Link


Table | Strict Translation

By default, Database Desktop uses the ANSI character set to display characters onscreen. Paradox for DOS uses the OEM character set. Choose Table|Strict Translation to limit the characters used by Database Desktop to those in both the OEM and ANSI character sets.

Otherwise, if you try to view a table that contains OEM characters that don't exist in the ANSI set, or if you type ANSI characters into a table created with the OEM set, some characters may not appear as expected.

With Strict Translation on, when you type a character outside of the OEM character set, Database Desktop considers it an error, and won't let you leave the field. Additionally, when you start to edit a field, Database Desktop warns you if the field contains OEM characters.

See Also

Limiting character sets


Record menu overview
Record menu

Use commands on the Record menu to quickly find records in a table, as well as to insert, delete, or lock them. The Record menu is only available in the Table window.

Here are the Record menu commands. Insert, Delete, Lock, and Post/Keep Locked are only available when you're editing data.

First	goes to the first record

Last	goes to the last record

Next	goes to the next record

Previous	goes to the previous record

Next Set	goes to the next set of records

Previous Set	goes to the previous set of records

Insert	inserts a blank record above the selected record

Delete	deletes the selected record

Lock	lets you lock a record you're editing, then unlock it when you're through

Cancel Changes	undoes the last change to a table entry while in Edit mode

Post/Keep Locked	lets you hold a lock on a record even after you've posted (saved) its value. Use this when you want to keep editing a record after you post it.

Lookup Help	shows the table containing valid entries for this field

Move Help	lets you move a detail record to a new master record in either a 1M form or a referential integrity relationship

See Also

Locking records

Inserting and deleting records

Using edit mode


Move Help dialog box

Use the Move Help dialog box to move a detail table to a new master record.

Select the new master record from the master table in the Move Help dialog box and click OK.  The detail record you select is now assigned to the new master record.

Move Help is only available in fields for which a one [image] many relationship or a referential integrity is defined.


Types of files

In Database Desktop you work with two types of files: tables and queries. There are a few other types of files, such as indexes, but you don't need to know about these to work with Database Desktop.

Here are the extensions of all files used by Database Desktop:

Extension	Type of file

.CFG	Configuration files, used to store aliases and system configuration settings

.DB	Paradox table

.DBF	dBASE table

.DBT	Memo field values for a dBASE table

.INI	Preference files, used to store working directory settings

.MB	Memo field values for a Paradox table

.MDX	Maintained index of a dBASE table. Indexes determine the order in which Database Desktop accesses the records in a table. Indexes organize records so that data can be found more quickly. When a dBASE table is indexed, Database Desktop creates a file that contains the indexed field's values and their corresponding record numbers. Database Desktop refers to this index file when locating and displaying the records in a table.

.PX	Primary index of a Paradox table; similar to the index of a dBASE table (see preceding item). Paradox organizes the records of a keyed table according to the values in the key field(s). This is its primary index. By default, all indexes organize and access data in ascending order (A to Z, or 0 to 9).

.QBE	Saved query

.TV	Table view settings for a Paradox table

.TVF	Table view settings for a dBASE table

.VAL	Validity checks and referential integrity for a Paradox table

.Xnn	Secondary index for a Paradox table, numbered. A secondary index defines an alternate view order to temporarily change the display order of the records.

.Ynn	Secondary index for a Paradox table, numbered


 Opening files

To open a file,

1.	Choose File|Open.

2.	Choose the type of file to open--query or table.

3.	You see the Select File or Open a Table dialog box where you specify the file to open. These dialog boxes both have a Path and Type list.

Note:	To access tables stored on a network, you must tell Database Desktop the location of the network control file. See the Help Topic Database Configuration for details.

The Path list

The Path list indicates which directory Database Desktop displays. If you've given a directory path a name (an alias), the Path list displays that name, rather than the full path. By default, Database Desktop looks in your working directory, which is assigned the alias :WORK.

The Type list

The Type list shows which type of file is listed--table or query.

Tables with passwords

Paradox tables can be protected with a password. When you first try to access a password-protected table in Database Desktop, the Enter Password(s) dialog box appears. To access a password-protected table, type the password and choose OK.

During a session, the password for a table need only be entered once because Database Desktop maintains a password list. When you exit Database Desktop, the password list is erased. You can use the Enter Password(s) dialog box to manage the password list by:

entering multiple passwords. After each password you type, choose the Add button. This lets you enter passwords for several password-protected tables that you plan to use in a session.

deleting one or more passwords. After each password you type, choose the Remove button. This lets you delete passwords from the password list, and is useful when you exceed the maximum number of passwords.

deleting every password. Choose the Remove All button to delete the password list for the current session.

Note:	While a password-protected table is open or in use by a query, deleting its password from the password list has no effect until you close the table or query. The next time you try to access the table, however, the Enter Password(s) dialog box will appear.

See Also

Types of files

Working in the application window


Saving files

To save a query, use File|Save or File|Save As. Database Desktop gives query files a .QBE extension.

Don't use the Save or Save As commands to save table data. Database Desktop automatically saves data when you do anything that unlocks the record (such as moving off of the record, turning off Edit mode, or choosing Record|Unlock).

To save a table's property settings (column width, arrangement, scroll locks, and row height), choose Properties|Save from the Table window. Properties are saved to a file with the same name as the table and a .TV (for Paradox) or .TVF (for dBASE) file-name extension. If the query is untitled, the properties file is called ANSWER.

Note:	If you change a table's properties and don't save them, Database Desktop prompts you to save them when you close the table.

See Also

Types of files

Working in the application window


Setting up a working directory

The working directory is where Database Desktop looks first for your files. The working directory setting controls which files are listed in the Select File, Open a Table, and Save File As dialog boxes.

To specify a working directory, choose File|Working Directory.

Files stored in your working directory are preceded by :WORK. in the Select File, Open a Table, and Save File As dialog boxes. When you specify a directory as your working directory, Database Desktop creates a file called DBDWORK.INI and stores it in that directory. This file contains the last-saved Database Desktop workspace.

Note:	You should not directly edit DBDWORK.INI. Database Desktop saves changes you make when you change working directories or exit Database Desktop. If you delete DBDWORK.INI, Database Desktop uses default application settings.

See Also

Working in the application window


Setting up a private directory

If you're working in a multiuser environment, you need to store your temporary tables, such as Answer, in a nonshared directory. Every person who uses Database Desktop on a network must have a private directory.

Choose File|Private Directory to establish a private directory.

Files stored in your private directory are listed in the Select File, Open a Table, and Save File As dialog boxes, preceded by :PRIV. Private directory files are visible and available to you, but not to other network users.

Note:	If you don't specify a private directory, Database Desktop creates one on your local hard disk. If you have no local hard disk, use the network home directory on the file server as the private directory.

See Also

Working in the application window


Aliases

An external database is a collection of files. These files can be kept in a directory on your local hard disk or on a network server. You can assign a name (an alias) as a shortcut to a directory using the Alias Manager dialog box.

Aliases give you several advantages:

You avoid typing long path names.

You can access a database from any directory without changing directories.

For example, if you have a collection of tables and queries in one directory, you can specify the alias :MYWORK: rather than type the entire path.

See Also

Creating an alias

Changing an alias definition

Removing an alias


Creating an alias

To create an alias,

1.	Choose File|Aliases.

2.	Choose the New button in the dialog box.

3.	In the Database Alias edit field, type the name (alias) you want to give the directory.

4.	Leave STANDARD as the driver type. This lets you create an alias for Paradox and dBASE tables.

5.	Enter the full path to the directory in the Path edit field.

6.	Choose Keep New. A message in the dialog box tells you that the alias has been added to the session.

7.	To make this a permanent alias--usable any time you use Database Desktop--choose Save As and save the alias to the file ODAPI.CFG. (Otherwise, the alias will exist only until you exit Database Desktop.)

8.	Choose OK to exit the Alias Manager dialog box.

Tip:	To create an alias with similar characteristics to one you already have, choose New to open up a space in the Alias list, then select the alias to copy from the list. Copy it and make the necessary changes to the resulting display. When you choose Keep New, you save the new alias without affecting the one you copied from.

See Also

Aliases

Changing an alias definition

Removing an alias

Working in the application window


Changing an alias definition

To change an alias definition,

1.	Choose File|Aliases.

2.	Specify the name of the alias to change in the Database Alias edit field (you can choose it from the list).

3.	Edit the path.

4.	If you want to make this change permanent, choose Save As and save the new definition to ODAPI.CFG.

5.	Choose OK.

Note:	Database Desktop uses :WORK: as the alias for the working directory. If you change the working directory to a directory that already has an alias, Database Desktop replaces that directory's alias with :WORK: for that session.

See Also

Aliases

Creating an alias

Removing an alias

Working in the application window


Removing an alias

To remove an alias,

1.	Choose File|Aliases.

2.	Specify the alias to remove.

3.	Choose the Remove button.

4.	Choose OK.

See Also

Aliases

Creating an alias

Changing an alias definition

Working in the application window


Database Desktop as a DDE server

To use Database Desktop as a DDE server, you select and copy values to the Clipboard, then paste a link to another application. You can link a single field value or an entire table.

Linking a single field value

To link a single field value to your application,

1.	In a Table window, select one value in any field.

2.	Click the Copy button in the SpeedBar or choose Edit|Copy.

3.	Activate the client application and choose where to paste the link.

4.	In your application, choose the command to insert the link (usually Edit|Paste Link).

Disconnecting a link

After a DDE link is pasted into an application, the Table|Notify On command is activated and the link is "live." When you select another record in the linked table (in Database Desktop), the new value is delivered to the application.

To disconnect the link, uncheck Table|Notify On in Database Desktop. While this command is inactive, no changes are delivered to the application. To reconnect the link at any time, choose Table|Notify On.

If you create a DDE link to an entire table, Table|Notify On works similarly. When any record in the linked table changes, the entire table is refreshed in the application. Changes are posted whenever you move off of the record.

Linking an entire table

To link a entire table to your application,

1.	In a Table window, select all values in the table (choose Edit|Select All). To manually select the entire table, be sure to include the leftmost column that contains the record numbers.

2.	Click the Copy button in the SpeedBar or choose Edit|Copy.

3.	Activate the client application and choose where to paste the link.

4.	In your application, choose the command to insert the link (usually Edit|Paste Link).

After the link is created, the entire table--including column names--appears in your application. Whenever values in the linked table are changed, the link delivers the new values to the application.

Note:	The leftmost column in the Answer table (the record number column) is linked by default.

See Also

Exchanging data


Database Desktop as a DDE client

To use Database Desktop as a client, you paste a link from your application to a query.

1.	Select your application so it's the active application.

2.	Select the value you want to link to a field in the Database Desktop table.

3.	Use your application's Copy command.

4.	Select Database Desktop so it's the active application.

5.	Activate the query image you want to paste the DDE link into.

6.	Select a field in the query image.

7.	Choose Edit|Paste Link.

8.	To establish the link, choose Query|Wait for DDE.

To view the value in its source application, select the field and press Shift+F2. A message displays to let you know the DDE server is being launched.

See Also

Exchanging data


Database Desktop as both DDE client and server

When Database Desktop is used as both a DDE client and server, all database query actions can be performed in your application. To use Database Desktop as both client and a server, follow these instructions:

1.	Copy a value from your application to the Clipboard.

2.	Paste a DDE link from the application value to a query.

3.	Choose Query|Wait for DDE.

4.	Run the query by entering a value in the DDE-linked value.

5.	Choose Edit|Select All in Database Desktop to select the entire Answer table.

6.	Copy the Answer table to the Clipboard.

7.	Paste a DDE link from the Answer table into your application.

See Also

Exchanging data


Creating a query

Follow these general steps to create a query,

1.	Create a new query by clicking the New Query button or by choosing File|New Query. The Select File dialog box appears in front of an empty Query window.

2.	Add a table to the Query window by selecting the name of the table you want to query. A table in the Query window appears as a query image. A query image contains all the fields in a table, but none of the records.

3.	Define the query by composing an example of the data you want. For example, placing a checkmark in a field's check box assures that values in that field appear in the query result.

4.	Run the query. The query result appears in a temporary table, which is overwritten after each query. To save its data, use Properties|Answer Table to save the table with a different name.

Adding multiple tables to the Query window

You can add multiple tables to the Query window at the same time.

1.	In the Select File dialog box, select the name of the table you want to add to the Query window. Drag to select multiple contiguous tables, or hold down Ctrl as you click to select several non-contiguous tables.

2.	Choose OK.

To add tables to an existing query, activate the Query window, then either click the Add Table button or choose Query|Add Table.

Note:	If you've rearranged the columns in a Table window by dragging the column titles, your custom view of the table doesn't appear in the Query window.

Arranging tables in the Query window

You can use the Properties menu to arrange the tables (query images) in a Query window:

Tile Tables is the default; the tables or query images appear one below the other.

Cascade Tables displays the tables or query images as individual, stacked windows.

To move between tables in the Query window, click the table you want. You can also use F3 and F4 to cycle through all the tables.

Removing tables from the Query window

To remove a table from the Query window,

1.	Click the Remove Table button.

2.	In the Remove Table dialog box, select the table name to remove.

3.	Choose OK.

See Also

Creating query statements

Defining the query

Running the query

Saving the query

Using query-by-example (QBE)

Working with the Answer table


Defining the query

You define the query with checkmarks and query statements:

Checkmarks identify the fields that appear in the Answer table. Each check box in the query image has a check menu with five checkmark types. The different checkmarks produce different results.

Query statements determine which records are included in the results of a query

See Also

Creating a query

Running the query

Saving the query

Using query-by-example (QBE)

Working with the Answer table


Creating query statements

A query statement determines which records are included in the results of a query. To create query statements that narrow a search to specific records, you type selection conditions into fields in the query image.

Note:	Each field in a query image can hold up to 255 characters.

To view long selection conditions, you can resize a column in the query image by dragging its right border (just as you can in the Table window).

Selection conditions can contain any combination of reserved symbols or words, values, or operators. For example, query statements can locate records based on

an exact match

a range of matching values

an inexact match

a pattern

Click one of these topics below for more information on what you can do with query statements:

Editing query statements

Including punctuation

Matching exact values

Matching inexact values

Matching patterns of characters

Matching ranges of values

Performing table operations

Using arithmetic expressions

Using checkmarks

Using example elements

Using multiple conditions

Using sets

Using special operators

Using summary operators


Including punctuation in query statements

To include punctuation marks and other reserved characters in a query statement, enclose the value in double quotation marks (" "). Quotation marks identify characters as literal characters, instead of as part of a reserved word or operator.

Note:	You don't need to enclose blank spaces in quotation marks.

If you don't want quotation marks and backslash characters to appear in the Answer table, precede them with a backslash (\).

Character	Example of use

     "	Literal quotation marks in the value must be preceded by a backslash (\). For example, when you type "Thomas E. \"Ned" Lawrence", Thomas E. "Ned" Lawrence appears in the Answer table.

Backslash (\)	Literal backslash (\) characters in the value must be preceded by another backslash (\\).

Typing numbers into query statements

When you type a number into a query statement, you can ignore the format specified for the field. If you include a comma for a whole number separator in the numeric value, an error occurs because the comma is the AND operator.

To search for numbers with decimal portions, include the decimal point in the example you type. For example, to find all values that match 4150.5, type 4150.5.

See Also

Creating query statements


Editing query statements

Editing query statements is similar to editing table values, except there is no Edit Data button or corresponding Edit mode.

When you select a field that contains a query statement, any characters you type are added at the end of the existing statement. To revise a query statement, use field view or persistent field view.

To cancel the last change you made in a query image, press Esc before moving off the field.

Using field view

To enter field view, select the field, then use one of the following methods:

click the Field View button in the SpeedBar

press F2

click the field again (or double-click an unselected field)

To stay on the current field and exit field view, use any of the actions you used to enter field view. When you click another field or press Tab or Shift+Tab, you leave the field and exit field view.

To select another field and remain in field view, use persistent field view.

Using persistent field view

Press Ctrl+F2 to enter persistent field view. When you press Tab or Shift+Tab to move to another field, you remain in field view.

To exit persistent field view, press Ctrl+F2.

See Also

Creating query statements


Matching exact values in queries

Usually you'll want to restrict the results of a query so you see only certain records in a table.

Exact values

To search for only those records with a specific value in a field, type the value into the field in the query image. Exact matches are case sensitive; only values that are exactly the same as the value you type will appear in the Answer table.

To locate more specific records, you can type values into several fields. Only the records that match all typed values will appear in the Answer table.

Typing values in query statements

To include a field value in the results of the query,

1.	Place a checkmark in the field's check box. The insertion point appears beside the check box.

2.	Type the exact value you want to find.

3.	To modify a value using standard text-editing keys, click the Field View button in the SpeedBar, choose Query|Field View, or press F2. To edit the entry in another field, double-click it.

4.	Repeat steps 1 and 2 until you finish the query.

See Also

Creating query statements


Matching ranges of values in queries

To see records that match a range of values--not just one value--you can use comparison operators. Comparison operators work with the following field types:

dBASE: character, float, number, and date fields

Note:	An error appears if any operators are used to search dBASE logical fields.

Paradox: alphanumeric, numeric, short, currency, and date fields

Using comparison operators

To use a comparison operator, type it before the value you're interested in. These operators are listed in the next table.

Operator	Meaning

=	equal to (optional)

>	greater than

<	less than

>=	greater than or equal to

<=	less than or equal to

Ranges of values with upper and lower limits can be specified by combining comparison operators with other operators, such as the AND (,) operator.

When you search for numbers stored in scientific notation, use the greater than (>) or less than (<) operator. When you search for logical values, only use f, F, t, T, false, or true as search conditions.

See Also

Creating query statements


Matching inexact values in queries

When you don't know the exact value you want to match, enter one of the following operators before a value in the query image:

LIKE locates records that are similar to, but not necessarily identical to, a particular string value (character values in dBASE, alphanumeric values in Paradox).

NOT locates records that exclude the value you specify.

BLANK locates all records without values in that field. You don't have to specify a value after this operator.

TODAY locates all records with today's date, and can be used with other operators to locate records relative to today.

See Also

Creating query statements


Using the LIKE operator in queries

If a query isn't producing the results you expect, try using LIKE to see if the problem is a spelling error of the value in the query image.

Note:	The LIKE operator is used only to find string values.

There are two general rules to remember when using LIKE:

The first character in the value you type must match the value you're looking for (case doesn't matter). For example, like Kalifornia does not match California.

When the value you type includes half or more of the characters in a value, in the correct order, you'll probably get a match. For example, like lon, like ldn, like lnd, and like loo all match London. But like lo and like ln do not match London.

See Also

Matching inexact values


Using the NOT operator in queries

To select records that do not meet a specific condition or contain a specific value in a field, use the NOT operator.

When NOT precedes a selection condition, it reverses the result. NOT can precede other operators, exact values, ranges, or wildcards used in matching patterns.

To use the NOT operator, type not before the example of the value you don't want to see. All values--including blanks--that don't match the value appear in the Answer table.

See Also

Matching inexact values


Using the BLANK operator in queries

To find records without a value in a specified field, use the BLANK operator. Sometimes, the absence of a value is a useful piece of information. Or, you may want to find records with a blank field so you can fill in information that wasn't available when the record was originally entered.

To use the BLANK operator, type blank in the appropriate field.

You can combine NOT with BLANK to find all records that have any value in the specified field.

See Also

Matching inexact values


Using the TODAY operator in queries

To locate records based on a date field, use the TODAY operator. For TODAY to work correctly, make sure your computer's calendar is set to the correct date.

You can use the arithmetic operators + (addition) and - (subtraction) with the TODAY operator to do the following types of date calculations:

add a number (of days) to a date

subtract a number (of days) from a date

subtract a date from a date (the result is a number of days)

Following are some examples of arithmetic operations on dates.

Expression	Meaning

< TODAY	Finds dates earlier than today's date

< TODAY - 90	Finds dates earlier than 90 days ago

TODAY + 30	Finds dates 30 days after today's date

See Also

Matching inexact values


Matching patterns of characters in queries

To match patterns of characters in your query examples with more flexibility than the LIKE operator offers, use wildcard operators. When you use a wildcard to find a date, the pattern you define with the wildcard operator must reflect the current date format (set in the ODAPI Configuration Utility).

Note:	Changing how data displays in the Windows Control Panel changes only the view of the data. To query data in tables after changing Windows Control Panel settings, make sure you change your ODAPI settings to match.

Matching a series of characters

You can use the .. wildcard operator to match any series of any number of characters, including blank spaces. Here are some examples:

Pattern	Matches these field values

G..	Grant, glitch, Georgia (uppercase and lowercase letters match)

g..t	Grant, gross weight (the space character matches)

..T	hat, Elm St

..e..s	Thomas Edward Willis, roses

7..5	7485, 70005

6/../71	6/01/71, 6/30/71

Matching single characters

When you know how many characters are in the entry you're looking for, use the @ wildcard operator. The @ wildcard operator matches any single alphanumeric character. You can use any number of @ characters to specify a pattern.

Here are some examples:

Pattern	Matches these field values

m@@e	Mike, more, made

wom@n	woman, women

S@@@@	Smith, Smyth, scent

19@2	1932, 1952, 1992

See Also

Creating query statements


Using multiple conditions in queries

When you enter more than one selection condition on the same line of a query image, all of the conditions must be met before a record appears in the results. This is called a logical and because only those records that match the first condition, the second condition, and any other conditions appear in the results.

There are situations when you might want to select records that meet either the first condition or the second condition (or any other conditions, if there are any). This kind of query statement is called a logical or; to create it, you can use either:

multiple lines

the OR operator

Using multiple lines

To search for records that meet one of multiple selection conditions, you can enter the conditions on separate lines, or rows, of the query image.

Each line in a query image defines a search criterion that is independent from any other line. Example element are a unique situation because the value(s) they represent are usually defined on one line, then used on a separate line of the query image.

Note:	Be sure all lines in the query have checkmarks in the same fields for this kind of query to work.

Creating additional lines

To insert a line above the selected line in a query image, press Ins. There are two alternate methods for creating additional lines:

If a query image has a single line, select any field in the query image, then press Down. If a query image has multiple lines, select any field in the bottom line of the query image, then press Down. Repeat as needed.

Select any field in the query, press End to go to the last field in the table, then press Enter to create an additional line in the query image.

Deleting lines

To delete a line, select the line you want to delete and press Ctrl+Del to remove it from the query image.

Note:	Ctrl+Del deletes the line and any query statements in it, and cannot be undone.

Using multiple checkmarks

Checkmarks in queries with multiple lines follow the same precedence as in queries with single lines. When several checkmark types appear in a field that has multiple lines in the query images, the highest precedence checkmark overrides the others.

See Also

Creating query statements

Using example elements


Using special operators in queries

To ask specialized questions about the data in your tables, use a special operator:

AND (,)

OR

To specify a new name for a field in the Answer table, use the AS operator.

The AND (,) operator

To enter multiple conditions in a field and require that they all be met, separate the conditions with commas. Using the comma to separate conditions is called a logical and, meaning all conditions must be met for a match to occur.

Conditions in separate fields are also treated as logical and conditions. Each of the conditions in all of the fields in a query image must be met before a record can appear in the Answer table.

Note:	When a value has a comma (such as Fogg & Peat, Inc), remember to enclose the value in quotation marks.

The OR operator

To enter multiple conditions in a field and match all values meeting any condition, use the OR operator. This is the logical or operation.

To use the OR operator in a field of the query image, type or between the values you want to find.

Note:	Be sure to leave a space before and after the OR operator.

Combining OR with AND conditions

To combine OR operations with AND in a single query, define a query statement so that one field uses the OR operator and the other field has an exact value to match.

The AS operator

By default, a field you check in a query appears with the same name in the Answer table. To change the field name only as it appears in the Answer table, use the AS operator, as follows:

1.	Type the query statement (if any) in the field.

2.	Type as followed by a space, then the new field name you want.

3.	Run the query. The new field name you specified appears in the Answer table.

A field name specified in an AS query statement can contain an expression. It cannot contain an example element.

If you want to include reserved characters or words in a new field name, enclose it in double quotation marks.

See Also

Creating query statements


Using arithmetic expressions in queries

In number, currency, date, and short number fields, you can use arithmetic expressions in a query.

Operations with the highest precedence are performed first; operations with equal precedence are calculated from left to right. Use parentheses ( ) to combine and group operations and indicate which calculations should be performed first.

Database Desktop provides five arithmetic operators:

Operator	Description	Precedence

*	Multiplication	2 (highest)

/	Division	2

+	Addition (or concatenation of strings)	1

-	Subtraction	1

( )	Groups expressions	(Overrides)

Arithmetic operators are especially useful with the TODAY operator, the CALC operator, and example elements.

See Also

Creating query statements


Using checkmarks in queries

The result of a query depends on which checkmarks are used to perform the query. (This is called precedence.) Checkmarks with the highest precedence override checkmarks with lower precedence.

Note:	Checkmarks make field values appear in the Answer table after you run a query.

Checkmark	Precedence	Description

3 (highest)	CheckPlus: shows duplicate records, no sort

2	Check: shows unique records only, sorts in ascending order

1	CheckDescending: sorts in descending order

1	GroupBy: groups records in a Set query

Note:	Checks only take precedence over CheckPlus marks when they both appear in the same field of a query with multiple lines.

Using CheckPlus

Each row of fields that appears in the Answer table is treated as a single record. This record can contain one field or several fields.

A duplicate record is a record that contains all of the same values in each field as another record. If a query is displaying only a few fields in the Answer table, it's possible that some valid records will not appear when you use Checks to display the fields.

When CheckPlus is in any field in a query image, it assures that all records for that query, including duplicates, appear in the Answer table.

Using CheckDescending

CheckDescending, unlike CheckPlus, does not affect the entire record. Only the field in which you place a CheckDescending check is sorted in descending order.

When multiple CheckDescending marks are placed in a query image, the records are sorted in descending order based on the position of the fields in the query image. The leftmost field in the top line of the query image is the field that is sorted first. After that, duplicate values in the other fields will be sorted based on their order from top to bottom and left to right in the query image.

The fields in the query image can be rearranged to place specific fields to the left of others. This lets you view widely separated fields in the query image, and has no affect on the order in which fields appear in the Answer table.

Placing and removing checkmarks

1.	To place a checkmark in a single field, position the pointer on its check box and hold down the left mouse button.

2.	Drag the pointer to select the checkmark you want, then release the mouse button.

Note:	As a shortcut, click the check box to place a Check in a field.

To remove a checkmark, click the check box.

To place the same checkmark in every field in a table, use the check box below the table name and select the checkmark you want.

See Also

Creating query statements

Checkmark types


Checkmark types

Checkmarks identify the fields that appear in the Answer table. Each check box in the query image has a check menu with five checkmark types. The different checkmarks produce different results.

Type	Description

Check shows all unique values for the checked field, excluding duplicates. The values are displayed in A to Z (ascending) order. When used with a summary operator, Check specifies that the records be divided into groups based on the values in the checked field.

CheckPlus shows all values in a field, including duplicates. Because sorting removes duplicates, the Answer table appears unsorted.

CheckDescending shows unique values sorted in Z to A (descending) order.

GroupBy specifies a group of records to use in a Set query. Use the GroupBy check when you want to group by a field but without displaying its values in the Answer table.

Removes a check.


Running the query

To run the query:

click the Run Query button

choose Query|Run

press F8

While the query is running, a dialog box displays status information. If there's a problem with the query, an error message appears in another dialog box. When additional information about the error is available, you can use the >> or << button to see it.

If the query runs successfully, the Answer table appears.

Note:	While an Answer table is open in Database Desktop, you can't run a different query in another Query window until you either close the Answer table or rename it. This isn't necessary if you are running the same query, or modifications of the same query.

Setting run-time options

When the tables used by a query are located on a network, other network users might be editing table data when you run the query. To specify how you want to handle potential changes to the data while the query runs, open the Restart Query dialog box, and choose one of these options:

Restart Query on Changes guarantees the most current version of the table by running the query again when changes are made to the tables.

Lock All Tables To Prevent Changes keeps other network users from changing the data in the tables while the query is active.

Ignore Source Changes gives you a snapshot of the most current data at the time the query is run. Changes to the data in the source tables appear only if you run the query again.

See Also

Creating a query

Defining the query

Saving the query

Using query-by-example (QBE)

Working with the Answer table


Saving the query

To save a query, choose File|Save or File|Save As. By default, the file-name extension is .QBE. A .QBE file is an ASCII text file that contains all of the instructions for running the query.

See Also

Creating a query

Defining the query

Running the query

Using query-by-example (QBE)

Working with the Answer table


Working with the Answer table

The Answer table is a temporary table created after a query is run. It is overwritten every time you run a query.

Note:	By default, the structure of the Answer table is based on values in the checked fields in the query (from left to right, and top to bottom). The first field checked in the first query image becomes the first field of the Answer table, and so on.

To save the results of a query, you can rename the Answer table. You can also use the DOS COPY command or the File Manager to save the ANSWER.DB with a new name so that it isn't overwritten.

To change the Answer table, you can modify its:

Sort order. Before you run the query, you can choose Properties|Sort Answer to override the default sort order of the Answer table. This lets you change the order in which fields are sorted in the Answer table, without affecting the display order, or view, of the fields.

View. To change column order and size, or row height, use the mouse just as you do in the Table window. To make your revisions the default, choose Properties|Save Properties. Answer table properties are overwritten when you run a query.

Properties. Before you run a query, choose Properties|Answer Table to define a new name or data format for the Answer table. This lets you save the results of a query.

See Also

Creating a query

Defining the query

Running the query

Saving the query

Using query-by-example (QBE)


Sort order of records in the Answer table
Sort order of records

The Answer table records are sorted first by the table order of its fields, then by the values in its fields from left to right.

The table order is the default field order defined in the structure of the table. The table structure is different from the view (or onscreen image) of the table, query image, or Answer table.

The records are sorted by the values in the first field (again, first in the table order). Then any ties are settled by the values in the second field, and so on.

To change the display order of fields in the Answer table, rotate the columns in the query image by dragging their titles (just as you can in the Table window). To override the default sort order of field values in the Answer table, use Properties|Sort Answer.

Using ascending order

When a field in a query image contains a Check, Database Desktop sorts records in that field in ascending order:

from lowest to highest number

from earliest to latest date

alphabetically from A to Z

Using descending order

To sort records by descending order--the opposite of ascending order--place a CheckDescending check in the field.

Note:	Sorting in either ascending or descending order always removes duplicate records. When you use CheckPlus to retain duplicate records, the records in the Answer table are not sorted.

To increase the speed of a query, try replacing Checks with CheckPlus marks. CheckPlus queries are faster because the results aren't examined for duplicates nor are they sorted.

See Also

Checkmark types

Sorting the Answer table

Working with the Answer table


Sorting the Answer table

To sort the Answer table,

1.	Complete the query definition in the Query window.

2.	Choose Properties|Sort Answer.

3.	Select a field from the Available Field list to sort the Answer table by.

4.	Choose the Right Arrow button to move the field to the Sort By list.

5.	Repeat steps 3 and 4, adding the fields in the order you want the Answer table sorted by.

6.	To remove a field from the Sort By list, select it, then choose the Left Arrow button.

7.	To rearrange the field order in the Sort By list, select a field and then use the Up and Down Arrow buttons to move it up or down in the list.

8.	When you finish, choose OK.

9.	Run the query.

Note:	If a query is undefined or has syntax errors, the Sort Answer dialog box is unavailable.

See Also

Sort order of records

Working with the Answer table


Answer table properties

While the Answer table is active, you can use the Properties menu to change the table name or data type, and save, restore, or delete its properties:

Properties|Answer Table renames the Answer table and changes its data type to dBASE or Paradox. These settings take effect the next time you run the query.

Properties|Save writes the column arrangement and width, row height of the table, scroll locks, and position of the table title to the ANSWER.TV or .TVF file.

Note:	Saving the Answer table properties lets you run the query several times in a row without setting the properties each time.

Properties|Restore resets the Answer table properties to the last saved version. This is useful for restoring the Answer table after experimenting with changes to its properties.

Properties|Delete erases the ANSWER.TV or .TVF file, and then removes the custom property settings from the Answer table.

See Also

Sorting the Answer table

Working with the Answer table


Using example elements

An example element is not a literal value you type, it's a variable (or placeholder) that represents all values or a subset of values in a field.

Example elements give you great flexibility because they can represent:

all values in a field

a subset of values in a field

one specific value in a field

joined fields in multiple tables

Note:	The name, or label, of an example element has no relation to the value it represents. To remind yourself of the values an example element represents, use a meaningful name.

See Also

Creating an example element

Defining the value of example elements

Using advanced query-by-example (QBE)


Creating an example element

To toggle on the placement mode for example elements, click the Join Tables button in the SpeedBar. To create a single example element, press F5 or type an underscore ( _ ) before you type its name. When you type or edit the name of an example element, remember to use:

A unique name. (The example element name doesn't appear in the Answer table.)

Valid characters, including any alphabetical character (A to Z) or number (0 to 9). An example element cannot have a space in it, nor any reserved characters (such as a comma, +, -, *, !, or /).

Example elements appear highlighted or in a different color from other text you type in a query statement.

Note:	If you have a color monitor, example elements appear as red text.

Creating an example element with the SpeedBar

To create example elements with the SpeedBar, use the Join Tables button. The Join Tables button toggles placement mode on and off. Because placement mode creates two example elements, it's typically used to link, or join, two tables in a query.

When you click the Join Tables button, you enter the example element placement mode and the Joining indicator appears in the status line. Your next two clicks each create an example element with the same name. After the second example element is created, the pointer returns to normal mode.

If you click twice to create two example elements in the same field, the AND (,) operator is inserted between the example elements. This is useful when you want to use an example element in a calculation.

Note:	If you use the Join Tables button to create a single example element, be sure to click the Join Tables button again after you create the example element. This toggles off placement mode and the status line indicates placement mode is canceled.

Creating an example element with the keyboard

To create an example element with the keyboard,

1.	Select a field you want to create an example element in.

2.	Press F5 or type an underscore ( _ ), then type the characters you want to use. The underscore causes the next group of characters to be treated as an example element; it doesn't appear in the field.

3.	To add more text in the same field, press Spacebar or type a comma, a dash, or an underscore when you finish typing the example element. Subsequent characters you type appear as regular text.

4.	Repeat steps 1 through 3 until all example elements are created.

See Also

Calculations using example elements

Defining the value of example elements

Editing an example element

Using multiple tables in a query


Editing an example element

To edit an example element, make sure the pointer is in normal mode. If you want to use standard text-editing keys to modify the example element, use field view.

To cut, copy, or paste example elements, use the SpeedBar buttons or the Edit menu commands. To select an example element, first select the field it's in, then double-click the example element.

Note:	If you delete the leftmost character in an example element's name, the text reverts to normal text (the color changes). To restore the example element formatting, position the pointer in front of the first character, then press F5 or type an underscore ( _ ).

See Also

Creating an example element

Defining the value of example elements


Defining the value of example elements

By default, the value of an example element is all values in a field. Typically, when you use example elements to join tables in a query, you want the example elements to represent all values in the common field.

To narrow a search using example elements, you can refine the values the example element represents by defining its value. To define the value of an example element, you create selection conditions in other fields on the same line of the query image. Once an example element is defined, you can use it to search for the same values in those fields. To define an example element as a range of values, create a query with multiple lines.

Note:	The value of an example element must be defined before it can be used.

See Also

Calculations using example elements

Creating an example element

Using multiple tables in a query

Using example elements


Calculations using example elements

To perform a calculation in a field, create an example element and then use a copy of the example element in the query.

You can use example elements with arithmetic and other operators, such as + (concatenation), AND, NOT, and LIKE.

Note:	You can't use the OR operator with example elements.

For example, to list all the customers who have ordered two or more different items, you can create a query with two example elements: One example element represents the specific item ordered, and the other represents the customer who ordered it.

See Also

Defining the value of example elements


Using multiple tables in a query

Up to 24 separate tables in a query can be joined through their common fields, which are the fields in each table that contain the same kind of information. To join any tables with common fields, you place the same example element in the common field shared by each of the tables.

Notice that multiple example elements placed in the same common field must be separated with a comma (the AND operator). The Join Tables button creates the comma and a second example element when you click a field that already contains example element.

Using selection conditions

When you use example elements to join tables, you can also add multiple selection conditions to any query.

When you enter example elements in multi-line, multi-table queries, be sure to join the corresponding lines in the query images. As with single tables, when you use the OR operator in different fields of a multi-table query, or define more than one set of OR conditions, you must enter them on separate lines of the query image.

Note:	When a query has multiple lines and multiple tables, you must use unique example elements for an OR operation across multiple lines. These example elements must also be placed on separate lines in the other tables.

See Also

Defining the value of example elements


CALC queries

With query-by-example, you can do more than ask questions about the values in tables. You can use the CALC reserved word to perform the following calculations on field values:

construct and evaluate mathematical expressions

combine values from two or more fields

combine field values with constants

The Answer table generated by a CALC query contains an additional field for the calculated result. Because of this, it doesn't matter which field of the query image you type the CALC expression in, and you don't need to place a checkmark in the field.

When using CALC with arithmetic operators (+, -, *, /, and ( ) ), you can also use

constants (like 154 or 7/12/91)

example elements

Because example elements represent values, you can use them to perform calculations on those values. This means instead of separately locating values and performing calculations on the results, you can use a single query with example elements.

You can combine (concatenate) alphanumeric values and constants using CALC and the + operator. Constant values that are also Database Desktop reserved characters, such as commas and spaces, must be enclosed in double quotation marks to be treated as literal characters.

See Also

Defining the value of example elements

Forming groups to calculate

Performing queries on groups of records


Performing table operations with reserved words

Database Desktop reserved words perform operations that do not use checkmarks and do affect tables in the query (except FIND, which doesn't affect tables).

Note:	An error message appears if you use checkmarks with table operators.

Here are the Database Desktop reserved words:

Name	Description of result

INSERT	Inserts records from one or more tables (called the source table(s)) into a target table. Source tables are unaffected by the INSERT query. The target table must already exist before it can be added to the Query window. After the query is run, a list of the records that have been inserted into the target table appears in the Inserted table.

DELETE	Removes whole records (not specific values in records) from a table. This query type is appropriate when the records to be deleted have something in common that can be specified in selection conditions. After the query is run, a list of the records deleted from the table appears in the Deleted table.

CHANGETO	Alters values in a table based on conditions you specify in a query. CHANGETO offers a global search and replace capability, and is useful when you want to change many values in a similar way. After the query is run, a list of the records changed by the query appears in the Changed table.

FIND	Locates records or groups of records in a table. FIND queries let you define elaborate selection criteria based on several fields or several tables to search for the records you want.

If a problem occurs while a query is inserting, deleting, or changing records, the data is saved in temporary tables called ERRORINS.DB, ERRORDEL.DB, or ERRORCHG.DB, respectively. If you're querying a dBASE file, these files are saved with the .DBF extension.

To define a query as an INSERT, DELETE, or FIND query, click under the table name in the Query window and choose the reserved word you want from the menu. Or, use the keyboard to move to the leftmost column, then type the first letter of the operation you want (I, D, or F).

To remove one of these reserved words from the leftmost field, choose the blank option at the top of the menu.

If you are running a query on a network, Database Desktop places a full lock on tables involved in an INSERT, DELETE, or CHANGETO query because they change the values in the tables. You won't be able to process the query until all other users have released their locks on the table(s).

After an INSERT, DELETE, or CHANGETO query is run, a temporary table displays with the results. As with the Answer table, the Inserted, Deleted, and Changed tables are overwritten by the next query of the same type.

Note:	To save a copy of a temporary table, first create a query for that table with CheckPlus checks in all fields. Before running the query, choose Properties|Answer Table to specify a new name for the Answer table so it won't be overwritten by subsequent queries.

See Also

Using advanced query-by-example (QBE)


INSERT queries

Unlike other kinds of queries, the values you type into query statements in an INSERT query are expressions that create new values. They do not select records.

In an INSERT query, if you don't want a field to appear in the target table, you omit a query statement in that field. After the query is run, fields in the target table remain blank if fields in the source table are blank (don't contain query statements).

When you add data from a source table to a target table, the table structure doesn't have to match; only the fields with query statements must match.

If the target table is not keyed, records from the source table are inserted at the end of the target table. If the target table is keyed, the records are inserted in key sort order. If any records in the source table have the same key value as existing records in the target table, the records are not inserted. Instead, they appear in a Keyviol table, another type of temporary table. Multiple key violation tables are named Keyviol1, Keyviol2, and so on.

Note:	Do not place checkmarks in any fields of an INSERT query; checkmarks cause an error and the query won't run.

Creating an INSERT query

To create an INSERT query,

1.	Add the source tables and target table to the Query window.

2.	In the source table(s), place a unique example element in each field you want to insert in the target table.

3.	For each field in the source table(s), enter any selection conditions for the field values.

4.	In the target table, position the pointer under the table name, then hold down the mouse button and choose Insert from the list that appears.

5.	In the target table, use example elements that match example elements in the source table(s) to specify which fields you want to insert.

6.	To add any constants to the values, add them to the query expressions in the target table.

7.	Run the query.

To use an INSERT query to copy records to a separate table, you can use DOS or the File Manager to create a copy of the source table. Then delete all the records in the copy and use it as the target table. To also save a copy of the index, copy the table's .PX file (for Paradox) or its .MDX file (for dBASE).

Undoing an INSERT query

After you run a query, the Inserted table lists the records that have been added to the target table. To remove those records from the target table, create a new query as follows.

1.	Choose File|New Query.

2.	Add Inserted as the source table to the Query window.

3.	As the target table, add the table containing the inserted records you want to delete.

4.	In the target table, position the pointer under the table title, then hold down the mouse button and choose Delete.

5.	Use example elements to join the source table and the target table.

6.	Run the query.

Note:	If any records in the Inserted table duplicated any existing records, this procedure will delete the original as well as the duplicate records.

See Also

Performing table operations with reserved words


DELETE queries

When you want to delete multiple records that are similar enough to meet a set of selection conditions, you can use a DELETE query. This is faster than choose Record|Delete in Edit mode.

Note:	If you don't enter any selection conditions in a DELETE query, all records will be deleted from the table.

Creating a DELETE query

To create a DELETE query,

1.	In the Query window, add the table you want to delete records from.

2.	Position the pointer under the table name, then hold down the mouse button and choose Delete.

3.	Enter any selection conditions that identify the records to be deleted.

4.	Run the query.

Records deleted from the source table appear in Deleted, a temporary table.

To undo a deletion from a table, define Deleted as the source table in an INSERT query, and define the original table as the target table.

See Also

Performing table operations with reserved words


CHANGETO queries

To modify records in a table, you can create and run a CHANGETO query. A single CHANGETO query can define a calculation and write the new values resulting from the calculation. After you run the query, a temporary table named Changed appears. Changed contains a copy of the original records (as they were before you ran the query).

Creating a CHANGETO query

To create a CHANGETO query,

1.	In any field except the leftmost field in the query image, type the entry you want to replace, followed by a comma.

2.	Press Spacebar, then type CHANGETO followed by a space.

3.	Type the new value.

4.	Run the query.

Note:	Do not use checkmarks with a CHANGETO query.

Undoing changes made by CHANGETO

To restore the original contents to records changed by a CHANGETO query, you first delete the new records, then reinsert the original records. Because Changed is a temporary table, you must perform these steps before running another CHANGETO query:

1.	Create a DELETE query (see the previous section) for the table.

2.	Define selection conditions to remove the changed records.

3.	Run the DELETE query to remove the changed records.

4.	Create an INSERT query that defines the Changed table as the source table, and the original table as the target table.

5.	Run the INSERT query to reinsert the original records into the table.

Changing values with example elements

To change values with example elements,

1.	In the field with values you want to change, type the selection condition followed by a comma (the AND operator).

2.	Place a unique example element name after the comma.

3.	Type a comma, and then CHANGETO followed by a space, the example element, and the rest of the mathematical expression you want.

4.	Run the query.

See Also

Performing table operations with reserved words


FIND queries

When you run a FIND query, any records that meet the selection conditions are placed in the Answer table. The records in the Answer table are arranged in the same order they appear in the queried table.

Note:	Do not use checks and GroupBy checks with a FIND query.

Creating a FIND query

To create a FIND query,

1.	Choose Find from the menu that appears when you click below the table name.

2.	Enter the selection conditions that specify the records you want to locate.

3.	Run the query.

See Also

Performing table operations with reserved words


Performing queries on groups of records

You can define queries about groups of records in a table to:

select records based on characteristics of a group (such as items that appear on two or more orders)

calculate statistics on groups of records (such as the average number of orders placed by each city)

compare characteristics of a group with other records (such as which customers have placed more orders than any California customer)

Note:	As with other queries, you check a field to include it in the Answer table. However, when a checkmark appears on the same line as a summary operator, the records are also divided into groups based on the values in the checked field.

See Also

Calculating group statistics

Using advanced query-by-example (QBE)

Using sets


Summary operators

To specify conditions in a query for groups of data, you use summary operators. The following table describes the types of summary operators.

Name	Description

AVERAGE	Averages the values in the group. Valid for operations on Paradox fields (number, short number, currency, and date) and on dBASE fields (number, float number, and date).

COUNT	Counts the number of values in the group. Valid for operations on all Paradox or dBASE field types.

MAX	Identifies a maximum value for the group. Valid for operations on Paradox fields (alphanumeric, number, short number, currency, and date) and on dBASE fields (character, number, float number, and date). The current language driver, typically ASCII sort order in the U.S., determines the maximum value in alphanumeric and character fields. For example, the value AAC is the maximum for the group AAA, AAB, and AAC because the decimal code number for C in the ASCII sort order is higher (67) than it is for A (65) or B (66).

MIN	Identifies a minimum value for the group. Valid for operations on Paradox fields (alphanumeric, number, short number, currency, and date) and on dBASE fields (character, number, float number, and date). The current language driver, typically ASCII sort order in the U.S., determines the minimum value in alphanumeric and character fields. For example, the value ZZA is the minimum for the group ZZC, ZZB, and ZZA because the decimal code number for A in the ASCII sort order is lower (65) than it is for B (66) or C (67).

SUM	Totals the values in the group. Valid for operations on Paradox fields (number, short number, and currency) and on dBASE fields (number and float number).

ALL	Includes all values in a group (including duplicates).

UNIQUE	Discards duplicate values when performing a group operation (used in combination with other summary operators).

To override default grouping in a CALC operation, you can add either the reserved word ALL or UNIQUE to the query statement.

See Also

Calculating group statistics

Forming groups to calculate


Calculating group statistics

The CALC operator that calculates new fields for the Answer table can be used to calculate statistics for groups of records. For example, you can use it with

AVERAGE

COUNT

MIN

MAX

SUM

ONLY

All CALC queries create a new field in the Answer table. The new field is named SummaryOperator of FieldName (where SummaryOperator is the name of the operation performed on the value, such as SUM; and FieldName is the name of the original field).

To rename the field before you run the query, use the AS operator.

See Also

Forming groups to calculate

Performing queries on groups of records

Summary operators

Using advanced query-by-example (QBE)

Using sets


Forming groups to calculate

When CALC is used with a summary operator, calculations are performed on groups of records. When you place checkmarks to display fields in the Answer table, you are also forming groups to perform the calculation on. To perform a calculation on all records in the table, don't check any fields; the entire table is the group.

Here are some examples of combining CALC with summary operators:

Combination	Description of result

CALC COUNT	A checkmark in the field you want to group the records by, and the expression CALC COUNT in the field with quantities, returns the number of unique group values in the table. To group by more than one field, place checkmarks in each field you want to group by. To include duplicates in a COUNT operation, type all after the CALC COUNT operator.

CALC MIN	A checkmark in the field you want to group the records by, and the expression CALC MIN in a date field, returns the values in the table and the earliest dates associated with the values.

CALC SUM	A checkmark in the field you want to group the records by, and the expression CALC SUM in the field with quantities, returns the sum of the quantities for each group in the table. To group by more than one field, place checkmarks in each field you want to group by.

By default, the COUNT operator counts only unique values. To include duplicate values in a count of records, type ALL after CALC COUNT.

To display the minimum or maximum values in the group, you can use CALC MIN or CALC MAX. Because placing a checkmark in a field groups records on that field, only the CALC MIN and CALC MAX expressions are used to display the records in the Answer table.

When a query performs calculations on a group of records, the number of fields checked in the query image is significant. If you check:

a single field, a single, calculated value is returned for each value in the checked field.

multiple fields, a value is calculated for each combination of values in the checked fields.

Note:	Multiple checkmarks create groups based on multiple fields.

See Also

Calculating group statistics

Performing queries on groups of records

Using advanced query-by-example (QBE)

Using sets


Using sets

A set is a specific group of records about which you intend to ask more questions. Set operations are useful for revealing trends and patterns in data with a single query. Once you've defined a set in a query, you can make two kinds of comparisons:

To other groups of records. The set comparison operators (ONLY, NO, EVERY, and EXACTLY) compare other groups of records to the set.

Summary comparisons with other groups. Because a set is a type of group, you can use summary operators to compute its values, then compare the result to values in other records.

Creating a Set query

Defining a Set query is very similar to creating selection conditions. Every Set query consists of a:

Set definition. One or more lines in a query image can define a set. To define a set, you click below the table name, then choose Set. You also create example elements and selection conditions on the same line in the query image. Lines that are part of the set definition cannot contain checkmarks or summary operators. Don't use checkmarks on the line that defines a set of records.

Set comparison. To compare a defined set to other records, you use set comparison operators. The set operators ONLY, NO, EVERY, and EXACTLY determine which records meet specific comparisons to the set. You can also use a summary operator instead of a set comparison operator. To form groups of records to compare to the defined set, you use checkmarks.

Optionally, you can display related information about the records by checking other fields or adding joins to more tables.

Comparing records to a set

To ask questions about other records or groups of records, you use the following set comparison operators.

Name	Description

ONLY	Displays groups that only contain values in the set (set members are not displayed).

NO	Displays groups that don't contain any of the values in the set. (To find individual records that don't match the selection conditions of a "set," use the NOT operator.)

EVERY	Displays groups that contain every value in the set (and possibly others).

EXACTLY	Displays groups that contain only values in the set and no others.

To use these set comparison operators, first define the set. Then, on another line of the query image, type the name of the operator followed by the set name.

Defining a set

To define a set,

1.	Click the New Query button in the SpeedBar.

2.	Add the table(s) you want to query.

3.	To create a Set query, click the menu under the table name in the Query window, then choose Set.

4.	To define the set, place a unique example element in each field you want to select.

5.	To refine the set further, enter any selection condition(s) that specify the records to be included in the set.

6.	To join fields in multiple tables, use matching example elements.

Comparing a set

To compare a set to records in another table,

1.	Create selection conditions in the other table.

2.	To list the query results of any field, place a checkmark in the field.

3.	Run the query.

To group the records by a field, without displaying that field in the Answer table, use the GroupBy check instead of a checkmark.

See Also

Calculating group statistics

Performing queries on groups of records

Using advanced query-by-example (QBE)


Using inclusive links

To retrieve all records in a table, whether or not they match records in another linked table, you can use the inclusion operator ! (an exclamation point) to create an inclusive link.

A query with an inclusive link retrieves the complete set of records from the table with the ! operator in its query image. The corresponding records that match the selection conditions are then retrieved from the other table(s). If there is no matching record in the other table, the corresponding fields in the Answer table are blank.

In other words, the ! operator overrides Database Desktop's default for linked tables. You can use the ! operator to

use multiple !s to retrieve all the records from multiple tables

use ! in an arithmetic expression

use both inclusive and exclusive links in the same query

Note:	As in all other queries, to see duplicate records in the Answer table, you must use the CheckPlus mark in the query image.

Processing order of inclusive links

The order in which inclusive links are processed is significant. Selection conditions in the master table (the table with the ! operator) are always processed first. Because the records in a master table are always included in the Answer table, selection conditions in the linked table(s) might not produce the results you want.

Using multiple ! operators

A query that uses the ! operator is sometimes called an outer join. A query that uses a single ! operator is also called an asymmetrical outer join because an inclusive link has been specified for only one of the tables involved in the query.

A symmetrical outer join is a query using multiple ! operators; you can link tables this way to reveal information that might get lost in other queries.

To clearly see the relationships between two tables, you can create a symmetrical outer join that is all-inclusive; it retrieves all values in the common fields of the tables (without requiring that the values match each other).

Note:	 You can also use the ! operator in arithmetic expressions.

See Also

Rules for linking tables

Using advanced query-by-example (QBE)


Rules for linking tables

Asymmetrical and symmetrical outer joins differ from other types of queries. The main differences concern the order in which the elements of a query are processed, and the ways you can link the different lines of a query.

Any two lines in a query statement can use either an inclusive link (!) or an exclusive link to associate them (but not both).

You can use both inclusive and exclusive links in the same query statement, if they don't both involve the same pair of lines. When both link types exist in one query, they are processed in order from least to most inclusive.

You can use ! with any given example element only once per line and twice per query.

See Also

Using inclusive links


QBE file syntax

Text in a .QBE (query) file defines the query, which is always enclosed by the reserved words Query and EndQuery. Once you learn the .QBE file syntax, you can create queries directly in your application. The following sections describe the syntax of .QBE files.

Answer table name

Below the reserved word Query in a .QBE file, the line that begins with ANSWER: defines the location and file name for the Answer table created by the query. This line is optional because the Answer table is created by default in your program directory.

Temporary tables, such as the Answer table, are overwritten each time a query is run. To keep a copy of the Answer table, replace the answer.db text with another valid file name. Just open the .QBE file in Notepad, edit the Answer table name, save the file, then run the query again.

Note:	If you use this technique, be sure to use a unique file name. If there is already an existing file with the name you specify, Database Desktop will overwrite it.

Sort order

The line that begins with SORT: defines the sort order. This definition is optional, and only appears in a file if you have set Answer table sort order options in the Properties|Sort Answer dialog box.

Tables and fields

Below the sort order definition (if there is one), and slightly indented from the left margin, is the query image information. Indentation of this information is optional.

On the first line, the location and name of each table in the query is followed by each field name that contains checkmarks or conditions. The pipe character (|) delimits, or separates, table names and field names.

Note:	It's optional to use spaces before or after delimiters, and to align delimiter characters.

The line directly below the table and column name in the .QBE text file contains the information you placed in the query image. When there are multiple lines in a query image, the number of lines and information in the lines of the .QBE file match the query image.

Checked fields in the query image are indicated by the keyword Check, CheckPlus, CheckDescending, or GroupBy. Example elements are preceded by an underscore character ( _ ).

Note:	Blank rows are required between each line or group of lines that contain a reserved word, a query image definition, or a wrapped piece of a query image definition.

Line wrap

Lines of text in a .QBE file are never broken in the middle of a column name, selection condition, or sort field definition. When a line of text exceeds 80 characters, it appears on another line as follows:

SORT section text (optional) wraps to the next line. Field definitions are broken only after the comma that separates them.

Query section text skips one line, then wraps to the following line. The table location and name are repeated for clarity, before the column names are defined.

See Also

Using advanced query-by-example (QBE)


Viewing tables

When you first open a table, the data appears in the Table window in View mode. The data appears in column and row format and displays any formatting properties included in the file. Each Table window contains an independent view of a table, so different views of a single table can be open at the same time. Up to 24 tables can be open at one time.

Note:	To be able to access tables stored on a network, you must tell Database Desktop the location of the network control file. See the Help topic Database Configuration for more details.

See Also

Customizing a table view

Entering and editing data

Moving through a table's records


Moving through a table's records

Use the SpeedBar navigation buttons or the Record menu to move through the records of the table. The buttons and commands work as follows:

First moves to the first record in the table.

Last moves to the last record in the table.

Next moves to the next record.

Previous moves to the previous record.

Next Set is like PgDn; it displays the next full screen of records. For example, if records 1 through 6 appear, choosing Next Set displays records 6 through 11 (screens of data overlap by one record).

Previous Set is like PgUp; it displays the previous full screen of records.

Using scroll bars

To scroll left or right through the columns of the table, use the horizontal scroll arrows. To scroll up or down one record at a time, use the vertical scroll arrows.

When you drag the box on the vertical scroll bar, a range of record numbers appears in the status line. These numbers represent the records that display in the window when you release the mouse button; the view isn't updated until you release the mouse button.

Note:	If the table is keyed, when you move the vertical scroll box, the status line uses entries in the key field (or the first field of a composite key) rather than record numbers to indicate which records will be displayed.

Using scroll lock

To lock one or more columns in place as you move horizontally through the table's columns, use a scroll lock. All columns to the left of the lock remain stationary as you move through the table's columns.

The scroll lock is a triangle in the lower left corner of the Table window. To place a lock, drag the triangle to the right side of the column(s) you want to lock. An active scroll lock appears as two triangles when you release the mouse button.

See Also

Customizing a table view

Viewing tables


Customizing a table view

The view of a table is how it appears onscreen; you can modify and save a custom view of a table. Changing the view makes it easier to see specific fields; the actual structure of the table (its definition of field order and size) remains the same. To customize a view, you can rearrange, resize, and lock columns, and resize rows. You can also lower the table in the window by dragging the table name in the record number column.

The table view contains hot zones that indicate areas on a table where you can drag to modify the view. As the pointer passes over a hot zone, the pointer changes shape.

Rearranging and resizing columns

To move a column, position the pointer on a column's heading. When the pointer changes shape, drag the column to its new position.

To resize a column, position the pointer on its right boundary line (either the heading area or the top row of data). When the pointer changes shape, drag the boundary line to increase or decrease the width of the column.

Resizing rows

To resize the height of all of the rows in a table, drag the line under the first record number. Move the line up to decrease the row height, or down to increase the row height.

Repositioning the table

To reposition the table down or up in the Table window, drag the table name. The table name is located above the leftmost column (which contains the record numbers).

Saving a custom view

To save property settings for an active table, choose Properties|Save. All display changes you make in the table view (except scroll locks) are saved to a file. The file that holds table view properties has the same name as the table, but the file-name extension is either .TVF (for a dBASE table) or .TV (for a Paradox table).

Undoing changes to a view

To erase any unsaved changes you've made to a view and restore the last-saved view, choose Properties|Restore. Database Desktop reinstates the view properties stored in the associated .TV or .TVF file. If there is no view properties file, the default view of the table is reinstated.

Restoring the default view

If you've changed and saved the view of the active table, but now want to return to the default view, choose Properties|Delete. Properties|Delete deletes the .TV or .TVF file associated with the active table.

After you run a query, if the resulting Answer table looks different than expected (for example, if the columns are in a different order than they should be), try choosing Properties|Delete, and then run the query again. Database Desktop  might be applying an old ANSWER.TV file to your new Answer table.

See Also

Moving through a table's records

Viewing tables


Using edit mode

To change data in a table, you must be in Edit mode. To enter Edit mode, do one of the following:

click the Edit Data Speedbar button

choose Table|Edit Data

press F9

In Edit mode, you can select any field and begin typing to replace its existing entry. When you enter Edit mode, the Edit Data button remains pressed in and the status line tells you Edit mode is active.

Note:	To position the insertion point within the entry so you can change a spelling or typing error, use field view.

See Also

Cutting, copying, and pasting data

Entering and editing data

Field view

Inserting and deleting records

Removing entries from fields

Selecting fields and records

Using undo


Selecting fields and records

When you move to a field or click it, the field is highlighted. This indicates the field is selected. In Edit mode, if you type anything into a selected field, you'll replace the existing entry with the value you type. The cut, copy, and paste operations affect the entire field entry when it's selected.

You can select more than one field at a time, or select a portion of a single field entry.

Note:	In Edit mode, if a field is already selected, clicking the same field again enters field view. To exit field view, move off of the field by clicking another field, pressing Tab, or pressing an arrow key.

Selecting multiple fields

To select multiple fields across rows and columns, drag from one corner to the other (be sure you're not in field view). During multiple selection, the pointer appears as a four-headed arrow. Fields selected with this method must be contiguous.

Selecting all records

To select the entire table, choose Edit|Select All. A selection box surrounds the table.

See Also

Using edit mode


Field view

In Edit mode, you can change a field's entry in one of two ways:

Select the field and type a new value. When you begin typing, the new value replaces the old entry.

Select the field and edit the existing entry.

When you revise field entries in Edit mode, you can insert or delete characters without retyping the whole entry. Begin by selecting the field you want to change. Then enter field view in one of the following ways:

Select the field, then click the Field View Speedbar button

Select the field, then press F2.

Select the field, then choose Table|Field View.

Double-click an unselected field (or click a selected field again). This method places the insertion point in the field where you double-click.

Note:	Data you enter into a field must match the field's data type.

When you enter field view, you can edit within a field entry. The insertion point appears at the end of the field or at the place where you clicked.

You can move the insertion point by clicking in the field, pressing editing keys such as Home or End, or pressing arrow keys. Backspace deletes characters to the left of the insertion point, and Del deletes characters to the right. You can also drag in the field to select characters.

When you move off of a field, you exit field view. This happens when you click another field, or press Enter, Tab, or Alt plus an arrow key. To exit field view and remain on the current field, you can click the Field View button, press F2, or choose Table|Field View.

Persistent field view

Unlike field view, which ends as soon as you leave the field you were editing, persistent field view lets you move among fields without leaving field view. In Edit mode, press Ctrl+F2 to enter persistent field view.

In persistent field view you can use Home, End, and the arrow keys just as in standard field view. Press Tab, Enter, or Alt plus an arrow key to move from field to field.

When you first select a field in persistent field view, the entire field is highlighted. Replace mode is still the default mode for data entry, until you click the mouse button (or press an arrow or editing key) to position the insertion point.

To exit persistent field view, press Ctrl+F2.

See Also

Using edit mode


Cutting, copying, and pasting data

In Edit mode, you can cut or copy a value, then paste it into a field. You can also copy blocks of records or portions of records can be copied to the Clipboard, then paste them into other Windows applications.

Note:	Changes you make in Edit mode are posted (saved in the table) when you move off of the record.

You can use the following Edit menu commands to cut, copy, and paste operations:

Command	Operation it performs

Cut	Deletes a single field entry from the table, or any number of whole records, and places the data on the Clipboard.

Copy	Places a duplicate of the selected field entry(s) on the Clipboard. To select only a portion of an entry in a field, first enter field view. To select all entries in a column, double-click the column heading.

Paste	Inserts a single field value from the Clipboard into a selected field in the table. Multiple values on the Clipboard are pasted only into a notebook (or a word-processing application).

Paste Link	Unavailable in the Table window, it inserts a Dynamic Data Exchange (DDE) link from the Clipboard to a field in the query image.

Delete	Removes the selected entry without placing it on the Clipboard.

Select All	Selects all entries in the active table.

Note:	In Edit mode, you can paste a value into a field only when its data type matches the field type.

See Also

Using edit mode


Using undo

In Edit mode, edits are posted (saved in the table) when you move off of a record. To discard any edits and restore the original record, choose Edit|Undo or Record|Cancel Changes before moving off of the record.

After the original record is restored, a message in the status line tells you the changes were discarded.

To discard changes to a single field value and restore the original field entry, press Esc before you move off of the field.

Note:	You can't use Edit|Undo or Record|Cancel Changes to retrieve a Paradox record you've deleted (but you can retrieve a dBASE record).

See Also

Using edit mode


Removing entries from fields

In addition to the Cut command in Edit mode, Database Desktop provides the Delete command to remove the selected entry without placing it on the Clipboard.

To remove a single field entry in Edit mode, select a field, then choose Edit|Delete. (If multiple fields are selected, the command is unavailable.) If field view is also active, you can select specific text, then choose Edit|Delete to remove it.

Note:	Edit|Delete empties the selection.

To remove an entire record (row), select all fields in the record, including the record number, then choose Record|Delete. A dialog box confirms the action; after you choose OK, the record and all field entries in it are removed.

Note:	Record|Delete removes the entire record.

You can use Edit|Delete only on records (rows) or single field entries of the table. You can't delete a field (column) from a table.

See Also

Using edit mode


Inserting and deleting records

In Edit mode, you can insert new blank records into or delete existing records from a table.

Inserting records

Choose Record|Insert (or press Ins) to insert a blank record above the selected record.

When you insert a record into a keyed Paradox table, then enter values into its fields, the record immediately moves to its proper position according to the sort order. Records inserted in nonkeyed tables remain at the positions where they were entered. Records inserted in dBASE tables move to the end of the table.

Note:	In a keyed table, a newly-entered record might vanish from the screen because of its sort order.

In Database Desktop, when you insert a record, then move off of the record without entering a value in one of its fields, the blank record is removed from the table.

Deleting records

Choose Record|Delete (or press Ctrl+Del) to delete the selected record.

When using a Paradox table, be sure you want to delete the entire record before you choose Delete; you can't retrieve a deleted record.

See Also

Using edit mode


Field types

When you work with tables, you enter and edit data in a variety of field types. A field type determines the kind of data a field contains. The following tables list Paradox and dBASE field types.

Paradox field type	Possible values

Alphanumeric	ASCII characters except null (such as letters, numbers, and special symbols like %, &, #, and =), up to a maximum of 255 characters.

Number	Only positive or negative numbers in a range from -10307 to 10307 with 15 significant digits. To enter the value -10307 in scientific notation, type 1e-307; to enter the value 1308, type 1e+308. By default, the format of values in scientific notation is established by the Windows Control Panel and the displayed value is a rounded-off version of the actual stored value.

Currency	Exactly like number fields.

Date	Any valid date from January 1, 100 to December 31, 9999. Entries are checked for validity (leap years and leap centuries are correctly handled). By default their format is established by the Windows Control Panel.

Short number	Only whole numbers (integers) in the range -32,767 to 32,767. Values with decimals or in scientific notation are invalid in this field type.

Memo	Exactly like an alphanumeric field, except that its data is limited only by the storage limits of your system. Although memo field values don't appear in the table, you can perform a query on the contents.

Formatted memo	Similar to a memo field, except that its text can also be formatted. Text attributes (different typefaces, styles, colors, and sizes) and formatting preferences (like tabs, line returns, and justification) are stored with the data.

Binary	Typically, data (such as sound strings) used by application developers and advanced users. This field type is not available for use in Database Desktop.

Graphic	Graphics (pictures), typically created in a painting, drawing, or scanning application. These values do not appear in the table in Database Desktop.

OLE	(Object Linking and Embedding) objects placed in a table from other Windows applications that support OLE as a server. These values don't appear in the table.

dBASE field type	Possible values

Character	Any printable character, including blank spaces. The maximum size for this field type is 254 characters.

Float number	Numeric data in a binary floating-point format. The size of a float number field can be from 1 to 20 digits, which includes the decimal places, decimal point, and sign (if any).

Number	Numeric data stored in a BCD (Binary Coded Decimal) format. The size of a number field is exactly like a float number field, but the precision of calculation is greater.

Date	Any valid date from January 1, 100 to December 31, 9999.

Logical	Values representing True or False (Yes or No) values. The values the field accepts as true and false are determined by the Logical Format property.

Memo	A value too large to be stored in a character field. The contents of memo fields are stored externally to the table. Although the value doesn't appear onscreen, you can use the contents of the field in a query.

See Also

Entering and editing data


Locking records

When you begin editing a record in Edit mode, Database Desktop locks the record. When a record is locked, other users can view it, but can't edit or delete it. A message in the status line tells you when a lock is active. Database Desktop removes the lock and posts, or saves, the record when you move off of the record or turn off Edit mode.

Manually locking records

To manually lock a selected record while in Edit mode, choose Record|Lock (or press F5 ). You might want to manually lock a record when you:

work in a multiuser environment

use different views of the same table in one session

With manual locks, a message in the status line lets you know when a lock is active, and the Lock command is replaced by the Unlock command (Shift+F5). Before other users can access manually locked records, the records must be unlocked.

After you choose Record|Lock, the Record|Post/Keep Locked command (Ctrl+F5) is available on the Record menu. If you use this locking option, the insertion point remains on the record, even if the record is relocated due to the table's sort order. If necessary, your view of the table is updated.

See Also

Entering and editing data


Fields with validity checks

Validity checks are a Paradox feature that prevent data from being entered in a field unless the data meets certain requirements. Validity checks must be defined in Paradox before you can use them in Database Desktop.

Note:	Multiple validity checks--such as a minimum, maximum, and default value--can be active for a single field.

If the value in any field is invalid, an error message appears after Database Desktop attempts to post, or save, the record. An attempt is made to post the record after you try to move off of the record or unlock it. Database Desktop offers the following types of validity checks:

Type	Restriction

Required field	A value must be entered before you can move off of the record.

Minimum value	No value less than the minimum value can be entered.

Maximum value	No value greater than the maximum value can be entered.

Default value	The default value appears when a new blank record is inserted.

Picture	Uses a template to format the data entered into a field. A common picture is (###)###-#### (the standard pattern of phone numbers in the U.S.). When this picture is assigned to a field, only numbers can be entered, not the parentheses or hyphen. For example, when the blank field is selected, the left parenthesis appears immediately and the right parenthesis appears after three numbers (the area code) are typed. The hyphen appears after three more numbers are typed. Invalid characters, such as letters, are ignored and don't appear onscreen.

See Also

Entering and editing data


Looking up table values

A table lookup is a defined relationship between two Paradox tables--a lookup table and the table you're editing. These table relationships must already be defined in Paradox before you can use them in Database Desktop.

Table lookup is a data entry tool that lets you:

enter only valid data in a table

refer to another table to look up the acceptable values for a field

copy values from another table into the table you're editing

For example, if you're not sure how a customer's name is spelled, you can use table lookup to browse in the lookup table. The correct customer name, and corresponding values such as a customer identification number and address, can be copied from the lookup table.

Note:	To use table lookup, it must already be specified for the Paradox tables before running Database Desktop.

See Also

Entering and editing data

Using table lookup

Limiting character sets


Using table lookup

In Paradox, you can assign a table one of two types of table lookups:

Just Current Field checks values you enter in the current field against the values in the first field of the lookup table. If the value is invalid, an error message appears and the value is not entered into the table.

All Corresponding Fields does what Just Current Field does, and also fills in values from corresponding fields in the lookup table. Corresponding fields are fields that match both in field name and in type.

Each type of table lookup has two options:

Fill No Help keeps the lookup table from appearing, so you can't validate an entry.

Help and Fill lets you view the lookup table from the table you're editing, by pressing Ctrl+Spacebar or choosing Record|Lookup Help.

The following table lists the ways you can combine lookup types with options:

Lookup type	Option	Description

Just Current Field	Fill No Help	When you enter valid data (that exists in the lookup table) into a field of the table you're editing, the data is accepted in that field. Otherwise, an error message appears. The lookup table is protected and cannot be viewed during editing.

Just Current Field	Help and Fill	When the pointer is in a lookup field, a message in the status line tells you which keys to press to view the lookup table. To view the values in the lookup table, press Ctrl+Spacebar. When the lookup table appears, a scroll lock is placed to the right of the lookup field. As you browse the fields in the lookup table, the values in the first field remain onscreen. To copy a value from the lookup table, select it, then press Ctrl+Spacebar. Or, type the value into the field.

All Corresponding Fields	Fill No Help	Same as the Just Current Field type with the Fill No Help option, except that all values (instead of a single value) from the lookup table fields have the same field name and type as fields in the table you're editing are copied to the table you're editing .

All Corresponding Fields	Help and Fill	You can enter data into a field by typing it in, but no corresponding values will be filled in. To display the lookup table, press Ctrl+Spacebar. When you choose the value you want, it and all corresponding field values are copied from the lookup table to the table you're editing.

See Also

Limiting character sets

Looking up table values


Limiting character sets

By default, Database Desktop (and Windows) uses the ANSI character set to display characters onscreen. Paradox for DOS uses the OEM character set. While these sets have most characters in common, some characters are only one set or the other.

If you try to view a table that contains OEM characters that don't exist in the ANSI set, or if you type ANSI characters into a table created with the OEM set, some characters may not appear as expected.

To prevent such surprises, you can choose Table|Strict Translation. This command limits the characters used by Database Desktop to those both in the OEM and ANSI character sets.

With Strict Translation on, when you type a character outside of the OEM character set, Database Desktop considers it an error and won't let you leave the field. Also, when you start to edit a field, Database Desktop warns you if the field contains OEM characters.

See Also

Using table lookup

Looking up table values