IIf statement help needed

[Pat_RI]

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

 

[Jeff Boyce]

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

 

[Pat_RI]

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.

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 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

 

[Ken Sheridan]

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
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.

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 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

 

[Ken Sheridan]

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

 

[Pat_RI]

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