Sorting Dates with over laping date ranges 1

[WOWO10]

I have a table with the following
Begin_Date, End_Date, Process_Date and Status

I want to pic out the most current status for every date range. Some of the date ranges overlap.
Any Ideas where to start?

 

[VicM]

WOWO10:

Need a bit more info. Are the dates always present in the table, or can some be null. For instance, does the End_Date not get input until the process is over? Can the Process_Date be null until the process has started?

What range are you talking about: Begin_Date to End_Date;
Begin_Date to Process_Date; Process_Date to End_Date?

Vic

 

[WOWO10]

Thanks for replying, I hope this will clearify it. For this question none of the dates contain null values.
The date range that I am looking at is Begin_Date Thru End_Date.
Process_Date is just the date the status was determined.(Time Stamp)

 

[VicM]

WOWO10:

Correct me if I'm wrong, but wouldn't the most recent Process_Date be the determining factor here?

If your Process_Date is a timestamp for the status, then when the status is recorded the Process_Date is set to the current date. So when the process is completed, it might be before, on, or after the end date. But the process date would still be the determining factor, because when the status is changed to complete, the Process_Date is set to current, regardless of the End_Date.

Does your input overwrite the Process_Date and Status for a given record, each time it's updated? Or do you keep historical records of when each Status level was reached?

Sorry for asking more questions. I should have been more thorough the first time.

Could you give examples of the records you're dealing with? That may avoid the need to keep asking questions.

Vic

 

[WOWO10]

Good Question......I think some data would help explain my dilema.

Person Begin END Status Process
John Smith 01/01/00 01/31/00 OPEN 02/02/02
John Smith 01/01/00 01/31/00 Suspend 02/03/02
John Smith 01/15/00 02/28/00 CLOSED 03/03/00

Off this I would want to create a table with the following
John Smith 01/01/00 01/15/00 Suspend 02/03/02
John Smith 01/15/00 02/28/00 Closed 03/03/00

You were correct with the assumption of having a to look at historical data.

 

[VicM]

WOWO10:

I'm assuming that the table containing your data has an autonumber field as the Primary Key. But even if it doesn't, the following query should work. I would suggest testing it with various combinations of Process_Date and Status.

After changing the table name and field names to your data, copy the following code into the SQL window of a new query.

Test is my table name. The field names after the AS keyword can be anything you like. However, they CANNOT be the same name as the field in the table. Access will complain bitterly.

SELECT Last(Test.ID) AS LastOfID, Test.Name, Test.Begin_Date, Test.End_Date, Max(Test.Process_Date) AS MaxOfProcess_Date, Last(Test.Status) AS LastOfStatus
FROM Test
GROUP BY Test.Name, Test.Begin_Date, Test.End_Date
ORDER BY Max(Test.Process_Date);

Hope this helps,

Vic