3 Replies Latest reply on Apr 16, 2012 11:54 AM by philmodjunk

    Report Based on Checkboxes

    DominickReda

      Title

      Report Based on Checkboxes

      Post

      Afternoon everyone,

       

      I'm hoping this is fairly simple:

       

      I have a table with multiple fields - the ones in question today are (1) "Name" [text field limited a value list of 4 constant choices] and (2) "Type" [Checkboxes - a total of roughly 50 choices - some records may have multiple selections]

      I want to do a report as follows:

      Lets say we have Joe, Joe and Jane for the names and red, blue, green and white as checkbox choices. There are 5 records so far as follows:

      Joe - red, white

      Jane - green

      Joe - red

      John - blue, green

      Jane - white

       

      I want to make a report that groups by each name and shows a total count of each checked choice for each person. So based on the info above the report would yield:

      Jane

      Red - 0

      Blue - 0

      Green - 1 

      White - 1

      --------------

      John

      Red - 0

      Blue - 1

      Green - 1 

      White - 0

      --------------

      Joe

      Red - 2

      Blue - 0

      Green - 0

      White - 1

      --------------

      As always, thanks in advance for the help!

      Best,

      Dominick Laughing

        • 1. Re: Report Based on Checkboxes
          philmodjunk

          I think you need to modify the structure of your database so that you do not have a single check box field with 50 values in it. If you replace it with a related table of up to 50 records for each person--1 for each value selected for that person, producing your summary report becomes much easier to do.

          • 2. Re: Report Based on Checkboxes
            DominickReda

            OK - I think I see what you mean but would this work if the "person" is repeated multiple times?

            The real purpose is that I have a DB in which Contractors are issues "safety notices" so the contractors are the same set of four names but the safety issues (colors in my earlier example) change each time a new "violation" is written. Each contractor can receive multiple violations a day with different violation categories - we're trying to create a trending report to show where the contractors need to improve.

            Your help is much appreciated!!

             

            Best,

            Dominick

            • 3. Re: Report Based on Checkboxes
              philmodjunk

              I see no reason why it wouldn't work. You'd set up some tables in a many to many relationship like this:

              Contractors---<Contractor_Violation>-----Violations

              Contractors would be one record for each contractor.

              Violations would list each of your 50 violation types (and adding/changing them is now a data entry job, not a programmer's)

              Contractor_Violation has one record for each and every safety notice issued for a contractor.

              Contractors::ContractorID = Contractor_Violation::ContractorID
              Violations::ViolationID = Contractor_Violation::ViolationID

              You'd use a portal to contractor_Violation with fields added from Violations on your contractors layout to issue safety notices for that contractor.

              A more sophisticated structure could include a projects table for cases where you need to manage this for more than one project.