>Thanks to all for your replies. It seems that these queries work (even the duration) as long as I specify a status to look for and if that status cannot be duplicated. For example, given the data
>

>consumer id     status       date
>  1            applicant     01/01/2001
>  1            active        01/15/2001
>  1            suspend       03/01/2001
>  1            resume        04/01/2001
>

>
>If my select specifies status = 'suspend', I correctly get the begin and end dates, the next status and the duration. This works so long as a status cannot be duplicated (applicant, active) but not if it could be duplicated (suspend, resume). It would be nice to be able to figure out the duration of each status (perhaps add a field to the table for end date), or a given status (status and date).
>
>Thanks for your help.
>
>John
>
>>
>>You can join the table to itself and calculate the MIN date that's greater than the current date (part of the JOIN condition). That'll give you your two dates. You can't calculate the number of days in the same SQL. You can do that in another view or in the form or report or whatever.
>>
>>select a.customer_id,;
>> a.status as beginstatus,;
>> a.denddate as dbegindate,;
>> b.status as nextstatus,;
>> min(b.denddate) as denddate;
>>from customer_Status a JOIN customer_Status b;
>> ON a.customer_id=b.customer_id and;
>> b.denddate>a.denddate;
>>GROUP BY 1 , 2

Will adding a group on Status work?