query problems

[Kim T]

I need to have a query pull totals on questions met and not met.
I've set up 3 tables PatientInfo, Outcome, Questions

patient info table identifies patient with ID as P key
question table ask the 15 questions with patient id as F key
outcome table is for a dropdown box of Met, Not Met, NA which will be with
each question

how do I make a query that will pull total met, total not met, total na

 

[Jerry Whittle]

Are the 15 questions in one field or are there 15 question fields?

 

[Kim T]

15 questions field- I've put as q1, q2, q3 etc

Are the 15 questions in one field or are there 15 question fields?

 

[Jerry Whittle]

I was afraid of that.

Create a Totals query that gathers the data for the first question field.
Count on that field. Put in something like the following in one of the
fields: QuestionNo: "q1" .

Get the query working correctly for just the first question.

Next create a similar question for the second question.

Join these two queries together in a UNION query or at least in one SQL
statement. Get it to work correctly.

Once that is done, add the other 13 fields in the same way. Then pray that
no one ever decides to add a 16th question.

 

[Kim T]

is there away to change my question table to make it easier?

 

[Jerry Whittle]

Instead of across, like in a spreadsheet, think down. Here's how I'd probaly
do it.

Qid would be an autonumber primary key field to uniquely identify each record.

Patient_id would be the FK to the PatientInfo table.

Outcomes would be the FK to the Outcome table or you might just use the
Outcome table as a place to pick a value to store in Question.

I'd make the combination of patient_id and Question fields a unique index to
prevent duplicates. If the patient could have the same test more than once,
I'd add a date field and make it part of the unique index too.

Qid patient_id Question Outcome
1 1 q1 NA
2 1 q2 Met
3 1 q3 Not Met
and so on.

This way a totals query grouped on the Question and Outcome fields could
easily count the number of outcomes for each question. You could also just
group by Outcome and get an overall count. You could also group by
patient_id and Outcome and get those totals per patient.