Join

Applies to:

VAccess

Description

Restricts the records available to the control, and maintains relational positioning in the associated table relative to field values of another VAccess control.

Changing the IndexNumber or TableName property will erase the current join.

Syntax

object.Join = "VAccessName:joinedField
[,joinedField]"

The Join property syntax has these parts:


Part	Description
object	Name of the VAccess control to join. Records available to this control will be a subset of all of the records in the file associated with the control, limited by the criteria in the Join property of the control.
VAccessName	Name of the VAccess control which will serve as the master control data source for linking object.
joinedField	Name of a field or fields in the control referenced by VAccessName. Separate the names of multiple fields with commas. Each field named must be of identical storage type and length to the corresponding index segment of the selected index for object, as specified in the IndexNumber property.

Remarks

The Join property may be used to automatically synchronize related data files in one-to-one or one-to-many relationships.

VAccess controls may be joined together hierarchically by referencing index segments from the current index of one file to fields within another file. The Join property accomplishes this quickly, and automatically keeps the relationships updated during record navigation on any of the joined files.

To join one VAccess control to another, simply specify which index to use in the IndexNumber property, specify the name of the master VAccess control in the Join property, followed by a colon, followed by the names of the fields in the master control which will correspond exactly to values in the selected index of the joined control.

Example

Suppose you have four tables:

A table called Customers containing information about your customers, their names, addresses, phone numbers, and company codes, indexed by company name,

A table called Orders containing order header information fields such as CompanyCode, OrderNumber, OrderDate, TotalAmount, ShippingDate, etc, indexed by CompanyCode by OrderDate,

A table called OrderLineItems containing fields such as OrderNumber, LineNumber, Quantity, and ProductCode, indexed by OrderNumber by LineNumber,

And a table called Products containing ProductCode, Description, and Price, indexed by ProductCode.

To get a snapshot of an order beginning with a company name, join Orders to Customers by CompanyCode, join OrderLineItems to Orders by OrderNumber, and Products to Items by ProductCode (see Figure 6-2). Getting a Customer by company name will fetch all of that customer's orders, all line items for each order selected, and the correct product information for any selected line item. Choosing a different order or a different line item will re-synchronize the data sets of all of the subsequent joined controls.

Figure 6-2 Example of Join Tables