[Solved] Calculated fields in Database

[Sailor455]

I am so sorry if this has been asked before but I can not find what i am looking for.
Have a simple Database With fields like sales tax and Premium paid , total cost of item, ( they are simple math calculations based on amounts in other fields) I would like to set them up to automatically calculate the correct amount.

But can not find out where to enter the formuala or how to set up the field.

Example. Sales tax= (Item cost x Qty) x 7.5%

Can this be done in an existing field in the table or do I need to have a field created in a form? Every thing i find is how to do it in a spreadsheet which is not how i want to keep the records.

Again very sorry sure it was asked but any help would be greatly appreciated.

[Villeroy]

Create a new query in SQL view.

Code: Select all

SELECT *, "Item cost" * "Qty" * 0.075 AS "Sales tax" FROM "table_name"

where "Item cost" and "Qty" are actual column names and "table_name" is the actual name of the table.
This select all fields (*) from the specified table plus a calculated column labeled "Sales tax".
You can use the query in the same way as you can use the table. The calculated field is not editable.

[Sailor455]

thanks ran a few test and think i see what i need to do... in the query view create the code for every field i want to be calculated. A lot different then the spreadsheet but get the idea for now.
Thanks again for the help greatly appreciated.

[Villeroy]

This is easier to edit in SQL view without query designer.
Unlike spreadsheets, the calculation applies to all rows of a field.
You can be sure that the field works because all data types are correctly typed.
You can enforce that a field can not have empty values.
In case of an empty value, any calculation returns an empty value instead of error.
COALESCE("field", 1) returns 1 if "field" is empty.
Decimal fractions: 5/3 = 1 integer because both input values are integer.
5/3.00 = 1.67 because we use an explicit 2-digit value as divisor.

[Sailor455]

getting a syntax error even if i just substitute my field names with those in the statement. not sure why worked and now it will not.

SELECT *, "Item Cost" * "Qty" * 0.075 AS "Sales Tax" From "Inv"
errors
SQL Status: HY000
Error code: 1000

Syntax error in SQL expression

will not let me save it.

[Sliderule]

Use the following Query:

Code: Select all

SELECT 
   "Inv".*, 
   "Item Cost" * "Qty" * 0.075 AS "Sales Tax" 
FROM "Inv"

Explanation:

1. Since your SELECT clause returns more than just the contents of each column in the table, the SQL ( Structured Query Language ) requirement is to include the table name before the * ( "Inv".*, ).
2. You might want to consider the following . . . which adds a ROUND function to have the "Sales Tax" rounded to two decimal places.
   

   Code: Select all

   SELECT 
      "Inv".*, 
      ROUND("Item Cost" * "Qty" * 0.075, 2) AS "Sales Tax" 
   FROM "Inv"
   

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

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[Sailor455]

yes that will help also found other mistake.
Question, to do multi able Calculated fields they all must be in one string Correct?
Does SQL Look at "( )" and "[ ]" like a math formula will? working from the inside out?

With your help i have managed to make a statement to create four Calculated Fields. Thanks..

[Sliderule]

Question, to do multi able Calculated fields they all must be in one string Correct?

I do not understand your question, perhaps, the trailing comma after the ALIAS . . . where the Alias is the name of the new column in the Result Set, means, you could add another column.

Does SQL Look at "( )" and "[ ]" like a math formula will? working from the inside out?

You may use parentheses to indicate which operations you want to perform first. Without parentheses, as with other computer arithmetic operations, multiplication and division are performed prior to addition and subtraction.

Sliderule

[Sailor455]

thanks again,,
looks like have a long statement to write as about 6 calculated columns

[Villeroy]

But you have to define this statements only once for all time which makes a huge difference to spreadsheets where you are constantly moving around formula cells.