ReportSmith 3.0 Linking

Please help...
 
I am using ReportSmith 3.0 and I am having problems
with a master/detail relationship.
 
I am using 3 Paradox tables:
 
BM - Broker Master file (Main table for report).
PT - Policy transaction (Detail file) - Index Broker Code.
PM - Policy Master file (Lookup File) - Index Policy Number.
 
I have linked the first two tables.
 
BM->Broker Code -------> PT->Broker Code
 
This works great, however I need to relate each PT record
to the last file PM to pick up names and addresses.
(The PM file is a large database +- 100 000 records!)
 
The link is a follows:
 
PT->Policy Number --------> PM->Policy Number
 
When I do this I have to wait for ages while it sorts
through what seems like the whole PM file (15-20mins)
this is unacceptable.  Do you know how I might
speed up the process or create a better link.
 
The PM file does have an index on the Policy Number field.
What I need is a one to one lookup on the Policy Number.
Some PT records may not have a Policy Number in which
case I want it to return a blank.
 
Thanking you in advance for your help.
 
Ian Hopewell
Hopewell and Associates
Business Information Systems.
 
e-mail: ianah@iaccess.za
Tel: 27-31-2075059
Fax: 27-31-2074868
IanahAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hpiersonCommented:
If you are good with SQL, you could press the EDIT SQL button and try to improve on Reportsmith's SQL, perhaps using a nested SELECT statement.

Alternatively, if the PT table is relatively small (lets say a few hundred to even a couple of thousand records), you may obtain an improvement by changing the report to use only two tables, the first being BM, and the second a table which you populate before running the report, which includes the fields from the PT table, plus the fields you need from PM table. Set a tcursor on the PM table, set its index to Policy number. Build the new table record by record by going through the PT table, and use findkey on the indexed cursor on the PM table to find the right record in the PM table. The report should then have very little work to do, since it will not involve the PM table at all.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.