Table set up when you have more than the available fields

[KristenSA]

Hi.


I have a project that I've been spinning my wheels on and was hoping someone could just point me in the right direction. Access might not be the best tool for this, but I want to use it because it would make entry and reporting easier for the end user.
Here's what I'm trying to do....

For each account (this is unique) we have a series of questions that need to be answered. Some questions will be Yes/No and some will be a selection of three choices (L, M, S). I'll need a report that is basically a score sheet of those answers (basically how many of each answer). The problem is there are about 320 questions and each of those questions will also have a note field, so I'll have a total of about 640 fields, which I know if way too many for one table. I can break these questions down into about 20 categories.

From the data entry side, I'll have a form with all the account information at the top (account number, names, dates etc.). Then I will have a tab control with tabs for each category. Since there is a limit to how many controls on a form, I'm thinking I need to make each tab/category a subform.

My problem is the best what to set up the tables. My original thought is to have a table for each category (Category ID, Question 1, Question 1 Note, Question 2, Question 2 Note, etc). This allows me to have a field for each question and note on the form for entry. My concern with this is the reporting side, because I would still need to pull all the questions together into a query to get summary info and I think that it will be too many fields.

My other thought was a "Responses" table that would have a lot of records, but not so many fields (AccountID, CategoryID, QuestionID, Answer, Note). I have attached sample tables. So, in this case there would be 320 records added to the table for each account (we would do an average of 10 accounts per month, so about 3200 records added each month). This seems much cleaner, but then I was having issues with the data entry side (I don't have a field for each question to add to the form for the end user).

Would one of these option work or is there a better way I should look into?
Any advice is greatly appreciated.

[June7]

Have you searched the forum for topic 'questionnaire database'? I think there are several discussions.

Unfortunately, normalized data structure often will not look like a paper form. Time and Attendance form with days arranged horizontally is one example of this. Data entry personnel need to understand that and be educated on how to read data on the paper form and transfer to digital db entry form.

Unless you want to use UNBOUND data entry form to present the appearance of paper form. Requires a LOT of code to read/write data.

[KristenSA]

Thank you for your feedback.

I have searched for questionnaire databases and haven't found anything that will help (nothing I've found has had as many fields as I do).

This will be a new process for us and I want to get it all in from the beginning so that we can report on the responses. I'm not trying to replicate any paper forms so the design is completely open to whatever I want to do to make it user friendly (only one user). To get started while I build the database, they have all the questions in Excel. That works fine, but won't be easy to report on once there are a lot of them. I was hoping to avoid a lot of code, but maybe that is my only option.

Thanks for you help.

[Bulzie]

I would suggest just break it up like you mentioned. Have your main Account table with AcctNumber as the key. Then break the questions up to say 50 question fields, 50 answers fields, 50 note fields so you might have 7 of these tables. Their structure would be like:
tblSet1:
Set1ID(autonumber)
AcctNumber (long integer) this will link back to tblAcctInfo
Q1_Question
Q1_Answer
Q1_Note
Q2_Question
Q2_Answer
Q2_Note
....
Q50_Question
Q50_Answer
Q50_Note

tblSet2:
Set2ID(autonumber)
AcctNumber (long integer) this will link back to tblAcctInfo
Q51_Question
Q51_Answer
Q51_Note
Q52_Question
Q52_Answer
Q52_Note
....
Q100_Question
Q100_Answer
Q100_Note

Do the rest of them. Once you have the tables, create the 7 subforms, place in the tabs and use AcctNumber in the LinkMaster and LinkChild property. When all done, to get this on a report you will need to do subreports just like the subforms to get it all in 1 report. Might be easier way to use the 1 table and figure which question you are on or something.

[June7]

Identify data entities and relationships. Build tables accordingly. It is a balancing act between normalization and ease of data entry/output. Normalize until it hurts, denormalize until works.

[Micron]

1) did you look here for a question db model - http://www.databaseanswers.org/data_models/
2) info on normalization, which you should read if not familiar (we might suggest the perfect schema, but how would you create your own next time?)
b) and a bunch of other good stuff...
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp

3) to me, it seems the schema would resemble

tblAcct
AcctID	AcctName
1	Acme
2	Apex
3	Alcoa

tblQuestion
Qid	Question
1	What is your name?
2	Favorite color?
3	Who's yer Daddy?

tblAcctQuestion
AcctQ_ID	Acct_FK	Quest_FK
1	1	2
2	1	3
3	2	1
4	3	2

Account 1 is asked q's 2 & 3. 2 is only asked q1, and 3 is only asked 2 (based on the values shown in the linking table tblAccountQuestion).
If you wanted to report on who gets asked q2, it should be easy. I'm not seeing a need for a whole bunch of code...

EDIT: forgot you'd need a notes table, but if you get the drift and I'm on the right path, you can probably figure out how that fits in. At least you should be able to after studying normalization.

[KristenSA]

Bulzie:
Thank you for your feedback.
This is what I was originally leaning towards, because it would make the forms easier, but I'm still concerned about the reporting.
Set up just like you said, I'd have 20 categories with a varying number of question in each, 20 subforms on 20 tabs and so forth. For each account, the user would have to answer every question.
This is a quality control database so each question has a "responsible party" (which I will deal with in the queries).

