Calculation on Previous Record

[haggama]

Hi, I'm wondering if anyone can help me with this query. I've had a look at some of the previous posts under previous record, but don't seem to be able to get to the bottom of it:

I have a set of data on employees who have all had one or more financial searches done on them and I want to return a field based on the previous record of that employee.

My data is as follows

Search ID----Employee ID-----Search Result

1-----001-----P
2-----002-----FCCJ
3-----002-----FCCB
4-----003-----P

I would like an extra field that looks at the previous row, decides whether or not the previous row is the same employee id and if it is returns "Same" and if it isn't returns "Different"

Thanks

 

[Sammy B]

Bring in all your fields into a query and then in a new query column type the following:
Test:IIf(Val(EmployeeID)=searchID,"Same","Different")

Hope this works for you.

Sam

 

[haggama]

Hi Sam, thanks for this, however this will compare the values in the same row, I really want to compare the employee id in the current row against the employee id in the previous row

Thanks

 

[Sammy B]

Could you tell me the reason you need to search the previous row? I ask because depending on what it is you are trying to do there may be a better way. In any case I do not have the answer to the question as you lay it out.

Sam

 

[haggama]

Sammy, the reason I need to look at the row above is that I want to have a query that returns one row per employee id with an additional field that would for example tell me if they have had more than one search on them

Thanks