500 ft. incremental distances

[lp4799]

I created a query to pull orders that have a distance = or > 14.0, but I need to know if there is a way to possibly manipulate the results (or the query itself). I have distances between 14.0 and 50.0, but I need to provide a count of the total number of orders in 500 ft. increments (i.e., 14.0-14.5; 14.5-15.0; 15.0-15.5 and 15.5 on)

DISTANCE
14.00
14.00
14.00
14.00
14.00
14.02
14.04
14.04
14.09
14.10
14.10
14.16
14.18
14.20
14.20
14.26
14.28
14.29
14.32
14.32
14.36
14.37
14.38
14.40
14.43
14.46
14.47
14.48
14.48
14.49
14.50
14.58
14.60
14.60
14.60
14.61
14.68
14.69
14.70
14.73
14.74
14.76
14.77
14.77
14.78
14.79
14.80
14.80
14.80
14.80
14.80
14.80
14.80
14.82
14.82
14.84
14.85
14.85
14.86
14.86
14.86
14.86
14.92
14.92
14.94
14.95
14.96
14.97
14.97
14.98
14.98
15.00
15.01
15.01
15.01
15.02
15.02
15.03
15.03
15.06
15.08
15.09
15.09
15.10
15.10
15.10
15.10
15.12
15.14
15.19
15.20
15.20
15.28
15.36
15.39
15.40
15.41
15.44
15.44
15.48
15.55
15.56
15.60
15.62
15.65
15.68
15.69
15.70
15.79
15.85
15.89
15.99
16.03
16.13
16.16
16.21
16.29
16.36
16.39
16.42
16.45
16.46
16.47
16.50
16.51
16.55
16.57
16.58
16.60
16.62
16.73
16.80
16.80
16.90
16.90
16.99
17.00
17.22
17.23
17.24
17.25
17.30
17.43
17.50
17.66
17.76
17.84
17.97
17.98
18.00
18.00
18.06
18.07
18.12
18.14
18.16
18.17
18.19
18.20
18.21
18.21
18.30
18.31
18.33
18.34
18.39
18.46
18.47
18.48
18.48
18.50
18.52
18.54
18.62
18.65
18.68
18.68
18.76
18.80
18.84
18.86
18.87
18.87
18.89
18.91
18.94
18.94
18.98
19.02
19.06
19.10
19.11
19.12
19.16
19.17
19.20
19.20
19.21
19.22
19.24
19.24
19.24
19.24
19.24
19.26
19.27
19.30
19.31
19.32
19.33
19.37
19.40
19.40
19.41
19.41
19.41
19.42
19.44
19.47
19.47
19.47
19.48
19.50
19.50
19.55
19.56
19.60
19.60
19.65
19.67
19.68
19.70
19.70
19.70
19.71
19.71
19.72
19.72
19.74
19.74
19.76
19.76
19.76
19.78
19.80
19.80
19.80
19.80
19.86
19.87
19.87
19.87
19.88
19.94
19.94
19.94
19.95
19.96
19.97
20.00
20.03
20.07
20.07
20.08
20.14
20.17
20.17
20.17
20.19
20.19
20.19
20.23
20.23
20.24
20.25
20.27
20.38
20.38
20.41
20.43
20.47
20.48
20.50
20.52
20.52
20.52
20.53
20.54
20.57
20.57
20.60
20.60
20.65
20.65
20.67
20.68
20.68
20.71
20.73
20.80
20.81
20.83
20.84
20.85
20.85
20.86
20.88
20.88
20.90
20.92
20.92
20.93
20.95
20.95
20.97
20.98
20.98
20.98
20.99
21.04
21.04
21.04
21.04
21.11
21.11
21.12
21.15
21.15
21.17
21.17
21.19
21.22
21.23
21.25
21.26
21.27
21.28
21.28
21.28
21.33
21.33
21.39
21.41
21.46
21.47
21.48
21.49
21.51
21.54
21.55
21.56
21.58
21.58
21.59
21.59
21.60
21.61
21.62
21.66
21.66
21.73
21.74
21.74
21.75
21.76
21.82
21.83
21.90
21.90
21.92
21.96
21.98
21.98
21.98
21.98
21.99
21.99
22.06
22.07
22.08
22.08
22.09
22.12
22.13
22.17
22.18
22.20
22.31
22.32
22.32
22.38
22.39
22.40
22.40
22.40
22.40
22.42
22.42
22.44
22.44
22.45
22.47
22.51
22.64
22.74
22.80
22.93
22.93
22.96
22.97
22.97
23.13
23.28
23.33
23.47
23.58
23.83
24.07
24.43
24.69
24.79
24.89
24.89
25.09
25.24
25.56
25.83
26.03
26.50
26.58
26.77
26.84
27.03
27.39
28.00
28.04
28.04
28.14
28.16
28.20
28.26
28.28
28.31
28.33
28.50
28.50
28.53
28.60
28.88
28.96
29.10
29.20
29.20
29.28
29.30
29.43
29.49
29.55
29.58
29.62
30.05
30.11
30.11
30.21
30.22
30.34
30.44
30.50
30.51
30.60
30.71
30.74
30.74
30.74
31.10
31.30
31.41
31.60
31.65
31.70
31.70
31.70
31.71
31.81
31.82
31.84
32.00
32.23
32.23
32.50
32.60
32.80
32.80
32.82
32.90
33.05
33.11
33.12
33.14
33.15
33.18
33.27
33.46
33.76
33.80
33.81
34.08
34.17
34.27
34.60
34.74
34.75
34.78
35.20
35.43
35.57
36.30
36.35
36.35
36.66
37.02
37.28
37.28
37.29
37.52
38.78
39.78
39.78
40.00
40.12
40.50
41.00
41.93
43.19
46.23
49.70
50.15

 

[pcs]

wow,
think i burnt out my PageDown key!

you might check Help for:
Partition Function

not sure that is what you are looking for.

hth,
al

 

[lp4799]

I am trying to produce data in the following format:

Distance Total
14.0 > x => 14.5 2637
14.5 > x => 15.0 3580
15.0 > x => 15.5 2461
15.5 > x => 16.0 1359
16.0 > x => 16.5 1042
16.5 > x => 17.0 878
17.0 > x => 17.5 810
17.5 > x => 18.0 889

I'm not sure what function in Access I can
to create this data, or what criteria or expression I can build to create this.

 

[Alexandre]

The following functions will give you the upper and lesser limits where your distances find themselves:

Option Compare Database
Option Explicit


Public Function DistanceMin(X As Single)
    DistanceMin = IIf((X - Int(X)) > 0 And (X - Int(X)) < 0.5, Int(X), Int(X) + 0.5)
End Function


Public Function DistanceMax (X As Single)
    DistanceMax = DistanceMin (X) +0.5
End Function

I would avoid to use them in a query directly, for performance reasons (most functions in queries will prevent Access from doing its optimization task). I would use DistanceMin as a grouping function in a report. You would then get your results grouped per interval lesser value. It would not be difficult then to have a label indicate the lesser AND upper (+0.5) limit for each group, and the number of elements in this group (use: Count(*)).

I can send you a quick demo db if you wish.

Alex

[This message has been edited by Alexandre (edited 02-01-2002).]