Adding records with VBA

[jackjfarrell]

I am creating a database for workorders and keeping time. I have everything the way I want it except for a way to track that managers time. They managers time is split up equally between all workorders that his crew has put time on that day. For example if they crew worked 100 hours on June 21st and they worked 15 hours on Workorder 1, 15% of the managers time would be applied to workorder 1. Siminarly if they worked 25 hours on workorder 2 on the 21st then 25% of the managers time is applied to workorder 2.



I can do this using excel but it is a pain to export the data from access and then go back in access and add time to each of the workorders for the manager. (This is a large scale operation and there are often times 25-35 workorders per day.

Is there a way to use VBA to add up the crews total time for the day, divide by each workorder to see the % of time worked on each workorder for the day and then create a record for the manager with that percentage of his time? The manager is identified by the WWID field.

I know its a little complicated (or at least it seems that way to me). I really appreciate your help.

[ranman256]

you should be able to do this with queries, not vba.
1.a sum qry for the crews / day , a make table qry to write to a temp table
2. then apply this sum to each manager using an update query.

[ssanfu]

Is there a way to use VBA to add up the crews total time for the day, divide by each workorder to see the % of time worked on each workorder for the day and then create a record for the manager with that percentage of his time?

Yes, of course.

You would create a query (in VBA) to get sum of the hours worked by WO number for the crew of the manager ID and the date.
Add up all of the hours, then loop by WO, do the division, calculate the hours and insert the manager's hours.

Basically the same way you do it in Excel or manually.

The next problem is how/when to execute the code. And for how many/what days? (text box on a form to select the day??)


BTW, "TIME" is a reserved word in Access and shouldn't be used as object names.