Select query help

M

MikeR

I've got a table (tmplog) that I need to retrieve sorted by
freq(number), mode, cid. (Select * from tmplog ordered by freq, mode
CID). The problem is that I need to treat a range of numbers as a
single number, for instance between 1 an 2 as though they were all the
same number and between 3 and 4 as the same, etc. I'm not sure at this
point how the CIDs will come out, as they are of different lengths(1-5).

There are 12 ranges in all, but maybe this will convey the idea. I've
included a set returned incorrectly, and below that, how it needs to be.

Thanks, Mike

Here is the result of my query.
CALL Freq Mode CID
AA4MM 1.8 PHO K
V47KP 1.8 PHO V4
VE3EJ 1.8 PHO VE
VP9DX 1.8096 PHO VP9
VP5VW 1.83 CW VP5
CY9CWI 1.83 PHO CY9
ZL7C 1.8305 CW ZL7
TI4CF 1.833 CW TI
XE1RCS 1.833 PHO XE
P49I 1.8336 CW P4
WP2DX 1.8338 PHO KP2
C6AJT 1.834 PHO C6
C6AHX 1.836 CW C6
NP4A 1.85007 PHO KP4
C6APU 3.5 CW C6
DL2NBU 3.5 CW DL
VE3KTN 3.5 CW VE
FP8CO 3.5 PHO FP
W1NG 3.5 PHO K
ND0U 3.5 PHO K
KK9A 3.5 PHO K
VE3EJ 3.5 PHO VE
CY0AA 3.513 CW CY0
VP2EZA 3.5194 CW VP2E
VP5VW 3.5393 CW VP5
W4GKM 3.5852 TTY K
K9MUG 3.58604 TTY K
N2WK 3.59244 TTY K
XE1YYD 3.5935 TTY XE
TO5M 3.7 PHO FP
PJ9B 3.7 PHO PJ2

Here's how I need it.
CALL Freq Mode CID
C6AHX 1.836 CW C6
P49I 1.8336 CW P4
TI4CF 1.833 CW TI
VP5VW 1.83 CW VP5
ZL7C 1.8305 CW ZL7

C6AJT 1.834 PHO C6
CY9CWI 1.83 PHO CY9
AA4MM 1.8 PHO K
WP2DX 1.8338 PHO KP2
NP4A 1.85007 PHO KP4
V47KP 1.8 PHO V4
VE3EJ 1.8 PHO VE
VP9DX 1.8096 PHO VP9
XE1RCS 1.833 PHO XE

C6APU 3.5 CW C6
CY0AA 3.513 CW CY0
DL2NBU 3.5 CW DL
VE3KTN 3.5 CW VE
VP2EZA 3.5194 CW VP2E
VP5VW 3.5393 CW VP5

FP8CO 3.5 PHO FP
TO5M 3.7 PHO FP
W1NG 3.5 PHO K
ND0U 3.5 PHO K
KK9A 3.5 PHO K
PJ9B 3.7 PHO PJ2
VE3EJ 3.5 PHO VE

W4GKM 3.5852 TTY K
K9MUG 3.58604 TTY K
N2WK 3.59244 TTY K
XE1YYD 3.5935 TTY XE
 
K

KARL DEWEY

Build a RangeSort table like this --
RangeSort Low High
1 1 2
2 3 4
3 5 10

Then apply like this query --
SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

[Travel]
Name Mileage
joe 650
dd 100
ll 10000
p 10

[RateTable-Mileage]
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521


KARL DEWEY
Build a little - Test a little
 
M

MikeR

Karl -
Thanks, but I think I wasn't clear, or I don't understand your
solution. What's the RangeSort table for? Just an example of the
RateTable-Mileage?

I don't want to select on the ranges, but to order by them. I need all
rows in the table.

Maybe an update query to replace a range i.e. 'Update tmpLog set freq
= 1.8 where freq between 1 and 2' etc. for each range. The actual
value in the freq field isn't important except for the grouping.

KARL said:
Build a RangeSort table like this --
RangeSort Low High
1 1 2
2 3 4
3 5 10

Then apply like this query --
SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

[Travel]
Name Mileage
joe 650
dd 100
ll 10000
p 10

[RateTable-Mileage]
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521


KARL DEWEY
Build a little - Test a little


