Results 1 to 1 of 1
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Exclamation Unanswered: Filtering report controls and grouping

    I'm trying to create a report that shows data horizontally by month.

    I can't use a crosstab query because I have multiple criteria that exceeds the crosstab option. I'm trying to use iif statements in the detail controls , =IIf(([mthyr])="jan-03",[admits],0), but I'm having problems with the groupings. I am grouping by bed type and putting the iif statement in detail section of each control to look for appropriate month but it keeps repeating all the evt_desc for every month even though I don't have any month groupings on it. Ultimately, I want one line for each bed type and evt_desc. Any ideas? Once I have the detail, I then need to total it accross and down.

    Simple example of what I am trying to accomplish:

    Columns in table:
    Bed_Type
    Month
    Evt_desc
    Admits
    Days

    End Result required in report:

    Bed Type 1--------Jan---------Feb------ Mar---------Total
    --evt_desc 1-----admits------admits-----admits-----sum admits for year
    ------------------days -------days-------days-------sum days for year
    ---evt_desc 2-----admits------admits-----admits-----sum admits for year
    -------------------days -------days-------days-------sum days for year

    Bed Type 2--------Jan---------Feb------ Mar---------Total
    ---evt_desc 1-----admits------admits-----admits-----sum admits for year
    -------------------days -------days-------days------sum days for year
    ---evt_desc 2-----admits------admits-----admits-----sum admits for year
    -------------------days -------days-------days------sum days for year


    Total-evt_desc 1--admits-----admits------admits-----sum admits for year
    -------------------days -------days-------days------sum days for year
    Total-evt_desc 2--admits-----admits------admits-----sum admits for year
    -------------------days -------days-------days------sum days for
    Last edited by analyst; 07-08-04 at 15:22.

Posting Permissions

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