[Solved] Adding a calculated field in subform

[farmkid]

I've built a database for a project at work. The main form I use for entering and viewing the data has 6 subforms in it. Each subform has 2 columns in which I enter the data: column 1 is text and column 2 is an integer. What I need is a calculated field for each subform that will display a running total of all the numbers entered in the second column.

Any ideas how to do this?

[Villeroy]

http://www.1keydata.com/sql/sql-running-totals.html

[farmkid]

Thanks, but that didn't make a lot of sense to me. I only started learning about database design about two weeks ago, so I'm still rather new at it. However, I don't think that's quite what I want, anyway. Perhaps this will help:

Here's a screenshot of my form. You can see the 6 subforms. What I want is a box at the bottom of or below each subform with the total of the numbers in the second column of that subform.

[eremmel]

You might add to each subform an other sub-subform with a query that sums up the results. I do not know when this sub-subform is updated when you add another record to the corresponding subform...

[Villeroy]

There is no way to do anything without SQL. SQL is not a programming language. It is extremely easy to learn the basics with a good tutorial and a tool like Base. Just put all the object names in double-quotes.

I tried to modify one of my example databases to demonstrate a running total on a list of integers ordered by time stamps. It leads to a bug report. About every third Base document I try to create results in a bug report.

The database is attached to http://www.openoffice.org/issues/show_bug.cgi?id=107104
The table and "Form1" demonstrate how to enter integer numbers with auto-IDs and time stamps.
I added Query1 and Form2 which demonstrates the bug.
I added Query2 and Form3 which demonstrates that it works with the ID instead of the stamp.

If an number determines the order for the running total there should be no problem. A time stamp seems to be unable to bind a subform to a main form.

[Villeroy]

I'd suggest to do running totals in Calc.
Create a query with the data in the right sort order.
Register the database (Tools>Options...Base>Databases)
Get a Calc document and drag the query from the left pane of the beamer window (F4) onto some spreadsheet cell.

Data>Define...
Select "Import 1" and hit button [More]
Check the "Insert/Remove" option and "Keep Formatting".
hit [Modify] and [OK]

