Creating Calculated Fields in OpenOffice Base

When you make a database, you of course have it chock full of data.

Contractor name, Contractor pay rate, Number of hours the contractor worked, etc.
Invoice ID, Item price, Number of items purchased, etc.

Now, let's say you want to figure out something based that data. The pay rate times the hours to equal the total amount paid, for instance.

To do that, you make a query.

1. Create a database. (File > New > Database, select the first radio button in that window and continue.)
2. Create or add a table.  (Choose the Wizard or design view.)

<>

3. Open the database file. (File > Open and find the .odb file.)
4. Click the Queries icon at the far left.

5. Click Create Query in Design View.

6. In the window that appear, select the table that contains the fields you need to calculate and click Add.

7. The table will appear in its own separate window. Add all the fields that you want in the query -- you might all of them. To add a field, double-click it. It will appear in the design area at the bottom.

8. To add the calculation, click in the top cell of the first blank column. Type fieldname*fieldname

<>

For instance, to multiply the contents of Payrate by Hoursonjob,   type
Payrate*Hoursonjob

In the cell below that, the cell to the right of the Alias label, type the name you want to use to refer to this calculated field, like TotalPaid.

Then click the Run Query icon to run the query.

You'll see your results, with the calculation.

For any of the columns, including the result, you can apply currency formatting. Right-click on the column heading and choose Column Format.

Select the format you want and click OK.

The formatting is applied.

Now you can:

• Just view the data
• Run a report based on the query
• Create a mail merge of some sort based on the query

---

Comments

[Richard]

A really useful intro to calculated fields - thank you!

[Solveig Haugland]

Hi Richard,

Thanks! It's one of those things that's not very wizardy but does work more or less simply.

Solveig

[Chuy]

WOW!!! this was just exactly what I was looking for... REALLY apreciate.. TY

[Marion]

Is there a way to use this in a form?

[Solveig]

Hi Marion,

In principle I think it should work, create a numeric field in a form and then use the approach in this blog entry, but I haven't tried it.

[Thiemo]

i am trying the same thing with a base file connected to a calc spreadsheet. I can select columns with a normal query like "select a,b from sheet1". But when I try to do "select a, b, a-b from sheet1" the column "a"-"b" is listed, but blank. a and b are decimal variables, of course. What am I missing?

[Thiemo]

i am trying the same thing with a base file connected to a calc spreadsheet. I can select columns with a normal query like "select a,b from sheet1". But when I try to do "select a, b, a-b from sheet1" the column "a"-"b" is listed, but blank. a and b are decimal variables, of course. What am I missing?

[Thiemo]

i am trying the same thing with a base file connected to a calc spreadsheet. I can select columns with a normal query like "select a,b from sheet1". But when I try to do "select a, b, a-b from sheet1" the column "a"-"b" is listed, but blank. a and b are decimal variables, of course. What am I missing?

[Solveig]

Hi Thiemo,

I'm afraid I'm not sure right now without seeing the file. This might be more of a consulting issue.

[lussy]

i am trying the same thing with a base file connected to a calc spreadsheet. I can select columns with a normal query like "select a,b from sheet1". But when I try to do "select a, b, a-b from http://www.batterygoshop.co.uk/acer/btp-42c1.htm acer travelmate c100 c102 c104 btp-42c1 ,
sheet1" the column "a"-"b" is listed, but blank. a and b are decimal variables, of course. What am I missing?

[John]

Solveig, I appreciate the effort you put into your blog. The information you share from time to time has helped me become more efficient in my use of OOo.

I am about to hang out my own shingle (legal practice) and plan to use OOo and other open-source programs rather than closed-source applications (such as MS Office and Timeslips).

One of my first projects is to create a database that, among other things, allows me to collect and store time spent on legal matters. The tutorial above shows me how to calculate field results to obtain a billing amount, which gets me half-way (or more) to my goal. Are you aware of a way to incorporate a timer into my database that will allow me, for each legal matter, to start and stop time and then write the aggregate time for that matter to the database.

