Identifying the Fitting Range in Query

[jpalmer150]

I have a table [Categories] with a list of categories and their corresponding maximum income.

CATEGORY MAX_INCOME
Category 1 $20,000
Category 2 $30,000
Category 3 $40,000

I have another table [Incomes] with an Income value. I'd like to be able to go to the [Categories] table to determine which category the Income falls within. For example. The Income value "$19,250" should receive the value "Category 1". The Income value "$33,500" should receive the value "Category 3".

Anyone have an idea on how this is accomplished?

 

[TonyJollans]

Hi jpalmer150,

This should do it ..

[blue][tt]SELECT [Income],
(SELECT TOP 1 [Category]
FROM [Categories]
WHERE [Max_Income] > [Income]
ORDER BY [Max_Income]) AS IncomeCategory
FROM [Incomes];[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.