MikeR said:
I've got a table (tmplog) that I need to retrieve sorted by
freq(number), mode, cid. (Select * from tmplog ordered by freq, mode
CID). The problem is that I need to treat a range of numbers as a
single number, for instance between 1 an 2 as though they were all the
same number and between 3 and 4 as the same, etc. I'm not sure at this
point how the CIDs will come out, as they are of different lengths(1-5).

There are 12 ranges in all, but maybe this will convey the idea. I've
included a set returned incorrectly, and below that, how it needs to be.

Thanks, Mike

Here is the result of my query.
CALL Freq Mode CID
AA4MM 1.8 PHO K
V47KP 1.8 PHO V4
VE3EJ 1.8 PHO VE
VP9DX 1.8096 PHO VP9
VP5VW 1.83 CW VP5
CY9CWI 1.83 PHO CY9
ZL7C 1.8305 CW ZL7
TI4CF 1.833 CW TI
XE1RCS 1.833 PHO XE
P49I 1.8336 CW P4
WP2DX 1.8338 PHO KP2
C6AJT 1.834 PHO C6
C6AHX 1.836 CW C6
NP4A 1.85007 PHO KP4
C6APU 3.5 CW C6
DL2NBU 3.5 CW DL
VE3KTN 3.5 CW VE
FP8CO 3.5 PHO FP
W1NG 3.5 PHO K
ND0U 3.5 PHO K
KK9A 3.5 PHO K
VE3EJ 3.5 PHO VE
CY0AA 3.513 CW CY0
VP2EZA 3.5194 CW VP2E
VP5VW 3.5393 CW VP5
W4GKM 3.5852 TTY K
K9MUG 3.58604 TTY K
N2WK 3.59244 TTY K
XE1YYD 3.5935 TTY XE
TO5M 3.7 PHO FP
PJ9B 3.7 PHO PJ2

Here's how I need it.
CALL Freq Mode CID
C6AHX 1.836 CW C6
P49I 1.8336 CW P4
TI4CF 1.833 CW TI
VP5VW 1.83 CW VP5
ZL7C 1.8305 CW ZL7

C6AJT 1.834 PHO C6
CY9CWI 1.83 PHO CY9
AA4MM 1.8 PHO K
WP2DX 1.8338 PHO KP2
NP4A 1.85007 PHO KP4
V47KP 1.8 PHO V4
VE3EJ 1.8 PHO VE
VP9DX 1.8096 PHO VP9
XE1RCS 1.833 PHO XE

C6APU 3.5 CW C6
CY0AA 3.513 CW CY0
DL2NBU 3.5 CW DL
VE3KTN 3.5 CW VE
VP2EZA 3.5194 CW VP2E
VP5VW 3.5393 CW VP5

FP8CO 3.5 PHO FP
TO5M 3.7 PHO FP
W1NG 3.5 PHO K
ND0U 3.5 PHO K
KK9A 3.5 PHO K
PJ9B 3.7 PHO PJ2
VE3EJ 3.5 PHO VE

W4GKM 3.5852 TTY K
K9MUG 3.58604 TTY K
N2WK 3.59244 TTY K
XE1YYD 3.5935 TTY XE
Click to expand...
Click to expand...
 
K

KARL DEWEY

<<What's the RangeSort table for? Just an example of the RateTable-Mileage?
it will establish your 12 ranges. Yes.

<<I don't want to select on the ranges,
You will notice the two tables are not joined. The selection criteria is to
select the range from the RangeSort table that your data falls into, not to
selct records from your dataset.

Arrange your fields in the design grid, left to right starting with
RangeSort field followed by the other fields you are sorting by.
--
KARL DEWEY
Build a little - Test a little


MikeR said:
Karl -
Thanks, but I think I wasn't clear, or I don't understand your
solution. What's the RangeSort table for? Just an example of the
RateTable-Mileage?

I don't want to select on the ranges, but to order by them. I need all
rows in the table.

Maybe an update query to replace a range i.e. 'Update tmpLog set freq
= 1.8 where freq between 1 and 2' etc. for each range. The actual
value in the freq field isn't important except for the grouping.

KARL said:
Build a RangeSort table like this --
RangeSort Low High
1 1 2
2 3 4
3 5 10

Then apply like this query --
SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

[Travel]
Name Mileage
joe 650
dd 100
ll 10000
p 10

[RateTable-Mileage]
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521


KARL DEWEY
Build a little - Test a little


