>Hi everyone,
>I was wondering if somone can help me with the code that I need for a form. I have two tables where the relationship between the two is the employee number and I would like for all records that do not have "0" for the value of one particular field to be updated according to the corresponding employee number and date from the other table.
>
>i.e.
>
>Employee Table1
>+---------+-------------+-------------+
>| emp_num | Commission | RptDate |
>---------------------------------------
> 5 8.00 10/15/01
> 7 0.00 10/15/01
> 9 4.00 10/12/01
>
>Employee Table2
>+----------+-------------+-------------+
>| emp_num | Commission | RptDate |
>----------------------------------------
> 5 0.00 10/15/01
> 7 0.00 10/15/01
> 9 0.00 10/15/01
> 12 0.00 10/15/01
>
>So I would want only records that match the date I specify (in this case 10/15/01) to be updated so the commission for Table 2 employee number 5 should be the only field updated to say 8.00
>The field for the commission for table2 may have values other than "0.00" but the record should only be updated to Table1's commission field value if the dates and employee number match as there will be duplicate employee numbers but distinct dates for each.
>
>What select statement will accomplish this?
>
>Thanks in advance!
>Max

I would use the SQL command UPDATE, combined with my personal lookup function, which I include here.

**********************************************************************
FUNCTION MyLookup(tcAlias, tcOrder, txSeekValue, tcReturnValue, tlEmptyValues)
	* Search in another table. This function solves certain problems with VFP-relations
	* (the problem is that the user only sees new values after a TableUpdate()).
	* The parameter tcReturnValue should be passed as a string that will be evaluated
	* in the function.
	* If the fifth parameter is .T., the function returns an empty field (rather than null)
	*   if the record is not found.
	* For fast re-opening, the table is opened with a special alias, and remains open.

	local lnSelect, llFound, lcAlias
	lnSelect = select()
	lcAlias = tcAlias + "_mylookup"
	if not used(lcAlias)
		use (tcAlias) again alias (lcAlias) in 0
	endif
	select (lcAlias)
	set order to (tcOrder)
	llFound = iif(isnull(txSeekValue), .F., seek(txSeekValue))
	local lxReturnValue
	lxReturnValue = eval(tcReturnValue)	&& This will assign correct type, even if not found
										&& (and final result is NULL)
	if not (llFound or tlEmptyValues)
		lxReturnValue = NULL
	endif
	select (lnSelect)
	return lxReturnValue
ENDFUNC