Link to home
Start Free TrialLog in
Avatar of johnson00
johnson00

asked on

ASP code to create new column in database if all fields are full

I have an application that records test results.  I'd like to not have to continue to add additional columns to the database when users exceed the number of columns designed to hold test results, but I am not able to overwrite the test result either.

For example, the database is set at a default of allowing 3 separate test results, and cannot overwrite previous results recorded.  What is the best way to either create new columns for additional test results, or some action to move results from one tabe to an archive table if there is no more room for additional results?

Please let me know if I need to clarify.  Hard to explain!

Thanks!
Avatar of peh803
peh803
Flag of United States of America image

This sounds like a case of a database that needs to be redesigned.  The number of columns in a good db design should never need to change.  If you are finding that you need to add columns as time goes by, that means that you need to normalize your database structure.

So, you have some tests and some users and some scores, correct?

I will post a potentially better db structure here in a minute or two...

Regards,
Phil / peh803
Avatar of jitganguly
jitganguly

You should have 2 tables with one to many realtion

Test Master
Test Id - Key
Tester Name
etc.
etc.

Test Details : Test Id+Test Date = Key
Test Id
Test Date
and some other columns
ASKER CERTIFIED SOLUTION
Avatar of peh803
peh803
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm glad you were able to adapt this solution.  I think your life will be much easier with this kind of structure.

Let me know if you have any further questions on it.

Best regards,
Phil / peh803