>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?
>
>Thanks for any thoughts.
>
>
>

>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
>

Hello Don,

Can you do you order by _year desceding order that way what you want is at the top of the table and you don't have to do a go bott which if the file is large could take time.
Just a thought, don't know if it will work or not.

Tim