Tasks and Times database

[njhildebrand]

I am trying to develop a database that captures daily tasks and time it
took.
Some (Activity)tasks have a standard (# of units per hour)
I have a table with taskcode, description and standard
Then I have a task log table which has name, date, task, Minutes, etc.
Some tasks are breaks, lunch etc (task code 901 and 902)
Exception tasks will not have a standard (meetings, housekeeping,
maintenance, etc.) Those tasks have codes 903 up to 915)
The last of the tasks are for activities with standards. (101 to 200)

My question is:
How should I structure this so that I can sum by activity codes while
separating the breaks and Exception times?

I need to capture time spent in one day on activities and ratio of time
on breaks and exceptions.

Any suggestions?

Thanks,
Norma

 

[Jeff Boyce]

Norma

I'm not sure but what I know more about your task codes than I need to <g>!

I don't, however, have a very good picture of your entities and
relationships. This is the starting point for any effort to query your
data.

It sounds like you have:

Tasks
Types of tasks (Exception, ...???)
Log of Persons/Tasks

(so it also sounds like you have:

Persons

Is this how your tables are structured?

 

[njhildebrand]

Jeff,

I have 3 tables:

[ActivityCodes]
with 3 fields, ActivityCode, ActivityDescription and Standards (not all
activities will have a standard, ie: Exception codes)

[Employees]
with 1 field, Name

[TaskLog]
With Employee (lookup)
Date
Activity (lookup)
ActivityMinutes
Pallets
Cases
Errors
Comments

I am not sure if this is structured correctly to acheive what I need.
We need to look at
1) Productivity (as compared to standard)By date, week or month.
2)By persons average prodivity overall and average per task.
3)Total time on Exception tasks
And several other ways to compare our productivity.

I had tried to develop this with a start and stop time. I wrote a
module that will change the times into hours and minutes in
calculations but when trying to use the various task time totals, I
would get errors, so I thought capturing the minutes would be better...

I also tried to separate the codes into 3 tables, Activitys,
Exceptions, breaks

Any thoughts?
Thanks for your help with this.

Norma

 

[Jeff Boyce]

Sorry for the delay. I've been out of town...

(see comments in-line below)

Jeff,

I have 3 tables:

[ActivityCodes]
with 3 fields, ActivityCode, ActivityDescription and Standards (not all
activities will have a standard, ie: Exception codes)

[Employees]
with 1 field, Name

This is a potentially problematic design ... I wonder what will happen when
you have two employees with identical names? Also, Access treats the word
"Name" as a reserved word. If you are planning to keep this table, change
the fieldname to something other than "Name", or both you and Access could
be confused.

[TaskLog]
With Employee (lookup)

Unless you are quite experienced with Access, and unless only YOU will ever
be trying to query the data, using a lookup data type field is risky. In
your situation, since you only have "name", using the lookup data type is
less risky (but using the "name" in the above table is risky in itself!).

Date

Also a reserved word -- see comment above.

Activity (lookup)

If you are using a "lookup" data type here, the same issue. The preferred
option is to use a foreign key field, and do your "lookup" via a combo box
in your form(s).

ActivityMinutes
Pallets
Cases
Errors

(I don't recall if this is a reserved word or not...)

Comments

I am not sure if this is structured correctly to acheive what I need.
We need to look at
1) Productivity (as compared to standard)By date, week or month.

"Productivity" may mean something different to you than to me. How do you
calculate "productivity"?

2)By persons average prodivity overall and average per task.

By "task", you mean "activity"? Or could several "activities" be considered
components of a "task"?

3)Total time on Exception tasks

I'm still not clear if you have one or many activities that can be
considered "exception tasks". What is the relationship between "task" and
"activity"?

And several other ways to compare our productivity.

I had tried to develop this with a start and stop time. I wrote a
module that will change the times into hours and minutes in
calculations but when trying to use the various task time totals, I
would get errors, so I thought capturing the minutes would be better...

If you are measuring how long something takes/lasts, recording the duration
in the lowest unit of measure (e.g., minutes) makes your calculating easier,
but may make your data entry harder. On the other hand, finding and
adapting, or building your own "parsing" routines for HH:MM:SS (in Access,
hh:nn:ss) data is a bit of work all its own. It may be a toss-up, to be
decided by you/your users for which is easier.

I also tried to separate the codes into 3 tables, Activitys,
Exceptions, breaks

This sounds to me like you are saying that you can categorize your
"activities" (??"tasks"??) into three categories. If so, why not use an
ActivityCategory table that lists your (three) categories, and add an
ActivityCategory field to the "Activity" table (as a foreign key)? With
something like this, finding the total (or average) time/duration for all
"Exception" activities/tasks is simply a matter of querying with that
field's parameter.

Any thoughts?
Thanks for your help with this.

Norma

Good luck

Jeff Boyce
<Access MVP>