This sample db demonstrates monitoring of common intersections (number of
days) between multiple instances of date spans (Leave availed by employees) visa-
vis the selected date range. Such an intersection takes place when (Start date of
date span is less than end date of selected date range) And (End date of date span
is greater than start date of selected date range).
Selection of date range (DtFrom and DtUpto) for which employee's leave status
is to be viewed, is carried out via combo boxes on the main form. On opening the
form, default values on these combo boxes cover the full date range reflected by
available data. As per built in safeguard, the user can not have a value for DtFrom
that is greater than DtUpTo. Similarly, it is not feasible to use a value for DtUpTo
that is less than DtFrom.
The subform at left displays the names of all employees whose leave periods
overlap the date range (either partly or in full). Against each employee, the total
number of days overlap is also shown. By user selection via an option group, this
list can be sorted either as per employee name (asc) or total days overlap (desc), as
desired. Current record is highlighted in special color, while the font for total days
overlap is highlighted in dark red color.
For the employee featuring in current record of above subform, details of
individual spells of leave (StartDate / EndDate in each case) for that employee, get
displayed in chronological order in an adjacent subform at right. Only such of the
leave spells are shown as have a positive intersection with the selected date
range. In each case, portion of leave period that actually falls in the selected date
range is also shown, duly highlighted in color.
Whenever the user alters the date range by making new selections in the combo
boxes on the main form, the information displayed by the two subforms as
mentioned above, gets updated simultaneously.
Report Generation is provided in three styles as follows:
Style 1 - All Employees: Sorted as per employee name.
Style 2 - All Employees: Sorted as per total leave days overlap (Desc).
Style 3 - Current Employee Only.
Leave Chart (Employee-wise) - An interesting feature has been incorporated in
the above reports. For each employee, individual leave spells overlapping the
selected date range are depicted as a series of colored horizontal strips
superimposed on a background bar representing the selected date range.
Note - A common report is used for all three styles listed above. Filtration and sort
order are tailored at run time, to match the combination of options selected by the
user. As added convenience, the caption and fore color of the label at top right
corner of the report are set to distinct values to mark different styles.
Download Access 2K/XP/2K3/2K7: http://www.rogersaccesslibrary.com/forum/uploads/37/Query_DateSpansInDateRange.zip -
|