[SOLVED] How to sum a column in a subform?

[Billyray]

I have a form with a details subform. In the subform one of the columns (datasheet style sub-form) is a column of numeric integers. I would llike to sum that column and display it in a control on the main form. Any help appreciated!
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003.

[RPG]

Hello

Maybe this can help you.

For each sum control you must make one more subform for displaying a value

Romke
_________________
OOo 3.4.5 on openSUSE 12.1
Use this forum : http://user.services.openoffice.org/en/forum

[Arineckaig]

[Villeroy]

http://user.services.openoffice.org/en/forum/download/file.php?id=11663 (ignore the macro warning. No macros are used)
The main form collects filter criteria in a filter table.
The first subform shows the editable row sets based on the filter criteria.
The second subform is not linked through common fields. It reads its criteria from the same filter table. Being a subform, it refreshes automatically with its parent.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[Billyray]

Thanks EVERYONE for the great suggestions. I'm checking out Villeroy's sample right now. It appears to be just what I need to do. I will check back in with the results....
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003.

[Villeroy]

Found another one where the calculated form is bound through a common ID so the calculation is related to the currently selected item:
http://user.services.openoffice.org/en/forum/download/file.php?id=8641
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[Billyray]

Arineckaig:

I've tried your idea, and the control on the main form is actually in a new subform that links to the main form in the same way that the data sheet connects. If I select the field name Quantity for its data, it puts the first correct quantity (as seen in the data sheet subform) as I move from record to record, the correct new Quantity shows.

But I've tried every way I can think of to say SUM(Quantity) even using the query builder and copying its SQL to the data content, but I have not been able to find the correct way to specify SUM for that control. (It's a text box BTW.) It always is blank if I don't specify just the field name.
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003.

[Arineckaig]

Many apologies for the delay in my reply but I have been away for a few days and did not see your post. Suitable methods are fully shown in Villeroy's two examples together with much other valuable information. I dare, however, to offer a grossly oversimplified and utterly meaningless demo in a form document I have added to a file that can be downloaded from http://db.tt/4UcEMM4. You will find in that .odb file a form document "Sum of years by Class" which displays the meaningless sum (not the Count) of the years of students in each of three classes: the years are integers and thus the same method could just as easily be used to total a more relevant column of data.

You will see that the data content for the second ("Totals") sub-form is the SQL:

[Billyray]

A Thousand thanks Arineckaig! Your sample clarified everything! I knew Villeroy's sample was showing exactly what I wanted to do, but the sample form was just too complex for me. I wasn't sure how much of it was necessary, and how much was just his way of doing forms. For example I didn't know if I needed to base my form on externally created queries the way his form was set up.

Your sample, on the other hand, made me realize right away where I was making my mistake. I had set up the subform correctly in the Form Designer Navigator. What I was missing was that the subform needed the SQL statement to group the records. I was assuming that the Master/Slave record link would already make that grouping the way it does for the table control. But of course I don't know what the internal workings of the table control are.

I used the SQL builder and used Preview on the subform's data field, once I saw the records selected correctly for the group, (and added the sum function to the quantity field) I knew I was on the right track. Then when I went to that subform's field, the selection for SUM("quantity") was already in the drop down list in the data field! So that made things easy!

I've marked this thread [SOLVED]

BTW, thanks to Villeroy too! I was just a little to dense to get his sample THIS time.
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003.

[Villeroy]

If your SELECT ... FROM ... GROUP BY is a query or some SQL stored in the form makes no difference.
You will find queries convenient when you need the same row set in many forms, reports or as a base for other queries.
When you store it in the form you can use the same query design tool as with queries. Just press the [...] button next to the SQL input box.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[Arineckaig]

Billyray:

[Billyray]

Arineckaig:

Thanks for the explanation. But I don't understand. In my subform (datasheet style table control), which was created by the Form Wizard, I just specified the master/slave fields from both tables. (It's a one to many relationship.) At that point the subform was then showing the records "group" for each of the main form's table records. That's the same group of records I wanted to sum the quantity field for. What am I missing?
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003.

[Arineckaig]

Mea culpa. I should have taken more care over drafting my post.

It is indeed true that the same group of records is used for the record-set displayed in the sub-form and for the sum of the quantity field. A differenct route or method, however, has to be used to get those end results: hence, a need for two separate sub-forms even though both are linked to the same main data form and both use the same linkig fields. In each case the link between sub-form and its parent form has the effect of filtering the record-set available to the respective sub-forms according to the record selected in the main data form. Whenever the record pointer is moved in the main form, Base automatically refreshes the respective record-sets available to any linked sub-form to reflect the requirement that the values in the linking fields must always match. The Base help file explains that the relationship between a sub-form and its parent is similar to that of the WHERE clause in an SQL SELECT statement.

The table form control, however, has no special role in this filtering process: it is merely a device to display several records at any one time in a form. Exactly the same filtering of the sub-form record set occurs if the sub-form comprises other form controls, but of course only one record at a time would then be displayed in the sub-form.

SQL does provide the WHERE, or even the HAVING, clause to permit an aggregate query to be applied to a filtered record-set. It is not readily applicable in this case because the crucial content of the clause would have to be reset to a new value every time the record pointer in the main form is moved. It is simpler to apply the aggregate query first to all the records in the source table:

[Billyray]