For the reporting, I would need to query all the questions (there could be 3200 or more per month) to see how many each responsible party had of each answer.
Example-
Bob:
400 Low
300 Moderate
500 Critical

John:
600 Low
200 Moderate
700 Critical

I see how I could get this data for each category using subreports, but I don't know how I'd be able to get all questions as a whole.


Micron:
Thank you for all the links. I will look to see if there is an existing model on that website. I have been reading up on normalization, but will check out the links you suggested.

What I have right now is similar to what you are suggesting, but I'm not seeing how I wouldn't need a lot of code. How would I populate the table? I'm thinking I either need a field in the table for the entered data to go (field for each question) or I'd have to have unbound fields on the form that would need to be appended. Is there another way I'm missing?

I have a questions table that is just a list of all of the questions like you show.
My Responses table has the Account ID, Question ID, Answer and Note
In my form (a subform for each category) I have unbound fields for the answer and the note, then I have code to append the fields in the Responses table. This seems like it will make reporting easier because I'd just basically be filtering the responses table, but it's a lot of code to append each record. The code is very simple, it will just be ALOT of it.

Thank you all for you help.

[Micron]

Set up just like you said, I'd have 20 categories with a varying number of question in each (questions go into their own table), 20 subforms on 20 tabs and so forth. For each account, the user would have to answer every question.

Answers go into their own table. You need tblCategories as well, and maybe a few others if you want to avoid free form fields (e.g. for responsible persons, so that Rodger doesn't get entered as Roger when they are one and the same).

I see how I could get this data for each category using subreports, but I don't know how I'd be able to get all questions as a whole.

This could be as simple as a Totals query, grouped first by 'responsible' then by category if all you want is totals. Or all questions grouped by responsible if you want to list the questions. Sounds like one report for each way - I'm not seeing a need for subs at this point.

20 subforms on 20 tabs

or one form with one sub. Main form, user selects the category from a combo & the resulting questions get listed on one subform. If they are questions to be answered, the question field is locked (except maybe for Notes, if an admin is doing notation). If they are answered questions, all the fields are locked. Or a report can print out the questions on paper if that's how they are being answered, or the answers can be on a report.

I'm not seeing how I wouldn't need a lot of code. How would I populate the table?

Not sure if you're thinking a query is code - it's not, really. Sure, there'd be some code (the more sophisticated the db needs to be, the more you'd need). The answer is, you populate a table via a query whenever possible or practical, in order to avoid writing code that takes 30 lines instead of one query. Plus, any form/query/report that can be re-used should be; i.e. not one form for entering data, another for editing, one for viewing - when it's all the same fields.

I get the feeling your Access knowledge level is not helping you foresee how this could be designed. I say that only because I spent 6 years in ISO 9000 and 14001 (left just as TS came out) and a questions db is not foreign to me. I didn't create the one we used, but if I put my Access and QS knowledge together, I think I feel confident in making some of these claims.

[Bulzie]

Correction on my post, I looked back at a DB I had done this same process and I do not have a field for the questions, those are hardcoded on the forms and reports. So each of your category tables would have the answer and note fields. Doing a report is not too bad using the subreports(you can copy the fields and structure from the subform to the subreport and add the recordsource, etc. On getting the data, you would link the category tables to the main AcctInfo one, but yes there is a limit there as well. But maybe you can export each section if needed to excel and then combine them outside of access. Again, not sure this is the best way, others might have better solution, this is just how I got it to work on the project I was doing.

[KristenSA]

I get the feeling your Access knowledge level is not helping you foresee how this could be designed. I say that only because I spent 6 years in ISO 9000 and 14001 (left just as TS came out) and a questions db is not foreign to me. I didn't create the one we used, but if I put my Access and QS knowledge together, I think I feel confident in making some of these claims.

I appreciate your input based on your 6 years of experience, but I've been working in Access for a very long time and know that code and query are not the same thing. My questions are not how to design a database, but how to work around the Access limitations and uncover any unforeseen issues those workarounds may uncover.


Looking the two options discussed, having a Responses table to house all the answers would be clean and easy to report on, but causes a lot of extra work on the table/form side. There would be a lot of unbound fields and appends (via query or VBA) to populate the table.

The other (and preferred option) is to have tables for each category with fields for each question. The first problem with this is the limit on the number of controls on a form (754 for the lifetime of the form), which is easily worked around by using sub forms. Then the query limitations on the number of tables, joins and fields causes the issue with reporting. I will definitely exceed the number of joins and fields and could possibly go over on the number of tables as well.

The categories and questions are not yet set in stone, so I'm working with the user to try to reduce the number so the Access limitations won't be an issue. If we can't do that, I will work to build multiple queries, that work within the Access constraints, to consolidate the data for reporting.

Ultimately, this should be a simple database for one user to collect data on 10 accounts a month and run a couple simple reports, rather than shuffling through multiple Excel files.

Thanks to all who offered suggestions.