compare a datafield with the one before and after in same columns

[itsalesguru]

have a employee database table with four columns
1. LastName
2. FirstName
3.employeeID
4.DocumentType

So each employee is one or more times in the table, but not all the rows have the Employee ID in them.
I want to find the empty field, then check the Lastname and firstname and compare it with immediatly the above row and below row, if find at least one match then copy the employee ID form matched row to the empty filed??
table has 80,000 entries!

 

[Scooterbug]

You can use an update query. Just set the WHERE criteria so that LastName = LastName and FirstName = FirstName. Also set the criteria for employeeID to display only null fields.

Note that there will be problems if you have ppl with both the same first name and last name...for example if you have 2 Bob Smiths, then there is no way to determine who is who. Also note that if EmployeeID is an autonumber and is set to be unique you are going to have to create a new field and store the ID number in there.

I dont know if you designed this database or not, but if you have the reoccuring data like you describe, then you have a normalization issue. Each Employee should only be in the table once and their EmployeeID should be a unique number.

 

[itsalesguru]

thank you for response, but I am not sure how this solve the problem? in update query lastname=lastname, firstname=firsname and criteria is ofr employeeID=null, then employee ID filed would be updated with null value? am i missing something from your answere?

employeeID's are not autonumber, and No I did not design this database, trying to get to a point that you described at the last part.

 

[ByteMyzer]

You might try something like the following (substitute highlighted text with actual table/field names):

UPDATE [B][I]MyTable[/I][/B] T1
INNER JOIN [B][I]MyTable[/I][/B] T2 ON T1.[B][I]LastName[/I][/B] = T2.[B][I]LastName[/I][/B] AND
                         T1.[B][I]FirstName[/I][/B] = T2.[B][I]FirstName[/I][/B]
SET T1.[B][I]employeeID[/I][/B] = T2.[B][I]employeeID[/I][/B]
WHERE T1.[B][I]employeeID[/I][/B] Is Null
AND T2.[B][I]employeeID[/I][/B] Is Not Null;