Average of the number of check boxes checked

[Jennpen1]

I have a series of checkboxes on a report that read from a table. For each checkbox I have its control source as an If statment that reads if there is an "A" in the table that the checkbox will be set to 1 and if not it will be set to 0. I have a total of 8 checkboxes and each ones control source looks for a diferent letter. I then find the percentage of boxes checked. i.e. 1 out of 8 boxes is 12.5% and so on. This same procedure is done multiple times on one report, and I need to find the average of all of the percentages. The AVG function will not allow me to do this--it keeps asking me to enter the parameter values. Since I am taking the average of text boxes that are calculated fields I do not think there is easy way for me to do this. I need to find a way to find the averages ASAP. I would really appreciate any help someone could give me.

Jennpen1

 

[PauloRico]

Create another field on the report that has the Source -

=([CheckBox1]+[CheckBox2]+[CheckBox3]+[CheckBox4]+
[CheckBox5]+[CheckBox6]+[CheckBox7]+[CheckBox8])/8

Format it as Percent
Call it RowPercentage

Put another control in the report footer that has the ControlSource -

=Avg([RowPercentage])

If this doesn't work, move the first field into the underlying query.

Also, (if it works), it may return a negative number. This is because boolean values are stored as 0 or -1. Just wrap the addition of all the checkboxes in brackets and multiply by -1.

Hope this works.

Paul

 

[CosmoKramer]

You can't perform an aggregate function on a calculated control, you have to re-calculate it. So, in the report footer, to get the grand average, set the control source of the text box to something like this, of course changing to your field / query / table names:

=Sum([chkbox1]+[chkbox2]+[chkbox3]+[chkbox4]+[chkbox5]+[chkbox6]+[chkbox7]+[chkbox8])/(DCount("[chkbox1]","YourTableorQuery")*8)

This should work as long as chkbox1 always has a value (is not null). Because its in the report footer it sums up all of the check boxes for all of the records and divides that by the total record count multiplied by the number of check boxes.

Let me know if this helps.....

 

[Jennpen1]

This didn't work becasue it prompts me to enter [chkbox1]. So I put the expression that I had for chkbox1 into the sum expression, however I had to do it for all 8 checkboxes. The problem now becomes that the expression is too long. This is so very, very frustrating. Is there anything else I can do??????

 

[CosmoKramer]

Can you change the names for the check box controls to a shorter name?? What are they named now??

 

[Jennpen1]

I get past one problem and another arises. I shortened the names of the fields and it no longer says that the expression is too long. However, the report will not open. When I am in design view and try to print preview it, the program runs for a few minutes and then goes back to design view. My computer is fairly new, so I do not think it is a lack of memory. Has this ever happened to anyone before??? Any advice would be much appreciated.

Jennpen1

 

[CosmoKramer]

Can you post the code for the control source as it appears now?? Did you modify anything else, other than the field names and this control source??

 

[Jennpen1]

The only thing I modified was the control source and the field names. I replaced the old control with the new field names and everything worked fine, so I know it has nothing to do with that. Let me first explain the report a little clearer. I have 8 queries that each look for a different letter (qry1 looks for A, qry2 looks for B, etc.). In these queries there are only two fields, VehNum and VanSec (VanSec is the one that contains the letters). I then have 8 separate reports that are each based on a query. I have my main report based on a table that contains all of the Vehicle Numbers (data is in the field VanSec). The main report also contains the 8 checkboxes and the 8 subreports that are based off of the queries. The subreports are linked to the main report by the filed VehNum. The code for the checkboxes is as follows:

=IIf([Vehicle Number]=[qry1].Report![Vehicle Number],1)

Essentially what this says if the vehicle number in the subreport matches the vehicle number on the report, set the checkbox's control to 1 (in other words to a check mark). Initially I ran into a problem here because if the vehicle numbers did not match it meant that the subreport would not show up, therefore the checkbox was null. To fix this by adding a hidden text box that reads:

