Nested IIF

Hi folks!

I have a currency field in a query (called "ave1") and I want to create a
classification field based on the following so the new field should consist
of the numbers 1 to 5:

£35-£45 – Group 1
£45-£55 – Group 2
£55-£65 – Group 3
£65-£75 – Group 4
£75- – Group 5

The bottom limit should be "=" and the uipper limit should be "". Group 5
should be £75 or higher.

As a test I have the following:

Expr1: IIf(35=[Ave1]45,1,0).

Is this correct? Or should there be an "OR" statement in there as well?

cheers.

This should do it!

iif([avel]35,0,iif([avel]45,1,iif([avel]55,2,iif([avel]65,3,iif([avel]75,4,5)))))


I have a currency field in a query (called "ave1") and I want to create a
classification field based on the following so the new field should consist
of the numbers 1 to 5:

£35-£45 - Group 1
£45-£55 - Group 2
£55-£65 - Group 3
£65-£75 - Group 4
£75- - Group 5

The bottom limit should be "=" and the uipper limit should be "". Group 5
should be £75 or higher.

As a test I have the following:

Expr1: IIf(35=[Ave1]45,1,0).

Is this correct? Or should there be an "OR" statement in there as well?

cheers.

Cheers! :-)


"Jason Lepack" wrote:

This should do it!

iif([avel]35,0,iif([avel]45,1,iif([avel]55,2,iif([avel]65,3,iif([avel]75,4,5)))))


I have a currency field in a query (called "ave1") and I want to create a
classification field based on the following so the new field should consist
of the numbers 1 to 5:

£35-£45 - Group 1
£45-£55 - Group 2
£55-£65 - Group 3
£65-£75 - Group 4
£75- - Group 5

The bottom limit should be "=" and the uipper limit should be "". Group 5
should be £75 or higher.

As a test I have the following:

Expr1: IIf(35=[Ave1]45,1,0).

Is this correct? Or should there be an "OR" statement in there as well?

cheers.

Instead of a hard to maintain nested IIf statement, consider using a Case
statement. Paste the following in a module and compile it.

Function fGroups(strGroups As Variant) As Long
Dim TheGroups As Long
Select Case strGroups
Case 35 To 44.99
TheGroups = 1
Case 45 To 54.99
TheGroups = 2
Case 55 To 64.99
TheGroups = 2
Case 65To 74.99
TheGroups = 4
Case Is = 75
TheGroups = 5
Case Else ' Other values.
TheGroups = 0
End Select
fGroups = TheGroups
End Function

In your query, just put the following:

fGroups

Also you really shouldn't create a new field in a table if that's what you
mean. Any time that you want to see the group, use the function above.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"scubadiver" wrote:

Hi folks!

I have a currency field in a query (called "ave1") and I want to create a
classification field based on the following so the new field should consist
of the numbers 1 to 5:

£35-£45 – Group 1
£45-£55 – Group 2
£55-£65 – Group 3
£65-£75 – Group 4
£75- – Group 5

The bottom limit should be "=" and the uipper limit should be "". Group 5
should be £75 or higher.

As a test I have the following:

Expr1: IIf(35=[Ave1]45,1,0).

Is this correct? Or should there be an "OR" statement in there as well?

cheers.

Strange. Some of what I typed in is missing.

TheGroup: fGroups([ave1])

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Jerry Whittle" wrote:

Instead of a hard to maintain nested IIf statement, consider using a Case
statement. Paste the following in a module and compile it.

Function fGroups(strGroups As Variant) As Long
Dim TheGroups As Long
Select Case strGroups
Case 35 To 44.99
TheGroups = 1
Case 45 To 54.99
TheGroups = 2
Case 55 To 64.99
TheGroups = 2
Case 65To 74.99
TheGroups = 4
Case Is = 75
TheGroups = 5
Case Else ' Other values.
TheGroups = 0
End Select
fGroups = TheGroups
End Function

In your query, just put the following:

fGroups

Also you really shouldn't create a new field in a table if that's what you
mean. Any time that you want to see the group, use the function above.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"scubadiver" wrote:

Hi folks!

I have a currency field in a query (called "ave1") and I want to create a
classification field based on the following so the new field should consist
of the numbers 1 to 5:

