Complex If-command

[Guest]

Hi!
Hopefully someone knows this:
I have a column with numbers which I need to sort into categories of 5 cm
each. (0-4.99cm will be category 0, 5-9.99cm cat 5 etc) The categories in
the output column will be represented by just one number to avoid using text.

How do I write the query??? (I´ve used the command If before, but that
includes the command "or else", which I can´t use here)

Thanks!

Example:
Original (cm) Output (kategories of cm)
26 25
23 20
7 5
14 10

 

[Michel Walsh]

Hi,

You can try a crosstab and PARTITION. See that world in the help file.

Otherwise, you can also group by on the integer result division of the size,
divided by 5:

int( original /5.00)



Hoping it may help,
Vanderghast, Access MVP

 

[Chris M]

Hi!
Hopefully someone knows this:
I have a column with numbers which I need to sort into categories of 5 cm
each. (0-4.99cm will be category 0, 5-9.99cm cat 5 etc) The categories in
the output column will be represented by just one number to avoid using
text.

How do I write the query??? (I´ve used the command If before, but that
includes the command "or else", which I can´t use here)

Thanks!

Example:
Original (cm) Output (kategories of cm)
26 25
23 20
7 5
14 10

If there are not too many catagories you could use iif:

iif(<condition> , <result if condition true> , <result if condition false)

This is nestable, so the result if false can be another iif statement:

iif(length between 0 and 4.99,0,iif(length between 5 and 5.99,5,iif(length
between 10 and 14.99,10)))... etc.

Or maybe you could use somthing like this:

Catagory: Int([Length]/5)*5

Cheers,

Chris.

 

[Guest]

Thanks! It´s working perfectly. Just a b it long to write - I´ll have about
20 ):s when I´m done.... But it was an easy enough query for me to manage. =)
/Johanna

"Chris M" skrev:

Hi!
Hopefully someone knows this:
I have a column with numbers which I need to sort into categories of 5 cm
each. (0-4.99cm will be category 0, 5-9.99cm cat 5 etc) The categories in
the output column will be represented by just one number to avoid using
text.

How do I write the query??? (I´ve used the command If before, but that
includes the command "or else", which I can´t use here)

Thanks!

Example:
Original (cm) Output (kategories of cm)
26 25
23 20
7 5
14 10

If there are not too many catagories you could use iif:

iif(<condition> , <result if condition true> , <result if condition false)

This is nestable, so the result if false can be another iif statement:

iif(length between 0 and 4.99,0,iif(length between 5 and 5.99,5,iif(length
between 10 and 14.99,10)))... etc.

Or maybe you could use somthing like this:

Catagory: Int([Length]/5)*5

Cheers,

Chris.

 

[Chris M]

Hi!
Hopefully someone knows this:
I have a column with numbers which I need to sort into categories of 5
cm
each. (0-4.99cm will be category 0, 5-9.99cm cat 5 etc) The categories
in
the output column will be represented by just one number to avoid using
text.

How do I write the query??? (I´ve used the command If before, but that
includes the command "or else", which I can´t use here)

Thanks!

Example:
Original (cm) Output (kategories of cm)
26 25
23 20
7 5
14 10

If there are not too many catagories you could use iif:

iif(<condition> , <result if condition true> , <result if condition
false)

This is nestable, so the result if false can be another iif statement:

iif(length between 0 and 4.99,0,iif(length between 5 and
5.99,5,iif(length
between 10 and 14.99,10)))... etc.

Or maybe you could use somthing like this:

Category: Int([Length]/5)*5

Cheers,

Chris.

Thanks! It´s working perfectly. Just a b it long to write - I´ll have
about
20 ):s when I´m done.... But it was an easy enough query for me to manage.
=)
/Johanna

Do consider the 'Int' way of doing it if it's practical in your case. All
those nested IIFs and )'s can be a nightmare! You only need to miss one out
or get it in the wrong place, and it can take YEARS to find the problem
(well it sure feels like it!)

Chris ;-)