MikeR said:
I've got a table (tmplog) that I need to retrieve sorted by
freq(number), mode, cid. (Select * from tmplog ordered by freq, mode
CID). The problem is that I need to treat a range of numbers as a
single number, for instance between 1 an 2 as though they were all the
same number and between 3 and 4 as the same, etc. I'm not sure at this
point how the CIDs will come out, as they are of different lengths(1-5).

There are 12 ranges in all, but maybe this will convey the idea. I've
included a set returned incorrectly, and below that, how it needs to be.

Thanks, Mike

Here is the result of my query.
CALL Freq Mode CID
AA4MM 1.8 PHO K
V47KP 1.8 PHO V4
VE3EJ 1.8 PHO VE
VP9DX 1.8096 PHO VP9
VP5VW 1.83 CW VP5
CY9CWI 1.83 PHO CY9
ZL7C 1.8305 CW ZL7
TI4CF 1.833 CW TI
XE1RCS 1.833 PHO XE
P49I 1.8336 CW P4
WP2DX 1.8338 PHO KP2
C6AJT 1.834 PHO C6
C6AHX 1.836 CW C6
NP4A 1.85007 PHO KP4
C6APU 3.5 CW C6
DL2NBU 3.5 CW DL
VE3KTN 3.5 CW VE
FP8CO 3.5 PHO FP
W1NG 3.5 PHO K
ND0U 3.5 PHO K
KK9A 3.5 PHO K
VE3EJ 3.5 PHO VE
CY0AA 3.513 CW CY0
VP2EZA 3.5194 CW VP2E
VP5VW 3.5393 CW VP5
W4GKM 3.5852 TTY K
K9MUG 3.58604 TTY K
N2WK 3.59244 TTY K
XE1YYD 3.5935 TTY XE
TO5M 3.7 PHO FP
PJ9B 3.7 PHO PJ2

Here's how I need it.
CALL Freq Mode CID
C6AHX 1.836 CW C6
P49I 1.8336 CW P4
TI4CF 1.833 CW TI
VP5VW 1.83 CW VP5
ZL7C 1.8305 CW ZL7

C6AJT 1.834 PHO C6
CY9CWI 1.83 PHO CY9
AA4MM 1.8 PHO K
WP2DX 1.8338 PHO KP2
NP4A 1.85007 PHO KP4
V47KP 1.8 PHO V4
VE3EJ 1.8 PHO VE
VP9DX 1.8096 PHO VP9
XE1RCS 1.833 PHO XE

C6APU 3.5 CW C6
CY0AA 3.513 CW CY0
DL2NBU 3.5 CW DL
VE3KTN 3.5 CW VE
VP2EZA 3.5194 CW VP2E
VP5VW 3.5393 CW VP5

FP8CO 3.5 PHO FP
TO5M 3.7 PHO FP
W1NG 3.5 PHO K
ND0U 3.5 PHO K
KK9A 3.5 PHO K
PJ9B 3.7 PHO PJ2
VE3EJ 3.5 PHO VE

W4GKM 3.5852 TTY K
K9MUG 3.58604 TTY K
N2WK 3.59244 TTY K
XE1YYD 3.5935 TTY XE
Click to expand...
Click to expand...
Click to expand...
 
M

MikeR

There was some sort of glitch, I posted a reply yesterday, Thunderbird
has it marked as replied to, and it's in my 'Sent' box, but I don't
see it here, so here it is again.....

Karl -
Thanks, but I think I wasn't clear, or I don't understand your
solution. What's the RangeSort table for? Just an example of the
RateTable-Mileage?

I don't want to select on the ranges, but to order by them. I need all
rows in the table.

Maybe an update query to replace a range i.e. 'Update tmpLog set freq
= 1.8 where freq between 1 and 2' etc. for each range. Or I could
transform the values when creating the table. Hmmmm... this one sounds
like a good solution. The actual value in the freq field isn't
important except for the grouping.

An additional requirement has come to mind. The data in the field
'Call' may be duplicated. So I'd like 2 selects, one as above, but
where 'Call' is unique, and the other to get all rows that contain
dupe calls, where within the a group of like calls the above sort is
maintained.

KARL said:
Build a RangeSort table like this --
RangeSort Low High
1 1 2
2 3 4
3 5 10

Then apply like this query --
SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

[Travel]
Name Mileage
joe 650
dd 100
ll 10000
p 10

[RateTable-Mileage]
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521


KARL DEWEY
Build a little - Test a little