=IIf(IsError([Check1]),[Check1]="0",[Check1]="1&quot

I think this is where my recent problem lies. All of this is done 8 times for each check box, and then continued numerous times for each vehicle number. This was the code I was trying and the result was that absolutely nothing happens:

= SUM( IIf(IsError(IIf([VehNum]=[qry1].Report![VehNumber],1)),”0”,”1”) + IIf(IsError(IIf([VehNum]=[qry2].Report![Vehicle Num],1)),”0”,”1”) + IIf(IsError(IIf([VehNum]=[qry3].Report![VehNum],1)),”0”,”1”) + IIf(IsError(IIf([VehNum]=[qry4].Report![VehNum],1)),”0”,”1”) + IIf(IsError(IIf([VehNum]=[qry5].Report![VehNum],1)),”0”,”1”) + IIf(IsError(IIf([VehNum]=[qry6].Report![VehNum],1)),”0”,”1”) + IIf(IsError(IIf([VehNum]=[qry7].Report![VehNum],1)),”0”,”1”) + IIf(IsError(IIf([VehNum]=[qry8].Report![VehNum],1)),”0”,”1”))

I am just trying to Sum, and then I will battle with trying to find the average. I know this is all very confusing, and I do not blame you if you do not have to the time to evaluate it. Any advice that you would have would help me out so much!!!!!!!!!!

Jennpen1

 

[MichaelRed]

It is a bit late for my tiredness. Relational dbs are generally quite good at set operations. Unfortunatly, they are orinetated towards COLUMN sets for aggregation, VB does provide the building blocks to manipiulate row set aggregates - but upi need to use the blocks to build the functions. I have constructed a number of these and posted several on Tek-Tips. Unfortunatly, hte search feature is somewhat limited (crippled?) so finding them is difficult (not reliable or possible from TEK-TIPS), so I will post one more:


The table

MyId	MyVal1	MyVal2	MyVal3	MyVal4	MyVal5	MyVal6	MyVal7	MyVal8
1	Yes	No	Yes	Yes	No	Yes	No	No
2	No	Yes	Yes	Yes	No	Yes	No	Yes
3	Yes	Yes	Yes	Yes	No	No	No	No
4	Yes	Yes	No	Yes	Yes	No	Yes	Yes
5	Yes	No	No	No	No	No	No	Yes
6	No	Yes	No	Yes	No	Yes	No	No

The "function"

Public Function basRowAvg(MyVal As Variant, ParamArray MyFlds() As Variant) As Single

    'Michael Red    8/27/02
    'Return The Number of Occurances of "MyVal" in the Fields listed in MyFlds

    Dim Idx As Integer
    Dim MyCnt As Integer

    While Idx <= UBound(MyFlds)
        If (MyFlds(Idx) = MyVal) Then
            MyCnt = MyCnt + 1
        End If
        Idx = Idx + 1
    Wend

    basRowAvg = MyCnt / (UBound(MyFlds) + 1)


End Function

The Query (as an SQL string)
SELECT tblYesNo.MyId, tblYesNo.MyVal1, tblYesNo.MyVal2, tblYesNo.MyVal3, tblYesNo.MyVal4, tblYesNo.MyVal5, tblYesNo.MyVal6, tblYesNo.MyVal7, tblYesNo.MyVal8, basRowAvg(True,[MyVal1],[MyVal2],[MyVal3],[MyVal4],[MyVal5],[MyVal6],[MyVal7],[MyVal8]) AS AvgYes
FROM tblYesNo
WITH OWNERACCESS OPTION;



The Query (as a recordset)

MyId	MyVal1	MyVal2	MyVal3	MyVal4	MyVal5	MyVal6	MyVal7	MyVal8	AvgYes
1	Yes	No	Yes	Yes	No	Yes	No	No	0.5
2	No	Yes	Yes	Yes	No	Yes	No	Yes	0.625
3	Yes	Yes	Yes	Yes	No	No	No	No	0.5
4	Yes	Yes	No	Yes	Yes	No	Yes	Yes	0.75
5	Yes	No	No	No	No	No	No	Yes	0.25
6	No	Yes	No	Yes	No	Yes	No	No	0.375

This may (or not) be the exact / specific 'calculation' of interest. It is CERTAINLY not 'production quality' code, lacking most rudimentary error checking. I'm sure there are additional 'errors and ommissions' (e.g. bugs and flaws) even if the generic calc is correct.

The POINT of th posting is simply to bring the generic csapability to mind. Using a ParamArray, one may pass a variable number of fields (of a single record) to a procedure and -within the procedure- calculate any 'aggregate' value desired and return it to the calling object. You can -in a similar manner- get the Max, Min, Count or Sum of a set of fields. Other 'Aggregates' may also be derived for those with a greater knowledge of stastics.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Jennpen1]

I am not sure that this code is going to work for me. It is in the report that I assign the checkboxes either a yes(1) or a no(0). This information was not pulled from a table. However, is there some way that I can calculate a number in a report and send it back into a table????

 

