Temporary table based on current record?

[Eman_Resu]

I am creating a database that stores student exam data. Each exam question has three corresponding database fields: objective measured (there are many more questions than objectives), student response and correct response. I need to create a design that takes the fields from a single student exam record and creates a temporary table (or temporary repeating fields) where each record in the temporary table corresponds to a single question.

The reason for this is that the exam questions are randomly distributed such that there are not predetermined clumpings of questions associated with a specific objective. I need to produce a list that orders the questions from a single student exam by objective rather than question index.

This is a simplified example of what I am trying to accomplish:

Field Schema for Original Table:

Objective1

Objective2

Objective3

StudentResponse1

StudentResponse2

StudentResponse3

CorrectResponse1

CorrectResponse2

CorrectResponse3

The number suffixes on each fieldname correspond to the question index.

Field Schema for Temporary Table:

Objective, Question, StudentResponse, CorrectResponse

So in the context of this example, the Temporary Table would have 3 records corresponding to the exam questions.

Example temporary list before sort:

3, 1, A, A

1, 2, B, B

2, 3, C, C

Example temporary list after sort:

1, 2, B, B

2, 3, C, C

3, 1, A, A

The order of exam questions and objectives vary, so the temporary list has to be regenerated for each record (I only expect to look at one record at a time).

Any advice of how to approach this problem (I have FileMaker 8 Pro Advanced).

-Eman Resu

[bruceR]

You don't need a temporary table and you DEFINITELY don't need repeating fields. You just need a standard relational design, where the responses go in a separate table and each response record has fields testID, questionID, and response. Anytime you see a design using numbered fields like you suggest - question1, question2, etc. it should instead use a relational design.

[Eman_Resu]

Hi BruceR,

First, I thank you for your advice. I see how the relational design that you suggested would lend itself to generating the single student report I described. However, there is another report that I already have implemented with the current design (FM5, yikes) that I am not sure how to reimplement with the relational design. In short, I have a report in which the rows are student records and the columns are their responses, with the columns ordered by ascending question index. It is not clear to me how I can generate the many columns if I only have a single field for student responses. I have attached a better example of the two reports I am working on. Well, I have much to learn about relational designs and the new methodologies of FM >=7. I appreciate your time and comments that help me along my way.

-Eman

example_reports.pdf

[bruceR]

You have much more reporting flexibility if the data starts out properly normalized. And repeating fields can sometimes be a good choice for reportinb, but NOT as an original data source.

In the case of your first report, the normalized data, 1 response per record, is really easy. You also need to know about a feature that began in 7, where you can split out portal rows. I think you can understand that if you used a normal portal for your five responses, they would appear in five rows. Just create a "horizontal portal" - make one portal that only shows row 1; to the right, another single cell portal that only shows row 2; etc. You will need to look carefully at the new portal setup options while you are in layout mode.