Dan,

The code you wrote doesn't seem to correspond to the description of the problem.

You want to select records for the current year first, right? So, may be you need to change your where clause in the first select to _year = year(date())

Then check history.

Then decrement one year and proceed.

E.g. you would have a recursive function.

Also you need to have an index on _year and PermID.

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