Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    100

    Unanswered: calculations on subgroups in a report

    I have a report based on a crosstab query. I have the query grouping time entries based on several categories and the report finishes the job. My only problem is that I would like to be able to do a calculation within the detail portion of the report by group. Let me explain by example....

    Table:

    Client.....Project.........Date..............Hours
    IBM..........Pencil.........1/1/2003........6
    IBM..........Pencil.........2/12/2003........5
    IBM..........Keys..........5/5/2003........4
    IBM..........Keys..........5/25/2003........4
    SUN..........Pencil.........1/5/2003........6
    SUN..........Pencil.........3/1/2003........6
    SUN..........Keys..........5/15/2003........4
    SUN..........Keys..........6/20/2003........5

    Query Results (in which the hours are summed for a given company and project):

    Client.....Project..............Hours
    IBM..........Pencil................11
    IBM..........Keys...................8
    SUN.........Pencil.................12
    SUN.........Keys...................9

    Report Results (same as query):

    Client.....Project..............Hours
    IBM..........Pencil................11
    IBM..........Keys...................8
    SUN.........Pencil.................12
    SUN.........Keys...................9

    Hoped for Report Results: (I want to show the highest date that falls into that grouping while still maintaining the grouping) I also do not want to show the highest date in the summary portion but want it to be alongside the other data items in the detail section.

    Client.....Project..............Hours.....MaxDate
    IBM..........Pencil................11.......2/12/2003
    IBM..........Keys...................8.......5/25/2003
    SUN.........Pencil.................12......3/1/2003
    SUN.........Keys...................9.......6/20/2003


    Any ideas??

    Thanks!

    Norm

  2. #2
    Join Date
    Jan 2004
    Posts
    100

    Re: calculations on subgroups in a report

    Originally posted by norm801
    I have a report based on a crosstab query. I have the query grouping time entries based on several categories and the report finishes the job. My only problem is that I would like to be able to do a calculation within the detail portion of the report by group. Let me explain by example....

    Table:

    Client.....Project.........Date..............Hours
    IBM..........Pencil.........1/1/2003........6
    IBM..........Pencil.........2/12/2003........5
    IBM..........Keys..........5/5/2003........4
    IBM..........Keys..........5/25/2003........4
    SUN..........Pencil.........1/5/2003........6
    SUN..........Pencil.........3/1/2003........6
    SUN..........Keys..........5/15/2003........4
    SUN..........Keys..........6/20/2003........5

    Query Results (in which the hours are summed for a given company and project):

    Client.....Project..............Hours
    IBM..........Pencil................11
    IBM..........Keys...................8
    SUN.........Pencil.................12
    SUN.........Keys...................9

    Report Results (same as query):

    Client.....Project..............Hours
    IBM..........Pencil................11
    IBM..........Keys...................8
    SUN.........Pencil.................12
    SUN.........Keys...................9

    Hoped for Report Results: (I want to show the highest date that falls into that grouping while still maintaining the grouping) I also do not want to show the highest date in the summary portion but want it to be alongside the other data items in the detail section.

    Client.....Project..............Hours.....MaxDate
    IBM..........Pencil................11.......2/12/2003
    IBM..........Keys...................8.......5/25/2003
    SUN.........Pencil.................12......3/1/2003
    SUN.........Keys...................9.......6/20/2003


    Any ideas??

    Thanks!

    Norm

    I figured it out....

    I just needed to use the Max funtion within the query. (I accidentally had 2 date fields in the query)

Posting Permissions

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