2 Formulas for a report needed

[Leesha]

Hi,

1. I have a report that trends patient admissions. One of the areas trended is by gender. The Contol {txtGender} returns "Male" and "Femaile" and the control [1] gives the breadown. This works fine. At the end of the report I need to provide provide the percent difference between the genders. I'm not sure how to write the formula for this using [txtgender] and [1].

2. I have another report that returns totals based on [reasonfordischarge]. This is done using a crosstab query with controls [1], [2], [3], [4], [5] which represent 5 different years. This step is working fine in the query and the report when I want to list all of the reasonforDischarge. I also need to be able to show the [reasonfordischarge] that has the highest total, without any of the others showing. I've tried using max but that didn't work.

Thanks,
Leesha

[HansV]

This is a bit hard without seeing the database. Could you post a stripped down, compacted and zipped copy of the database? Delete or alter sensitive information from the copy.

[Leesha]

It's a huge database linked to sql tables so I made tables that contain the data I need in order to get this to you. I've put notes in the footer of each report to describe (I hope) what I'm looking for.

Thanks!
Leesha

[HansV]

In the attached version, I have created a series of queries to obtain the results you want.

The final one for the difference between females and males is sqryGenderFooter; this is the record source for the report srptGenderFooter that has been placed as a subreport in the footer of srptCOSGender-Combined.

The final one for the discharge reason with the highest number in year 1 is sqryMaxReason; this is the record source for the report srptMaxReason.

Copy of Copy of Mini RSMDashboard.zip

[Leesha]

Thanks Hans!! It's almost a relief to know that it wasn't a simple formula to get the results because I was feeling soooooooooo dumb that I couldn't come up with them!!

Leesha