Calculations on numerous choices
I have inherited a database that I am trying to clean up, however I am not a very experienced user. I have access to version 8 and v10 Advanced for development, however my users access the database in version 8 (PC).
Here is the issue: there is a section of the database in which users can create an evaluation form with a 5 point scale for events they have hosted. They have a choice between 3 different forms with different focus areas (each choice goes to a separate layout), then they have the choice of creating a 6 or 7 or 8 or 9 or 10 or 11 or 12 question form (each choice goes to a separate layout). The questions are created by using an 'item number' field and 'description 'field from a table. All of the forms are looking to the same table for 'item number.' Their are over 750 options they can choose from. (I know I am already overwhelmed with the number of options in play!) Creating the evaluation form doesn't seem to be the problem, however data entry and summarization of the results is. The person who designed the database created a layout with fields for all 750+ 'item number' options and you have to search through 5 tabs that contain sub-sections of the 'item numbers' to enter the results into the field box for the particular 'item number' that corresponds to the 'item number' used to create the eval form question. To view summarized data there is another layout that shows fields for all 750+ items as well as a 'Mean' and 'Standard Deviation' field for each of the 750+ options across 5 tabs and you have to look through the tabs to see the results.
It seems to me there should be something I can do with calculation, lookup and/or summary fields that should pick up which field was originally used in the evaluation form and return the results for just this field, etc. for just each of the original questions (item number) used. Ideally these results would be shown on a layout similar to the original evaluation form, that shows the question used as well as the results. It doesn't seem to me that we need all of these fields when a calculation should return the results.
I apologize in advance if I wasn't clear enough to follow, but my logical mind isn't quite capturing the logic in the current design. Any suggestions on ways I can simplify any part of this would be much appreciated. As it is, the data is relatively useless as it often doesn't get entered and their are too many fields to sort through for the results.