[Solved] Calculate sum of subform column

[wienerschnitzel]

I was trying to add a formatted field below a subform which would show the sum of a certain column of that subform.

Similar to the sumproduct example in this post:
http://user.services.openoffice.org/en/ ... 28&p=63918

The idea is that the summation should only be done using the records from the subform that match the record in the mainform via a common key.

I created a query according to the sample in the above link and tried to use it in the subform's form properties, but sofar it isn't working yet. I'm having trouble establishing the master and slave link.

[Villeroy]

You do not sum up form values unless you write a program to do this. You sum up database values. You can attach more than one subform to a main form. You can attach subforms to subforms.

http://user.services.openoffice.org/en/ ... hp?id=2950
The example shows vertical aggregation results (sum, cound, min, max,...) for every combination of categories in the main form (A_A, A_B, B_C,...).

The availlability of aggregations and groups depends on the database type. The dBase driver supports COUNT(*) only.

[wienerschnitzel]

You do not sum up form values unless you write a program to do this. You sum up database values. You can attach more than one subform to a main form. You can attach subforms to subforms.

Yes, you are right about the form values, it was pretty late and I wasn't as sharp anymore. What I meant is the summing of the database values. I'm using Base as a front end to an mysql db.

http://user.services.openoffice.org/en/ ... hp?id=2950
The example shows vertical aggregation results (sum, cound, min, max,...) for every combination of categories in the main form (A_A, A_B, B_C,...).

That's the example I was referring to in my initial post. I analyzed it and was trying to use the same approach in my db but I got stuck.

I should have been more clear to my problem - the better the question, the better the answer

I have a table called Unit (with primary key UnitID) which has a 1-to-n relationship to a table transaction. The transaction table contains some fields that I'd like to sum up. So a unit can have multiple of those transaction records.

My main form contains a subform of the transaction table in table view and I can successfully navigate through all units and the transactions show correctly for each unit.

The query I created similar to the one used in your example did work, i.e. it showed the total of that particular column, but of course it did this for all units.

I'm not sure if I correctly configured the field (that will show the sum), which I added to a new subform using form navigator.

I will provide some screenshots if all this isn't clear enough yet.

[Villeroy]

Look at the subform's data-properties. It gets the contents from "Query1" which is a synonym for:

Code: Select all

SELECT "Category1", "Category2", 
SUM( "Value" ) AS "Sum", 
COUNT( "Value" ) AS "Count", 
MIN( "Value" ) AS "Minimum", 
MAX( "Value" ) AS "Maximum", 
AVG( CAST ( "Value" AS FLOAT ) ) AS "Average" 
FROM "Data" 
GROUP BY "Category1", "Category2" 
ORDER BY "Sum" DESC

Load that query and table "Data".
"Category1" and "Category2" are grouped. This means that every combination of categories constitutes one row and the aggregates (SUM & Co) are built for each of them.

