How can I populate a table column with data (data had been calculated / has to be calculated first) in forms?

[TheSmallest]

Hello!

Tables:
The first table (table1) looks like this:

ID	ItemName	ItemCount	TotalValue_double
0	Item_A	1	-1
1	Item_B	41	-1

"-1" means the data is invalid / not yet calculated.

The second table (table2) looks like this:

ID	ItemName	PercentagePerItem
0	Item_A	0.01
1	Item_B	0.05

Goal:
I want to create a form and insert the data, but TotalValue_double from table1 needs to be calculated first. It is beeing calculated by multiplying ItemCount from table1 with PercentagePerItem from table2.

Form:
So the form would look like this:
ItemName [ TEXTBOX ]
ItemCount [ TEXTBOX ]
TotalValue_double [ TEXTBOX ]

Desired Result:
I type in e.g. “Item_A” into the ItemName text box and any number for ItemCount, e.g. 10. The TotalValue_double should be calculated by multiplying the ItemCount from Item_A (table1) with the PercentagePerItem from Item_A (table2). It would be like this:

10*0.01 = 0.1

where 0.1 will be stored in TotalValue_double from table1. I need the TotalValue_double stored, because I’m going to retreive it later in another step and calculate something else with it.
(PercentagePerItem from table2 isn’t going to change / the value stays the same. It’s like a constant.)

SQL to calculate and display the result in a form
After testing and researching for quite a long time, I figured out how to calculate the result I need using a query and store the result in a column that I create using this query. Every time when I enter the data in the form and save it (leaving out TotalValue_double) the query calculates the result and shows it. But the value is never actually stored in table1 where it belongs.

SQL Code:

SELECT
	"table1"."ID",
	"table1"."ItemName",
	"table1"."ItemCount",
	[ItemCount]*[PercentagePerItem] AS "TotalValue_double"
FROM "table1", "table2"
WHERE "table1"."ItemName" = "table2"."ItemName"

Please help me in calculating the TotalValue_double and storing it in the table. Also, I don’t necessarily need to see the calculated value in the form. It just needs to be calculated correctly and be stored in the table.
Also, since I can’t seem to understand macros it would be great if a “no macro solution” exists.

Thank you for your time!

[TheSmallest]

I cannot edit my post anymore so I’ll add it in this comment:
I created a file (populate_table.odb (21.4 KB)) that shows you what I mean.

table1 and table2
Have same structures and contents as those in my question above.

Query1
Just contains the SQL code that I posted in my question above.

Forms: Query1 and table1
Basically what I described in my answer above in “Form”.
Query1 shows TotalValue_double which is automatically calculated by the query / SQL code I created.
table1 serves to enter data, since I can’t insert data if I use a query.

The Problem
As you can see the query calculates the TotalValue_double but it isn’t stored in table1. How do I change that and actually store this value in table1 or at least any table, so that I can reuse those values later?

If you need anything else from me in order to help me, please tell me what you need!

[RobertG]

This will be the code for update all fields in “table1” to get the calculated value:

UPDATE "table1" AS "a" 
SET "TotalValue_double" = 
( SELECT "ItemCount" * "PercentagePerItem" 
FROM "table1", "table2" 
WHERE "table1"."ItemName" = "table2"."ItemName" 
AND "table1"."ID" = "a"."ID" )

You could also execute this command for the current value of “table1”.“ID” by macro when changing data in the table through a form.

But: Most people will say: “Never save a calculated value in a database. What would happen if on of the factors of calculation will be changed? Data will be corrupted.”

[TheSmallest]

@RobertG

UPDATE "table1" AS "a" 
SET "TotalValue_double" = 
( SELECT "ItemCount" * "PercentagePerItem" 
FROM "table1", "table2" 
WHERE "table1"."ItemName" = "table2"."ItemName" 
AND "table1"."ID" = "a"."ID" )

Indeed that works! Why do we need the table as “a” and where does “a”.“ID” come from? Is it automatically created / copied from table1?

@RobertG

You could also execute this command for the current value of “table1”.“ID” by macro when changing data in the table through a form.

I tried quite a while to do that, but for some reason I couldn’t figure it out. However, after numerous tries I finally got a macro solution to work, that updates the target tables column. I used the following macro with one one my several dozen test files:

SUB CalcNums(oEvent AS OBJECT)
DIM oForm AS OBJECT
DIM oField AS OBJECT
DIM oField2 AS OBJECT
oField = oEvent.Source.Model
oForm = oField.Parent
oField2 = oForm.getByName("UpdateMe")

IF oForm.getByName("Item").getCurrentValue() = "Item_A" OR oForm.getByName("Item").getCurrentValue() = "Item_C" THEN
    oField2.BoundField.UpdateDouble(oForm.getByName("Amount").getCurrentValue * 3.5)
ELSEIF oForm.getByName("Item").getCurrentValue() = "Item_B" OR oForm.getByName("Item").getCurrentValue() = "Item_D" THEN
    oField2.BoundField.UpdateDouble(oForm.getByName("Amount").getCurrentValue * 0.5)
ELSE
    oField2.BoundField.UpdateDouble(-1)
END IF
END SUB

@RobertG

But: Most people will say: “Never save a calculated value in a database. What would happen if on of the factors of calculation will be changed? Data will be corrupted.”

Yes, maybe. In my specific use case this shouldn’t be true, I don’t think. But to be honest I just wouldn’t know how to do it without storing the value. Besides: Isn’t it usually better to store values instead of recalculating them over and over?

I’ll mark your comment as an answer, since you indeed answered my question. Thank you for your time, I really appreciate it!

[RobertG]

@TheSmallest

Why do we need the table as “a” and where does “a”.“ID” come from?

The subquery needs to know the value from “table1”.“ID” where the update should happen. It is a correlated subquery. The specific value could be found by setting an alias to “table1”, so I could use “table1” two times and the subquery will know “a”.“ID” is value from the outer Update code.