Help!! calculations for statistics.

[altair]

Hi all,

I'm trying to make a Statistics for my DB.

Example:

Status............Rank

=======....======

Onboard.......Master

Onboard.......A/B

Ex-crew.........Master

RIP..............Cook

Ex-crew.........Master

Onboard.......Master

Onboard.......Cook

Result:

Onboard

Master = 2

Cook = 1

A/B = 1

Ex-Crew

Master = 2

Cook = 0

A/B = 0

RIP

Master = 0

Cook = 1

A/b = 0

Out of my example I need to show this to my layout for a quick glance on the stats and also so i can put it in my report. I do not know if this is the right place to post this, but I do not know where to start?

Thanks in advance and I hope you can understand my example and the result that i want to produce.

[BobWeaver]

You can do this very simply with a subsummary report using summary fields. Create a summary field RankCount defined as Count of Rank. Then create a layout with two subsummary parts and no body part. The first subsummary part is grouped/sorted by Status, and the second one is sorted by rank.

In the Status summary part, place the status field, and in the rank summary part, place the rank field and the RankCount field. To generate the report, sort by Status and Rank in that order then go into preview mode or simply print the report. You can set up a script to do this very simply.

[altair]

Hi Bob,

Sorry for the late reply Thanks for the advice tried it and worked fine, but is there a way i can make a field or fields that i can put in a layout and use browse mode to see the same data as in preview mode.

Thanks again.

[BobWeaver]

You can do it with an additional table and several relationships.

If you only have 3 different status values, then I would do it this way:

Create a new table called Ranks with these four fields:

Rank - text

Status_Onboard - calculated, Formula: "Onboard"

Status_Ex-Crew - calculated, Formula: "Ex-Crew"

Status_RIP - calculated, Formula: "RIP"

Now, set up three relationships r_Onboard, r_Ex-Crew, and r_RIP, from this table back to your main table, as follows:

r_Onboard:

(Ranks::Status_Onboard = Main::Status) and (Ranks::Rank = Main::Rank)

r_Ex-Crew:

(Ranks::Status_Ex-Crew = Main::Status) and (Ranks::Rank = Main::Rank)

r_RIP:

(Ranks::Status_RIP = Main::Status) and (Ranks::Rank = Main::Rank)

Now create 3 more fields in the ranks table:

Count_Onboard - calculated, Formula: Count(r_Onboard::Rank)

Count_Ex-Crew - calculated, Formula: Count(r_Ex-Crew::Rank)

Count_RIP - calculated, Formula: Count(r_RIP::Rank)

Finally, populate the table with one record for each different rank, and set the rank field accordingly.

The count fields will display the count of each status for the given rank.

Edited October 5, 2006 by Guest
typo