Categorize Continuous Variable into a Discrete One based on a table

[javivd]

Dear all,

I want to categorize a coninuos variable into a discrete one. The
continuous variable is days since last payment (DSLP) of a client
database.

I want to categorize them in 1 to 30 days ("1-30"), 31 to 60 ("31-60")
and so on. Until now i made several
iif(DSLP>120,"120-180",iif(DSLP>90,"90-120"... and so on to construct
this category. but i want to make an auxiliar table with

beginingCategory endCategory Category
1 30 "1-30"
31 60 "31-60"

so that i can match DSLP with that table and bring the Category.
Obvoiously i can't make an INNER JOIN because DSLP doesn't necesarilly
match beginingCategory or endCategory.

Any ideas of how to do this?

Thanks in advance!!

Javier

 

[John W. Vinson]

Dear all,

I want to categorize a coninuos variable into a discrete one. The
continuous variable is days since last payment (DSLP) of a client
database.

I want to categorize them in 1 to 30 days ("1-30"), 31 to 60 ("31-60")
and so on. Until now i made several
iif(DSLP>120,"120-180",iif(DSLP>90,"90-120"... and so on to construct
this category. but i want to make an auxiliar table with

beginingCategory endCategory Category
1 30 "1-30"
31 60 "31-60"

so that i can match DSLP with that table and bring the Category.
Obvoiously i can't make an INNER JOIN because DSLP doesn't necesarilly
match beginingCategory or endCategory.

Any ideas of how to do this?

Thanks in advance!!

Javier

You're absolutely on the right track! You need a "Non-Equi join". You can't do
it in the grid, but you can in SQL view:

SELECT ..., [Category], ...
FROM yourtable INNER JOIN Categories
ON yourtable.DSLP >= Categories.BeginningCategory AND yourtable.DSLP <=
Categories.endCategory;

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com