My question is:
Is 100+ columns in one access table a bad idea?
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)
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