MikeR said:
I've got a table (tmplog) that I need to retrieve sorted by
freq(number), mode, cid. (Select * from tmplog ordered by freq, mode
CID). The problem is that I need to treat a range of numbers as a
single number, for instance between 1 an 2 as though they were all the
same number and between 3 and 4 as the same, etc. I'm not sure at this
point how the CIDs will come out, as they are of different lengths(1-5).

There are 12 ranges in all, but maybe this will convey the idea. I've
included a set returned incorrectly, and below that, how it needs to be.

Thanks, Mike

Here is the result of my query.
CALL Freq Mode CID
AA4MM 1.8 PHO K
V47KP 1.8 PHO V4
VE3EJ 1.8 PHO VE
VP9DX 1.8096 PHO VP9
VP5VW 1.83 CW VP5
CY9CWI 1.83 PHO CY9
ZL7C 1.8305 CW ZL7
TI4CF 1.833 CW TI
XE1RCS 1.833 PHO XE
P49I 1.8336 CW P4
WP2DX 1.8338 PHO KP2
C6AJT 1.834 PHO C6
C6AHX 1.836 CW C6
NP4A 1.85007 PHO KP4
C6APU 3.5 CW C6
DL2NBU 3.5 CW DL
VE3KTN 3.5 CW VE
FP8CO 3.5 PHO FP
W1NG 3.5 PHO K
ND0U 3.5 PHO K
KK9A 3.5 PHO K
VE3EJ 3.5 PHO VE
CY0AA 3.513 CW CY0
VP2EZA 3.5194 CW VP2E
VP5VW 3.5393 CW VP5
W4GKM 3.5852 TTY K
K9MUG 3.58604 TTY K
N2WK 3.59244 TTY K
XE1YYD 3.5935 TTY XE
TO5M 3.7 PHO FP
PJ9B 3.7 PHO PJ2

Here's how I need it.
CALL Freq Mode CID
C6AHX 1.836 CW C6
P49I 1.8336 CW P4
TI4CF 1.833 CW TI
VP5VW 1.83 CW VP5
ZL7C 1.8305 CW ZL7

C6AJT 1.834 PHO C6
CY9CWI 1.83 PHO CY9
AA4MM 1.8 PHO K
WP2DX 1.8338 PHO KP2
NP4A 1.85007 PHO KP4
V47KP 1.8 PHO V4
VE3EJ 1.8 PHO VE
VP9DX 1.8096 PHO VP9
XE1RCS 1.833 PHO XE

C6APU 3.5 CW C6
CY0AA 3.513 CW CY0
DL2NBU 3.5 CW DL
VE3KTN 3.5 CW VE
VP2EZA 3.5194 CW VP2E
VP5VW 3.5393 CW VP5

FP8CO 3.5 PHO FP
TO5M 3.7 PHO FP
W1NG 3.5 PHO K
ND0U 3.5 PHO K
KK9A 3.5 PHO K
PJ9B 3.7 PHO PJ2
VE3EJ 3.5 PHO VE

W4GKM 3.5852 TTY K
K9MUG 3.58604 TTY K
N2WK 3.59244 TTY K
XE1YYD 3.5935 TTY XE
Click to expand...
Click to expand...
 
K

KARL DEWEY

I do not know how you are viewing this but here is the rersponse I gave to
your earlier post --
<<What's the RangeSort table for? Just an example of the RateTable-Mileage?
it will establish your 12 ranges. Yes.

<<I don't want to select on the ranges,
You will notice the two tables are not joined. The selection criteria is to
select the range from the RangeSort table that your data falls into, not to
selct records from your dataset.

Arrange your fields in the design grid, left to right starting with
RangeSort field followed by the other fields you are sorting by.

--
KARL DEWEY
Build a little - Test a little


MikeR said:
There was some sort of glitch, I posted a reply yesterday, Thunderbird
has it marked as replied to, and it's in my 'Sent' box, but I don't
see it here, so here it is again.....

Karl -
Thanks, but I think I wasn't clear, or I don't understand your
solution. What's the RangeSort table for? Just an example of the
RateTable-Mileage?

I don't want to select on the ranges, but to order by them. I need all
rows in the table.

Maybe an update query to replace a range i.e. 'Update tmpLog set freq
= 1.8 where freq between 1 and 2' etc. for each range. Or I could
transform the values when creating the table. Hmmmm... this one sounds
like a good solution. The actual value in the freq field isn't
important except for the grouping.

