Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: Could some1 help on Calculating Totals for each group in a Report please?

    I have created a report based on a query, despite all my efforts for some reason I am unable to calculate the total £amount for every employee separately. I have the following on the report:

    Report Header
    EmployeeID Header
    Detail
    EmployeeID Footer

    In 'Detail' section I have employees working hours and in EmployeeID footer section I have created text box to calculate their total amount.
    When I view the Report, the report calculates total of every page on the next page, and again on the next page and finally on the last page (meaning calculates the total amount of all employees on the last page.

    I want the total amount for every employee to be separate and on a separate page i.e. when I print employees payslip I want each employee's payslip to have the their own total amount. For some reason, it does the opposite, when I print employees payslip the report keeps adding every employee's total to the next one, next one and finally calculates the grand total in the last employee's payslip.

    I have set the Running Sum for all the text boxes that calculates employees working hours to OverGroup and OverAll and No but it still does not work.

    Any help would be grately appreciated.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    I'm trying to do this off the top of my head so let's see how close I get...

    First, to total the amount of each employee I would just add a textbox in the EmployeeID footer and use the Sum function. Something like =Sum([Amount]). Don't worry about the Running Sum unless you want it to calculate for the entire report.

    Next go into Sorting and Grouping and select Together. There you can tell it how to group the sections together. Play around to get it grouping the way you want. Also, somewhere there is a Force New Page property that you might have to play with as well. Search for it in the Help as I can't remember exactly where it's at.

    Hope that gets you going in the right direction.

    C

  3. #3
    Join Date
    May 2006
    Posts
    386
    Hi, Thank you for your response.
    I have tried all that but it just doesnt work. It keeps adding the total for every employee. For example, if i want to view 10 employees payslip which includes travel time, work time, travel expense, parking and bus fare, it keeps adding the total amount of every employee onto another employees total amount. For example. Mike's total pay is £100, Bob's total pay is £100, and Lesley's pay is £50 so each of their payslip should only show their own total amount BUT the print does the opposite, it adds Mike's £100 onto Bob's £100 and then adds their total onto Lesle's total pay and it goes on like this on all employees.

    I have attached a screen shot of how they look like so you know what I mean. As you can see from the screen shot, Mike's toal Pay has been added onto Bob's total pay.
    YOur kind response and assistance wouldbe much appreciated.
    Attached Thumbnails Attached Thumbnails Example.bmp  
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    add a textbox in the EmployeeID footer and use the Sum function. Something like =Sum([Amount])
    This will work. Just make sure Running Sum property is not on.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2006
    Posts
    386
    Hi, the Running Sum is set to NO but it still does not work. I will be most grateful for any help.
    Emi-UK
    Love begets Love, Help Begets Help

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I vaguely remember someone else having the same problem tbh. I wish I could remember it clearly.

    Do you have any page breaks or Force New Page: before/after sections applied?

    It has to be something simple... Sum in a grouping level header/footer always works for me
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    May 2006
    Posts
    386
    Hi, thank you for your response.

    Yes, I have the following on EmployeeID Footer
    1. Force New Page 'After Section'
    2. Force New Row 'After Section'
    3. Keep Together 'Yes'

    And I have the following on EmployeeID Header:
    1. Force New Page ' None
    2. Force New Row ' None'
    3. Keep Together 'Yes'

    I also have the following on 'Detail' section

    1. 1. Force New Page ' None
    2. Force New Row ' None'
    3. Keep Together 'Yes'

    I am really stuck in this and have tried changing the above to yes and no and none and etc but none of them worked. I would really be grateful for any help.
    Emi-UK
    Love begets Love, Help Begets Help

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    are these sums in a page footer or a group footer
    if the sums are in a group footer which is triggered on change of employee ID I can't see there being a problem.
    it may be that you are trying to fit these totals at the foot of the page..... in which case you are going to have to be a wee bit creative.

    you may need to either drop that requirement
    OR
    use a parent / child report
    the details of the payslip got int he child report
    the page footers go in the parent report, pusching vlaues form the child report into the parent report (forms!myparentreport!myvaraiblename = thisvalue, and then assign the value of myvariablename to an appropriate control in the group footer in the parent forms group footer on format event)
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    May 2006
    Posts
    386
    Dear Healdem, Thank you for your response.
    Yes, the sums are in a group footer. I don't necessarily want the Totals to be at the bottom of each page as long as it creates a new page for every employee which means that the Total could be on the 2nd, 3rd or 5th page of every employee.

    I have never used a parent/ child report and therefore I dont think I would know how to deal with this without some assistance. But I am puzzelled that i have some other reports and they work peferctly alright and I even matched the details of those reports with this one but it just does not add up.

    Thank you so much for your advice.
    Emi-UK
    Love begets Love, Help Begets Help

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you are getting near the stage of having to post the db here (with all sensitive data removed or masked)
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    May 2006
    Posts
    386
    I wish I could attach the DB, it is a very confidential database and unfortunatley I am not allowed to post it on this forum. However, below is what I have in the text box under EmployeeID footer:
    =Sum([txtSumJourneyAmount]+[txtSumWorkAmount]+[txtSumExpenses]+[SumJourneyFee]+[txtTotalCallAmount]+[Bonus4employee]+[UnderPaidAmountFrombefore])


    I hope you can help with this. I just cannot figure out as to what might missing.
    Emi-UK
    Love begets Love, Help Begets Help

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Can you now tell us what each of these are:

    [txtSumJourneyAmount]
    [txtSumWorkAmount]
    [txtSumExpenses]
    [SumJourneyFee]
    [txtTotalCallAmount]
    [Bonus4employee]
    [UnderPaidAmountFrombefore]

    For each one, is it:

    * a field in the underlying query?
    * a control on your report which is bound to a field in the underlying query?
    * a control on your report which contains a calculation/formula.

    If these are controls, then which section of the report are they in?

    Sum will only work for fields which are bound to a field in the underlying query.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    =Sum([txtSumJourneyAmount]+[txtSumWorkAmount]+[txtSumExpenses]+[SumJourneyFee]+[txtTotalCallAmount]+[Bonus4employee]+[UnderPaidAmountFrombefore])
    ? shouldn''t that read
    Code:
    mycontrolname.value =[txtSumJourneyAmount] + [txtSumWorkAmount] + [txtSumExpenses] + [SumJourneyFee] + [txtTotalCallAmount] + [Bonus4employee] + [UnderPaidAmountFrombefore]
    ..I'm not too sure how access vba handles the summation of a control that is in itself a control and not a column form the db (well Im pretty certain it doesn't...)

    it may be worth doing an express cast
    [code]mycontrolname.value =csng([txtSumJourneyAmount]).....

    It may be that you are trying to sum a currency value and that is causign problems...
    try going back to the original data

    BTW are you making certain you are using a rounding function on the the data.. you need to make certain that the grand total is the same as the sum of its parts. if you are doing any calculations make certain that each element ROUND()s to the correct decimal place, and totals using thise values use the ROUND()ed value

    Accountants and other forms of financial pondlife can can very upset if they see a discrepancy
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    May 2006
    Posts
    386
    Thanks to Healderm and StartTrekker,
    In response to StarTrekker,

    Can you now tell us what each of these are:
    [txtSumJourneyAmount]
    [txtSumWorkAmount]
    [txtSumExpenses]
    [SumJourneyFee]
    [txtTotalCallAmount]
    [Bonus4employee]
    [UnderPaidAmountFrombefore]
    All of the above are:
    * controls on my report which are bound to fields in the underlying query and all of the above are in the EmployeeID footer
    ---------------------
    Healdem, can you please explain do you mean it should read like this:
    =([txtSumJourneyAmount]+[txtSumWorkAmount]+[txtSumExpenses]+[SumJourneyFee]+[txtTotalInterpretingAmount]+[Bonus4Inters]+[InterUnderPaidAmount])
    if not, then please tell me what do you mean by control?

    I am sorry that don't really know what Express Cast. Yes the grand total is the true total of other amounts as shown above.

    It is weird that it worked well when I was printing one by one. I mean only one employee at a time but since I wanted to print all employee's payslips in one go so it makes it easy for us to pay them and file their payslips, this problem has occured.

    I would honestly be so grateful for any possible solution to this.
    Emi-UK
    Love begets Love, Help Begets Help

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    express cast.. expressly casts a variable to a specific datatype, using an inbuilt function. eg to convert to
    a single precision number csng(myvalue),
    an integer cint(myvalue)
    a date cdate(myvalue)....

    i suspect the problem is two fold
    you are trying to use the sum function on controls.. either sum the original values or add together the values in the controls or do some basic maths behind the scenes int he report group header & footers

    I suspect Access is throwing a wobbler becuase you are trying to add columns which have the currency symbol in.. that too may cause problems
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •