I had 2 of the 4 indexes you suggested. After adding the other 2 it still is slow. By slow I mean the value has to be determined for each record in a 300 record report. It takes a couple of minutes to generate the last page of the report (100 pages total). Its tolerable when I only need one business, but running the whole batch takes a while.


>Do you have an index on PEI.PermId, PEI._Year, History.PermId and History._Year?
>
>>I have an application that includes data including the number of employees at a business. The number of employees is reported twice per year and eachreport is maintained in different tables. Each of these 2 tables contains multiple businesses and multiple years. The challenge is to obtain the most recent employee number for the business. The logic goes something like this:
>>1. Look in table1 (pei) for this business and select the employee number for the current year.
>>2. If the number thus obtained is 0, look in table2 (history) for this business and select the employee number for the current year.
>>3. If the number thus obtained is still 0, go back to table1 and select the employee number for this business for the previous year.
>>
>>I have written the following function to perform this task which works, but it is slow. Does anyone have any suggestions on how to speed this up, OR is there a totally different approach to the task?
>>
>>

>>function GetRecentFTE()
>>	lparameters lcPermID
>>	local closeit, lnFTE, lnFTE2, lnOldWorkArea
>>	lnOldWorkArea = select()
>>	closeit = used('pei')
>>	select (fulltime + parttime) as tFTE ;
>>		from new_pe_pei_database!pei ;
>>		where PermId = lcPermID and _year <= year(date()) ;
>>		order by _year ;
>>		into cursor crsTempFTE
>>	if _tally > 0				&&	If I found at least 1 record
>>		go bott
>>		lnFTE = tFTE
>>		if ! bof()
>>			skip -1
>>		endif
>>		lnFTE2 = tFTE
>>		use in crsTempFTE
>>		if ! closeit
>>			use in pei
>>		endif
>>	else
>>		lnFTE = 0
>>		lnFTE2= 0
>>	endif
>>	closeit = used('history')
>>	if lnFTE = 0				&&	There was no data in PEI so ck the survey history
>>		select surveys_distributed ;
>>			from newsurveys!history ;
>>			where PermId = lcPermID and _year <= year(date()) ;
>>			order by _year ;
>>			into cursor crsTempFTE
>>		if _tally > 0
>>			go bott
>>			lnFTE = surveys_distributed
>>		else
>>			lnFTE = 0
>>		endif
>>		use in crsTempFTE
>>		if ! closeit
>>			use in history
>>		endif
>>	endif
>>	select(lnOldWorkArea)
>>	if lnFTE > 0
>>		return lnFTE
>>	else
>>		return lnFTE2
>>	endif
>>