A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Date Calculation



 
 
Thread Tools Display Modes
  #1  
Old January 27th, 2010, 09:26 PM posted to microsoft.public.access.tablesdbdesign
jmk
external usenet poster
 
Posts: 29
Default 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  
Old January 28th, 2010, 02:12 AM posted to microsoft.public.access.tablesdbdesign
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default 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  
Old January 29th, 2010, 02:11 AM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.