1 Reply Latest reply on May 21, 2015 11:10 AM by philmodjunk

    Due Dates



      Due Dates


      Hoping someone can help me with a new project I have.  The data base will be used to track the "due dates" of a report called "ISP's" that each of our youth will have due every month.  The initial report is due 30 days after the youth is admitted and then every thirty days until he discharges.  

      I have 2 questions, the first being an "architecture" question and the other a calucation question for the due dates. 

      Should I have one record with a "due date" field for each month (1st mo due;  2nd mo due; 3rd mo due, etc) or would it make more sense to have a record for each "due date" individually?  Also, since we do not know how many months a youth will be with us, I was trying to figure out the best way to create these "due dates".  I was thinking of using a script that would create 24 new records in the due dates table (or records, depending on the answer the first question).  However, some kids may stay only 6 months and others up to 36 months, so I would have to figure out a way to add months if they are going to be here longer than the original 24 months created when they arrived.  

      Now for the calcuation question:  Regardless of whether I have 1 record holding all of the due dates or 1 record for each of the due dates, I need a "due date" based on the admit date to generate and be every 30 days.  I understand adding days to the admit date, but cannot figure out how to make it work for example to get the due date for the 2nd month report if the admit date is November 4, 2014 (Admit Date +60) doesn't work and even the calculations using the Month and Day won't give me the proper due date either.  

      I know I have asked for a lot and any assistance you can provide is greatly appreciated!

      Thank you

        • 1. Re: Due Dates

          You'll need to determine whether you want one ISP a month or one ISP every 30 days as not all months are 30 days in length. Both options are possible and I'll include sample calculations for each.

          I'd set up two tables, one with one record per youth created on admission. One with a table with one record for each ISP. On Admission and each time that your youth submits their ISP, you would generate one new ISP record for that youth with a due date either 30 days in the future or for the same day of the next month. Creating multiple ISP records in advance would seem a needless complication to the process.

          To compute a date 30 days in the future from a date entered into a field of type date:

          DateField + 30

          To compute a date for the same day of the month for the next month on the calendar:

          Date ( Month (DateField ) + 1 ; Day ( DateField ) ; Year ( dateField ) )

          Note that if you use this last calculation with a date in December in the date field, you will get a date for January of the following year so you do not need special handling for dates that fall in December.