An additional requirement has come to mind. The data in the field
'Call' may be duplicated. So I'd like 2 selects, one as above, but
where 'Call' is unique, and the other to get all rows that contain
dupe calls, where within the a group of like calls the above sort is
maintained.

KARL said:
Build a RangeSort table like this --
RangeSort Low High
1 1 2
2 3 4
3 5 10

Then apply like this query --
SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

[Travel]
Name Mileage
joe 650
dd 100
ll 10000
p 10

[RateTable-Mileage]
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521


KARL DEWEY
Build a little - Test a little


MikeR said:
I've got a table (tmplog) that I need to retrieve sorted by
freq(number), mode, cid. (Select * from tmplog ordered by freq, mode
CID). The problem is that I need to treat a range of numbers as a
single number, for instance between 1 an 2 as though they were all the
same number and between 3 and 4 as the same, etc. I'm not sure at this
point how the CIDs will come out, as they are of different lengths(1-5).

There are 12 ranges in all, but maybe this will convey the idea. I've
included a set returned incorrectly, and below that, how it needs to be.

Thanks, Mike

Here is the result of my query.
CALL Freq Mode CID
AA4MM 1.8 PHO K
V47KP 1.8 PHO V4
VE3EJ 1.8 PHO VE
VP9DX 1.8096 PHO VP9
VP5VW 1.83 CW VP5
CY9CWI 1.83 PHO CY9
ZL7C 1.8305 CW ZL7
TI4CF 1.833 CW TI
XE1RCS 1.833 PHO XE
P49I 1.8336 CW P4
WP2DX 1.8338 PHO KP2
C6AJT 1.834 PHO C6
C6AHX 1.836 CW C6
NP4A 1.85007 PHO KP4
C6APU 3.5 CW C6
DL2NBU 3.5 CW DL
VE3KTN 3.5 CW VE
FP8CO 3.5 PHO FP
W1NG 3.5 PHO K
ND0U 3.5 PHO K
KK9A 3.5 PHO K
VE3EJ 3.5 PHO VE
CY0AA 3.513 CW CY0
VP2EZA 3.5194 CW VP2E
VP5VW 3.5393 CW VP5
W4GKM 3.5852 TTY K
K9MUG 3.58604 TTY K
N2WK 3.59244 TTY K
XE1YYD 3.5935 TTY XE
TO5M 3.7 PHO FP
PJ9B 3.7 PHO PJ2

Here's how I need it.
CALL Freq Mode CID
C6AHX 1.836 CW C6
P49I 1.8336 CW P4
TI4CF 1.833 CW TI
VP5VW 1.83 CW VP5
ZL7C 1.8305 CW ZL7

C6AJT 1.834 PHO C6
CY9CWI 1.83 PHO CY9
AA4MM 1.8 PHO K
WP2DX 1.8338 PHO KP2
NP4A 1.85007 PHO KP4
V47KP 1.8 PHO V4
VE3EJ 1.8 PHO VE
VP9DX 1.8096 PHO VP9
XE1RCS 1.833 PHO XE

C6APU 3.5 CW C6
CY0AA 3.513 CW CY0
DL2NBU 3.5 CW DL
VE3KTN 3.5 CW VE
VP2EZA 3.5194 CW VP2E
VP5VW 3.5393 CW VP5

FP8CO 3.5 PHO FP
TO5M 3.7 PHO FP
W1NG 3.5 PHO K
ND0U 3.5 PHO K
KK9A 3.5 PHO K
PJ9B 3.7 PHO PJ2
VE3EJ 3.5 PHO VE

W4GKM 3.5852 TTY K
K9MUG 3.58604 TTY K
N2WK 3.59244 TTY K
XE1YYD 3.5935 TTY XE
Click to expand...
Click to expand...
Click to expand...
 
M

MikeR

KARL said:
I do not know how you are viewing this but here is the rersponse I gave to
your earlier post --
Click to expand...

NNTP and Thunderbird - I didn't see my previous post or yours.
Something burped......
<<What's the RangeSort table for? Just an example of the RateTable-Mileage?
it will establish your 12 ranges. Yes.

<<I don't want to select on the ranges,
You will notice the two tables are not joined. The selection criteria is to
select the range from the RangeSort table that your data falls into, not to
selct records from your dataset.

Arrange your fields in the design grid, left to right starting with
RangeSort field followed by the other fields you are sorting by.
Click to expand...
OK -

