IIf statement help needed

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pat_RI - 14 Feb 2008 00:36 GMT

I am trying to create an IIF statement where if Plan="den1" then look to see
if plan="med1" return "medical" if not return Plan. What I am trying to do
is if a person has DEN1 then look to see if they have MED1 and if they do
return MEDICAL if not return whatever the Plan is equal to.

table would look something like this

ID Plan Level
1 den1 1
1 med1 1
2 den1 2
2 med2 2
3 den1 3
4 den1 3
4 med1 3
5 med1 1

What I am looking for results :

ID Plan Level
1 den1 1
1 medical 1
2 den1 2
2 med2 2
3 den1 3
4 den1 3
4 medical 3
5 med1 1

Reply to this Message

Jeff Boyce - 14 Feb 2008 00:54 GMT

I can't be sure from the example you gave, but it seems like you could look
at this a different way.

Would you get the same result if you took every combination of ID + Plan,
then took the Max([Level]) for each of those unique combinations?

The problem with using your approach (if Plan="den1", then if Plan ="med1")
is that the query works against ONE ROW at a time. Plan would NEVER be both
"den1" AND "med1" in the same row, right?

Regards

Jeff Boyce
Microsoft Office/Access MVP

>I am trying to create an IIF statement where if Plan="den1" then look to
>see if plan="med1" return "medical" if not return Plan. What I am trying to
[quoted text clipped - 24 lines]
> 4 medical 3
> 5 med1 1

Reply to this Message

Pat_RI - 14 Feb 2008 01:21 GMT

That is correct they will always be in separate rows. What I was thinking
of doing was making a make table query that pulls every "den1" row to
isolate those IDs. Link that table back to the original Plan table and use
iif(plan=med1,medical,plan). The one thing I don't know how to do is make
an Access query to return all rows from table2 where table1 ID doesn't equal
table2 ID. The reason I say this is because once I change the med1 to
medical I need to return all rows with eff_date =date()-1 so I was thinking
of making a table of all of the medical rows an all other rows for those
ID's then append the remaining rows.

>I can't be sure from the example you gave, but it seems like you could look
>at this a different way.
[quoted text clipped - 39 lines]
>> 4 medical 3
>> 5 med1 1

Reply to this Message

Ken Sheridan - 14 Feb 2008 11:20 GMT

If I understand you correctly I think you should be able to do this without
the need to create a table by using a UNION ALL operation, each part of which
uses a subquery to check if any other relevant row for the current ID exists
or not:

SELECT ID, Plan, Level
FROM YourTable As T1
WHERE Plan <> “med1”
OR (Plan = “med1”
AND NOT EXISTS
(SELECT *
FROM YourTable AS T2
WHERE Plan = “den1”))
UNION ALL
SELECT ID,”Medical”, Level
WHERE Plan = “med1”
AND EXISTS
(SELECT *
FROM YourTable AS T2
WHERE
Plan = “den1”);

Ken Sheridan
Stafford, England

> That is correct they will always be in separate rows. What I was thinking
> of doing was making a make table query that pulls every "den1" row to
[quoted text clipped - 48 lines]
> >> 4 medical 3
> >> 5 med1 1

Reply to this Message

Ken Sheridan - 14 Feb 2008 18:18 GMT

Doh! I really messed that up (I'll blame the cable company engineer who
interrupted me while I was drafting my reply). Firstly, I didn't correlate
the subqueries, and secondly I missed the FROM clause out of the second part
of the query. It should have been:

SELECT ID, Plan, Level
FROM YourTable As T1
WHERE Plan <> "med1"
OR (Plan = "med1"
AND NOT EXISTS
(SELECT *
FROM YourTable AS T2
WHERE T2.ID = T1.ID
AND Plan = "den1"))
UNION ALL
SELECT ID,"Medical", Level
FROM YourTable As T1
WHERE Plan = "med1"
AND EXISTS
(SELECT *
FROM YourTable AS T2
WHERE T2.ID = T1.ID
AND Plan = "den1");

Apologies for the confusion.

Ken Sheridan
Stafford, England

Reply to this Message

Pat_RI - 15 Feb 2008 02:09 GMT

Thanks. Those damn cable guys will do it all the time.

> Doh! I really messed that up (I'll blame the cable company engineer who
> interrupted me while I was drafting my reply). Firstly, I didn't
[quoted text clipped - 26 lines]
> Ken Sheridan
> Stafford, England

Reply to this Message