If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Calculation
Good Afternoon,
I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I would like is for the end user to input the data into a form, have it autocalculate the expiry date on the form so they can verify the information and then once the form is closed, have this information fed into the table so that we can print training reports for the managers. Im not versed in VB and am reaching my limit of understanding with queries, so any help is graciously appreciated. Thanks in advance |
#2
|
|||
|
|||
Date Calculation
JMK wrote:
Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I would like is for the end user to input the data into a form, have it autocalculate the expiry date on the form so they can verify the information and then once the form is closed, have this information fed into the table so that we can print training reports for the managers. Im not versed in VB and am reaching my limit of understanding with queries, so any help is graciously appreciated. Thanks in advance I don't see any reason you cannot do this in a query. It's only a simple DateDiff() query... By creating a query, the SQL created is re-run every time you open the query... Why is that not sufficient? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201001/1 |
#3
|
|||
|
|||
Date Calculation
You might find this helpful --
How do you have your tables setup? Maybe like this -- Employee -- EmpID - Autonumber - primary key LName - text FName - text MI - text HireDate - DateTime Depart - DateTime ....etc. Training -- CourseID - Autonumber - primary key Title - text Period - text (m- Monthly, q- Quarterly, yyyy- Yearly) ReCur - Number - long integer ReqdBy - text (OSHA 1910, HR Manual 5.8.3, Finance 4.23.1) Grace - Number - long integer ā number of days grace period allowed EmpTraining -- EmpID - number - long integer CourseID - number - long integer TngDue - DateTime CompDate - DateTime Pass - Yes/No Employee.Depart Is Null AND EmpTraining.Max(CompDate) AND EmpTraining.Pass = -1 Next_Training_Required: DateAdd([Period], [ReCur], IIF(Abs(DateDiff(ādā, Max(CompDate), Max(TngDue))) = Grace, Max(TngDue), Max(CompDate))) You could append training due records following update of any due record using the two fields TngDue and CompDate. Then run query with duedate descending to show all next training. Here are two post of mine on maintenace that might apply to training -- You need a ServiceReq table listing the services and interval. Then a VehicleSvcReq the has vehicle ID, ServiceReq, Method. Method is whether next service is the last schedule plus interval or last completion plus interval. The interval needs to be the lowest common denominator of all services such as weeks if any one of the services is to be accomplished on a weekly basis - bi-weekly - monthly - quarterly. All intervals will be multiples of the selected interval. If fluid checks are weekly and oil change every three months then oil change would be interval 13 - 13 weeks. ----------------------- --------------- In a Task table have a field indicating interval number for the maintenance. Use the lowest common denominator such as weeks, months or quarters. If you can not make it work with the lowest common denominator then use two fields, one for interval type and other for numerial -- m 2 - for 2 months d 30 - for 30 days q 2 - for 2 quarters Use these in DateAdd function to create your workorders using an append query. Another thing to think about is whether to schedule based on last performed date or straight calendar. If a maintenance task was performed late or earlier should the next one be be form the completion date or whenever the calendar says it should be. Have a field in the task table indicating which if you have mixed. The workorder needs a date field for DueDate and Completed. The append query will look at task table for interval information and which date to use - last completed or last scheduled. -- Build a little, test a little. "JMK" wrote: Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I would like is for the end user to input the data into a form, have it autocalculate the expiry date on the form so they can verify the information and then once the form is closed, have this information fed into the table so that we can print training reports for the managers. Im not versed in VB and am reaching my limit of understanding with queries, so any help is graciously appreciated. Thanks in advance |
Thread Tools | |
Display Modes | |
|
|