I know, but I'm working with tables and indices from an OLD system that it took 10 years to fully finish and implement as is and I'm converting it to VFP over a period (long one) of time. It is in use as it is converted so it is a mixture of FPD26 (some not very well organized) and VFP LIVE. I have to work within the constraints of the system as it is or else it will take me 10 years to find all of the places I need to change it! :o) Actually, whenever I can, I do exactly that. However, in this case, 3 other apps (FPD26, VFP, and Delphi) use the SAME tables with the same indices.. Sheesh...crazy I know... :o)
Tracy

>

>select cName from Table1 inner join TableB on upper(alltrim(Table1.cName))=upper(alltrim(Table2.cName)) where Table2.LogField = .t.

>
>BTW, strange choice - why do you want alltrim in index? It's not a good idea...
>
>>I'm stumped! I've been looking at this too long I think. I am trying to create a temporary cursor of only those records in table (a) that have a record in table (b) AND whose table (b) field fontreq value =.t.
>>
>>Here is the structure/relationship:
>>
>>lcontrol table (a)
>>cn_name c(10) *--tag is name and is on UPPER(ALLTRIm(cn_name))
>>
>>ctrlset table (b)
>>ctrlname c(10) *--tag is ctrlname and is on UPPER(ALLTRIM(ctrlname))
>>fontreq L *--always .t. or .f. tag is fontreq
>>
>>lcontrol.cn_name has the same value as its related record in ctrlset.ctrlname
>>
>>Again, I want to create a temporary cursor that contains the field cn_name from the lcontrol table but it should only contain records that have a matching record in ctrlset *AND* whose fontreq field is .T.
>>
>>Here is what I've tried:
>>
>>Example 1:
>>

>>SELECT cn_name FROM lcontrol x WHERE ;
>>   EXISTS (SELECT * FROM ctrlset y WHERE x.cn_name = y.ctrlname)
>>

>>
>>The result from example 1 is that I get ALL records from the lcontrol table for some reason
>>
>>Example 2:
>>

>>SELECT cn_name FROM lcontrol x WHERE ;
>>   EXISTS (SELECT * FROM ctrlset y WHERE UPPER(ALLTRIM(x.cn_name)) = UPPER(ALLTRIM(y.ctrlname)))
>>

>>The result from example 2 is that I get an error message "ERROR CORRELATING FIELDS"
>>
>>If I were to do this using filters, it works correctly doing this:
>>
>>

>>SELE lcontrol
>>SET ORDER TO TAG NAME    && tag is ALLTRIM(UPPER(cn_name))
>>SELE ctrlset
>>SET ORDER TO TAG CTRLNAME    && tag is ALLTRIM(UPPER(ctrlname))
>>SET FILTER TO ctrlset.fontreq=.T.
>>SELE lcontrol
>>SET RELATION TO cn_name INTO ctrlset
>>SET FILTER TO FOUND('ctrlset')
>>

>>
>>This shows the correct records, but I need a select statement so I can have a temporary cursor to use and also not disrupt the dataenvironment (relationships) already in place.
>>
>>I've tried a BUNCH of other SQL statements, but I think I've just looked at this too much and its all blurry!!!!
>>
>>Any ideas?
>>THANKS in advance,
>>Tracy