I would rather use OOo than an existing program, such as gnotime, because it appears to be much simpler to create and edit reports than it is in gnotime, which requires knowledge of HTML.

Any help you can provide will be much appreciated!

Thanks, John

[Solveig]

Hi John,

I STRONGLY recommend Harvest, it's an online system. It has a timer and I LOVE it. Not free but very affordable. It's very configurable and it integrates with Basecamp if that's something you use. It's also exportable to a spreadsheet/csv. So you could just enter hours, export it, set it up as a database, and then just keep exporting or overwriting as you need to do billing. It's not seamlessly integrated, you can't integrate with jdbc.harvest.com or anything, but the hoops you need to jump through are minimal and quick.

http://www.harvestapp.com

Not sure but it might be something you could even do your invoicing through, it integrates with quickbooks.

[John]

Thanks. I'll check it out.

[Abner Napitupulu - Indonesia]

How can you figure if the case like this...
Field1(text): 7010 Field2(text):1300 Field3(numeric):82

The result must be shown:
7010-1300-082 (field1 dash field2 dash field3(became text)

Appreciated your help. Thanks

[Used Refurbished Laptops]

The Aspire 2000 mobile series boasts a huge 15.4" panoramic display and the powerful feature set of Intel Centrino mobile technology. The wide-screen 15.4" WXGA (1280 x 800) display offers improved viewing angles - 130 degrees horizontally and 100 degrees vertically. But the Aspire 2000 is much more than a great way to watch movies while on the go. Viewing multiple files simultaneously is easy on the panoramic display, allowing you to accomplish more in less time. If a big part of your job involves working with Microsoft Excel files, you'll wonder how you ever managed with an ordinary notebook display. The wide screen and high resolution will help to make your job easier. Most remarkable, perhaps, is that the Aspire 2000 packs in so many features yet tips the scales at a modest 6.6 pounds.

[Peter Rowan]

Hi! That's a nice guide to calculating. Is it possible to create a calculated field in a table. I want to input [firstname] and [lastname] separately, concatenate them to [name] and then link that to another table. Is that possible?

Peter

[Solveig]

Hi Peter,

I think anything is possible in SQL.... ;> and of course there is the SQL view but I couldn't tell you how to do that.

Solveig

[Peter Hughes]

Hi I am 72 y/o and have found this the best site for an old feller to learn open office

[Solveig]

My dad got his first computer at 83 and worked his way through some of my materials on his own--one of my proudest moments!

[Esko]

How do you make a query work within a form ? I haven't been able to google anything useful to help me. I have three numeric fields to enter, and after the last one is entered, the query should fire off and both display the result and update a fourth field in the same table.

[Dennis]

How do you total the column "TotalPaid"?

[Club Penguin Cheats]

I was very pleased to find this site.I wanted to thank you for this great read!! I definitely enjoying every little bit of it and I have you bookmarked to check out new stuff you post.

[Kit]

I couldn't find anything about calculated fields in the OpenOffice help guide (I'm sure I just wasn't using the right keywords). I should have figured it was pretty much the same as Access. A quick search on Google and your site came up first. I liked the screen shots and the straightforward instructions. It took me less than a minute and now I'm off and running. Thanks!

[Gavin Katts]

Hi! I have set up a database, complete with tables, forms and queries for calculated fields.
Everything works fine, UNTIL I save a copy as an .odt file to open up in Writer. What happens is the formatted field is blank when the document is opened in writer. When I export to .pdf the total is displayed in the formatted field. This would be fine but the amount of data and means that I would have to open it up in writer to set the proper spacing so that one record is not split over two pages. Please help

[Solveig]

Hi Gavin,

I just tested and saving a copy to .odt works fine for me; the calculated amount is displayed. Did you save the report as a Dynamic report when you created it?

You might also try just creating the report by choosing View > Data Sources, finding the data source and query. Look at the data in the right-hand pane and click and hold down on the gray upper left corner box to the left of the heading row. Drag into the Writer document and you'll get the option to drag in as fields text or table; choose Fields.