>>But it seems I can't work another outer join to gather the rest of the information (namely the lawyer and codes information. Barring writing routines into the deletion functions of the lawyer and codes file to prevent deletions of they're used (the client wants the luxury to delete former or deceased lawyers and inactive counties without losing integrity), does anyone know how to put more than one outer join into a SELECT - SQL command?
>>
>>The manual and help file, as well as the MSKB, only show how to use one join per command.
>>
>>Thanks in advance,
>>JR
>There's really no way to delete all those without losing integrity. There needs to be one main table from which all files are linked. If you delete parent records, you are going to leave orphans - that's not good. You need to write functionality into the deletion of the records to check for that case, and not allow it. IE, do not delete a lawyer if he has clients - instead, assign them a new lawyer?
>
>Wayne

That wouldn't be enough - the previous lawyer _was_ on the case, wasn't he? AFAIK the lawyers need lots of history. The solution would be to restore the deleted lawyers/counties from any backup available, and mark the records as inactive (filtering them from views etc, but not deleting them).

Until this is done, John may issue his SQL without lawyer/county info, just like this:

SELECT Clients.cLname, space(60) as lLname, Case.caRetDate,
Case.caTrialDat, space(80) as Descr, Case.caLawyerID,Case.caTrialLocID ;
FROM Case, Clients;
WHERE Clients.CustID =Case.caCustID AND
Case.caTrialLocID = lcTrialLocId ;
INTO CURSOR tCase
sele 0
use (dbf("tCase")) again alias cCase

use lawyers in 0 orde 1
set rela to caLawyerID into lawyers
replace all lLname with lawyers.lLname
use in lawyers
use codes in 0 orde 1
... the same story as with lawyers...
inde on lLname tag lLname

And yes, for any deleted lawyers and counties there'll be a blank name.
Another issue - making SQL selects on something's name is not safe. Having a WHERE clause on a string literal/var containing a name may lead to unwanted results. Imagine you had three counties, "Hell", "Hell East", "Hell Outa Here". Having a

WHERE Clients.CustID =Case.caCustID AND
Lawyer.LawID =Case.caLawyerID AND ;
Codes.Fld =[Hell] AND Codes.Code =Case.caTrialLocID ;

would not give you Hell, but all three of them. Last time I based something on names of the places or people was back in 1986, and I still remember the trouble I had with it. Using just codes is safer and precise - the user, of course, doesn't have to know them, that's what the lookups are for. In this case, they wouldn't be able to pick a county they've deleted, and they'd very soon understand why deletion in lookups is not healthy :)