How to find the Range.

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Hello,
I would appreciate if some one help me on this..
I have a table with two fields: Office and Amount.
How can I calculate next to Amount field another field
named AmountRange that gives the range of the Amount
field, where 0 is $0.00, 0-1K is $0.00-$1000.00 and so on

Office Amount AmountRange
A 0 0
B 1000 0-1K
C 1500 1-2K
D 1800 1-2K
D 3000 2-3K

Thank you very much.
Bob
 
-----Original Message-----
Hello,
I would appreciate if some one help me on this..
I have a table with two fields: Office and Amount.
How can I calculate next to Amount field another field
named AmountRange that gives the range of the Amount
field, where 0 is $0.00, 0-1K is $0.00-$1000.00 and so on

Office Amount AmountRange
A 0 0
B 1000 0-1K
C 1500 1-2K
D 1800 1-2K
D 3000 2-3K

Thank you very much.
Bob


.
Click to expand...
 
Hello,
I would appreciate if some one help me on this..
I have a table with two fields: Office and Amount.
How can I calculate next to Amount field another field
named AmountRange that gives the range of the Amount
field, where 0 is $0.00, 0-1K is $0.00-$1000.00 and so on

Office Amount AmountRange
A 0 0
B 1000 0-1K
C 1500 1-2K
D 1800 1-2K
D 3000 2-3K

Thank you very much.
Bob
Click to expand...

I'd suggest a small auxiliary table, Ranges, with three fields: Low,
High, and AmountRange with records like

0,1,"0"
0,1000,"0-1K"
1000,2000,"1-2K"

and so on.

Now create a "Non Equi Join" query. You'll need to go to the SQL
window to do this:

SELECT [yourtable].[Office], [yourtable].[Amount],
[Ranges].[AmountRange]
FROM yourtable
INNER JOIN Ranges
ON yourtable.Amount >= Ranges.Low AND yourtable.amount < Ranges.High;
 
Hi John,
This logic is very helpful and it is working , except
between 0-1000 range.For amount 2 it gave 1-2K for 30 it
gave 2-3K, for 40 and 400 it gave 3-4. Any idea why it is
not working for this range?

Thank you
Bob
-----Original Message-----
Hello,
I would appreciate if some one help me on this..
I have a table with two fields: Office and Amount.
How can I calculate next to Amount field another field
named AmountRange that gives the range of the Amount
field, where 0 is $0.00, 0-1K is $0.00-$1000.00 and so on

Office Amount AmountRange
A 0 0
B 1000 0-1K
C 1500 1-2K
D 1800 1-2K
D 3000 2-3K

Thank you very much.
Bob
Click to expand...

I'd suggest a small auxiliary table, Ranges, with three fields: Low,
High, and AmountRange with records like

0,1,"0"
0,1000,"0-1K"
1000,2000,"1-2K"

and so on.

Now create a "Non Equi Join" query. You'll need to go to the SQL
window to do this:

SELECT [yourtable].[Office], [yourtable].[Amount],
[Ranges].[AmountRange]
FROM yourtable
INNER JOIN Ranges
ON yourtable.Amount >= Ranges.Low AND yourtable.amount < Ranges.High;



.
Click to expand...
 
Back
Top