Is this possible to do in a query ?

[rlan214]

let's say I have a column of time:
10:50 AM
10:53 AM
11:05 AM
11:06 AM
11:07 AM
11:11 AM
what if I want to count 1 for rows 1 and 2 since they have less than 5 min in between
and 2 for rows 3,4,5 and count 3 for row 6....
Is there a way I can do this in Access ?
query possible ?

I think I can do this in excel but not sure if I can do this in an access database

Row Time Count
1 10:50 AM 1
2 10:53 AM 1
3 11:05 AM 2
4 11:06 AM 2
5 11:07 AM 2
6 11:11 AM 3

 

[namliam]

Query? Dont think so... VBA yes easy...

 

[rlan214]

Query? Dont think so... VBA yes easy...

by any chance can you pls tell me.....

i kno it has to do with if statement and some loop but im an access person that's just learning vba... it would be trully appreciated

 

[workmad3]

well, your pseudo code would essentially be:

while (still got records) do
get current time
if (current time - previous time) < 5 Then
add 1
elsif (current time - previous time) < 10 then
add 2
else
add 3
end if
current time = previous time
end while

that shouldn't be too hard to turn into VBA code.

 

[rlan214]

will work on it tom def let you guys kno by tomorrow

 

[rlan214]

well, your pseudo code would essentially be:

while (still got records) do
get current time
if (current time - previous time) < 5 Then
add 1
elsif (current time - previous time) < 10 then
add 2
else
add 3
end if
current time = previous time
end while

that shouldn't be too hard to turn into VBA code.

was working on the vb code but im stuck in getting current time - previous time

also I want adding to go infinite not just up to 3 does that mean that I have to use another loop ?

and another question is that im planning to use this in a query does this mean that I save this as a function ?

 

[rlan214]

Would DateDiff function work to compare current time and previous time?

 

[namliam]

Yes datediff will work or just do as suggested simply deduct currenttime-previoustime