I've been having some problems figuring out how to join all my data in together for a report that shows cost/unit of production for several facilities. What I have is a LaborCost table that has how many hours each employee has worked for a given date. I have a Production table that has how many units each facility has produced. I have an EmployeeInfo table that has the employee and which facility they work at and their RegularPay and OTPay.
Ultimately, what I'm trying to figure out is Cost/Unit. So you can see that I need to first have a query that links EmployeeInfo and LaborCost together based on employee number. My first hurdle is trying to figure out the pay for each employee on a weekly basis. I've summed up the hours worked by week by grouping by [in a Totals query] DatePart("ww",Date). Then I make a subreport out of that query and use the IIF function to determine pay rate whether over 40 or 40 or less like so: =IIf([TotalHrs]>40,[PayRate]*40+[OTPayRate]*([TotalHrs]-40),[TotalHrs]*[PayRate]).
Then I have salaried employees that I need to make another query/subreport for. I'm just going to divide the salary by 365 and multiply it by the number of days in the date range they're looking at.
For production, it will just be the number of units produced in the given time period.
Then pull the values from the subreports for the calculations of costs/units.
Am I on the right track here or has there been something I've started off wrong on. I'm sure this scenario has come up countless times with anyone trying to figure out production costs. Any help would be greatly appreciated. Thanks in advance!
I've recently found out that, no matter how much VB code you know, learning the art of queries and reports is a completely different thing. I'd love to have a resource that points to teach some techniques in query/report design.
Okay, I'm having another problem. I have the IIF function above giving me a value in every record in detail. What I'd then like to do is sum those values in a text box at the end of the report. I need to reference this later from other places on the report.
Whenever I run the report with a text field in the Report footer with control source as =Sum([txtHourlyCost]), which is the field in the detail with the IIF function, it prompts me for the value of txtHourlyCost. Any suggestions? Is there no way to sum text fields that aren't actually fields?
I think you can only sum things that are actually fields in your record source, you cannot sum an unbound text box. You could create a second non-visible text box, txt2, and in the format event of the report you could just say
That worked great. I don't know why I didn't put 2 and 2 together to get that. Tunnel vision.
One thing I'm having to make sure of, though, is that my public variable doesn't cross over from one subreport to the next. I have it displaying a subreport for each production facility. What I experienced was that, if I don't set the public variable back to 0 in the subreport's Open event, it retains its value and is added again in the next subreport.
So, just to make sure I understand what's going on here:
Subreport Opens and Closes for each Master link field in the report, but still carries over the public variable. Shouldn't the public variable in the subreport module become empty once the subreport has run for each instance of the main report value, essentially closing it?
I've found a problem. The values in the report footer are showing twice the amount they should be. Do you know a reason for this happening? Is it back-stepping in the format and causing it to add twice? I'll be trying to figure it out. Thanks for your help!