Jump to content

Temporary table based on current record?


Eman_Resu
 Share

This topic is 5762 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Newbies

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5762 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.