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

CALCULATED FIELDS?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
MAIGUA10000
General User
General User


Joined: 06 Aug 2006
Posts: 6

PostPosted: Sun Aug 06, 2006 6:33 am    Post subject: CALCULATED FIELDS? Reply with quote

THIS QUESTION IS FOUND IN SEVERAL PLACES IN THE FORUM.
HOW DO YOU RETURN THE PRODUCT OF TWO NUMBERS FROM DIFFERENT FIELDS IN A DATABASE. I SEE THAT EVERY TIME THIS IS ASKED, THE PERSON ASKING IS DIRECTED TO AN OLD ANSWER TO THE QUESTION. THE PROBLEM IS THAT THE ANSWER IS A WRITTEN IN A PNG, THAT IS NOT SHOWING UP. I CAN'T SEE THE ANSWER.
Please help. The question is how do you create a field that displays the product of data from other fields.
I just want to multiply numbers in the database.
help! help!
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sun Aug 06, 2006 7:54 am    Post subject: Reply with quote

MAIGUA10000:

You asked,
Quote:
HOW DO YOU RETURN THE PRODUCT OF TWO NUMBERS FROM DIFFERENT FIELDS IN A DATABASE.


Perhaps, this graphic from a Base Query will help. In this example, the database Table name is: test_02, with three fields ( columns ):
    ID as the Index
    Cost as a decimal
    Units an integer ( number of units sold)

In this case, with the QUERY, I wanted it ( the query ) to multiply the Cost by the number of Units. Therefore, you can see . . . I entered on the Field line . . . "Cost" * "Units" . . . and . . . added a descriptive Alias ( a name for the calculated column ) of Total Due.



For your information . . . the SQL ( if needed ) would look like this:

Code:
SELECT
   "ID",
   "Cost",
   "Units",
   "Cost" * "Units" AS "Total Due"
FROM "test_02" "test_02"


I hope this helps . . . please be sure to let me / us know if this helps.

Sliderule
Back to top
View user's profile Send private message
mrstone
Newbie
Newbie


Joined: 13 Aug 2006
Posts: 1

PostPosted: Sun Aug 13, 2006 4:15 pm    Post subject: Calculated Fields Reply with quote

As a newby, I too have struggled to sort the problem of putting calculated fields in a form.
I found this link useful http://sheepdogguides.com/fdb/fdb1calcf1.htm
The trick is to create the calcuated fields in the query BEFORE you create a form.
The query should include ALL the fields from your table which you subsequently want in your form.
THEN create your form from the query using the wizard.
Back to top
View user's profile Send private message
MAIGUA10000
General User
General User


Joined: 06 Aug 2006
Posts: 6

PostPosted: Thu Aug 17, 2006 6:44 am    Post subject: THANKS VERY MUCH Reply with quote

THIS HAS SOLVED MY PROBLEM
Back to top
View user's profile Send private message
brite1
Newbie
Newbie


Joined: 17 Aug 2006
Posts: 4

PostPosted: Thu Aug 17, 2006 8:14 pm    Post subject: Reply with quote

I am a newbie and am stuck on the same issue. I tried the solution presented here but have two problems:

1 The calculated field created in the query does not appear in the database table, which is where I need it.

2. The form appears as a read only document and am not able to enter data into it.

Please assist if I missed something or need further guidance.
Back to top
View user's profile Send private message
audionuma
OOo Enthusiast
OOo Enthusiast


Joined: 23 Feb 2006
Posts: 126
Location: France

PostPosted: Thu Aug 17, 2006 9:24 pm    Post subject: Reply with quote

Quote:
1 The calculated field created in the query does not appear in the database table, which is where I need it.

you have to create a view from the query and use it as the source table. The calculated field will in fact not be written into the database, it will be calculated each time you run the query.

Quote:
2. The form appears as a read only document and am not able to enter data into it.

