I am very much a beginner and could use some help. I am building a photography related database and would like to include some calculations in the main table. Here is an example including 4 fields. The first two fields are input to the table and I’d like the second two fields to be calculated with values returned to the table.
Cell B1 has 25 entered
Cell B2 has 180 entered
3a) =B1*B2
3b) =B3/60/60/24 format cell as Time [HH]:MM:SS
Thanks. I’m a real newbie… How do I input this to Libre Base?
Ahh sorry. I saw Calc in the tag and assumed it referred to Calc. I don’t use Base so can’t help.
You could try it in SQL in different ways:
SELECT "Number", "Length", "Number" * "Length" AS "Duration" FROM "Tablename"
You will get the result in seconds.
Next way is to do the same as in Calc:
SELECT "Number", "Length",
"Number" * "Length" *1.0000000000/(60*60*24) AS "Duration" FROM "Tablename"
You will get the duration as a decimal value. Note you have to multiplicate with something like 1.0000000000 to get enough decimal places. Without this you won’t get any decimal places and the duration will be 0. You could use this value in a formatted field, formatted as time.
And there is another way to get the “time” as string. This is nice to see, but you couldn’t calculate with this time any more.
SELECT "Number", "Length",
"Number" * "Length" / 3600 AS "Hours",
MOD("Number" * "Length", 3600) / 60 AS "Minutes",
MOD(MOD("Number" * "Length", 3600), 60) AS "Seconds",
FROM "Tablename"
This query shows the hours, minutes and seconds in different columns. You could concatenate this with ||':'|| to a string, which shows the time as text. But you have to set 2 leading ‘0’ for the minutes and cut the string after 2 characters from the right. If you won’t do you will get something like 8:1:0, which isn’t really what you want.
As it was not mentioned: For databases you usually avoid to store calculated fields. So @RobertG gave a solution, where you calculate the derived values “on the fly” in a select statement.
.
To use this in a form you may need to refresh/reload values, and your form needs the query as source of data, not the table.
.
If you really wish to write to the table you could use an UPDATE command. But for Base you would need either a macro or manual input at the sql-command window.