Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Tejpal, A.D.
  New Posts New Posts RSS Feed - Query_CrosstabAttendanceMarking.mdb
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

>
Want a good read? Try The Summer of His Life (available on Amazon!)

Query_CrosstabAttendanceMarking.mdb

 Post Reply Post Reply
Author
Message
A.D. Tejpal View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 30 Jun 2008
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote A.D. Tejpal Quote  Post ReplyReply Direct Link To This Post Topic: Query_CrosstabAttendanceMarking.mdb
    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)

Download Access 2K/XP/2K3/2K7: uploads/37/Query_CrosstabAttendanceMarking.zip

--

Email: adtejpal@gmail.com
Back to Top
Sponsored Links
>
Want a good read? Try The Summer of His Life (available on Amazon!)

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.