I created a table named ranges
sort_f low_r high_r
1 1.7 2.1
2 2.9 4.1
3 4.9 5.5

table tmpLog -
call freq mode CID
A4MM 1.8 PHO K
V47KP 1.8 PHO V4
VE3EJ 1.8 PHO VE
VP9DX 1.8096 PHO VP9
VP5VW 1.83 CW VP5
CY9CWI 1.83 PHO CY9
ZL7C 1.8305 CW ZL7
TI4CF 1.833 CW TI
XE1RCS 1.833 PHO XE
P49I 1.8336 CW P4
WP2DX 1.8338 PHO KP2
C6AJT 1.834 PHO C6
C6AHX 1.836 CW C6
NP4A 1.85007 PHO KP4
C6APU 3.5 CW C6
DL2NBU 3.5 CW DL
VE3KTN 3.5 CW VE
FP8CO 3.5 PHO FP

This query

SELECT tmpLog.call, [ranges].sort_f
FROM TmpLog, [ranges]
WHERE (((tmplog.freq) between [low_f] and [high_f]));


Access prompts for values for low_f and high_f (not what I want), it
is a selection criteria (limiting the result set to that range), and
not there is no sort.

I think I still haven't conveyed what I want to do.

This ultimately will be used in a Delphi program, not from within Access.
 
M

MikeR

MikeR said:
KARL said:
I do not know how you are viewing this but here is the rersponse I
gave to your earlier post --
Click to expand...

NNTP and Thunderbird - I didn't see my previous post or yours. Something
burped......
<<What's the RangeSort table for? Just an example of the
RateTable-Mileage?
it will establish your 12 ranges. Yes.

<<I don't want to select on the ranges,
You will notice the two tables are not joined. The selection criteria
is to select the range from the RangeSort table that your data falls
into, not to selct records from your dataset.

Arrange your fields in the design grid, left to right starting with
RangeSort field followed by the other fields you are sorting by.
Click to expand...
OK -

I created a table named ranges
sort_f low_r high_r
1 1.7 2.1
2 2.9 4.1
3 4.9 5.5

table tmpLog -
call freq mode CID
A4MM 1.8 PHO K
V47KP 1.8 PHO V4
VE3EJ 1.8 PHO VE
VP9DX 1.8096 PHO VP9
VP5VW 1.83 CW VP5
CY9CWI 1.83 PHO CY9
ZL7C 1.8305 CW ZL7
TI4CF 1.833 CW TI
XE1RCS 1.833 PHO XE
P49I 1.8336 CW P4
WP2DX 1.8338 PHO KP2
C6AJT 1.834 PHO C6
C6AHX 1.836 CW C6
NP4A 1.85007 PHO KP4
C6APU 3.5 CW C6
DL2NBU 3.5 CW DL
VE3KTN 3.5 CW VE
FP8CO 3.5 PHO FP

This query

SELECT tmpLog.call, [ranges].sort_f
FROM TmpLog, [ranges]
WHERE (((tmplog.freq) between [low_f] and [high_f]));


Access prompts for values for low_f and high_f (not what I want), it is
a selection criteria (limiting the result set to that range), and not
there is no sort.

I think I still haven't conveyed what I want to do.

This ultimately will be used in a Delphi program, not from within Access.
Click to expand...
Karl
Nothing further? Anyone?
 
H

huangda 晋江兴达机械

MikeR said:
MikeR said:
KARL said:
I do not know how you are viewing this but here is the rersponse I gave
to your earlier post --
Click to expand...

NNTP and Thunderbird - I didn't see my previous post or yours. Something
burped......
<<What's the RangeSort table for? Just an example of the
RateTable-Mileage?
it will establish your 12 ranges. Yes.

<<I don't want to select on the ranges,
You will notice the two tables are not joined. The selection criteria
is to select the range from the RangeSort table that your data falls
into, not to selct records from your dataset.

Arrange your fields in the design grid, left to right starting with
RangeSort field followed by the other fields you are sorting by.
Click to expand...
OK -

I created a table named ranges
sort_f low_r high_r
1 1.7 2.1
2 2.9 4.1
3 4.9 5.5

