Merging records using a criteria

[adrianolsantos]

Hi

I don't have much experience with programming but I amm trying to build a MsAccess database to help with my work.

The problem I have is that, based on some parameters, I need to merge some records in a table. For example:

DATE | WorkOrder | StartTime | FinishTime
01/01/2000 | 100 | 08:00 | 10:00
01/01/2000 | 100 | 10:45 | 12:00
01/01/2000 ! 100 ! 17:30 | 19:00

What happens is, for the same work order, in the same date, if the time off between 2 shifts is smaller than 4 hours, I need to merge both records using the StartTime of the first record and the FinishTime of the next record as the new start and finish times as below:

DATE | WorkOrder | StartTime | FinishTime
01/01/2000 | 100 | 08:00 | 12:00
01/01/2000 ! 100 ! 17:30 | 19:00

Is it possible to be done on MsAccess? How?

Thanks for your help

Adriano

[cactusdata]

You can either just loop the records of a recordset and check finish and start times; or you can use this fancy query having two subqueries:

SELECT
    W.Date, 
    W.WorkOrder, 
    MIN(W.StartTime) As BeginTime,
    W.EndTime
FROM
    (SELECT 
        WorkOrderTime.Date, 
        WorkOrderTime.WorkOrder, 
        WorkOrderTime.StartTime, 
        (Select 
            Max(T.FinishTime) 
            From [WorkOrderTime] As T 
            Where 
                T.Date = [WorkOrderTime].Date And 
                T.WorkOrder = [WorkOrderTime].[WorkOrder] And 
                T.StartTime < DateAdd("h", 4, [WorkOrderTime].[FinishTime])) AS EndTime
    FROM 
        WorkOrderTime) As W
GROUP BY
    W.Date, 
    W.WorkOrder, 
    W.EndTime

Sample data:

Output:

[adrianolsantos]

It Works !!!

Thanks for your help

[Rabbit]

This isn't quite right btw, if you move up that forth record to 16:30, it should get collapsed but it wouldn't.

[cactusdata]

Yes, it needs a little adjustment.

Create this query:

SELECT 
    WorkOrderTime.Date, 
    WorkOrderTime.WorkOrder, 
    WorkOrderTime.StartTime, 
    Null As FinishTime
FROM 
    WorkOrderTime
WHERE 
    ((Select Max(T.FinishTime) 
    From WorkOrderTime As T 
    Where 
        T.Date = WorkOrderTime.Date And 
        T.StartTime < WorkOrderTime.StartTime)) Is Null 
        Or 
        ((Select Max(T.FinishTime) 
        From WorkOrderTime As T 
        Where 
            T.Date = WorkOrderTime.Date And 
            T.StartTime < WorkOrderTime.StartTime)) < DateAdd("h",-4,[StartTime])
 
UNION ALL
 
SELECT 
    WorkOrderTime.Date, 
    WorkOrderTime.WorkOrder, 
    Null As StartTime, 
    WorkOrderTime.FinishTime
FROM 
    WorkOrderTime
WHERE 
    ((Select Min(T.StartTime) 
    From WorkOrderTime As T 
    Where 
        T.Date = WorkOrderTime.Date And 
        T.StartTime > WorkOrderTime.StartTime)) Is Null 
        Or 
        ((Select Min(T.StartTime) 
        From WorkOrderTime As T 
        Where 
            T.Date = WorkOrderTime.Date And 
            T.StartTime > WorkOrderTime.StartTime)) > DateAdd("h",4,[FinishTime]);

Save it as Q1. Then create a final query:

SELECT 
    Q1.Date, 
    Q1.WorkOrder, 
    Q1.StartTime, 
    (Select Min(T.FinishTime) 
    From Q1 As T 
    Where T.FinishTime > Q1.StartTime) AS EndTime
FROM 
    Q1
WHERE 
    Q1.StartTime Is Not Null
ORDER BY 
    Q1.Date, 
    Q1.WorkOrder, 
    Q1.StartTime;

Output:

Date    WorkOrder    StartTime    EndTime
2020-01-01    100    08:00:00    13:00:00
2020-01-01    100    17:30:00    19:00:00
2020-01-01    100    23:15:00    23:45:00

or, when changing 17.30 to 16.30:

Date    WorkOrder    StartTime    EndTime
2020-01-01    100    08:00:00    19:00:00
2020-01-01    100    23:15:00    23:45:00