Update Table issue

[Bill Neilsen]

Hi guys,
I expect most will frown on what I need to do, but, trust me I need to do it.
I have a master table that has the details of work orders (vehicle servicing), and the relevant fields are [ID], [JobNumber], [Vehicle] and [Totalmanhours].
The child table has the details of individual tasks to be carried out on the vehicle and the manhours required for each task.
The relationship is one to many, so I can have multiple tasks linked to the job number.


Now, what I need to do is, calculate the total manpower for all the tasks linked to the job number which is obviously on the master table, and then update the master table [Totalmanhours] with this total, eg Job number - 0050, Vehicle - Holden 1234, tasks to be done - radiator flush- 5 hrs, oil change - .5 hrs, gearbox overhaul 25 hrs. I could then have the job number total calculated to be 30.5 hrs.
There is another table with the manpower available by date, so, the reason I need to do it like this is to enable me to have reports for each day showing the all the vehicles booked in showing Vehicle, Job Number and Total manpower, without showing the individual tasks.
I intend to have a report showing the days manpower available, and a linked subreport with all the individual vehicles job manpower requirements. This will then enable me to calculate on the main form any short fall or not of manpower.

Can anyone help please?
Access 2007

[John_G]

Is there any reason you need to keep the [totalmanhours] data in the table? From your description of your requirements, you don't need to - the result you want can be achieved with a Totals query, in which the [totalmanhours] is a calculated SUM field. A totals query can be used almost anywhere a table can be used, especially on reports.

Your requirement (or ones like it) is common, but as I said it is best resolved with a totals query.