[CosmoKramer]

I thought that the intent of the original post was to get the grand average; that you were already calculating the detail record average. Now, with multiple queries, subreports, etc. that definitely changes things......

 

[Jennpen1]

That is the thing...I am able to calculate the average for each record. The expression to do that is simply:

[txtAvg]=([Check1] + [Check2] + [Check3]+ [Check4] + [Check5] + [Check6] + [Check7] + [Check8])/8

I need to get the overall average of [txtaverg]. I tried
=AVG([txtAvg]) but I get a prompt to enter [Check1], [Check2], etc. Is any of this making sense???

Jennpen1

 

[CosmoKramer]

OK, then we get back to my first post in this thread. Did you try that logic in your report footer??

 

[Jennpen1]

Yes, I tried that and it does not work. It prompts me to enter [Check1], [Check2], etc. THe reason this does not work is the same reason that the AVG won't work on my [txtAvg] control. [Check1] is not a field from the table from which the report is based. It is a calcualted field.
I am beginning to think that this is just not possible.

 

[MichaelRed]

In a GENERAL sense, I tend to avoid a lot of report based calculation, and move most to the recordsource (query). This provoides more flexability in the use of the object(s) (queries) -at least in my opinion) and (partially) achieves one of my pet 'goals' - avoidance of repeating a calculation.

As for the actual / detail question, the "fields" passed to the function may be 'calculated' fields, and thus are amenable to the same 'calculation' presently in your form. The "advantage' - if it is appropiate is that the report "field" looses the 'aggregate' property and thus is quite ammenable to being (further?) aggregated in the report.

As I mentioned earlier, the 'old grey mare / martter' is a bit tired at the moment, so this is just a "possability" re the recordsource query:

Chk1: IIF(Left(Field1], 1 = "A", 1, 0)

Of course replacing field above with your actual field names, and (perhaps) changing the names of hte Chk(i]I fields to your desired names. Further, you then remove the report calculation of the check box value in the report and simply reference the calculated field name.

Finally, while I do not mean / intend it as disparaging, you really should get some further 'educational' support in both VB(A) and SQL. Having a better knowledge of these would make this exercise MUCH easier for you.

As for your last remark, it is not only possible, it really isn't very difficult. The "soloution approach' I have outlined and generally posted certainly is possible. CosmoKramer's approach is also quite "possible". I would not choose it. either for my own use or to suggest, but mostly because it requires -in my opinion- more detailed direction and implementation steps, and is generally available to more knowledgeable users w/o assistance (at least from me?).




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CosmoKramer]

Once again Michael, if we review the original direction of this thread: calculating a grand average from eight detail fields, I believe my approach certainly would, and does work. I've used a similar method quite often, and it works well.

Regarding the "more detailed direction and implementation steps" that you describe: If adding a single unbound text box to the report footer meets this criteria, then there's little hope for anyone to follow any multi-step solutions.

This is just another one of those cases where there are many ways to accomplish the same goal. Again unfortunately, you demean any other method that will also work, unless it's yours.

 

[Jennpen1]

Thank you for your help and understanding CosmoKramer. I am sorry to say that although your approach should work, it still is not. It still prompts me to enter in the values for the checkboxes. I decided to enter the control expressions for the checkboxes into your expression and, again, the report will not show in print preview. Maybe your approach is just not going to work for me. If you have run out of other ideas or suggestions, I think I will be forced to try another approach.

 

[CosmoKramer]

Jennpen1,

You're right, my approach won't work when each control is a calculated one. If I can come up with another option I will post it back here again.

Good luck

 

[MichaelRed]

At the risk of (again) offending, I weould (again) suggest moving (at least MOST) of the calculations to the report recordsource (query). Realizing that my eye sight is no longer the sharpest it has ever been, I think of the multiple compounded IIF statement as a health hazzard, so would almost certainly remove it to a module and "Dress it up a bit" for at least readability. This would also help in spotting errors. Functionally, it appears to nothing more than a counter, albeit hte use of fields from eight seperate queries is somewhat daunting. I (again) refer to the rather simple procedure previously posted as a canidate to replace the expression and (again) suggest it be placed in the QUERY -NOT- the report. I am sure there is much ado about the process / report which I do NOT understand (and probably do not want to). On the other hand, I believe the previous posts adequetly describe and illustrate a QUITE REASONABLE SOLOUTION.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places