the 'readonly' string on the form window title only means that you are using the form and not editing it. It doesn't prevent you to edit data in the table. If you really can't enter data, there might be several reasons, the most common one being that there's no primary key on the table. An other reason might be (depending which rdbms you're using) that you have no right to alter the table.
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Fri Aug 18, 2006 4:19 am    Post subject: Reply with quote

brite1 wrote:
I am a newbie and am stuck on the same issue. I tried the solution presented here but have two problems:

1 The calculated field created in the query does not appear in the database table, which is where I need it.



Perhaps you are meaning a value calculated from two controls "fields" on a form. If so then you might look at this thread http://www.oooforum.org/forum/viewtopic.phtml?t=23217

audionuma, I see, answered the second question.
Back to top
View user's profile Send private message Send e-mail Visit poster's website
brite1
Newbie
Newbie


Joined: 17 Aug 2006
Posts: 4

PostPosted: Sun Aug 20, 2006 8:06 am    Post subject: Reply with quote

Regarding the referenced thread http://www.oooforum.org/forum/viewtopic.phtml?t=23217, I am lost on your instructions for creating a macro for calculating table field results on a form. I am not familiar with "form controls" and assigning "names" to them. OO.org Help indicates that "form controls" are interactive devices such as buttons and combo boxes. I could not find a reference for assigning names to them. I need step by step instructions or references.

Regarding the sample macro in your post, are expressions such as "Calc1Update" or "dbfrm" only specific to your particular database? Not being familiar with the programming language, I am uncertain which of the terms I should transpose and which to substitute with my own database names.
Back to top
View user's profile Send private message
sergiopereira
Newbie
Newbie


Joined: 24 Mar 2006
Posts: 3
Location: Portugal

PostPosted: Wed Aug 23, 2006 1:00 pm    Post subject: spreadsheets & base Reply with quote

I am trying to creat an invoicing & client datebase.

Mostly I have always used spreedsheets, most of my data is in them. So I save them as dbase and imported them into Base.

When I try to do the calcultion you illustrate, (which works when I create a completly new DB in Base), I get no results?!

Could this be because I am using a Dbase file?

I also am not able to assign a Primary Key to this dbase DB? Rolling Eyes
_________________
Thanx
Back to top
View user's profile Send private message Send e-mail
(R. E.) Bruce Martin
General User
General User


Joined: 04 Feb 2006
Posts: 43
Location: Near Montreal, Quebec, Canada

PostPosted: Tue Jun 05, 2007 7:34 pm    Post subject: Thanks. Reply with quote

Dear Sliderule:

Thanks, it rates better than a sliderule!

What other math functions can be used there? Can we use verbs from Calc? Idea
_________________
Time Zone: Eastern. (Same as New York City)
Back to top
View user's profile Send private message MSN Messenger
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Jun 05, 2007 8:47 pm    Post subject: Reply with quote

Bruce Martin:

You asked
Bruce Martin wrote:
What other math functions can be used there?

The list of "Built-In Functions" ( these are from HSQL - the database used ) is documented here:

http://www.hsqldb.org/doc/guide/ch09.html#N124AF

Try them, and, enjoy.

Sliderule
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Wed Jun 06, 2007 6:36 am    Post subject: Reply with quote

Three is also a spreadsheet that lists these and I have noted which of those require that the designer be turned off, and the statement run direct to the engine.

http://www.paintedfrogeramics.com/OpenOffice//hsql_functions.ods

It also notes the one or two functions that raise an error within Base.
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Jun 06, 2007 9:08 am    Post subject: Reply with quote

Just a quick note, to DrewJensen's post above.

As of this writing ( date and time ) . . . the spreadsheet link above should be:

http://www.paintedfrogceramics.com/OpenOffice//hsql_functions.ods

Thanks Drew for the valuable information.

Sliderule
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Wed Jun 06, 2007 9:59 am    Post subject: Reply with quote

now if I could only spell
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
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