calculate statistical median

[Ken D]

Does anyone know how to create a calculated field in a
query that will yield the statistical MEDIAN for a range
of values in the same record/row? Specifically, I have a
query in which each row represents a patient case. Each
column/field (item.1 through item.28) represents a
question/item in a survey tool. I wish to create a
caculated field (Score.1_28)that calculates the MEDIAN
across the row for each individual patient case. This is
quite easy in Excel, but no MEDIAN function exists in
Access. I have seen code examples on msdn.com but such
examples calculate the MEDIAN for an entire table or
column, not for each individual row.

Thanks in advance,
Ken D

 

[PC Datasheet]

Ken,

The reason you are having trouble is that your tables are set up incorrectly!
Should be:

TblPatient
PatientID
FName
LName
etc

TblSurveyQuestion
SurveyQuestionID
QuestionNum
Question

TblSurveyAnswer
SurveyAnswerID
PatientID
SurveyQuestionID
Answer

To get what you want, you now build a query based on TblSurveyAnswer, include
the field Answer.and set its criteria to the patientID you want the statistics
for.