Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping and Summing (Access2000)

    With a report I am creating, I'm struggling on summing a total.
    The report groups on Employee and shows absenteeism and holidays less Workdays and holidays.

    The recordsource for the report is qryAttPeriod:

    <pre>SELECT tblAttPeriod.PeriodID, tblAttPeriod.UnitID, tblAttPeriod.FromDate, tblAttPeriod.ThruDate,
    tblAttPeriod.ColorKey, tblAttPeriod.WorkGroup, tblAttPeriod.Other
    FROM tblAttPeriod
    WHERE (((tblAttPeriod.FromDate)<=[Forms]![frmAttendance]![txtDateThru]) AND
    ((tblAttPeriod.ThruDate)>=[Forms]![frmAttendance]![txtDateFrom]))
    ORDER BY tblAttPeriod.ColorKey;</pre>


    There is a little code behind the report:

    <pre>Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Me.txtWd = WeekdaysMinusHolidays(Me.FromDate.Value, Me.ThruDate.Value)

    End Sub</pre>


    I need to sum txtWd grouped from Colorkey from within the query which I can see is only populated on format
    of the report.
    Is there a way to do this ?

    The result using the right hand column in the attached PNG would show:
    Bayley,D
    Holiday(Optional)= 17 Days
    Late = 1 Day
    Saturday Request = 1 Day

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

    Re: Grouping and Summing (Access2000)

    You should group the report on ColorKey within Employee.
    Does it work if you put a text box txtSum in the detail section with control source

    =WeekdaysMinusHolidays([FromDate], [ThruDate])

    and set its Running Sum property to Over Groups? If so, you can set the Visible property of this text box to No, create a group footer for ColorKey and put a text box in the group footer with control source

    =[txtSum]

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping and Summing (Access2000)

    Hans

    I'm not getting the results I was after.
    I've taken the code away and set the control source to a text "txtSum" as you said.
    What I'm after, is How many days "Late", how many days "Holiday(Optional)" how many days "Sickness".

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

    Re: Grouping and Summing (Access2000)

    Dave, could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping and Summing (Access2000)

    Here you go Hans

    I've stripped everything out to keep under 100k
    I can deal with the Weekdays etc later.

    (You're probably going to make me look stupid now.)

    Thanks again

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

    Re: Grouping and Summing (Access2000)

    Does the attached version do what you want? I added a grouping level for ColorKey, and an invisible text box txtSum in the detail section with Running Sum set to Over Groups. A text box in the group footer displays the value of txtSum. This is basically what I described in <post#=523,293>post 523,293</post: > higher up in this thread.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping and Summing (Access2000)

    Hans

    I've got it working now after a bit of messing about.
    Just one thing extra, To show the correct column from Colorkey, I need to set the control source to:

    <pre>="Total for " & ([ColorKey.column1])</pre>


    Which throwe up #Name?
    There's a very good reason why there's a combo in the report, it shows the correct caption from a related table.

    Thanks Again

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

    Re: Grouping and Summing (Access2000)

    Try

    ="Total for " & [ColorKey].[Column](1)

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping and Summing (Access2000)

    Just right.

    Thanks for all the help again.

Posting Permissions

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