Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Calculation (2000)

    Hi Everyone,

    Pretty much a beginner here. I have a report based on a query that returns a date and number of hours for that date that a person called in sick. I have another query that gives a running total of the number of hours using the Group by Sum field. In my report I see the employees name and the dates and hours they called in sick under the details section. I want a running total for each employee too in that section. I created a control and selected the hours field then selected "running sum" over the group but I get a sum for each line. I only want it to be next to the last line for that employee, adding up the total hours they called in sick. Perhaps there is a calculation to only show the total that I can type into my control box?

    Eventually I will also want a report to only see the hours per quarter, which is Jan-Mar, April-June, July-Sept, and Oct-Dec. I think I need to make a query then a report but if not I would love to know how to do this in the report.

    Thanks,
    Linda

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Calculation (2000)

    1. If you want a running sum but don't want it for each line, please try to define clearly what you mean by a running sum.

    2. If you group a report on a date field, the Sorting and Grouping window lets you specify how you want to group the dates. By Quarter is one of the available options. If you only want to present data summed by quarter, leave the Detail section empty (and give it a height of 0), and use controls with control source =Sum([FieldName]) in the group footer. You don't need to create a query that groups or sums the data for this; the Sorting and Grouping options of the report will take care of everything.

  3. #3
    New Lounger
    Join Date
    May 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Calculation (2000)

    Hi Hans,

    1. Sorry, I am not very well aquainted with the terms in access. I'll try to do a little better. I am self taught from trying, looking in the help files and reading on newsgroups. The only reason why I know the term running sum is because it was in the properties list after I created a text box in a report. I selected hours for the control source and then I saw "running sum" so I selected yes, to see what would happen. What I wanted was almost there and I understand why it added each line. What I want is a total of all the hours for each employee. I do have a query that does that but I don't know how to pull information from a query that my report is not based on.

    Here is my best shot at defining running sum. Please tell me what the proper description is. I'll attach a sample of my report in .rtf format so you can see how it is set up. As you look at Karen Maloche, she has 3 sick days. I want 28 (the total number of hours for her) to show up somewhere in the vicinity of her name. As I am dreaming up plans it may be nice to see total hours per quarter too. Maybe your #2 solution will be just what I want.

    I am going to try your number 2 suggestion as soon as I get a chance. Hopefully tonight or tomorrow. It looks like something I may be able to pull off if I cross my fungers.

    Thank-you, Linda
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Calculation (2000)

    What you want is called a total. I'll try to illustrate what is usually called a running sum in a sample table:

    <table border=1><td>Date</td><td>Hours</td><td>Running Sum</td><td align=right>1/28/03</td><td align=right>5</td><td align=right>5</td><td align=right>2/02/03</td><td align=right>7</td><td align=right>12</td><td align=right>2/09/03</td><td align=right>1</td><td align=right>13</td><td align=right>2/11/03</td><td align=right>4</td><td align=right>17</td><td align=right>2/26/03</td><td align=right>3</td><td align=right>20</td></table>
    As you see, the value for Hours in each row is added to the Running Sum from the previous row to obtain the new Running Sum.

    As far as I can tell, you are not interested in displaying this kind of running sum. Both the quarterly total and the end total can be calculated in the report, you don't need a separate query for that.

  5. #5
    New Lounger
    Join Date
    May 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Calculation (2000)

    Thanks. I'll have to try your report suggestion tomorrow when I get to work and let you know how it goes........Linda

  6. #6
    New Lounger
    Join Date
    May 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Calculation (2000)

    Hi Hans,

    I have been trying to work this out for a couple of days. I have learned a some great things that I can use on other reports but I can't find anywhere that says by quarter. I've posted a screen shot of my report in design view.

    Thanks,
    Linda
    Attached Images Attached Images

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Calculation (2000)

    Hi Linda,

    You have grouped your report on dttPTUDate. If you activate the Sorting and Grouping window (View | Sorting and Grouping), you will see a line with dttPTUDate there. Click in this line. The lower pane of the Sorting and Grouping window contains properties of this group level:

    <table border=1><td>Group Header</td><td>Yes</td><td>Group Footer</td><td>No</td><td>Group On</td><td>- set this -</td><td>Group Interval</td><td>1</td><td>Keep Together</td><td>None</td></table>
    Click the dropdown arrow in the Group On box and select Quarter.

  8. #8
    New Lounger
    Join Date
    May 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Calculation (2000)

    OK, after I tried a billion times and didn't see the qtr option, I didn't want to call you a liar. I figured I messed something up. I checked in the table in the dttPTUHours properties. I named it correctly but forgot to change the data type to date/time. It was set as text. What a knuckle-head I am. Thanks for telling me the same thing over and over again. <img src=/S/bash.gif border=0 alt=bash width=35 height=39> I FINALLY FOUND IT!

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Calculation (2000)

    I'm glad you found it. Since you used dtt as prefix, I assumed that you would have defined it as a date/time field <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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