Parameterize trip count in a select VBA routine

Question

I would like to get the following select, for so I will provide the example below, remembering that I use the VBA of Access 2007.

Goal: Count the journeys made by the fleet vehicles by Line / Direction. Taking into account the following parameters:

• 1º The vehicle must be in the same line and direction - with users systematically embarking with a few minutes, or seconds.
• 2º Being in the same line and direction and find that a user embarked with a difference of 30 minutes from the previous one, consider a new trip adopting the same parameters described in item 1.
• 3º Adopt the same procedures described in items 1 and 2 if at any time observe line change and direction.

Practical example:

veiculo sentido linha       data/hora 
91        Ida     9b  31.03.2014 06:00:25
91        Ida     9b  31.03.2014 06:01:32
91        Ida     9b  31.03.2014 06:02:42
91        Ida     9b  31.03.2014 06:40:45
91        Ida     9b  31.03.2014 06:41:55
91        Volta   9b  31:03.2014 06:45:55
91        Ida     9b  31.03.2014 07:01:55
91        Ida    33A  31.03.2014 07:30:00
91        Ida    33A  31.03.2014 07:30:55

Expected result for the above example, and according to the given parameters, would be:

CONTAGEM DE VIAGENS FINAL PARA O VEICULO 91

VEICULO SENTIDO LINHA      INICIO                FIM                 VIAGEM
91        Ida     9b   31.03.2014 06:00:25    31.03.2014 06:02:42       1
91        Ida     9b   31.03.2014 06:40:45    31.03.2014 06:41:55       2
91        Volta   9b   31.03.2014 06:45:55    31.03.2014 06:45:55       3
91        Ida     9b   31.03.2014 07:01:55    31.03.2014 07:01:55       5
91        Ida    33A   31.03.2014 07:30:00    31.03.2014 07:30:55       6  

Therefore:
Vehicle 91 made on 31.03.2014 6 trips distributed as above: Of course the above example is well summarized, because in reality there are thousands of users and several vehicles...

Answer 1

The problem can be solved with 3 queries/steps

Step 1 explained:

SELECT * INTO TEMP1 --Crio uma tabela temporária através da consulta
FROM (SELECT 
        *,  
        IIF( --Teste logico para verificar se é final de viagem
            DateDiff('s',T1.Data, 

                                    (
                                            SELECT 
                                            -- Aqui eu faço uma esperteza, se por acaso não existir data (null), trago a data da propria linha
                                            -- Desta forma quando for nulo, o datediff será igual a zero

                                            IIF(MIN(T2.Data) Is Null, T1.Data, MIN(T2.Data)) 
                                        FROM Plan1 T2  
                                        WHERE 
                                            T2.Cod_Veiculo = T1.Cod_Veiculo 
                                            AND T2.Cod_Linha = T1.Cod_Linha 
                                            AND T1.Direcao = T2.Direcao 
                                            AND T2.Data > T1.Data 
                                            AND DateDiff('s',T1.Data, T2.Data) <= 1800
                                    ) --Procuro pela menor data existente na tabela para o mesmo veículo, direcao e linha onde a 
                                      -- data é maior que a atual e a diferenca em segundos seja menor que 1800 (30 min)


            -- Se nao encontrar nenhuma data posterior (Retornar nulo e Datediff = 0), o campo retorna verdadeiro
            ) = 0, True, False 
        ) As FimDaViagem
        ,  

        --Teste logico para verificar se é início de viagem
        IIF(

            DateDiff('s',T1.Data, 
                                    (
                                        SELECT 

                                            -- Aqui eu faço uma esperteza, se por acaso não existir data (null), trago a data da propria linha
                                            -- Desta forma quando for nulo, o datediff será igual a zero

                                            IIF(MAX(T2.Data) Is Null, T1.Data, MAX(T2.Data)) 
                                        FROM Plan1 T2  
                                        WHERE 
                                            T2.Cod_Veiculo = T1.Cod_Veiculo 
                                            AND T2.Cod_Linha = T1.Cod_Linha 
                                            AND T1.Direcao = T2.Direcao 
                                            AND T2.Data < T1.Data 
                                            AND DateDiff('s',T2.Data, T1.Data) < 1800

                                            --Procuro pela maior data existente na tabela para o mesmo veículo, direcao e linha onde a 
                                            -- data é menor que a atual e diferenca em segundos desta data seja menor que 1800 (30 min)
                                    )
            ) = 0, True, False
        ) As InicioDaViagem
    FROM 
        Plan1 T1
)  AS TT1

-- Na tabela temporaria TEMP1 insiro apenas as pontas de inicio e fim de viagem identificadas
WHERE TT1.FimDaViagem = True OR TT1.InicioDaViagem = True

-- Ordenaçao padrao por veículo e data
ORDER BY TT1.Cod_Veiculo, TT1.Data;

Step 2 - Explained

SELECT 
(
    SELECT 
        Count(*) -- Numero de Flags de FimDeViagens/InicioDeViagens que acontecerem a partir da data do registro atual
    FROM 
        TEMP1 T2 
    WHERE 
        (T2.FimDaViagem = IIF(T1.FimDaViagem,True,False))  -- Se for um registro de FimDeViagem, conto o numero de Fins de Viagem e vice-versa
        AND T2.Data <= T1.Data 
        AND T1.Cod_veiculo = T2.Cod_Veiculo

    -- Crio um vínculo em comum entre o registro de inicio e o registro de fim de viagem respectivamente baseado no veiculo e nas ocorrencias 
    -- a partir da data do registro atual

) AS Vinculo, * 
INTO TEMP2

FROM 
TEMP1

Step 3 - Final Consultation

SELECT 
T1.Cod_Veiculo, 
DateValue(T1.Data) AS Data, 
T1.Direcao AS Sentido, 
T1.Cod_Linha, 
T1.Data AS Data_Inicio, 
T2.Data AS Data_Fim, 

(
    -- Soma o numero de registros de fim de viagem onde o veículo é igual ao veículo atual
    SELECT Count(*) 
    FROM 
        TEMP2 T3 
    WHERE 
        T3.FimDaViagem = True 
        AND T3.Cod_Veiculo = T1.Cod_Veiculo
) AS NrViagens
FROM 
TEMP2 AS T1, -- Trago duas vezes a tabela TEMP2 vinculando o mesmo registro de FIm com o de Inicio dela pelo numero do viculo + veículo
TEMP2 AS T2
WHERE 
T1.InicioDaViagem =  True -- Garanto que na T1 sao apenas os dados de inuicio de viagem
AND T2.FimDaViagem = True -- Garanto que na T2 sao apenas os dados de fim de viagem
AND T1.Vinculo = T2.Vinculo -- Vinculo entre T1 e T2
AND T1.Cod_Veiculo = T2.Cod_Veiculo -- Vinculo entre T1 e T2;