I have 2 fields in my table, one is manhours and the other is total_manhours. WHat I need to happen is when someone goes into form to log work done, they will enter into manhours the hours worked on job (manhours should be 0 when they call up form) . I would then like the manhours entered to be added to the total_manhours field to be kept in the table for total hours worked on this job. What is the easiest way to do this?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Total Manhours Problem
- Thread starter JimUK
- Start date
Following normilization rules a field like Total_ManHours for the job really should be a calculated field rather than one that is stored in the record. You see if you go back to a previous record and edit it because you entered the hours incorrectly then the Total_ManHours field for subsequent records is wrong. You can always calculate the value correctly on a form or report using an expression.
As an example on your form where you are putting in the manhours worked on the job the control for total manhours for the job can have its control source set to the following:
This way the total_manhours control on the form will always be accurate. As soon as the record is saved the form will recaculate all calc. controls and the value will be updated.
This same kind of function can be used in a query or report to display this value. Much better design and more accurate without the editing and data repairing issues that you would have if the field were hard coded for a value.
Post back if you have any questions.
[COLOR=006633]Bob Scriver[/color]
[COLOR=white 006633]MSU Spartan[/color]
As an example on your form where you are putting in the manhours worked on the job the control for total manhours for the job can have its control source set to the following:
Code:
=DSum("[manhours]", "yourtablename", "[jobID] = " & Me![JobID])This way the total_manhours control on the form will always be accurate. As soon as the record is saved the form will recaculate all calc. controls and the value will be updated.
This same kind of function can be used in a query or report to display this value. Much better design and more accurate without the editing and data repairing issues that you would have if the field were hard coded for a value.
Post back if you have any questions.
[COLOR=006633]Bob Scriver[/color]
- Thread starter
- #3
As you have suggested, I got rid of the total_manhours field in my table. The problem I have now is that when I put
=DSum("[manhours]", "NEWETR", "[ETR] = " & Me![ETR])
in the control source of the total_manhours field in my form it turns up with a #Error in it.
Tried
=DSum("[manhours]", "NEWETR", "[ETR] = " & [ETR]) and
=DSum("[manhours]", "NEWETR", "[ETR] = " & [Forms]![NEWETR]![ETR])
and then total_manhours field is blank instead of error.
Am I having a criteria argument problem here?
=DSum("[manhours]", "NEWETR", "[ETR] = " & Me![ETR])
in the control source of the total_manhours field in my form it turns up with a #Error in it.
Tried
=DSum("[manhours]", "NEWETR", "[ETR] = " & [ETR]) and
=DSum("[manhours]", "NEWETR", "[ETR] = " & [Forms]![NEWETR]![ETR])
and then total_manhours field is blank instead of error.
Am I having a criteria argument problem here?
Try this:
It is probably because [ETR] is a text type field and you must have quotes around the value of the form control for the criteria expression to not have a type mismatch. Let me know if this expression works now.
[COLOR=006633]Bob Scriver[/color]
[COLOR=white 006633]MSU Spartan[/color]
Code:
=DSum("[manhours]", "NEWETR", "[ETR] = '" & Me![ETR] & "'") andIt is probably because [ETR] is a text type field and you must have quotes around the value of the form control for the criteria expression to not have a type mismatch. Let me know if this expression works now.
[COLOR=006633]Bob Scriver[/color]
- Thread starter
- #5
Enjoy your time off. From your TT handle I am assuming you are from the UK. Take the time to remember those boys from both of our countries that gave unselfishly of themselves on this day 60 years ago. We have the freedom that we have today because of their sacrifices.
D-Day remembrance.
[COLOR=006633]Bob Scriver[/color]
[COLOR=white 006633]MSU Spartan[/color]
D-Day remembrance.
[COLOR=006633]Bob Scriver[/color]
- Thread starter
- #7
Hello Jim. When you say . . .
doesn't keep a running total for total_manhours. [/quote[
Do you want the running sum to be for the entire job or for this individual on the job. Is there a difference. What I mean is there a "job" identification. Like a worker can work on one job for a while and then get assigned to another job. Is ther a job id field that needs to be considered?
The field [ETR]. You never really said what that is. Please explain.
[COLOR=006633]Bob Scriver[/color]
[COLOR=white 006633]MSU Spartan[/color]
- Thread starter
- #9
Bob,
Let me explain. the ETR field is equivalent to a JOB ID. Each job will have an ETR number of it's own. There may be more then one person that works on a particular ETR. What I am looking for is that when a technician makes an entry in the ETR form that he updates his manhours worked and that is transfered to the total_manhours field so that the manager can see how long it took to complete this job.
Hope that helps.
Jim
Let me explain. the ETR field is equivalent to a JOB ID. Each job will have an ETR number of it's own. There may be more then one person that works on a particular ETR. What I am looking for is that when a technician makes an entry in the ETR form that he updates his manhours worked and that is transfered to the total_manhours field so that the manager can see how long it took to complete this job.
Hope that helps.
Jim
The DSum function provided should retrieve the all the records from the table NEWETR and sum the manhours field for display purposes. You say it is only showing the hours just entered into the current record. Please check your table and make sure that there are other records for that partciular ETR. I am just trying to rule out that you have only entered the current record for an ETR which would just show those manhours.
Post back with your finds.
[COLOR=006633]Bob Scriver[/color]
[COLOR=white 006633]MSU Spartan[/color]
Post back with your finds.
[COLOR=006633]Bob Scriver[/color]
- Thread starter
- #11
Bob,
Again thanks for the help here so far. There is only one record for each ETR generated. Only thing that will change in this record is the Action field (which is where the text of what was done is input) and the Status field (Closed, Open, Awaiting Parts, etc. Action field is memo and just appending text entries into it. So only one record per ETR.
Jim
Again thanks for the help here so far. There is only one record for each ETR generated. Only thing that will change in this record is the Action field (which is where the text of what was done is input) and the Status field (Closed, Open, Awaiting Parts, etc. Action field is memo and just appending text entries into it. So only one record per ETR.
Jim
You previously stated:
Now you have said:
If this is the case then we will only see a rollup of one record with the criteria that we were using. I understand that only one ETR for a job. But, it seems you want to enter more that one record for an ETR which would indicate a works hours for that ETR. Shouldn't you have another table where you create multiple records for an ETR with a worker ID and their hours. This would create a One-to-Many relationship between the ETR table and this WorkActivity table. Now the DSum expression provided above would rollup all of these multiple records that all have the same ETR no matter who the worker is.
Post back with questions and comments.
[COLOR=006633]Bob Scriver[/color]
[COLOR=white 006633]MSU Spartan[/color]
Now you have said:
If this is the case then we will only see a rollup of one record with the criteria that we were using. I understand that only one ETR for a job. But, it seems you want to enter more that one record for an ETR which would indicate a works hours for that ETR. Shouldn't you have another table where you create multiple records for an ETR with a worker ID and their hours. This would create a One-to-Many relationship between the ETR table and this WorkActivity table. Now the DSum expression provided above would rollup all of these multiple records that all have the same ETR no matter who the worker is.
Post back with questions and comments.
[COLOR=006633]Bob Scriver[/color]
- Thread starter
- #13
Bob,
Your right, the whole database needs to be redesigned. I inherited this from a coworker that has left the company and he pulled an old datatrieve (flatfile database) into Access. In the near future I will have to start from ground zero on it, but for the moment the boss wants total manhours shown on this particular form, so I guess I'm kinda stumped then? I thought for the time being I could add a field to table and update it. This is where this all started.
Jim
Your right, the whole database needs to be redesigned. I inherited this from a coworker that has left the company and he pulled an old datatrieve (flatfile database) into Access. In the near future I will have to start from ground zero on it, but for the moment the boss wants total manhours shown on this particular form, so I guess I'm kinda stumped then? I thought for the time being I could add a field to table and update it. This is where this all started.
Jim
When you are looking at these records how do you tell which job they are for? Which field tells you that? Is the field avaialble on the form at the top you want to see it?
[COLOR=006633]Bob Scriver[/color]
[COLOR=white 006633]MSU Spartan[/color]
[COLOR=006633]Bob Scriver[/color]
- Thread starter
- #15
The ETR number is on the form at the top. It is how the record is called up. The job is opened under the ETR number and then whoever works on this job updates this ETR number and fills in the action field on this form (memo field) with what was done. there may be several entries made or appended to the action field. When the job is complete the ETR form has a status field that gets updated as Closed. There is also a manhours field on this form and in the ETR table and was originally trying to add a total manhours field and have it update each time someone made an entry that they had worked on it into the manhours field. Basically total manhours would just be a sum of the hours entered into the manhours field everytime someone made an entry on ETR.
I realize that this whole schema is wrong, bigtime, but this was just a short term fix the boss wanted ;P
Jim
I realize that this whole schema is wrong, bigtime, but this was just a short term fix the boss wanted ;P
Jim
Really all that needs to be done here is have the workers create a new record in this form each time they fill in their work hours. This means that there will be multiple records added for any one ETR. When that happens then our DSum function will work showing the rollup of all of the records with the same ETR.
Considerations must be given as to the primary index of the table for these records. If you just add an autonumber field to the table and designate it as the Primary Index then all should work fine after that.
Post back if you have questions about this change.
[COLOR=006633]Bob Scriver[/color]
[COLOR=white 006633]MSU Spartan[/color]
Considerations must be given as to the primary index of the table for these records. If you just add an autonumber field to the table and designate it as the Primary Index then all should work fine after that.
Post back if you have questions about this change.
[COLOR=006633]Bob Scriver[/color]
- Thread starter
- #17
- Status
Similar threads
- Locked
- Question
- Locked
- Question