Back to the subform, there are 2 properties "Link slave fields" and "Link master fields". Push one of the small [...] buttons near the link-properties. The tiny wizard should explain best what it is about. It is set up so that every pair of "Category1" and "Category2" (table "Data") in the master form shows all matching "Category1" and "Category2" of the subform (Query1"). Since "Category1" and "Category2" are unique pairs (GROUP BY), you always get a single result for any pair of "Category1" and "Category2".

Re: Calculate sum of subform column

I believe (I did not try) you can show all the subform-records related to the main form and show the sum of those records at the same time when you create a second subform bound to a sum-query, grouped by the same fields that are used for the link to the main form. In other words: The main form has one subform showing all the records related to the currently selected main record and a second subform where the sum is shown for the corresponding grouped field(s).

[wienerschnitzel]

I believe (I did not try) you can show all the subform-records related to the main form and show the sum of those records at the same time when you create a second subform bound to a sum-query, grouped by the same fields that are used for the link to the main form. In other words: The main form has one subform showing all the records related to the currently selected main record and a second subform where the sum is shown for the corresponding grouped field(s).

Yes, that's what I was trying to achieve.

mainfrm unit
subform transactions
subform sums

I tried to add another subform at the mainfrm level and followed the approach as per your example. Obviously, I must have gone wrong somewhere. What steps could I take to check the correctness?

The query I created (for use in the subform sums) gives me:

Code: Select all

unit      sum of col
  1              32
  2              56
  3              21
etc.

which means that the query is working correctly. It is just that I have to figure out how to the subform to sum only the ones related to the current record in the main form.

[Villeroy]

Your form is bound to some recordset with many "units" and numbers, your subform is bound to something like SELECT "unit",SUM("numbers")AS "sum of col" FROM "table".
Are you shure that your subform is a subform? Is it a child of the main form? If so, it's data-properties looks slightly different than those of the main form. It should have the 2 properties "Link slave fields" and "Link master fields" with small push-buttons [...]? Push anyone of the 2 [...]-buttons and link the corresponding "unit" fields of both recordsets. When you select a record with "unit"=1 in the main form, the corresponding unit 1 of the subform is shown with it's "sum of col"=32.

[wienerschnitzel]

Your form is bound to some recordset with many "units" and numbers, your subform is bound to something like SELECT "unit",SUM("numbers")AS "sum of col" FROM "table".
Are you shure that your subform is a subform? Is it a child of the main form? If so, it's data-properties looks slightly different than those of the main form. It should have the 2 properties "Link slave fields" and "Link master fields" with small push-buttons [...]? Push anyone of the 2 [...]-buttons and link the corresponding "unit" fields of both recordsets. When you select a record with "unit"=1 in the main form, the corresponding unit 1 of the subform is shown with it's "sum of col"=32.

Here is the query for the subform and its result:

Here are the form properties for the transaction and the 'sum' subform:

Here is the form navigator:

I get an error: 'The data content could not be loaded. Column 'UnitID' in where clause is ambiguous'
the SQL statement is:
SELECT SUM( `transaction`.`Rent` ), `unit`.`UnitID` FROM `snap`.`transaction` AS `transaction`, `snap`.`unit` AS `unit` WHERE ( `transaction`.`UnitID` = `unit`.`UnitID` ) AND ( ( `UnitID` = :link_from_UnitID ) ) GROUP BY `unit`.`UnitID`

The query is different than when I run it by itself:
SELECT SUM( `transaction`.`Rent` ), `unit`.`UnitID` FROM `snap`.`transaction` AS `transaction`, `snap`.`unit` AS `unit` WHERE `transaction`.`UnitID` = `unit`.`UnitID` GROUP BY `unit`.`UnitID`

[Villeroy]

"Column 'UnitID' in where clause is ambiguous'"

`transaction`.`UnitID` = `unit`.`UnitID` ) AND ( ( ???`UnitID` = :link_from_UnitID )

[wienerschnitzel]

`transaction`.`UnitID` = `unit`.`UnitID` ) AND ( ( ???`UnitID` = :link_from_UnitID )

Yes, that's what I get when the error pops up - there is an option to show the sql that caused the error.
Is there another way to show the sql as the subform sees it?

[Villeroy]

Since (`transaction`.`UnitID` = `unit`.`UnitID` ) ...
... I'd think that both ...
( `transaction`.`UnitID` = :link_from_UnitID )
... as well as ...
( `unit`.`UnitID` = :link_from_UnitID )
... should solve the ambiguous-name-problem equivalently.

[wienerschnitzel]

Since (`transaction`.`UnitID` = `unit`.`UnitID` ) ...
... I'd think that both ...
( `transaction`.`UnitID` = :link_from_UnitID )
... as well as ...
( `unit`.`UnitID` = :link_from_UnitID )
... should solve the ambiguous-name-problem equivalently.

Where can I access the sql for the subform's query? This isn't the same query as Query_snap.transaction. It must be query that gets generated on the fly - I looked in the subform's form properties but there is no option to look at the sql directly.

[Villeroy]

Get the form navigator from the 5th button of toolbar "Form Design". It shows the hierarchy of all forms, subforms and their controls. Get the properties of the subform, tab "Data". It can be bound to a table a query or a query stored with the subform. In the latter case you can call the query-designer from the small [...] button, in the second case you access the query in the queries container, in the first case,... no, it must be some query.

[wienerschnitzel]

Get the form navigator from the 5th button of toolbar "Form Design". It shows the hierarchy of all forms, subforms and their controls. Get the properties of the subform, tab "Data". It can be bound to a table a query or a query stored with the subform. In the latter case you can call the query-designer from the small [...] button, in the second case you access the query in the queries container, in the first case,... no, it must be some query.

Look at the earlier screen shot of the two form properties, the one with the query, the ellipses are grayed out. I did however tried all this in a simple database with just two tables and I got it to work just fine. I don't have access to the other database until tonight and I will just start the subform from scratch. I might not have chosen the correct data source for the data field in the sum control (going by memory here).

[wienerschnitzel]

I double checked the query and the table. For some reason it just doesn't like the two UnitID fields being the same, even though I have tried it with a simpler database (using Base's built in db) with just two tables and there it works. Could it have anything to do with the fact that I'm using mysql as the backend?

There doesn't seem to be a way for me to manually change the sql since the ellipses next to the Query are always grayed out.

I tried to fool it by using an alias for one of the UnitID fields, now I get the following error when I open the mainform:

The SQL command leading to this error is:

SELECT `transaction`.`UnitID`, SUM( `transaction`.`Rent` ), `unit`.`UnitID` AS `testing` FROM `snap`.`transaction` AS `transaction`, `snap`.`unit` AS `unit` WHERE ( `transaction`.`UnitID` = `unit`.`UnitID` ) AND ( ( `testing` = :link_from_UnitID ) ) GROUP BY `transaction`.`UnitID`

Perhaps I should rename one of the fields in the mysql database - it was never a problem in the original MS Access database though.

[Villeroy]

Remove all confident data (or all data) and attach it.

[wienerschnitzel]

Remove all confident data (or all data) and attach it.

but it is in a msql database.

[wienerschnitzel]

Hi Villeroy,

I bit the bullet and renamed the field in one of the tables and migrated the database again. I actually didn't know that the field names can be different when creating the relationships - you learn something every day.

The sum control is now working as expected.

Thanks for your effort and patience though - I did learn a lot about Base in the meantime, which is a good thing.

[adfast]

Encountered the same issue with a query summing timestamps.
As I discovered this thread while looking for a solution, here is another workaround.

I did not want to update the name of a column that was used at several places so I created a "wrapper" query, grabbing all columns from my table and giving an alias to the ambiguous column.
Then I used that query in my subform query.
Might not be the best cpu wise but works like a charm.