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