I am trying to create a table that contains a numerator and denominator for each patient in a population for each of 22 health measures. My goal is to know, for a certain population, what percentage of all of the eligible patients had that measure done (flu shot, pneumo shot, BP reading, etc.)
My query looks at the data, figures out whether the patient is eligible for that measure, and if so, assigns the number 1 as the denominator. If the patient had that measure done, the numerator is 1, and if not, it is zero. For example, patient X had a flu shot during 2013. The criteria is that the patient is over 18 years old and did not refuse the vaccination. If they meet these conditions, the denominator field is calculated as 1. If they had the shot, the numerator field is calculated as 1.
I know that in Access parlance it's better not to store a calculated field in a table, but I need to report on all of these 1's and 0's for 22 different measures, by patient, by attributed physician, and the like.
Is there a way to store these values in a table? Because when I try to change the query type from "select" to "make table", I get the error described in the title of this post.
Any help would be appreciated.