I seldom use start and stop dates. I use only the start dates, and use the power of SELECT to give me the stops. I have worked with payroll a fair bit, and the effective date is
(MAX(EffDate)) FROM ... WHERE EffDate <= WorkDate In the case you are looking for, I would use something along the lines of SELECT t1.CaseNo, MAX(t1.ClientName), MAX(t1.Server), t1.OpenDate, + (MIN(t2.OpenDate)) AS ClsoeDate + FROM CaseTable t1, CaseTable t2 + WHERE t1.CaseNo = t2.CaseNo AND t2.OpenDate > t1.OpenDate + GROUP BY t1.CaseNo, t1.OpenDate The two MAX() are just to eliminate the comparisons for the GROUP BY I hope this helps! --- gries <[EMAIL PROTECTED]> wrote: > Hello everyone, > > I have a table that has the following columns; caseno, clientname, > server, opendate. > > The data consists of all clients and their assignments to a therapist > along with the date of the assignment. If a client was ever > reassigned to a new therapist then there would be multiple records > for that client. > > What I need to do is to add a column (closedate) to the table and set > the column to equal the open date of the previous record if there are > multiple records for that client, thus giving me a close date for > that record. > > I need to leave those without multiple records blank. > > The table is sorted by clientname, opendate asc. > > Thanks for your help. I miss you all! > > Ron > > Albert Berry Management Consultant RR2 - 1252 Ponderosa Drive Sparwood BC, V0B 2G2 Canada (250) 425-5806 (250) 425-7259 (708) 575-3952 (fax) [EMAIL PROTECTED]
