Attendance Monitor.

[pr2-eugin]

Hello there, I need some help with designing this database, I am now involved in designing a DB for tracking attendance of agents. At the moment there are 9 agents. The manager will use an Unbound Form to add the hours worked by each agent, for the particular day of the week.

I created a table with the following structure,

[B]
ATT_ID  [/B]||[B] AGENT_ID_fk [/B]||[B] WK_NO  [/B]||[B] Mon_Day[/B]||[B]  Tue_Day  [/B]||[B]Wed_Day  [/B]||[B]Thur_Day  [/B]||[B]Fri_Day [/B]||[B] WK_Start_Date[/B]

This was not really a good idea to begin with, but I found this more appealing, as this will reduce the number of row entries, but my manager wants to run a report on a day by day basis, this will only be running on a WEEK by WEEK basis.. So this is not really helping out. I am back to ground ZERO. I cannot think of a better design..

In simple words, I would like to add agent's working hours on a day by day basis.. So is this the only way to do this?

ATT_ID   AGENT_ID_fk     Working_Hours      WorkDate
1        83              7.5                08/10/2012
2        89              7.5                08/10/2012
3        3               7.5                08/10/2012
4        87              7.5                08/10/2012
5        9               7.5                08/10/2012
6        88              5.5                08/10/2012
7        83              7.5                09/10/2012
8        89              7.5                09/10/2012
9        3               7.5                09/10/2012
10       87              7.5                09/10/2012
11       9               7.5                09/10/2012
12       88              5.5                09/10/2012

If the above description is not helping, please let me know I will try to explain..

 

[plog]

Well, as your initial attempt shows, it is not the only way to do it. However, the new structure you've proposed is the correct way to do it.

 

[GlennOwns]

Use your second option. Your manager should be able to easily query work date ranges via an unbound form into a report much more easily the second way. Plus it's proper design.

Good job.

Also, if you wanted to really get specific, you could instead just have a start-shift and stop-shift option. Each of your 9 agents would clock-in/clock-out when going on lunch, leaving, etc... That way it's more accurate.. If this is a reactive approach, counting hours may be easier... But if you have, say, a badging system or entry controller operation, a clock-in/clock-out system also provides non-disputable data... =p