Access 2010 100+ table columns

Hi,

My question is:
Is 100+ columns in one access table a bad idea?

The reason:

I need to have a table for an access form that has 50+ questions for the user to answer.
However, each question will have 2 answers. The first answer is a number between 1-100 that the user enters into the form field (the amount), the second answer is a value of a sum that is auto calculated by the afterUpdate event for each question (the result).

My table (tblQuestionSetIFA)
QuestionSetIFA_ID
Client_ID
Question1Amt
Question1Result
Question2Amt
Question2Result
and so on for the next 48 questions

My concern is the amount of columns in the above table will be in excess of 100.

Should I have two tables, one to store the QuestionAmt and one to store the QuestionResult in order to reduce the amount of columns.

FYI, the database is never going to be a large scale database as the amount of clients in the database is not likely to exceed 10,000 over the next 5 years, with each client being linked to 1 question set per year
andrewpiconnectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Use three columns instead...

QuestionNo
Amount
 Result
0
mbizupCommented:
That would be my approach but other people may have better methods to handle the number of questions per form.
0
andrewpiconnectAuthor Commented:
three columns.....50+ tables??
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

andrewpiconnectAuthor Commented:
one table for each question do you mean?
0
hnasrCommented:
Normalization!: When you are faced with such "code1, value1, code2, value2, ..." then you need normalize the data, by using a separate table "code, value" with the addition of an id for that table.
personID, questionID, question, reselt.

To have a set of questions, you need to add a field to reflect the setID.

Check if previous comments are of any help, incorporate in a sample database and upload.
0
mbizupCommented:
<<one table for each question do you mean?>>
No... One table for the actual questions:

QuestionID  (Autonumber)
QuestionText

It would have fifty (or however many records)

And another for the results:

ResultID  (Autonumber)
UserID  (Foreign key -- ID from users table)
QuestionID (Foreign Key -- ID from questions table)
Amount
Result
CorrectYesNo
'etc...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
There are variations on this of course... for example, if your questions have *exact* answers such as True/False or specific options that can be included in a combo box, then you can include a CorrectAnswer in the questions table and provide immediate "grades" by querying the users' responses in comparison with the 'correct' answer listed in the questions table.
0
andrewpiconnectAuthor Commented:
Ok, i understand now.

Two tables it is. One for the questions, one for the results.

IYHO tho, generally do you think that having a table with 100+ columns should be avoided at all costs or only used when absolutely neccessary?
0
mbizupCommented:
Yes..  only when absolutely necessary.

If there is any possible way to represent a lot of fields with just a few, I will implement the solution with a few fields.  It makes displaying and reporting the data a lot more straight forward.
0
mbizupCommented:
Regarding the absolutely necessary... I have *never* come across a need for 100+ fields...

But I'm not ruling out the possibility that it may be best or necessary in some situations.
0
hnasrCommented:
I am still, did not get the full message the poster wants to convey.

QuestionsTable: Underlined fields form Primary Key.
EmployeeID (FK)
TestSetID      (FK)
questionID
QuestionText
QuestionResult

TestSetsTable: EmployeeID can be removed from above table if it is linked to a TestSetID, leaving:
TestSetID      (FK)
questionID
QuestionText
QuestionResult

EmployeeTests: Another table will be introduced:
EmployeeID (FK)
TestSetID      (FK)
other descriptive fields.

I should wait until he decides what approach to follow. A sample database with expected output reduces more guessing and assumptions from our side.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.