9 Replies Latest reply on Dec 29, 2009 4:08 PM by philmodjunk

    Calculation for select field

    BayArea123

      Title

      Calculation for select field

      Post

      Hi there,

       

      I will break this down for sake of simplicity.  I have 2 tables tblGreenhouse and tblApplication.  In table Greenhouse I have listed all the greenhouses in one field (Greenhouse) and amount of signs for each greenhouse in another field (Signs).

       

      The other table is an application table.  tblApplication. This is the working table where records are entered The tables are linked together.  On tblApplication I choose certain greenhouses using checkboxes, in field "GH".  I want to create a calculation that will add the # of signs needed together +4 (automatically) depending on which greenhouse(s) are checked off.  This will be a small number that will show up on the "Details" layout which already exists.

       

      Any help would be great. Thank you!

        • 1. Re: Calculation for select field
          philmodjunk
            

          Not sure I can follow your description of your database structure.

           

          Do you have a table where....

           

          You have a field that records the name or (better) serial number of a single green house and a second field signs that is a number field counting the number of signs for that greenhouse?

           

          Or do you just have a checkbox formatted field that lists all your greenhouses? If so, you should consider a redesign of your system here.

           

          I think you are dealing with the latter here and that's what's making life difficult for you. You can have both, a table where one record exists for every greenhouse and a checkbox field that lists all your greenhouses. With the table listing all your greenhouses, determining which sign fields to total up and then increase by 4 becomes a much easier proposition.

           

          Let me know if I'm on track and I or another poster can take you to the next step for here to do what you want.

          • 2. Re: Calculation for select field
            comment_1
              

            BayArea123 wrote:
            I want to create a calculation that will add the # of signs needed together +4

            Not sure what the "+4" means. To get the total amount of signs for the selected greenhouses, try =

             

            Sum (  tblGreenhouse::Signs )

            • 3. Re: Calculation for select field
              BayArea123
                

              Hi Phil,

               

              The tblGreenhouse is already filled out, much like a "lookup table" that has all the greenhouse numbers and numbers of signs already associated with them.  This table does not get updated.  It is linked to tblApplication which is has been converted to a layout where applications are recorded.  The "GH" field on this layout is actually based on a value list that I created.  So really the link between the 2 tables is there, but isnt really necessary or needed, until now. 

               

              Now I want the associated # of signs to be added up depending on which checkbox(es) are checked at the time of application. 

               

              Does that help explain it better?  Hopefully it does

              • 4. Re: Calculation for select field
                BayArea123
                  

                Thanks Comment

                 

                the +4 means I want 4 added to whatever the total is that results from the calculation, every time.  I am going to wait to hear back from Phil, I may be going about this the wrong way.

                 

                Thanks again

                • 5. Re: Calculation for select field
                  comment_1
                    

                  BayArea123 wrote:

                   

                  the +4 means I want 4 added to whatever the total is that results from the calculation, every time.  I


                  Well, then:

                   

                  Sum (  tblGreenhouse::Signs ) + 4


                  • 6. Re: Calculation for select field
                    philmodjunk
                      

                    Ok, this is how I read it.

                     

                    You have a check box formatted field such as:

                     

                    [ ] Greenhouse A

                    [ ] Greenhouse B

                    [ ] Greenhouse C

                     

                    and so forth. If the user selects greenhouse A, (5 signs) and Greenhose C, (2 signs), You want to display 7 signs + 4 extra = 11 signs correct?

                     

                    If your check box formatted field is the field you use in your relationship to link your two tables, then Comment's calculation, Sum (  tblGreenhouse::Signs ) + 4,  will return the total you want.

                    • 7. Re: Calculation for select field
                      BayArea123
                        

                      Thank you both, I will give that a shot.

                       

                      Question though, does it matter that the CheckBox formatted field is based off a value list?  The greenhouse names/numbers are the same that is in the other table, identically, but the field in tblApplication was formatted as checkboxes based off a value list that I made manually, rather than the records in "GH" field in tblGreenhouse.  If that makes sense.

                       

                      Thanks again for the help!

                      • 8. Re: Calculation for select field
                        comment_1
                          

                        A couple of points:

                         

                        1. If you have a table of Greenhouses, there's no point maintaining a custom value list listing all greenhouses - that's what the Greenhouses table is for. If you add/delete/modify a greenhouse, you want to do it once only.

                         

                        2. In the Greenhouses table, you shoud have a GreenhouseID field with auto-entered serial number. Your value list should use this field (also showing the name field), and the relationship should be based on matching Applications::GH to Greenhouses::GreenhouseID. This way, you can rename a greenhouse without breaking the relationship.

                         

                        3. Using a checkbox as the matchfield is often a convenient shortcut - but it does NOT establish a full-fledged many-to-many relationship:  you cannot enter any specific data pertaining to the join of Apllication X to Greenhouse Y, and you cannot produce some type of reports. For these, you need a third table to join these two.

                        • 9. Re: Calculation for select field
                          philmodjunk
                             Good points all. I would only use the relationship I described for this type of lookup function. A different relationship would be used for standard data entry.