All,

(hey, everyone is urged to take a stab at this, but note that this is VFP3 and Outer Joins are simulated...and slower)

I have two tables and need to extract information from both to create table three.

Examples of the two data tables:

TABLE A (both fields are always populated, ID serves as a key)

ID POLICY

1 111111

2 222222

3 333333

4 444444

5 555555

TABLE B (answer field is a MEMO and may be blank; Question may be blank, ID is never blank)

ID QUESTION ANSWER

1 1 Bob

1 2

2 1

2 2

3

3 2 Alaska

4 1 Larry

4 2 Texas

5 1 Joe

5 2 Virginia

The desired result:

TABLE C

POLICY CLIENTNAME STATE ID

111111 Bob 1

222222 2

333333 Alaska 3

444444 Larry Texas 4

555555 Joe Virginia 5

Description of TABLE C:

Include one record for ALL Policies from Table A whether there exists a record in TABLE B or not.

Include only one record for each Policy from Table A.

Include ID and POLICY in each record, but CLIENTNAME (answer to Question 1) and STATE (answer to Q 2) may be blank.

My understanding:

I don't think this can be done without three to four steps. Because this is VFP3, I am using a simulated OUTER join. To keep this message short, I am not including code samples, but can do so easily. While the memo field is sure to contribute to some performance degradation, it is necessary due to some answers being free-form, unlimited length (such as a COMMENTS field). The 255 character limit was unacceptable for this application.

Attempts so far:

I've got indexes on all fields. I've tried with and without DELETED tags. The tables have virtually no deleted records due to a record-recycling approach I've built in.

The problem:

The above task is taking 3 to 4 minutes over a network with 30 to 40 users with tables that only have several thousand records. I need to get it down into the seconds (like 15 to 30).

Question(s):

Can this be done in one step? Is the problem the way the tables are set up?

HELP!

Michael Copeland

Genesis Group Software

mailto:mike.copeland@genesis-group.net