table tmpLog -
call freq mode CID
A4MM 1.8 PHO K
V47KP 1.8 PHO V4
VE3EJ 1.8 PHO VE
VP9DX 1.8096 PHO VP9
VP5VW 1.83 CW VP5
CY9CWI 1.83 PHO CY9
ZL7C 1.8305 CW ZL7
TI4CF 1.833 CW TI
XE1RCS 1.833 PHO XE
P49I 1.8336 CW P4
WP2DX 1.8338 PHO KP2
C6AJT 1.834 PHO C6
C6AHX 1.836 CW C6
NP4A 1.85007 PHO KP4
C6APU 3.5 CW C6
DL2NBU 3.5 CW DL
VE3KTN 3.5 CW VE
FP8CO 3.5 PHO FP

This query

SELECT tmpLog.call, [ranges].sort_f
FROM TmpLog, [ranges]
WHERE (((tmplog.freq) between [low_f] and [high_f]));


Access prompts for values for low_f and high_f (not what I want), it is a
selection criteria (limiting the result set to that range), and not there
is no sort.

I think I still haven't conveyed what I want to do.

This ultimately will be used in a Delphi program, not from within Access.
Click to expand...
Karl
Nothing further? Anyone?
Click to expand...
 
J

John Spencer

First, you have gaps in your range table - that may be acceptable, but
usually it is not. Second you need to refer to the field names in the
table - Low_F and High_F are not fields in your table (low_r and High_r
are fields in your table).

sort_f low_r high_r
1 1.7 2.1
2 2.9 4.1
3 4.9 5.5

If there is a Freq of 2.8 where should it sort? Above all these ranges,
below all these ranges, or somewhere in between two of the ranges?

SELECT tmpLog.[Call]
, Freq
, tmplog.[Mode]
, CID
, sort_F
FROM tmpLog LEFT JOIN tblRanges
ON tmpLog.Freq >= tblRanges.Low_R
AND tmpLog.Freq <= tblRanges.High_R
ORDER By TblRanges.Sort_F
, tmpLog.[mode], tmpLog.[Freq], tmpLog.CID

I'm still not sure I have figured out exactly how you want things
sorted. And if I were building the table, I would use ranges that looked
like

sort_f low_r high_r
1 1.7 2.2
1.5 2.2 2.9
2 2.9 4.2
2.5 4.2 4.9
3 4.9 5.6
4 5.6 99 (or some other maximum)

AND change the join to
FROM tmpLog LEFT JOIN tblRanges
ON tmpLog.Freq >= tblRanges.Low_R
AND tmpLog.Freq < tblRanges.High_R


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

MikeR said:
KARL said:
I do not know how you are viewing this but here is the rersponse I
gave to your earlier post --
Click to expand...

NNTP and Thunderbird - I didn't see my previous post or yours.
Something burped......
<<What's the RangeSort table for? Just an example of the
RateTable-Mileage?
it will establish your 12 ranges. Yes.

<<I don't want to select on the ranges,
You will notice the two tables are not joined. The selection
criteria is to select the range from the RangeSort table that your
data falls into, not to selct records from your dataset.

Arrange your fields in the design grid, left to right starting with
RangeSort field followed by the other fields you are sorting by.
Click to expand...
OK -

I created a table named ranges
sort_f low_r high_r
1 1.7 2.1
2 2.9 4.1
3 4.9 5.5

table tmpLog -
call freq mode CID
A4MM 1.8 PHO K
V47KP 1.8 PHO V4
VE3EJ 1.8 PHO VE
VP9DX 1.8096 PHO VP9
VP5VW 1.83 CW VP5
CY9CWI 1.83 PHO CY9
ZL7C 1.8305 CW ZL7
TI4CF 1.833 CW TI
XE1RCS 1.833 PHO XE
P49I 1.8336 CW P4
WP2DX 1.8338 PHO KP2
C6AJT 1.834 PHO C6
C6AHX 1.836 CW C6
NP4A 1.85007 PHO KP4
C6APU 3.5 CW C6
DL2NBU 3.5 CW DL
VE3KTN 3.5 CW VE
FP8CO 3.5 PHO FP

This query

SELECT tmpLog.call, [ranges].sort_f
FROM TmpLog, [ranges]
WHERE (((tmplog.freq) between [low_f] and [high_f]));


Access prompts for values for low_f and high_f (not what I want), it
is a selection criteria (limiting the result set to that range), and
not there is no sort.

I think I still haven't conveyed what I want to do.

This ultimately will be used in a Delphi program, not from within Access.
Click to expand...
Karl
Nothing further? Anyone?
Click to expand...
 
M

MikeR

MikeR said:
KARL said:
I do not know how you are viewing this but here is the rersponse I
gave to your earlier post --
Click to expand...

