Update Value in table (1 Viewer)

Gismo · Apr 17, 2019

Hi All, please could you help me understand, i have this scenario and not sure whether i am approaching this correct.
I need to update the value in a control in a table by adding a value from another table to the existing value
can I do the addition in an update query form and to the same table or do i need to copy to a temporary table, do the addition and then copy back to the original table?

isladogs · Apr 17, 2019

If you can join the 2 tables in your query & the datatypes for each field are identical then you should be able to do it in the query.
Depending on whether its a number or text field, either add the new number value or concatenate the text fields

plog · Apr 17, 2019

I need to update the value in a control in a table by adding a value from another table to the existing value

Where does the initial value in the first table come from? Is it just the summation of values that exist in other tables?

Pat Hartman · Apr 18, 2019

Typically we do not store calculated values and I don't see a reason to deviate from that policy here. I would add a second column to the table to hold the additive value so that you can always distinguish it from the original value. Then in a query you would sum the two and on the form, you would have an unbound control that shows the total:

= Nz(fldA,0) + Nz(fldB,0)

Since one of the fields may be null (at least at some point during the data entry, you need to use Nz() to account for that)

Use DLookup() to obtain the additive value and place it in fldB at the point in time where the key to the value is available.

We don't have a lot of detail to work with so that is the best I can do.

PS - the reason for adding the second column is because that allows for manual override and it also allows for changes in value. So if the value to add is $10 today but tomorrow it is changed to $10.50, you want to be able to see the value at the time it was used. If the value "never" changes or you have a way to obtain the correct value at a point in time, then don't store anything. Just join to the lookup table in the query and calculate in the query.

Gismo · Apr 23, 2019

plog said:
Where does the initial value in the first table come from? Is it just the summation of values that exist in other tables?

Both values are manually entered, table one when created, table 2 would be from daily usage to accumulate hours history, after each activity, the time must be added to table 1 for total hours

Gismo · Apr 23, 2019

Pat Hartman said:
Typically we do not store calculated values and I don't see a reason to deviate from that policy here. I would add a second column to the table to hold the additive value so that you can always distinguish it from the original value. Then in a query you would sum the two and on the form, you would have an unbound control that shows the total:

= Nz(fldA,0) + Nz(fldB,0)

Since one of the fields may be null (at least at some point during the data entry, you need to use Nz() to account for that)

Use DLookup() to obtain the additive value and place it in fldB at the point in time where the key to the value is available.

We don't have a lot of detail to work with so that is the best I can do.

PS - the reason for adding the second column is because that allows for manual override and it also allows for changes in value. So if the value to add is $10 today but tomorrow it is changed to $10.50, you want to be able to see the value at the time it was used. If the value "never" changes or you have a way to obtain the correct value at a point in time, then don't store anything. Just join to the lookup table in the query and calculate in the query.

I need to store calculated value as it is my total hours accumulated. Table 1 for totals only, table 2 is the hours per activity history.

June7 · Apr 23, 2019

Except for extraordinary circumstances, aggregate data should be calculated when needed for output, not saved.

plog · Apr 23, 2019

I need to store calculated value as it is my total hours accumulated.

Nope. You need to build your database correctly. You've just created a point of failure for your data. In one year post your database and I will lay odds that not all calculated totals in table 1 equal their individual parts in table 2.

That is why when you want total hours you simply run a query that calculates it from the history table.

Update Value in table (1 Viewer)

Gismo

Registered User.

isladogs

MVP / VIP

plog

Banishment Pending

Pat Hartman

Super Moderator

Gismo

Registered User.

Gismo

Registered User.

June7

AWF VIP

plog

Banishment Pending

Similar threads

Users who are viewing this thread