How to add fields based on a different field value? Do i need a diff. design?

[w2wgamer]

I want to be able to put in a field "number of tests" and have fields automatically added that correspond to the number of tests.



I am running tests on a machine that runs tests on a certain part, say part A, and I run part A through the machine various times on various days. Each round may have anywhere from 1-100 trials yielding 1-100 results, 1 for each trial. I was thinking of having each round be a key field, and then having another field with the number of trials run (say X amount of times) then having X amount of fields be added to input the results. Part A will have multiple rounds, and there are multiple parts that need to be entered.

Is this possible? Should I redesign my organization? Please help! I am lost

Thank you so much for your help!!

[Rod]

Should I redesign my organization?

Yes, I think you should attempt to normalise your data. Reading your post I identify the entities: Part, Machine, Round (or Test) and Trial. There may be many others and you may not be particularly interested in formalising Machine or Part. Also Test may be another entity in itself if it is possible to perform different tests on a Part.

So as somewhere to start, I envisage a two-table database: tblRound and tblTrial. The relationship is that tblRound has a one-to-many relationship to tblTrial. You do not need to specify how many trials are to be/were performed; you simply enter trial results until your test data is exhausted.

Guessing at some of the table contents:

tblRound
ID (a meaningless primary key - use autoincrement)
TestDate (avoid the use of date on its own)
Part (this may in future become a foreign key)
Machine (this may in future become a foreign key)
...

tblTrial
ID (a meaningless primary key - use autoincrement)
RoundID (a foreign key related to tblRound)
TestResult
...

I don't know how you record your test results so tblTrial looks a bit spartan.