Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Report Formula needed (XP)

    Hi!

    I'm working on a report that is made up primarily of yes/no statements/questions. The report needs to show data broken out in table form for three separate offices all in the same table. For example, for the statement "No Action Needed", I need to so the results as follows:

    Office 1 Office 2 Office 3
    result result result

    I am not sure how to write the formula to show the result as described above. Breaking the report into separate reports by office will no meet the end user's needs.

    Thanks,
    Leesha

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

    Re: Report Formula needed (XP)

    Hi Leesha,

    There are two ways you can go about it; I don't know enough about your setup to know which one is best for your situation.

    1. Crosstab query.

    - Create a query based on the table that contains the data.
    - Add the result field, the office field and an arbitrary third field to the query grid.
    - Select Query | Crosstab Query.
    - Change the Total option for the third field to Count (the other fields will us the default Group By)
    - Set the Crosstab option for the result field to Row Heading, for the office field to Column Heading and for the third field to Value.
    - Switch to datasheet view to test if the query returns the desired results.
    - If so, close and save the query, and use it as Record Source for the report.

    or

    2. Sum and Abs

    You could create a report based on the table with the data, and calculate the counts you need in the Control Source of the text boxes, using Sum and Abs. Say that the office field is a number field OfficeID, with values 1, 2 and 3. The count of "Yes" answers to "No Action Needed" for Office 1 is:
    <pre>=Abs(Sum([No Action Needed]*([OfficeID]=1)))
    </pre>

    and similarly for the other offices. If the office field is a text field instead of a number, put quotes around the value:
    <pre>=Abs(Sum([No Action Needed]*([OfficeID]="Office 1")))
    </pre>

    You must of course substitute the actual field names.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report Formula needed (XP)

    Hi Hans,

    I decided to try suggestion 2 as I haven't done crosstab queries yet and it seemed a bit outta my league - of course you know I'll need to experiement. But, in the essence of time I went with suggestion 2. The formula reads as follows:

    =Abs(Sum([No Action Needed]*([Office]="norwich")))

    I'm not sure what went wrong as there are two records with a yes answere therefore I would've expected a 2 for an answer but instead got a 1.

    Leesha

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report Formula needed (XP)

    DAAAAAAAAA...................never mind, I found my error! The office data is further broken down into servcies provided. The office I was referring to had two separate services so the totals were by service. The formula works beautifully!!

    Thanks!!

    Leesha

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report Formula needed (XP)

    Hi Hans,

    I've been playing with variations of this all night with no success so I'm conceeding defeat and asking the expert! In the same report and same set of tables, I need to be able give a count of the total records in an office. For example there are 18 records total for all three offices. I need the individual count for each office.

    Thanks,
    Leesha

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

    Re: Report Formula needed (XP)

    Try

    =Abs(Sum([Office]="Norwich"))

    for the Norwich office, and similar for the others.

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report Formula needed (XP)

    Hi Hans,

    I had tried that one in my experimenting but it doesn't actually count the records. I need a total count of the records not the sum. I tried =Count([office]="norwich") but that gave me the count of all records in the database, not just the records for that office.

    Leesha

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

    Re: Report Formula needed (XP)

    The expression [Office]="Norwich" is either True = -1 or False = 0. By calculating the Sum of this expression, each record for which the Office is equal to "Norwich" contributes -1, and all other records contribute 0. So the sum is equal to minus the count you want. Abs takes care of the minus.

Posting Permissions

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