Ms Access - How to use query to track employee weekly attendance

[JamesKang123]

Hi, I am new to MS Access, My company want me to create a Weekly Attendance tracking system base on the table structure.Which enable to Summarize which employee took the most Annual Leave, Medical Leave.

I have a table called Attendance with fields like -
Which consist from Mon - Fri. When a new week, the system
will append a new set of records - EmpName, and I will have to fill up their status from Mon to Fri.

Name|..Mon..|.MonDD..|Tue|TueDD|Wed|WedDD|Thur|Thu rDD|Fri|
John...ML.....1/1/11......ML..2/1/11
Peter...........................AL..3/1/11..AL..4/1/11
David.................ML..2/1/11
Nancy............................................. ..

My form will display all empname for that week

1 Sep to 7 Sep
Emp Name | ML | AL |
John.........2....0
Peter........0....2
David........1....0
Nancy........0....0

I tried using MS Query, but my access knowledge was limited. Can anyone enlighten me.

[nico5038]

Hi James,

I would start with an advise to change your table structure to a normalized one like:
EmployeeName (The employee name)
WorkingDay (Just the Date)
LeaveType (ML/AL)

Now all data can be stored in one table.

For the counting you can use a so-called "cross-table" query.
Just place the EmployeeName, WorkingDate and LeaveType fields in the graphical editor.
Next change the query type to "cross-table" and two additional rows will appear.
In the first one specify EmployeeName and LeaveType as "GroupBy" and WorkingDate as "Count".
In the second row specify for EmployeeName "Rowheader", for LeaveType "Column header" and for WorkingDate "Value"
Now add the start- and end-date in the criteria like:
BETWEEN #9-1-2011" and #9-7-2011#

This query will show the date in the format you need for the form.

Getting the idea ?

Nic;o)

[JamesKang123]

Thanks for your reply , I will try it out.

[NeoPa]

You may find Database Normalisation and Table structures helpful.