£35-£45 – Group 1
£45-£55 – Group 2
£55-£65 – Group 3
£65-£75 – Group 4
£75- – Group 5

The bottom limit should be "=" and the uipper limit should be "". Group 5
should be £75 or higher.

As a test I have the following:

Expr1: IIf(35=[Ave1]45,1,0).

Is this correct? Or should there be an "OR" statement in there as well?

cheers.

On Feb 12, 4:30 pm, Jerry Whittle
wrote:
Instead of a hard to maintain nested IIf statement, consider using a Case
statement. Paste the following in a module and compile it.

Function fGroups(strGroups As Variant) As Long
Dim TheGroups As Long
Select Case strGroups
Case 35 To 44.99
TheGroups = 1
Case 45 To 54.99
TheGroups = 2
snipp

Instead of a VBA procedure, because this is after all the 'Queries'
group, how about using Jet SQL e.g.

SWITCH(
ave1 BETWEEN CCUR(35.0000) AND CCUR(44.9999), 'Group 1',
ave1 BETWEEN CCUR(45.0000) AND CCUR(54.9999), 'Group 2',
ave1 BETWEEN CCUR(55.0000) AND CCUR(64.9999), 'Group 3',
ave1 BETWEEN CCUR(65.0000) AND CCUR(74.9999), 'Group 4',
ave1 CCUR(75.0000), 'Group 5',
TRUE, '{{NO GROUP}}'
) AS group_name

But because this is a group about SQL DBMS, how about using *data*
rather than hard-coding the logic into the front end or the schema
(hint: inflation may cause the groups' limits to change):

CREATE TABLE Ave1Groups (
group_name VARCHAR(10) NOT NULL PRIMARY KEY,
min_value CURRENCY
DEFAULT -922337203685477.5808 NOT NULL
UNIQUE,
max_value CURRENCY
DEFAULT 922337203685477.5807 NOT NULL
UNIQUE
)
;

You'll probably want some more constraints to ensure min_value =
max_value and unambiguous value ranges: no overlaps, no gaps, etc.
Those DEFAULT values may look ridiculously extreme but that's what you
get when you choose CURRENCY ;-)

INSERT INTO Ave1Groups
(group_name, min_value, max_value) VALUES (
'Group 1', CCUR(35.0000), CCUR(44.9999))
;
INSERT INTO Ave1Groups
(group_name, min_value, max_value) VALUES (
'Group 2', CCUR(45.0000), CCUR(54.9999))
;
INSERT INTO Ave1Groups
(group_name, min_value, max_value) VALUES (
'Group 3', CCUR(55.0000), CCUR(64.9999))
;
INSERT INTO Ave1Groups
(group_name, min_value, max_value) VALUES (
'Group 4', CCUR(65.0000), CCUR(74.9999))
;
INSERT INTO Ave1Groups
(group_name, min_value) VALUES (
'Group 5', CCUR(75.0000))
;

To test the table:

CREATE TABLE Test (ave1 CURRENCY)
;
INSERT INTO Test (ave1) VALUES (50)
;
INSERT INTO Test (ave1) VALUES (0.01)
;
INSERT INTO Test (ave1) VALUES (123456789.1234)
;

Finally, the query:

SELECT T2.ave1,
IIF(DT1.group_name IS NULL, '{{NO GROUP}}', DT1.group_name) AS
group_name
FROM Test AS T2 LEFT JOIN
(
SELECT T1.ave1, G1.group_name
FROM Test AS T1, Ave1Groups AS G1
WHERE T1.ave1 BETWEEN G1.min_value AND G1.max_value
) AS DT1 ON T2.ave1 = DT1.ave1;

The idea is to allow the groupings' value ranges to change without
having to re-write your schema DDL, DML, front end code, etc. Put
another way, you change the data (easy, low risk, customer's side,
etc) rather than change the schema (difficult, high risk, coder's
domain, etc).

Jamie.

--

Jamie said: "But because this is a group about SQL DBMS, how about using *data*
rather than hard-coding the logic into the front end or the schema
(hint: inflation may cause the groups' limits to change): "

No doubt that your solution is ultimately the most correct. I hate long
nested IIf's, lines of Case statements, and in Oracle, long Decode statements.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.