Creating an if/then field on a report - can it be done?

[teneesh]

We have a table that stores data from an evaluation. The table was created kind of funky. The evaluation data has 4 questions. So normally a table design like this would go as follows. One record would be: Evaluator, Evaluatee, Question 1, Q2, Q3, Comments.

Example 1 table storing one evaluation record
evaluatee evaluator q1 q2 q3 comments


This new table on the other hand is storing this data differently. So each record would store one answer to the question, example: Evaluator, Evaluatee Question1
next record, evaluator, evaluatee, question2, 3rd record: evaluator, evaluatee, question3, 4th record evaluator, evaluatee, Comments.

example 2 table storing 1 evaluation record
evaluatee evaluator q1
evaluatee evaluator q2
evaluatee evaluator q3
evaluatee evaluator comments

So each evaluation is now stored in 4 records and I am attempting to create a report from this.
Here is the scenario - everytime question 3 is blank, the comments field is not. so is there a way to add a textbox onto the report in access- that captures the comments field if question 3 is null? and if so - how do i do this?


Example 1 table storing one evaluation record
evaluatee evaluator q1 q2 q3 comments


example 2 table storing 1 evaluation record
evaluatee evaluator q1
evaluatee evaluator q2
evaluatee evaluator q3
evaluatee evaluator comments


Thank you!!

[NeoPa]

I would start off by sorting out the quesion.
Is Evaluator first or Evaluatee? You seem to jumble up the examples - some one way around and some the other.

What is the identifying field(s) that enable the system to determine which records are logically together?
How does the system determine which question is in which record (you don't indicate)?

Fundamentally, I would be looking to create a GROUP BY query with your data, but the information you've provided is too scant (and partly contradictory) to go into any more detail than that.

If you can provide some reliable information we can look more closely at the sort of query we would need.
Please provide your metadata in the following format :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time