[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
MAIGUA10000
General User

Joined: 06 Aug 2006
Posts: 6

 Posted: Sun Aug 06, 2006 6:33 am    Post subject: CALCULATED FIELDS? 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!
Sliderule
Super User

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

Posted: Sun Aug 06, 2006 7:54 am    Post subject:

MAIGUA10000:

 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
mrstone
Newbie

Joined: 13 Aug 2006
Posts: 1

 Posted: Sun Aug 13, 2006 4:15 pm    Post subject: Calculated Fields 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.
MAIGUA10000
General User

Joined: 06 Aug 2006
Posts: 6

 Posted: Thu Aug 17, 2006 6:44 am    Post subject: THANKS VERY MUCH THIS HAS SOLVED MY PROBLEM
brite1
Newbie

Joined: 17 Aug 2006
Posts: 4

 Posted: Thu Aug 17, 2006 8:14 pm    Post subject: 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.
audionuma
OOo Enthusiast

Joined: 23 Feb 2006
Posts: 126
Location: France

Posted: Thu Aug 17, 2006 9:24 pm    Post subject:

 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.
DrewJensen
Super User

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

Posted: Fri Aug 18, 2006 4:19 am    Post subject:

 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.
brite1
Newbie

Joined: 17 Aug 2006
Posts: 4

 Posted: Sun Aug 20, 2006 8:06 am    Post subject: 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.
sergiopereira
Newbie

Joined: 24 Mar 2006
Posts: 3
Location: Portugal

 Posted: Wed Aug 23, 2006 1:00 pm    Post subject: spreadsheets & base 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? _________________Thanx
(R. E.) Bruce Martin
General User

Joined: 04 Feb 2006
Posts: 43

 Posted: Tue Jun 05, 2007 7:34 pm    Post subject: Thanks. Dear Sliderule: Thanks, it rates better than a sliderule! What other math functions can be used there? Can we use verbs from Calc? _________________Time Zone: Eastern. (Same as New York City)
Sliderule
Super User

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

Posted: Tue Jun 05, 2007 8:47 pm    Post subject:

Bruce Martin:

 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
DrewJensen
Super User

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

 Posted: Wed Jun 06, 2007 6:36 am    Post subject: 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/
Sliderule
Super User

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

 Posted: Wed Jun 06, 2007 9:08 am    Post subject: 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
DrewJensen
Super User

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

 Posted: Wed Jun 06, 2007 9:59 am    Post subject: now if I could only spell_________________Blog - http://baseanswers.spaces.live.com/
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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