1
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...
you came to develop something of your select?
– Math
Is it possible to define more precisely "with a few minute intervals" to try to mount an expression in SQL? You said 30 minutes isn’t worth it, but 15, 10 or 5 minutes?
– user4552
Ideal is to set up some auxiliary tables to help you and simplify the problem. I can see a solution for Voce, generating a column adds for each row informing the difference time between the current record and the previous record. Then with this field Voce generates the second field informing when the time is greater than 30 and informs a true or false, if it is cut. Finally in a last select you group this data and display in a pivot.
– Roger Barretto
Thanks for the attention of Math users - Osvaldo and Roger...
– user7198
To Math I inform that basically everything I develop in access is via assitente, I’m not very good in vba.....
– user7198
Osvaldo, reported that the 30 minutes is a fixed interval parameter, but nothing would prevent me in the future to modify it for more or less...see usually a circulating bus has regular shipments of users that usually do not exceed 30 minutes, so as a vehicle can sometimes continue in the outward direction indefinitely during the day, we decided to adopt this criterion... OK.....
– user7198
To Roger, I inform that I understood perfectly, but I have difficulties in writing this racicíonio in VBA, so: So "if possible" help me in this project ? ....
– user7198