Results 1 to 2 of 2
  1. #1
    lamrith is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    Tacoma, WA
    Posts
    1

    Question Help making Report provide totals at bottom of complex report.

    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.
    Click image for larger version. 

Name:	variancereport.jpg 
Views:	11 
Size:	79.9 KB 
ID:	35211

    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..?
    Click image for larger version. 

Name:	variancequery.jpg 
Views:	10 
Size:	75.4 KB 
ID:	35210

    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?

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    One method might be to write a UDF.

    You have all of the estimates in the table Jobs. Create and save a totals query of the estimated hours for open jobs. Then, in a standard module, create a function to get the result of the totals query. In the footer of the report, call that function.

    Example:
    Step 1: Create the totals query (qryTotOpenJobs):
    (I don't know your table design, but if you have multiple fields for estimated hours by task, you will need to add the times, then sum the records.
    And I don't know how you determine which jobs are "Open")
    Code:
    SELECT Sum(Jobs.disassembly+Jobs.Hone+Jobs.Polish+Jobs.Machine+Jobs.Weld+Jobs.Spray+Jobs.assembly+Jobs.test) AS SumOfDeductionAmount
    FROM Jobs
    GROUP BY [Selected]
    HAVING ((([Selected])=True));
    Step 2: Create function in standard module:
    Lets say in "Module1", you have
    Code:
    Public Function fGetTotals() As Currency
        Dim r As DAO.Recordset
    
        Set r = CurrentDb.OpenRecordset("qryTotOpenJobs")
    
        fGetTotals = r.Fields(0)
        r.Close
        Set r = Nothing
    End Function
    Step 3: In the footer of the report, add a text box. Set the control source of the text box to
    Code:
    =fGetTotals()
    Close, then open the report...... hopefully, you will get a grand total.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-25-2018, 09:53 AM
  2. Replies: 8
    Last Post: 05-29-2015, 11:52 AM
  3. How to display totals at bottom of query or report
    By Access_Novice in forum Access
    Replies: 1
    Last Post: 09-12-2014, 12:58 AM
  4. Replies: 5
    Last Post: 12-06-2011, 11:18 AM
  5. Moving from Top of report to bottom
    By BrianFawcett in forum Programming
    Replies: 0
    Last Post: 05-05-2010, 09:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums