I would like to get the following select
, so I'll provide the example below, remembering that I use Access 2007 VBA.
Objective : Count the trips made by the fleet vehicles by Line / Sense. Taking into consideration the following parameters:
- 1º The vehicle must be in the same line and direction - with users embarking systematically with some intervals of minutes, or seconds.
- 2º Being in the same line and meaning and to verify that a user embarked with a difference of 30 minutes in relation to the previous one, to 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 change of line 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: The vehicle 91 carried out 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 various vehicles ...