Go to the first cell behind the first record not counting the column labels. I assume C2 and the values to be totalled starting in B2.
C2 =SUM($B$2:$B2)
Copy C2 down until the end of the list (double-click the tiny square in the cell's bottom-right corner).
The formula sums everything from B2 until this row's value in B.

When the database has changed you can refresh the import range.
Select a single cell or the entire import (Data>Select) before you call Data>Refresh
The formula range will grow and shrink with the import range.

[evwool]

Here's a screenshot of my form. You can see the 6 subforms. What I want is a box at the bottom of or below each subform with the total of the numbers in the second column of that subform.

I know you asked for a Running Sum, Farmkid, (which is what you get in a bank statement, where each total refers to the line the above), but this sounds as if all you want is a single total of the second column of each subform.
You don't get Calculated fields like you do in Access, but Base is much nicer about subforms
based on other subforms, so what you'd actually do is a one row/one visible column Subsubform linked to each subform, as eremmel suggested.
So to expand on his answer, since you say you are new to this (I don't know how your database is structured so my instructions will need to be adapted to suit):
Create a query which contains the 2nd column and a field which links it to the subform
eg if all the fields in subformA referred to MyClass1 and there was a field called MyClass in the table
which had a 1 in it, then put the MyClass field and the Number field in your query.
Under the Number field in the Functions row, choose Sum. Under the MyClass field choose Group By.
You should now have only one row in your query and 2 cells.
In Form Design, click the Form Navigator button, right click on your subform there and choose New Form.
Give the new form a name you'll recognise.
Base it on your query ie right click on it and go to Properties
Choose Query next to Content Type. Choose your Totals query
Next to Link Master and Link Slave choose the MyClass field.
Click the More Controls button and drag a Table control onto your form. In Form Navigator, drag this Table Control onto your new Subsubform.
Click the Add Fields button and drag the Sum field into your subsub form. In Properties, take off the navigation bar.
If you have a look at my 'Work In Progress' database, here,http://user.services.openoffice.org/en/ ... 42&t=24906, in FrmOrders you will see this in practice.
If your database is well structured, you will be able to use the same query for all your Subsubforms: if each subform is based on a different table (naughty you!) then you'll need a seperate query for each subform.

[farmkid]

Thanks. I think I understand how this is supposed to work. Now that I'm back at work, I'll give this a try.

If your database is well structured, you will be able to use the same query for all your Subsubforms: if each subform is based on a different table (naughty you!) then you'll need a seperate query for each subform.

In the mean time, though, I'm curious what you mean by this. Each subform is based on a different table, but honestly, I can't see how it would be better to do it with only one table. The subforms aren't related to each other (well, the information is related biologically, but as far as the database is concerned, they aren't) and the information between them is not dupicated. To combine them would make the database structure more complicated and create one table with lots of empty space (about 2/3 of the cells would be empty). Is that actually preferred in good database design? I ask because, as I said, I'm very new to this and everything I know I've learned by reading the help files, tutorials, and by trial and error.

[farmkid]

I was waiting to mark this as solved hoping that evwool would answer the question I asked in the last post about proper database structure. I guess not, so I'll go ahead and mark it as solved.

[evwool]

I was waiting to mark this as solved hoping that evwool would answer the question I asked in the last post about proper database structure. I guess not, so I'll go ahead and mark it as solved.

Sorry, farmkid. Busy week and a long reply needed. It looks, from the picture of your form, (though looks can be deceptive) as if you may be putting yourself in the position where you need to add a new table when you want to add a new element to your database. I say this because your form shows a series of tables which seem to have a lot of characteristics in common. If this isn't the case, then please ignore me and just tut impatiently. A typical example would be someone with a stock-keeping database who, because different items have different characteristics, has a one table for washing machines, one for cookers and one for dishwashers. When he later decides to stock microwaves, all his forms/reports etc will need to be redesigned. He also has to create elaborate union queries whenever he wants to interrelate the items and then, when he needs to add a new characteristic ,(solar-powered) the whole thing needs to be changed again. A better design would have been to have had one table listing all the ItemTypes (washing machine, dishwasher, microwave), one table linking the itemtypes with Items, eg Washing machine in the ItemType table is linked to Candy Model 3R in the Item Table.. Another table listing additional possible features (rinse cycle, automatic timer, oven capacity) and then a table which links ItemTypes with features (washing machines have rinse cycles but no oven capactiy) another linking individual items with details about their features (the Candy Model 3R Washer has 3 different rinse cycles) Link the Item table to the ItemsInStock table which has contains those characteristics that stock items are most likely to have in common (RRP, Minimum number to keep in stock, Serial Number). This is just a possible structure which will vary according to the needs I may have, but what I'll be looking out for is that, if I ever need to add a new element, be it an Item Type, an Item, a Characteristic, or a Stock Item, I just have to add a new record rather than having to add a new field or table.

[farmkid]

Thanks for the explanation. That makes sense. I'll keep that in mind for the future. I could have set this database up in that way, but I think the way I did it is probably better in this specific case. The database is for keeping track of alleles (different versions) of genes that I find in a study I'm doing (I'm a molecular biologist doing some work in genomics). There are 6 genes, and each gene has a list of possible alleles I may find, and those lists don't overlap. Some of the genes are also present in up to 10 copies in the genome, so I can have anywhere from 1 to 20 data points for a given gene. The database is set up so that there is one table for each gene that contains simply the list of possible genes. That table supplies the information for the combo boxes where I specify the found allele. Another table for each gene contains the animal identifier, found allele, and a quantitative measurement.

In my case, the number of genes I'm looking at is fixed and won't change. The only thing that will change is the list of possible alleles will grow as I find new ones. I simply add them to the list as I find them. Querying the database might be easier if I had set it up the way you described, but i don't know yet. I'm just now trying to learn how to build queries to sort the information in ways that are useful to me.

Thanks for your help.

[evwool]

I've built a database for a project at work. The main form I use for entering and viewing the data has 6 subforms in it. Each subform has 2 columns in which I enter the data: column 1 is text and column 2 is an integer. What I need is a calculated field for each subform that will display a running total of all the numbers entered in the second column.

Any ideas how to do this?