You say your table has:
a UserID, the day, action count, and action hours
(w/ a few more action fields and a few more hours fields)
The "few more action fields" is where it's gone wrong.
That and the "few more hours fields" indicates that you have repeating
fields.
Since there are several 'actions' that may need to be taken to complete a
job, you will need tables like this:
Action table:
- ActionID AutoNumber primary key
- Action Text what this action is.
Employee table:
- EmployeeID AutoNumber primary key
- Surname Text
- Firstname Text
Job table:
- JobID AutoNumber primary key
- ClientID Number who this job is for.
- JobDescrip Memo description of what's tobe done.
JobDetail table:
- JobDetailID AutoNumber primary key
- JobID Number which job this row is about.
- EmployeeID Number who did this
- ActionID Number what action they did
- ActionDateTime Date/Time when they did it.
- Minutes Number how long it took.
To interface this, you will have a main form describing what needs to be
done, with a continuous subform showing the times people have worked on it.
If someone performs 2 actions, they complete 2 rows in the subform. The
total hours spent is given with a text box in the Form Footer of the
subform; set its ControlSource to:
=Sum([Minutes]) / 60
The total gets updated as soon as the row is saved.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
Are you sure you need to do that, Alex?
As you found, Access updates the calculated control after the record is
saved. You could therefore trigger the update by explicitly saving after
each record. That's a rather messy and inefficient solution, requiring
many
more writes, and with other possible flaws (e.g. it will fail if any of
the
fields that have not been filled in yet are required.)
Another possibity is to sum only the values from the *other* records, plus
the values of the text boxes in the current record. Again, this is messy
for
several reasons, and you will need to use Nz() to cope with adding null
values horizontally.
In the end, the problem here is that you are trying to design a
spreadsheet
in Access, rather than a relational database. Whatever x, y, and z are,
they
seem to be similar kinds of things: otherwise you would not be adding them
together. It therefore appears that you have repeating fields in your
table,
instead of related records in a related table, so the design looks likea
spreadsheet rather than a database. That would seem to be the locus of the
problem.
Thanks Allen for your help.
What I'm trying to do is create a database that tracks employee time
and what he/she did. I've setup the database with relating tables
such as tblEmployee and seperate tables for each department.
For the business department (for example); I have a table that
contains a UserID, the day, action count, and action hours (w/ a few
more action fields and a few more hours fields):
E.g., gsmith - wednesday Dec 10, 2008 - 12 - 1 (translated: Mr Smith,
on 12/10/2008, created 12 widgets that took 1 hour to complete.
I looked at the Time and Billing template in Access, but my situation
doen not appear similar. This system was in a spreadsheet, but I
don't think it should stay there.
I'm curious how you would set this up...The database needs to track
the employee, when the action happened, what the action was, and how
long it took.
I already have my employee table. I suppose I could create a table
that contains a work id and work description and tie them together in
a some kind of timecard table? Maybe someone's already done this and
could point me to an example.
thanks again,
alex- Hide quoted text -
- Show quoted text -
