Results 1 to 8 of 8
  1. #1
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60

    Report Calculation Resulting in Error


    I currently have set up two tables to track production. In the first table, I specify the production date. In the second related table, I enter the date as well as the output of the day. So in table one there is a single date but in table 2 there can be several records tied to the same date.

    I am trying to write a report that will calculate the average output per day as well as for selected time period. The average per day is fine. However, when I try to sum up the total hours worked over the selected time period, it adds in the daily hours for each individual line item of output.

    It is much easier to understand when you see the report. What is the best way to upload this information so it can be clearly understood?

    Thanks,

    Greg

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can generally attach the db here after doing a compact/repair and then zipping.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60

    DB Zip File

    I tried to upload my DB. If you look at DSObyDate, you may be able to see my issue.

    Thanks,

    Greg
    Attached Files Attached Files

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not clear on where you have the issue, as there's nothing in the report footer. I suspect I know what the problem is though. Try putting a hidden textbox in the group footer with the hours field in the control source and the running sum property set to "Over Group". In your calculation in the report footer, refer to that textbox instead of summing hours.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60
    Thanks for your interest. I remove the textbox in the report footer because it was not providing the info I needed. I use =sum([hours]) in the footer of the report so I could use the total worked hours over the time period to analyze production output. However, instead of adding up the hours per each production day, it would take the typical 8 hours but include it for every item produced on that day. So if three different pieces were fabricated that day, the hours would be 24 and not the 8 hours.

    I cannot see why because the hours are recorded one time and are in I the table tied to a single date.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you try my suggestion? If you run the report's source query you'll see why you get the problem. It may be in the table one time, but the date is repeated for every record in related tables. You normally wouldn't be summing data from the "one" table, just the "many" table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60
    Tried it and it worked like a charm. Thanks for the solution. I appreciate it.

    Greg

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help Greg!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 5
    Last Post: 09-01-2019, 11:16 AM
  2. Calculation Error in SubForm
    By adaoluz in forum Forms
    Replies: 1
    Last Post: 12-10-2018, 02:28 PM
  3. Overflow Error When Doing Calculation
    By Comtech in forum Access
    Replies: 5
    Last Post: 07-06-2017, 08:34 PM
  4. Replies: 1
    Last Post: 11-18-2014, 12:35 PM
  5. Calculation error
    By wasim_sono in forum Forms
    Replies: 1
    Last Post: 03-26-2009, 02:51 PM

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