Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report with extensive footer requirements (2000 latest)

    A report tallies requests for service provided by a municipal staff in a period of time selected. The service could have been in any of 30 different categories, by job codes. The individual 30 categories could include anywhere from 1 to 9 job codes.

    In a report footer there are 30 different text boxes, which provide the Sum of the staff hours for each category.

    The query that populates the report is based on 4 tables joined together.

    In order to provide the data for the report Footer text boxes, I have included in the query a series of IIf expressions. For example,
    MutualDPW: IIf(([JOBCODES]![JOB_CODE] In ("321","329","339","340","345")) And [SO_PE]![PE_TYPE]="P",[SO_PE]![PE_HOURS],0)

    The "P in the expression stands for Personnel, as there are separate calculations for Equipment.

    The IIf expressions do the job just fine. However, since there are 30 of them, I am wondering about a different approach that would be more efficient.

    Thanks for any suggestions.

    Tom

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Report with extensive footer requirements (2000 latest)

    It sounds like a cross-tab query might actually do most of the summing for you - and then you might be able to use that query as the data source for a subreport in the footer of the report. One of the bigger problems with the approach you've taken is that adding a new category, or moving job codes to a different category, requires that you go in and change the query. The whole thing must be a humdinger to try to debug or change as well.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report with extensive footer requirements (2000 latest)

    Wendell
    Thanks for your reply, regarding a crosstab query. However, this won't get around the problem of changing job codes. On the other hand, I'm also not partial to having 30 IIf expressions. And I'm trying to help out a friend who has to try and put the whole thing together.

    You're right. It's a nightmare to debug.

    Tom

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Report with extensive footer requirements (2000 latest)

    Actually with a table mapping of job codes to categories, the crosstab will do the trick for you. And if you change job codes to category mapping, the crosstab then doesn't need to change. You simply include the mapping table with a join to the job code of the detail data.
    Wendell

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report with extensive footer requirements (2000 latest)

    Wendell
    I like the idea. Could you explain more fully exactly what you mean by "table mapping of job codes to categories?"

    Thanks.
    Tom

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

    Re: Report with extensive footer requirements (2000 latest)

    Create a table with two fields: Job_Code and Category. Each individual job code has its own record in this table, and the Category field lists the category to which the job code belongs. You can add this table to your query, joined to the appropriate table on the Job_Code field. Add the Category field to the query grid.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Report with extensive footer requirements (2000 latest)

    Sorry for not responding sooner - and thus Hans gave you the answer. It's a simple two field table, and as he suggests, use a query to map each record to the right category, and base your crosstab query on that query.
    Wendell

  8. #8
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report with extensive footer requirements (2000 latest)

    Wendell and Hans
    Thanks for the further explanation. I assumed that is what you meant but wanted to check.

    I'll give that a try.

    HAPPY NEW YEAR!

    Tom

Posting Permissions

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