Total Sum for a Field named Units

[mykil]

Hi Folks.. I have a report for students.

I'm trying to sum up the field named Units so that I would know how many units was earned by the student.. By the way i have 2 columns in my report

 

[PNGBill]

You add a Unbound text box control in the report footer or page footer.
The record source for this is =Sum([Units]) This is a calculated Control.

Suggest use SubUnits or some other prefix or suffix to Units. Good practice to ensure Reserved Words are not used.

The Report has Controls, either Bound or Unbound. They can be used for calculations in another control - Unbound - that is a Calculated Control

 

[mykil]

Thanks for the reply, But still it brings me an error...I have 2 columns in my report, the value of the field "units" were all the same except if the Student had failed in this Subject..The Value of units may be 1.2 or 0.. How can I Add this for several times? Thanks for the help

 

[PNGBill]

If you have a report and in the main body you have a control named Units and this fis formated for number then you should be able to have a control in the footer to Sum the records in the body.

If you want to do something not so simple then you may need to something different.

BTW you can have a Calculated Control on the body as well.
eg you refer to colums which I assume means you have a continous report with two controls. If you wanted to add these you have third and this is your each record calculated control.
=[control1]+[control2] - I think or something similar.

Then in the footer you can sum each of these and or you can sum the original two (individually ) and then add them togther with the third calculated control in the footer.

 

[mykil]

Uhmm.. Thanks for the help... I've added an unbound text box in my report's report footer and put control on it. Thus, It works pretty cool

Have a nice day ahead

 

[Galaxiom]

If you wanted to add these you have third and this is your each record calculated control.
=[control1]+[control2] - I think or something similar.

Then in the footer you can sum each of these and or you can sum the original two (individually ) and then add them togther with the third calculated control in the footer.

This needs to be clarified.

Calculated controls in the body of the Report (or a Form) cannot be summed in this way. Indeed no control in a form or report can ever be summed across all records.

The Sum function is applied to Fields in the Recordset. Although the Wizard names the bound control the same as the field it needs to be understood that they are completely different objects.

 

[PNGBill]

Thanks Galaxiom, I forgot this.

I have records in a continous form that are added for each record ie in each record you have a calculated control that adds or subtracts one Field to or from another for that record. (like in a row)
In the footer, there are sum's and the sums are added together where the sum of the added control is required. (colums added for original data and the Sums of colums added for footer row vaue)

And yes the reference is to the Record Source of the control and not the control name.

You can do quite a lot with Calculated Controls and they allow forms or reports to show more detail then the Data Source has.

 

[mykil]

Actually I've used =Sum([Units]) as the record source of the field "Units" and placed it on the page footer that's why it results an error...After a headache in this problem, I tried to placed it under the Report footer and not in the page footer.. Sorry for not understanding well the problem...

 

[Galaxiom]

And yes the reference is to the Record Source of the control and not the control name.

I've used =Sum([Units]) as the record source of the field "Units" and placed it on the page footer that's why it results an error...

You guys both need to get straight on your terminology.

Mykil has used =Sum([Units]) as the ControlSource of a Control called "Units". Controls don't have a RecordSource.

RecordSource is a Property of the Form. It is a string that defines a tablename, queryname or SQL SELECT command that generates a Recordset (also a Property of the Form). The Recordset has a Fields Collection.

The ControlSource is a Property of a Control. It can be an expression (starting with equals sign) or the name of a Field in the Recordset that makes it a bound control.

 

[mykil]

Thanks Galaxiom

 

[brendandaly]

I have a problem very similar to this. I'm putting together a report where I need the percentages that sub groups make of a whole. So like, in Group A there is sub group 1 and 2 and in Group B there are sub groups 1 and 2. What I need is what percentage of group A 1 and 2 make up. This is killing me. I've been trying to figure it out for days and I think my job may rest on it. PLEASE PLEASE help.

 

[PNGBill]

Think about your Data Source ie the SQL / Query that supplies data to the Report.

Can you have the Percentages calculated by this query or an earlier one and included in the data source query ?

While Reports and Forms do have Calculated Controls where the calculated can be done in a query it is often better to do so.

Fall back would be to create queries for the percentages and then just join these into the data source and place the data where you require it in your Report.

Sometimes it may not look "pretty" when you Run the final query to see these "totals" on each record it makes no difference as users only see the report which will just have the result displayed once.

Trust this helps and post some sql's if you need assistance.

 

[brendandaly]

I actually figured it out. I ended up being able to do it in-report by simply taking the Sum from the Group A footer, renaming it and putting it in an expression in a field in the detail. I really appreciate the help. Honestly, it friggin burns me up that I spent DAYS, literally days, going through all these data transformation processes to try to nail down group sum as a non-calculated variable so I could work with it. But I figured it out in the end at least. Thank you very much for the suggestion.