I have one variable that holds hundreds of repair codes that will
describe what type of repair was done on a product. I want to group
certain codes (e.g., 211, all codes in the 300 range, 212 = group 1)
to summarize groups of repairs, so that Group 1 reflects all
electrical types of repairs.

What I need now is a report containing a count of all codes in each
group.
Group 1 will be a count of every instance of the codes that define it.

I think I need to first make a calculated variable called Group1, with
a formula that finds every instance of it's defining repair codes.

Eventually I need a count in each group, by month.
For example, the following report says there were 11 repairs in the
electrical repair category for this month

CODE REPAIR CODE COUNT BY MONTH
211, 212, 3** Misc Electrical 11
210 Battery Failure 7
4** Clamp 1


I'm stuck on how to write the correct formula - any help is greatly
appreciated! Thanks,
cat

On Feb 23, 3:20 pm, "cat" <cathyty... (AT) gmail (DOT) com> wrote:
Obviously I am new to FM! So far I have figured out that I need to
first make a boolean field. To test, I will just have FM return a "1"
if the code = 211. I wrote the following, and it works fine:
Case(RepairCode = 211;"1")

Then I tried a sum of my boolean field:
Sum ( G1boolean )

But it doesn't do what I want. It returns a 1 every time it sees a 1
in the boolean field, but I want a sum or a count of all the "1s" -
and I know there are 6 instances of value "211" in my field.
Hmm...help? I tried a count and that didn't work either.

On Feb 23, 4:38 pm, "cat" <cathyty... (AT) gmail (DOT) com> wrote:
Ah-ha. duh! The sum needs to be a summary field, not a calculated
field.
Thanks for reading but this solves my question.

In article <1172278170.799583.40840 (AT) h3g2000cwc (DOT) googlegroups.com>, "cat"
<cathytyner (AT) gmail (DOT) com> wrote:
It looks like you're getting there all by yourself. )

Because your trying to group together separate Codes you'll need to
change your Boolean Calcuation.
eg.
G1 {Calculation, Number Result, Unstored}
= Case (RepairCode = 211, 1,
RepairCode = 212, 1,
(RepairCode > 299) and (RepairCode < 400), 1
)

For the Summary field you can use either "Sum of" or "Count", both will
work for what you're trying to do. (One thing to remember is that Count
only counts records where the field has some data in it.)

Summary fields work best in Summary sections of a Layout, so you'll
need a layout something like:


CODE REPAIR CODE COUNT BY MONTH

Header
-----------------------

[GroupCode] [GroupRepairCode] [s_SummaryCount]

Sub-summary by Code (Trailing)
-----------------------

Footer {if needed}
-----------------------


You don't need the Body part unless you specifically want to list some
details from every record.

This will work in either Preview Mode or when printed, as long as the
records are first sorted by Code (and you've performed a Find to get
just the records for "this month").


The GroupCode and GroupRepairCode fields above are Calculation fields
set to give each record the appropriate grouping's text wording for the
summary line of the report.
eg.
GroupCode {Calculation, Text Result, Unstored}
= Case (RepairCode = 211, "211, 212, 3**",
RepairCode = 212, "211, 212, 3**",
(RepairCode > 299) and (RepairCode < 400),
"211, 212, 3**"
RepairCode = 210, "210",
RepairCode > 399, "4**"
)

GroupRepairCode {Calculation, Text Result, Unstored}
= Case (RepairCode = 211, "Misc Electrical",
RepairCode = 212, "Misc Electrical",
(RepairCode > 299) and (RepairCode < 400),
"Misc Electrical"
RepairCode = 210, "Battery Failure",
RepairCode > 399, "Clamp"
)


If there are lots of codes this can obviously get messy. It might be
better to use a second Table / File to store the various grouped codes
and their textual values which can be retrieved by a Relationship link.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

On Feb 23, 6:00 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com>
wrote:
Thank you so much, very helpful!

On Feb 26, 6:05 pm, "cat" <cathyty... (AT) gmail (DOT) com> wrote:
New user, help appreciated!

I have the summary count field working. Now I need to show what
proportion that count is of the total. I have looked at lots of
simialr questions in the forum, books, and help files but I'm stuck.

To detail:
I have Group1 with a summary count of 10. There are 100 total records.
How do I create a field showing that this count is 10% of the total
records? I tried to make a summary field using "fraction of a total
of" with the summary count, and it doesn't work.

Some more information about how your database is set-up is needed here.
What are the appropriate fields and perhaps some example data from a
few records.

The "Fraction of Total" Summary option does work, but only for simple
databases and only when the data is sorted correctly (with the field
appearing in the corect Sub-summary part of the layout).

It won't work for more complicated databases. In this case you need to
do some playing with extra fields and the Get Summary function to
replicate a similar idea.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

On Mar 13, 4:50 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com>
wrote:
Thanks for writing. Let me try to give you more detailed information,
as I'm still stuck.

There are hundreds of repair codes. Some are grouped together by
common repairs, so several repair codes will belong to Group 1 (G1),
which will be all the repair codes that fall into the Electrical pairs
category. There are 11 different groupings of repairs, each with
several individual repair codes. Here are the actual groupings.

G1 211, 212, 3** [i.e., all codes starting with 3] Misc Electrical
G2 210 Battery Failure
G3 4** Clamp
G4 52* Sensor PCB:
G5 53*, 54* Main PCB
G6 85* Motor/Cam
G7 811,815,817 Misc Mechanical/Rocker
G8 821, 822, 823, 825 Load Cell
G9 110, 111, 120, 310, 413, 422, 451, 6**, 7**, 812, 813, 816, 824
User Damage
G10 999 No Problem Found
G11 90*, 91*, 92*, 93*, 94*, 95*, 96*, 97*, 98* User Setting/Usage

There is a form where these repair codes get entered. There are 3
fields, because there are only 3 expected errors. There are codes
entered in RepairCode1, RepairCode2, and RepairCode3.

The end goal is to have a report of the number of repairs made in each
of the 11 groups, by month, and what proportion that count is of the
total number of cases in the table.

First I created boolean fields (in sets of 3) for each group's
corresponding 3 repair fields (E.g.: G1boolean1, G1boolean2,
G1boolean3, G2boolean1, G2boolean2, G2boolean3, etc.). Worked great -
I have a 1 returned if the appropriate repair code meets that group's
criteria.

Then I created a calculation field, a sum of the set of 3 boolean
fields for each group (E.g.: G1booleansum, G2booleansum, etc.)

Next, I created a summary field to give a total count of each group's
booean sum.

So far so good, I can say how many repairs there were for each group.

But...
now I need to make that count into a percentage. I don't see "fraction
of a total of" as an option for my summary variable...
and I can't figure out the "get summary code...."

Does this clarify? I can't thank you enough for your time in looking
at this!
Much much thanks,
cat