OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

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

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Billyray
OOo Enthusiast
OOo Enthusiast


Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio

PostPosted: Thu Mar 29, 2012 5:40 pm    Post subject: [SOLVED] How to sum a column in a subform? Reply with quote

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! Very Happy
_________________
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.


Last edited by Billyray on Tue Apr 03, 2012 8:07 am; edited 1 time in total
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Fri Mar 30, 2012 6:42 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Fri Mar 30, 2012 8:06 am    Post subject: Reply with quote

Quote:
I would llike to sum that column and display it in a control on the main form.

If I understand your requirement correctly, I would suggest a second sub-form linked to the main form with the same link fields as the 'details' table/grid sub-form. This extra sub-form could have a single numeric field which could be located on the form document so that it appeared to be part of the main form. The extra sub-form, however, would have an aggregate query or SQL statement as its data content: the linking fields would ensure the aggregate query is filtered to the same record-set as the 'details' sub-form.

Please come back if this suggestion merely confuses and I will try to prepare a demo file.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Mar 30, 2012 11:50 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Billyray
OOo Enthusiast
OOo Enthusiast


Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio

PostPosted: Sat Mar 31, 2012 10:25 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Mar 31, 2012 12:40 pm    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Billyray
OOo Enthusiast
OOo Enthusiast


Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio

PostPosted: Sat Mar 31, 2012 1:05 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Mon Apr 02, 2012 6:09 am    Post subject: Reply with quote

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:
Code:
SELECT "CLASS", Sum("YEAR") FROM "Table1" GROUP BY "CLASS"

The "CLASS" field/column link fileds effectively filter the record-set in this sub-form as they do for the table/grid sub-form.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu
Back to top
View user's profile Send private message
Billyray
OOo Enthusiast
OOo Enthusiast


Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio

PostPosted: Tue Apr 03, 2012 8:06 am    Post subject: Reply with quote

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. Shocked 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! Smile

I've marked this thread [SOLVED] Very Happy

BTW, thanks to Villeroy too! I was just a little to dense to get his sample THIS time. Confused
_________________
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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Apr 03, 2012 9:12 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Tue Apr 03, 2012 11:41 pm    Post subject: Reply with quote

Billyray:
Quote:
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.

At the risk of pedantry but essentially for the sake of others who may read this thread, I would stress that neither the form/sub-form links nor a table form control apply grouping or aggregation of records. A sub-form is merely a filtered record-set (filtered by the linking fields) and the table form control is merely the application of a particular format for the display of that record-set. Thus, any grouping or aggregation has to have been already applied to the data source for the sub-form and its table form control.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu
Back to top
View user's profile Send private message
Billyray
OOo Enthusiast
OOo Enthusiast


Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio

PostPosted: Wed Apr 04, 2012 4:22 am    Post subject: Reply with quote

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? Question
_________________
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.
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Wed Apr 04, 2012 10:54 am    Post subject: Reply with quote

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:
Code:
SELECT "CLASS", Sum("YEAR") FROM "Table1" GROUP BY "CLASS"

The resulting record-set comprises the whole of the source table but aggregated to a single record for each distinct value in the “CLASS” field, and this record-set can then be further filtered through the Base form/sub-form relationship. Thus each time the record pointer in the maim form is moved, just one single record is available for display in the second sub-form.

Thus at risk again of potential confusion, sub-forms in Base can be said to filter records and aggregate queries to provide a quite different method of grouping records.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu
Back to top
View user's profile Send private message
Billyray
OOo Enthusiast
OOo Enthusiast


Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio

PostPosted: Wed Apr 04, 2012 12:37 pm    Post subject: Reply with quote

Quote:
The resulting record-set comprises the whole of the source table but aggregated to a single record for each distinct value in the “CLASS” field, and this record-set can then be further filtered through the Base form/sub-form relationship. Thus each time the record pointer in the maim form is moved, just one single record is available for display in the second sub-form.


That is exactly what I discovered through my experimentation. When I selected just the field name in the subform control, I would just get the resultant first record quantity for that group's recordset. But by applying the correct SQL to the data of the Subform (which is what I was not doing at first), the subform's control now had "Sum(Quantity)" in the drop down list for the control's data.

Neat. Cool
_________________
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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group