NNTP and Thunderbird - I didn't see my previous post or yours. Something
burped......
<<What's the RangeSort table for? Just an example of the
RateTable-Mileage?
it will establish your 12 ranges. Yes.

<<I don't want to select on the ranges,
You will notice the two tables are not joined. The selection criteria
is to select the range from the RangeSort table that your data falls
into, not to selct records from your dataset.

Arrange your fields in the design grid, left to right starting with
RangeSort field followed by the other fields you are sorting by.
Click to expand...
OK -

I created a table named ranges
sort_f low_r high_r
1 1.7 2.1
2 2.9 4.1
3 4.9 5.5

table tmpLog -
call freq mode CID
A4MM 1.8 PHO K
V47KP 1.8 PHO V4
VE3EJ 1.8 PHO VE
VP9DX 1.8096 PHO VP9
VP5VW 1.83 CW VP5
CY9CWI 1.83 PHO CY9
ZL7C 1.8305 CW ZL7
TI4CF 1.833 CW TI
XE1RCS 1.833 PHO XE
P49I 1.8336 CW P4
WP2DX 1.8338 PHO KP2
C6AJT 1.834 PHO C6
C6AHX 1.836 CW C6
NP4A 1.85007 PHO KP4
C6APU 3.5 CW C6
DL2NBU 3.5 CW DL
VE3KTN 3.5 CW VE
FP8CO 3.5 PHO FP

This query

SELECT tmpLog.call, [ranges].sort_f
FROM TmpLog, [ranges]
WHERE (((tmplog.freq) between [low_f] and [high_f]));


Access prompts for values for low_f and high_f (not what I want), it is
a selection criteria (limiting the result set to that range), and not
there is no sort.

I think I still haven't conveyed what I want to do.

This ultimately will be used in a Delphi program, not from within Access.
Click to expand...

I haven't seen a response to this post. I posted after the above one
also, and don't see that post. I may not see this one.....<vbg>

Could someone reply directly to me, if those 2 (or any other after the
3/13 one is there? Any tips on what may be wrong?

TIA, Mike
 
M

MikeR

MikeR said:
MikeR said:
KARL said:
I do not know how you are viewing this but here is the rersponse I
gave to your earlier post --
Click to expand...

NNTP and Thunderbird - I didn't see my previous post or yours.
Something burped......
<<What's the RangeSort table for? Just an example of the
RateTable-Mileage?
it will establish your 12 ranges. Yes.

<<I don't want to select on the ranges,
You will notice the two tables are not joined. The selection
criteria is to select the range from the RangeSort table that your
data falls into, not to selct records from your dataset.

Arrange your fields in the design grid, left to right starting with
RangeSort field followed by the other fields you are sorting by.
Click to expand...
OK -

I created a table named ranges
sort_f low_r high_r
1 1.7 2.1
2 2.9 4.1
3 4.9 5.5

table tmpLog -
call freq mode CID
A4MM 1.8 PHO K
V47KP 1.8 PHO V4
VE3EJ 1.8 PHO VE
VP9DX 1.8096 PHO VP9
VP5VW 1.83 CW VP5
CY9CWI 1.83 PHO CY9
ZL7C 1.8305 CW ZL7
TI4CF 1.833 CW TI
XE1RCS 1.833 PHO XE
P49I 1.8336 CW P4
WP2DX 1.8338 PHO KP2
C6AJT 1.834 PHO C6
C6AHX 1.836 CW C6
NP4A 1.85007 PHO KP4
C6APU 3.5 CW C6
DL2NBU 3.5 CW DL
VE3KTN 3.5 CW VE
FP8CO 3.5 PHO FP

This query

SELECT tmpLog.call, [ranges].sort_f
FROM TmpLog, [ranges]
WHERE (((tmplog.freq) between [low_f] and [high_f]));


Access prompts for values for low_f and high_f (not what I want), it
is a selection criteria (limiting the result set to that range), and
not there is no sort.

I think I still haven't conveyed what I want to do.

This ultimately will be used in a Delphi program, not from within Access.
Click to expand...

I haven't seen a response to this post. I posted after the above one
also, and don't see that post. I may not see this one.....<vbg>

Could someone reply directly to me, if those 2 (or any other after the
3/13 one is there? Any tips on what may be wrong?

TIA, Mike
Click to expand...
Should I assume we're finished here?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select query help 4

Top