I work for a machine & repair shop and I have developed a database for job and hours tracking and I have hit a couple hurdles I have just not been able to get over.
- Grand total for estimated hours of all open jobs on bottom of open jobs variance report.
- Grouping of hours by job number has caused sequential jobs with same customer name to no show the name on report, only the job number and hours data.
I feel some background will be needed to help with an understanding of the database structure and is probably needed for you pro's to help me.
We have hundreds of jobs going at any given time with multiple employees working on each one, so accurate labor tracking is critical. The managers/leads estimate hours needed for each job and task prior to quoting the customer. When we get a "GO" the job then goes to shop and the individual technicians start doing their parts to get it ready to ship. It makes accurate labor tracking difficult. Until now they were manually entering, calculating and reentering data on Excel sheets to come up with hours variance reports. This took hours each week of manual data entry and transcribing. I knew Access could streamline the process to cut admin time so I started building structure and adding new things as they came up. Now the database has evolved into a full job tracking setup and handles all the job status information a well as pre-quote hours estimations.
The problem is the Variance reports are not quite how Management wants them due to the nature of our business and data generated by it. I will give a rundown of the basic data structure and then the issue I am having.
TABLES:
- JOBS (has base job data like job number, customer, description, estimated hours for each task, overall status data)
- HOURS (individual records of technicians time with name,job#, date and actual hours worked per task, there are entries for each tech by job, by day so each job can have 1-100 entries depending on how much work it needs.)
The root issue I believe is that we have multiple people performing similar labor and/or the same person performing the same labor on a job multiple times, which is what is making my last needed formula not work.
Open Variance report. I use a query to limit the data to only open jobs and supplying the fields I need on the report. I have been able to figure out and setup a report that summarizes the data by job number and runs a total for each given job (line on the report) as shown.
What I have not been able to do is get it to give a grand total of estimated hours for all jobs at the bottom of the report. The totals for actual hours works fine, but the estimated is always a huge obviously wrong number. I believe that is because, as shown below, the query is entering the estimated hours in the query results for every labor hours entry, then the report is totaling on that field thus multiplying the estimated amount by the number of entries in the query..?
Can anyone help? I am not a coder or VBA person, but maybe there is a way to get the estimated hours data on the report without having it in the query like it currently is?
Or maybe I need to rewrite the query?
Is there a way to nest a query inside another query? So one query totals up the per job hours then the final query pulls a single entry for the estimated hours from the jobs database and the calculated actual hours from the 1st query?