1 Reply Latest reply on Jan 14, 2010 10:44 AM by philmodjunk

    Calculations on numerous choices

      Title

      Calculations on numerous choices

      Post

      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.

        • 1. Re: Calculations on numerous choices
          philmodjunk
            

          I doubt any of us will be able to follow the logic either from your first post. Keep in mind that your different layouts may point to different tables or the same table. A field on a layout may be named "XYZ" on one layout and "ABC" on another.

           

          You'll need to open up Manage | Database | Relationships and look at the various table occurrences and their relationships, then go back to your layouts and examine them in layout mode to identify which layouts reference which table occurrences.

           

          If you can then post back here the table structure of your data base, identifying the data fields in question and the type of values stored in them, we'll have a better shot at helping you.

           

          Ultimately, you may have to apply the same solution Alexander the Great supposedly applied to the Gordian Knot problem. (He cut it with his sword.)