date specific validation rules

[Guest]

I am working on a database for employees. One of my tables tracks the
purchase and reimbursement for safety shoes and glasses. Employees are
eligible for reimbursement for 1 pair of safety shoes per year and 1 pair of
prescription safety glasses every 2 years. How would i set a validation rule
for thise, since the dates are different for all employees?

 

[Guest]

Use a query with criteria. Post your table structure and say how you
interface with it.

 

[Jamie Collins]

I am working on a database for employees. One of my tables tracks the
purchase and reimbursement for safety shoes and glasses. Employees are
eligible for reimbursement for 1 pair of safety shoes per year and 1 pair of
prescription safety glasses every 2 years. How would i set a validation rule
for thise, since the dates are different for all employees?

Use a start- and end date pair to model the period and test it for e.g.
two years less one second (one time granule):

CREATE TABLE SafetyShoes (
employee_ID INTEGER NOT NULL REFERENCES Employees (employee_ID),
start_date DATETIME DEFAULT DATE() NOT NULL,
CHECK (HOUR(start_date) = 0
AND MINUTE(start_date) = 0
AND SECOND (start_date) = 0,
end_date DATETIME NOT NULL,
CHECK (end_date = DATEADD(''s', DATEADD('y', start_date, 1) - 1))
);

Then you just need to ensure you have no overlapping periods for the
same employee. See:

http://groups.google.com/group/microsoft.public.access.forms/msg/04c3f233ba3336cc

Jamie.

--

 

[hamid]

(e-mail address removed)