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!