I have a file that has an identity column for uniqueness plus a bunch of other columns: employee, time, date among them. I want to locate a specific record and then find the prior record equal to a key value such as

id emp time date
1001 1111 9:05 05-16-2012
1002 2222 9:10 05-17-2012
1003 3333 9:11 05-17-2012
1004 3333 10:15 05-17-2012
1005 1111 10:20 05-17-2012

If I selected a record like so

Select id from tblTimes where emp = '1111' and date = '05-17-2012'
order by emp, date, time

I want to be able to scroll back one record for employee 1111 which would give me the record:

1001 1111 9:05 05-16-2012

I think I want to use a scrollable cursor but I couldn't find a satisfactory example. Has anyone done something like this before? All I have been able to find is examples where positioning is done by relative row numbers but I want to position by finding a value in the recordset and then doing a read on the previous record. I could loop through them until I find the values and then read back one record but there are thousands of records in the table so I'd like to just set the cursor at the record I am looking for and then moving the cursor back one row.

Something like this pseudo code :

Select id from tblTimes where emp = '1111'
order by emp, date, time

Set cursor where date = '05-17-2012'

read prior record

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2021 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.