Count or Sum - 1 field, multiple uses

[mdlaugh1]

I have a field called Attribute2. My report is grouped by Area (top level), then Type_of_Work (2nd level). Depending on the Area name, the usage of this field in the database differs. Here's an excerpt of my data pulled from the query:

WO# Type_of_Work Value Area
0600684 DR-SLTD Y DRAINAGE
0601765 DR-SLTD Y DRAINAGE
0601768 DR-SLTD Y DRAINAGE
0601860 DR-SLTD N DRAINAGE
0601865 DR-SLTD Y DRAINAGE
0500150 *8"PCRP 6 STREETS
0500221 *8"PCRP 5 STREETS

I need a calcs placed the Type of Work footer and Area footer to do three things:

If Area = Drainage, Count number of Y and Count number of N. -- Y = 4, N = 1

If Area <> Drainage, sum the values. -- total = 11

Last requirement, I want to suppress the field(s) if it's not relevant to the Area:

All Type_of_Work rows in Drainage Area will have 2 fields show up -- 1 for Y counts and 1 for N counts (suppress the SUM). Then non-Drainage Area groups would display only the SUM (suppress the COUNTS).

I hope I've been clear on this... thanks!!!

 

[snyperx3]

If Area = Drainage, Count number of Y and Count number of N. -- Y = 4, N = 1

Num of Y (in an unbound textbox):
=Sum(IIf([Area]="Drainage" And [Value] = "Y",1,0))

Num of N (in an unbound textbox):
=Sum(IIf([Area]="Drainage" And [Value] = "N",1,0))

If Area <> Drainage, sum the values. -- total = 11

(again unbound textbox):
=Sum(IIf([Area]<>"Drainage",1,0))

The rest i didnt understand. =]

-Pete

 

[snyperx3]

Sorry!

The last one i put should be:
=Sum(IIf([Area]<>"Drainage",[Value],0))

-Pete

 

[mdlaugh1]

Hi Pete...

explanation on the last part... On the rows inside the Drainage group I'd like to display just the Count boxes. On all other groups I'd like to display just the SUM box. I know how to suppress a field in Crystal, but can't see how to do it in Access... "VISIBLE" is either Yes or No.

to be sure i'm understanding, I will have THREE separate unbound text boxes?

**** ok, now New wrinkle.... even though the users "told" me Drainage was only ones using Y/N, I just saw a Y in the Streets group... Will your same calcs work just leaving out the "Area = ??" test, and use three boxes... one testing for and Counting up Y, one testing for and Counting up N and one "if <> Y and <> N", then SUM?? --- Which the solution to this last paragraph probably makes the "suppressing fields depending on Group" irrevelent.

thanks!!

 

[snyperx3]

leaving out the "Area = ??":
=Sum(IIf([Value] = "Y",1,0))

=Sum(IIf([Value] = "N",1,0))

the "if <> Y and <> N" part:
=Sum(IIf([Value] <> "Y" And [Value] <> "N",[Value],0))

Yes, You will have THREE seperate text boxes.

Do you still need it suppressed? We can do that.

-Pete

 

[mdlaugh1]

Yes, I would like to suppress if result = 0.

I have 3 grouping levels... Do I simply create an unbound text box with the formulas above, then copy that same box to the group footers for each level? Or do I have to "sum up the calculated values evaluated from the level above it?

thanks!

 

[snyperx3]

You can copy and paste them into each grouping level, it will give you the totals from each level.

By suppressing, i guess you mean that you just want it to be blank instead of a zero? We can attach this to the same formula. Replace the previous formulas with these:

=IIf(Sum(IIf([Value] = "Y",1,0))=0," ",Sum(IIf([Value] = "Y",1,0)))

=IIf(Sum(IIf([Value] = "N",1,0))=0," ",Sum(IIf([Value] = "N",1,0)))

=IIf(Sum(IIf([Value] <> "Y" And [Value] <> "N",[Value],0))=0," ",Sum(IIf([Value] <> "Y" And [Value] <> "N",[Value],0)))

The fields will still be there, but they wont contain anything if they are zero. You can set their backstyle properties to Transparent if you want to put one on top of another...or whatever.

Or if you have other fields that you need to be invisible as well, then we can do this in VBA code. Just let me know.

-Pete

 

[mdlaugh1]

Pete,

I currently just have the formulas on the inside (lowest level) group...(I've deleted them from the 2nd and top levels for now). I am getting the error "formula is typed incorrectly or is too complex to be evaluated....." I have copied off your exact formulas... (without the suppression).

I originally had the formulas without the SUM function as =IIf([Value] = "Y",1,0)..... which ran with no errors.

But when i copied the formula to the level 2 and 1 groups, i got 1 or 0 for every group total, not the sum for the entire group (which makes sense since I had left off the SUM function.)

Here's my formulas for level 3 which give the "typed incorrectly or too complex to be evaluated" error:

=Sum(IIf([Attribute2]="Y",1,0))

=Sum(IIf([Attribute2]="N",1,0))

=Sum(IIf([Attribute2]<>"Y" And [Attribute2]<>"N",[Attribute2],0))

 

[mdlaugh1]

ok, I deleted all 3 formulas from level 3 and added back one at a time. I get the error when I add in the 3rd formula.. the <> Y and <> N one.. It runs ok with just the 1st 2, and I copied the 1st 2 to Level 2 group and do get overall summary for all records in level 2.

Looks like there's just something with the <> Y / N one.

Once I get the error resolved I'll look at maybe suppressing...
thanks!!

 

[snyperx3]

Hmmm...we can try nesting the IIF:

=Sum(IIf([Attribute2]<>"Y",IIf([Attribute2]<>"N",[Attribute2],0),0))

-Pete

 

[mdlaugh1]

uuggh... still get the error It "looks
like it should work... Any property on the text box I need to check? The report saves without error. it's just when I go to run it I get the error. thanks!!

 

[snyperx3]

OK...why did we change variable names from Value to Attribute2? What is Value, and what is Attribute2?

And no, text box properties wont matter since we are comparing the value of the current record and not the value of the textbox.

-Pete

 

[mdlaugh1]

ok, sorry I confused the issue way back on my first post, by puting the column name as "value". the actual field in the db is "attribute2". I have been substituting attribute2 into my formulas though... and it's working great for the 1st 2.. atribute2 = Y, and attribute2 = N.

It's just the one that needs to ignore records with Y or N and sum up everything else.

You know, I just saw some records (bad data) that currently say "15 MIN" instead of just "15". will our formula just ignore those records or could that cause the error? thanks!

 

[snyperx3]

That will definately cause an error.


-Pete

 

[mdlaugh1]

can we strip off the text (min) or ignore those records someway. Stripping off would be better. Would that involve creating a calculated field in the actual base query to strip off the text..? then using the calculated field in the report

 

[snyperx3]

Stripping off the text would be rather difficult since we never know how much will be number or how much will be text. What i would suggest is validating that the value is either Y, N, or a number when it is ORIGINALLY ENTERED.

If you cant do that, then to ignore these records we would use:

=Sum(IIf([Attribute2]<>"Y" And [Attribute2]<>"N" And IsNumeric([Attribute2]),[Attribute2],0))

-Pete

 

[snyperx3]

^^ last post of the day. I will be back tomorrow.

-Pete