Query_CrosstabAttendanceMarking.mdb |
Post Reply |
Author | |
A.D. Tejpal
Microsoft MVP Joined: 30 Jun 2008 Status: Offline Points: 192 |
Post Options
Thanks(0)
Posted: 08 Jul 2008 at 10:15pm |
This sample db demonstrates date time marking of employee's attendance sheet, in crosstab style, while retaining the original data in normalized state. Since no keyboard inputs are involved (date time stamping is done via mouse dbl click), the cross tab query serves directly as form's record source, without needing any interim table. For the selected year and month, the integrated output gives following display: (a) First header row - shows weekday names. (b) Second header row - shows day of the month (c) Third row onwards - show attendance markings (date/time) for respective employees. (Navigation to the two header rows (a and b above) is prevented programmatically) The over-all union query composed of three crosstab components (a to c above) is devised in such a manner that its output adapts to different year/month combinations without requiring run-time manipulation of control source property for various controls representing days of the month. Saturdays and Sundays in the header row are highlighted in grey. Date time marking of attendance is carried out by dbl click in the column for current date (on the row pertaining to given employee). Attendance marking is permitted only in the column matching current date. Once marked, it can not be overwritten. Total days actually attended is displayed in column titled TotInMonth, placed adjacent to EmpName column. The form is in datasheet view and these two columns are frozen, so as to remain in view, irrespective of navigation amongst the remaining columns (each of which represents the day of month). On opening the form, it displays the status for current year and month and the cursor gets positioned in the column representing current day of the month, on the first record where attendance has not yet been marked. If there is no employee awaiting attendance mark, the cursor goes to the first employee's record. While navigating through the records, EmpName and Current day's attendance column for the current record are highlighted (in light blue if attendance marking is pending, otherwise in light pink). Blank attendance sheets for the selected month / year can be generated via report preview, by clicking the appropriate command button. Note: Adoption of datasheet view (as done here) affords the advantage of freezing EmpName and TotInMonth columns so that these are always in view. However, as the user scrolls down, the contents of header rows go out of view. Column headings remain visible and do provide an indication of the day of the month involved (e.g. D01, D02, -- etc). The other alternative would be to adopt continuous form lay out. In that case, freezing of desired columns would not be readily available. 5 - Version: Access 2K/XP/2K3/2K7 (Access 2000 File Format)
|
|
Sponsored Links | Want a good read? Try The Summer of His Life (available on Amazon!) |
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |