I think I need to use a VBA loop for a query, but not sure if this is the best approach. My VBA experience is more directed toward forms, so I'll need a little help if I'm going to work VBA into my queries.
Context - We have a database that stores detail on individual workorders, including labor time per person per workorder. The frontend has the ability to assign dates to each labor entry (best for multi-day workorders), but this is optional and underused. The workorder always has start and finish dates, so this is our fallback. We're currently putting together timesheet-like reports to check workorder time against timesheet time for each pay period. The problem cropped up when we found that several workorders span 2 or more days and don't assign labor to one specific date. The queries would have to calculate labor per work day and append a separate record for each day of the multi-day workorder.
For the VBA approach, I had roughly looked at starting with the number of days the workorder spanned (ie: Mon-Wed = 3), divide out the labor hours into equal parts (ie: 12hrs/3 = 4), and loop through the dates to append a row for each (ie: first record {Name, Wed, 4hrs}, 2nd {Name, Tues, 4hrs}, 3rd {Name, Mon, 4hrs}). I would also set it to handle weekends so that records are appended for weekdays only.
1) Would this give me the desired result?
2) Are there better ways to accomplish this?
3) What's the best way to work the code into my query? My only thought at this point (remember, my VBA experience mostly just covers forms at this point) is to set SQL language that would append 1 record at a time to a master table as the code loops. I would estimate that this would handle about 100-200 records each time the report is pulled, so it's doable but seems inefficient.
Thanks for the advice,
Tyler
Context - We have a database that stores detail on individual workorders, including labor time per person per workorder. The frontend has the ability to assign dates to each labor entry (best for multi-day workorders), but this is optional and underused. The workorder always has start and finish dates, so this is our fallback. We're currently putting together timesheet-like reports to check workorder time against timesheet time for each pay period. The problem cropped up when we found that several workorders span 2 or more days and don't assign labor to one specific date. The queries would have to calculate labor per work day and append a separate record for each day of the multi-day workorder.
For the VBA approach, I had roughly looked at starting with the number of days the workorder spanned (ie: Mon-Wed = 3), divide out the labor hours into equal parts (ie: 12hrs/3 = 4), and loop through the dates to append a row for each (ie: first record {Name, Wed, 4hrs}, 2nd {Name, Tues, 4hrs}, 3rd {Name, Mon, 4hrs}). I would also set it to handle weekends so that records are appended for weekdays only.
1) Would this give me the desired result?
2) Are there better ways to accomplish this?
3) What's the best way to work the code into my query? My only thought at this point (remember, my VBA experience mostly just covers forms at this point) is to set SQL language that would append 1 record at a time to a master table as the code loops. I would estimate that this would handle about 100-200 records each time the report is pulled, so it's doable but seems inefficient.
Thanks for the advice,
Tyler
Last edited:
