IIF then and conditional statements in queries.

[cgemmill1]

I am looking for assistance in creating a query. I am creating some hospital score cards and one indicator is falls. There are 95 hospitals that I need to create score cards for and they can choose up to 10 measures for falls. I want to create a query that essentailly picks which fall measure is listed on score card. Falls1 is my best measure and so on. Hospital had the option to choose any fall measure. How can I create a query to select the one they have chosen?

For example I want fall1 first, if blank then fall2, etc. Once I choose Fall1 I do not want the others retured in my query for that hospital.

I have tried the following Falls NUmerator: IIf([HRET_MeasureID]="EOM-Falls-37",[numerator],IIf([HRET_MeasureID]="EOM-Falls-38",[numerator],IIf([HRET_MeasureID]="MO-FALLS-4-1",[numerator],"missing")))

The challenge is that if a hospital has data for measure 37 and 4-1 it includes both. I want the query to only return on one of the falls measures. The returned measure should be based on the hieacrchy listed in the iif statement.

 

[jdraw]

With respect your question seems very much out of context.
I suggest you write down what you are trying to do and design your tables and relationships accordingly.

I have a feeling/hunch, that if you're doing a review/study of 95 hospitals, you will need some additional data.

Things that come to mind

Patient (Age, Gender, ReasonForAdmission...)
DateOfFall
SeriousnessOfFall

I think you will get ideas from this material.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[cgemmill1]

JDraw, thank you for your reponse. How is my question out of context? I already have a large database with all the data I am looking for. My boss would like me to create a scorecard for each hospital. We collect data on ten topics. For each of these ten topics hospitals have up to 8 measures they can collect to determine improvement in quality. For example falls, they can track all fall, falls with injury, falls with major injury.

Topics
Adverse Drugr Events
CAUTI
CLABSI
Falls

I want to assign a hiearchy to each measure so I can create the score card. For example Falls1 is the best measure. However a hospital may collect falls3 and another falls5. I want the query to select the best falls data to include in the score card. I can do this manually but want to automate the process. This is why I created the query.

I have used the IIF then to essentionally rank the measures. If a hospital has the 3 measures including Falls1 then use falls1. If they have falls3, falls5 and falls6. I want to choose falls5 and not display the others.

I hope this make sense. :banghead:

 

[jdraw]

You didn't mention this in post #1.

I already have a large database with all the data I am looking for.

This what you told us

I am creating some hospital score cards and one indicator is falls.

Thanks for a little context.
We still have no idea what Falls1, Falls3 or Falles5 means?
We only know what you tell us and I don't think there is a generic definition for Falls5 that 3 readers would agree on.

The readers of this list do not know you nor your environment; and have no familiarity with your database. For the average reader to understand consider this example:

Consider you are in the line up at McDonalds. You turn to the person behind you - who you don't know; who doesn't know you; has no idea of what Access is; has no background in hospital surveys/scorecards.

You say--" I'm considering using Falls3 or Falls5". The person says "What are you talking about"?

What do you say?


That's very similar to the audience here.