1 Reply Latest reply on Apr 28, 2009 7:24 AM by Orlando

    Calculations on main Table

    JDenault

      Title

      Calculations on main Table

      Post

      Hi there, 

       

      I have the following fields on my main application table where data is added:

       

      Application Date (current date, defaults)

      Re-Entry Date (current date +1 defaults)

      Application Time (drop down menu of half hours, entered in this format - "5:30pm")

      Re-Entry Time (?) 

      Control Agent (check box list of pesticides, maybe 20-25 or so)

       

      Each Pesticide has a Re-Entry Interval associated with it in a different table (6hours, 7hrs,  12 hours, 18 hours etc etc etc) They are all stored as numeric whole numbers

       

      My question is, is there a way to have the Re-Entry Time autofill according to the current Re-Entry Interval in the other table, once the Pesticide is checked in the box?   I would want this based off the Application Time that is entered +1 hour ( Re-Entry starts once application is finished, generally an hour)  

       

      To make things more complicated, there are times we spray 2 pesticides at once.  The Re-Entry Interval would then be the larger of the 2 Re-Entry Interval, plus half the smaller Re-Entry Interval. 

       

      Basically I am trying to bypass us adding these times in our head and manually filling in "Re-Entry Time"  as we have set up currently in Access.  Not sure it is possible though.  It is probably a complicated calculation if even possible

       

      Thanks 

        • 1. Re: Calculations on main Table
          Orlando
            

          Hi J Denaults

           

          You could setup Auto-enter Calculated value to get the Re-Entry Interval from the Pesticides table, you will need to create a link between the Control Agent field in your main applications table and the Pesticides table, if you don't have one already, and I would advise using an ID from the Pesticides table to select your Pesticide, again if not already.

           

          Also worth checking the format of the values in Application Time, are these time formats? For now I will assume they are. 

           

          Ok create a field called ReEntryIntervalMax and make it a number and click on options and tick the option for 'Calculated value' under the Auto-Enter tab.

           

          Now in the calculation dialog that pops up input the following:

           

          Max ( Pestercides:: Re-Entry Time ) 

           

          and click 'OK' and then un-tick the option for 'Do not replace existing value of field (if any)' 

           

          Click 'OK' and this will always grab the largest interval time from the Pesticides table whenever you select a pesticide.  

           

          Now do the same for the minimum value, so do the same but for ReEntryIntervalMin and input the calculation

           

          Min ( Pestercides:: Re-Entry Time ) 

           

           

          Once you do have the Re-EntryInterval fields value in your main application table you can then make your Re-Entry Time an Calculation and input the following:

           

          ( Application Time + 60 ) + 
          If ( ValueCount ( Control Agent ) = 1 ; ( ReEntryIntervalMax * 60 ) ; ( ReEntryIntervalMax + ( ReEntryIntervalMin / 2 ) * 60 ) )

           

          Although you may need to play around with this as I am unsure I have the + 1 hour part right. 

           

          And that I think should do the trick.

           

          Let me know if there are any issues or anything that is unclear and you want me to explain more.