If the 4 computed dates will always have a constant relationship to the start
date then you don't need to store them in the table, and moreover should not
do so as it introduces redundancy and the consequent risk of inconsistencies
in the data. If you want to be able to edit the computed dates, e.g. to
adjust them to avoid weekends or public holidays then you should store them
in columns in the table.
Taking the first scenario, where the relationship to the start date is
fixed, you'd have a table named Evaluations say, with a column EmployeeID
(rrelated to an Employees table containing the employees' details), and a
column StartDate.
In a form bound to this table put a combo box bound to the EmployeeID
column, but showing the name of each employee looked up from the Employees
table (The combo box wizard can set this up for you or we can walk you
through creating it), and a text box bound to the StartDate column. Add
unbound controls for each of the 4 computed dates with ControlSource
properties of:
=DateAdd("d",91, [StartDate])
=DateAdd("d",182, [StartDate])
=DateAdd("d",273, [StartDate])
=DateAdd("d",365, [StartDate])
The above expressions use your numbers of days but you can add months or
years instead, so as yours appear to be 3 monthly intervals you coild use:
=DateAdd("m",3, [StartDate])
=DateAdd("m",6, [StartDate])
=DateAdd("m",9, [StartDate])
=DateAdd("yyyy",1, [StartDate])
Taking the second scenario, where you need to store the dates in the table,
you'd add text boxes bound to the 4 columns rather than using unbound text
boxes. In the AfterUpdate event procedure of the StartDate control on the
form you'd assign values to the bound controls with code like this:
Me.[Eval3Month]=DateAdd("m",3, [StartDate])
Me.[Eval6Month]=DateAdd("m",6, [StartDate])
Me.[Eval9Month]=DateAdd("m",9, [StartDate])
Me.[EvalAnnual]=DateAdd("yyyy",1, [StartDate])
where Eval3Month etc are the names of the other bound controls.
To create a report which shows the various evaluations per month you need to
get them all into a single column in a query's result table. This is done by
means of a UNION ALL query along these lines (I've assumed scenario 1 with
the evaluation dates computed on the fly, not stored)
SELECT FirstName, LastName,
"3 Month Evaluation" AS EvalType,
DateAdd("m",3, [StartDate]) AS EvalDate,
YEAR(DateAdd("m",3, [StartDate])) AS EvalYear,
MONTH(DateAdd("m",3, [StartDate])) AS EvalMonth,
FORMAT(DateAdd("m",3, [StartDate]),"mmmm yyyy") AS EvalMonthText
FROM Evaluations INNER JOIN Employees
ON Employees.EmployeeID = Evaluations.EmployeeID
UNION ALL
SELECT FirstName, LastName,
"6 Month Evaluation",
DateAdd("m",6, [StartDate]),
YEAR(DateAdd("m",6, [StartDate])),
MONTH(DateAdd("m",6, [StartDate])),
FORMAT(DateAdd("m",6, [StartDate]),"mmmm yyyy")
FROM Evaluations INNER JOIN Employees
ON Employees.EmployeeID = Evaluations.EmployeeID
UNION ALL
SELECT FirstName, LastName,
"9 Month Evaluation",
DateAdd("m",9, [StartDate]),
YEAR(DateAdd("m",9, [StartDate])),
MONTH(DateAdd("m",9, [StartDate])),
FORMAT(DateAdd("m",9, [StartDate]),"mmmm yyyy")
FROM Evaluations INNER JOIN Employees
ON Employees.EmployeeID = Evaluations.EmployeeID
UNION ALL
SELECT FirstName, LastName,
"Annual Evaluation",
DateAdd("yyyy",1, [StartDate]),
YEAR(DateAdd("yyyy",1, [StartDate])),
MONTH(DateAdd("yyyy",1, [StartDate])),
FORMAT(DateAdd("yyyy",1, [StartDate]),"mmmm yyyy")
FROM Evaluations INNER JOIN Employees
ON Employees.EmployeeID = Evaluations.EmployeeID;
Group the report via the sorting and grouping dialogue in report design view
first by EvalYear, then by EvalMonth, then by EvalDate and give the EvalMonth
group a group header. In the group header add a text box bound to the
EvalMonthText column. In the detail section put controls bound to the
FirstName, LastName, EvalType and EvalDate columns.
When you run the report it will list all the evaluations grouped by month in
chronological order with each monthly group headed in the format 'November
2007'. You can of course restrict the report to a particular month if you
wish by a parameter in each part of the UNION ALL operation, e.g.
SELECT FirstName, LastName,
"3 Month Evaluation" AS EvalType,
DateAdd("m",3, [StartDate]) AS EvalDate,
YEAR(DateAdd("m",3, [StartDate])) AS EvalYear,
MONTH(DateAdd("m",3, [StartDate])) AS EvalMonth,
FORMAT(DateAdd("m",3, [StartDate]),"mmmm yyyy") AS EvalMonthText
FROM Evaluations INNER JOIN Employees
ON Employees.EmployeeID = Evaluations.EmployeeID
WHERE YEAR(DateAdd("m",3, [StartDate])) = [Enter year;]
AND MONTH(DateAdd("m",3, [StartDate])) = [Enter month as a number from 1 to
12:]
UNION ALL
….and so on, repeating the WHERE clause, adjusted for 6 months 9 months and
a year, in each part of the UNION ALL operation.
Ken Sheridan
Stafford, England
