Calculating Totals in a Report

[Guest]

Hello,

This is probably a fairly simple question. I have a word processing
operator worklog in Access 2002. The worklog is simple - the date, the
operator's name, job number, and a list of tasks, each of which has a
checkbox (Yes/No datatype).

Every day, my operators fill out their worklogs, and for each job number,
they select one or more tasks that pertain to that job.

What I would like to do is create daily and weekly reports. The reports
would list each operator, and would tally the amount of time they spent on
each task type that day. It would also total the amount of time spent by all
operators on each task type. This will give me an idea of how much time our
department spends on each of the types of tasks we do.

How do I set up my query/report to tally the totals for each task type,
first by individual operator, then a total for all operators?

Thanks,
Rosemary

 

[Duane Hookom]

You would start by normalizing your table so that tasks create records and
not fields. One simple solution would be fields like:
WorkDate
OperatorID
JobNumber
TaskID
WorkTime (double numeric to store number of minutes)

 

[Guest]

Hi Duane,

Yes, thank you. I see. There is one wrinkle however that I should add: we
will be scrapping this simple worklog database in favor of a more
encompassing workflow tracking system that I am writing in MSAccess. So, for
this reason, I will not be making any fundamental changes to the worklog
program -- but, if I can get reports that tally the individual task types as
I described, just for the interim until the new application has been built,
that would be great. Can I do this?

Regards,
Rosemary

 

[Duane Hookom]

You should be able to do this with a union query. However, you didn't
suggest any fields that store the amount of time for each task.

 

[Guest]

Hi Duane,

Ok, thanks. We do have fields like the ones you suggested before (WorkDate,
OperatorID, JobNumber, WorkTime). We have an operator name field, a date
field, and amount of time fields. The amount of time is actually two fields:
one for number of hours, and one for number of minutes.

I should add that I inherited this particular DB application, so I don't
have all the background on how the design came about, but I do know it was
meant to be very basic and simple.

Rosemary

 

[Duane Hookom]

If you want to find out "spends on each of the types of tasks" then you need
to have fields that store this in your current tables. You haven't suggested
how you are storing this information.

What are your current table structures?

 

[Guest]

Hi Duane,

Keep in mind that this is an application we will discard as soon as our new,
comprehensive database is built.

That said, all the tasks fields (there are 16) are Yes/No fields. The
operator checks one or more tasks (in the form of checkboxes) in the worklog
form that apply to the job they are working on.

Also, there is only one single table for this database. The job number,
operator name, time spent in hours, time spent in minutes, and all 16 Yes/No
task fields are in this one table.

I want to create a simple tally at the end of each day, and the end of each
work week, of the amount of time each operator spends on each type of task;
then I want to tally, at the end of each day and the end of each week, the
total amount of time spent by all operators on each type of task.

Does that give a better picture of the set-up?

I am hoping to have the new database application, with fully normalized
tables, and much more complex functionality, ready in about two months. But
in the meantime, I'd like to have the statistics I need on the department's
operations using the current operator worklog.

Many thanks for your help and have a great Thanksgiving.

Regards,
Rosemary

 

[Duane Hookom]

Rosemary,
Consider this record
JobNum 30
Operator "John"
HoursSpent 3
MinutesSpent 34
Task1 Yes
Task2 No
Task3 Yes
Task4 No
Task4 No
Task4 No
Task4 No
Task4 No
Task4 No

 

[Duane Hookom]

Sorry, the safety was off and I hit the enter key before I wanted to.
Basically, if more than one task is checked, how do you know how much time
is spent on each task?

 

[Guest]

Hi Duane,

You know, the exact same question occurred to me as well. There is really
no way to distinguish. So I think I have to rethink how the report will
display. Perhaps the only way I can display the task categories is by
creating a daily report, one for each operator -- and just have a tally of
how many TIMES each task was performed that day. The AMOUNT of time tally
will be the total amount of time the operator spent for all tasks, broken
down only by job number.

Does that sound like it would work?

Regards,
Rosemary

 

[Duane Hookom]

You should be able to create a union query that normalizes the table and
then a totals query based on the union query to sum the time and count the
number of each task.