Solved Combining Field Values

[Samantha]

Can someone please point me in the right direction?! I can't even begin to wrap my head around how to do this.

I have a continuous form called frmWOH; on the form the two fields I want to manipulate are ScheduledValue and ScheduledRemainingValue2. Both of these are currency fields.

I'd like to add a button on the form that takes the value from ScheduledRemainingValue2 and adds it to ScheduledValue leaving ScheduledRemainingValue2 with a zero balance.

 

[Gasman]

So it would be along the lines of
Me.ScheduledValue = ScheduledValue + Me.ScheduledRemainingValue2
Me.ScheduledRemainingValue2 = 0

 

[Samantha]

So it would be along the lines of
Me.ScheduledValue = ScheduledValue + Me.ScheduledRemainingValue2
Me.ScheduledRemainingValue2 = 0

Makes complete sense - sometimes I overthink and make it way more complicated in my head.

 

[Gasman]

That should be Me.ScheduledValue in both cases, sorry

 

[Samantha]

That should be Me.ScheduledValue in both cases, sorry

No worries - I appreciate the help. Here's what I came up with very basic - and it works however only for the record that has focus. How would I get this completed for all records? My thought goes to loop which is what scared me off to begin with.

Private Sub cmdCombineValues_Click()
Response = MsgBox("This action can not be reversed. If you select ok you are combining this years value with next year." & vbNewLine & "If you would like to continue please select ok.", vbOKCancel + vbExclamation)

If Response = 1 Then
Me.ScheduledValue = Me.ScheduledValue + Me.ScheduledRemainingValue2
Me.ScheduledRemainingValue2 = 0
MsgBox "Action Completed", vbOKOnly
Else: MsgBox "Action Canceled", vbOKOnly
End If

End Sub

 

[Samantha]

However, what you are doing sounds like a poorly structured schema and poor application design.

Pat so what is wrong with my structure? My first field is this years work load and my second is next years contracted work load. Once a year when we get to Jan 1 - I want to push a button and magically move all my totals to the current year.

 

[MarkK]

If the passage of time forces you to modify your data, you have a design flaw. In a very simple example, it is the difference between storing someone's age or their date of birth. If you store their age, then every year your data goes stale without warning, and you need to run some kind of update. This is a fundamental flaw.
This is the same flaw as if you store other quantities that are not static, like inventory levels, or next years contracted work load. In a database table, a contract should have a date and a value. Then, anytime you need to understand 'next years contracted work load,' you should query the contract table for the date period in question, summing value. Never store this summarized amount anywhere, rather, recalculate it from your raw data for every request, and for any time period. Any stored summary of this data is flawed in the same way that storing someone's age is flawed.
hth

 

[arnelgp]

recalculate it from your raw data for every request, and for any time period.

this is only applicable in small db.
this is not being followed in any banking system.
each account balance is held on a field.
you don't calculate it from the historical data.
if there are millions of users and are more than 5 years loyal to this bank.
imagine, if at same time they enquire their balance...
it would take them forever, because the system is calculating 5 years of data
and the server is somewhere out in space.

 

[MarkK]

Still no jellybean arnel?

 

[Samantha]

If the passage of time forces you to modify your data, you have a design flaw. In a very simple example, it is the difference between storing someone's age or their date of birth. If you store their age, then every year your data goes stale without warning, and you need to run some kind of update. This is a fundamental flaw.
This is the same flaw as if you store other quantities that are not static, like inventory levels, or next years contracted work load. In a database table, a contract should have a date and a value. Then, anytime you need to understand 'next years contracted work load,' you should query the contract table for the date period in question, summing value. Never store this summarized amount anywhere, rather, recalculate it from your raw data for every request, and for any time period. Any stored summary of this data is flawed in the same way that storing someone's age is flawed.
hth

Hi Mark,

Thanks for your detailed explanation. I will have to put some thought into how to modify things. Although these number are only temporary in nature - I'm not recalling any of this information at any point in the future. So, I am not sure it would be worth my time to redevelop. As much as I would love to be able to immerse myself into it - I have other primary responsibilities.

Thanks again!

 

[Samantha]

Sounds like you are making a new database each year or a new table (even worse). Both are spreadsheet thinking.

Neither Pat,

tblWOH holds my PK along with job status, and a few yes/no columns, and the two currency fields. My form fills based on a qry that filters based on job status. These currency fields change constantly based on the project managers estimation of what is left to be completed. Essentially from what I gather of your advice this should be one currency field and an additional field for a date value.

While I understand and appreciate your point of view I think this would make things too cumbersome. Lets say its the last week of December and we have poor weather (construction based industry) now all of the projects that we're supposed to finish have to be changed from this year to next. I am still modifying the data on a case by case basis or programmatically changing the values. The same thing I want to do now in my opinion. Instead of changing $$$ I'm changing dates.

Actual contract values are in a separate table completely and none of these values matter when the job is completed and comes off of my active list. I never recall this remaining value data for any reason - its a temporary estimation. Like in the birthday example - birthdays don't change its always the same day. As well as the Banking Balance there are additions and subtractions that can calculate that number. I have nothing to calculate. Sure I could ask the project managers to estimate a percentage from the original contract value but that still doesn't fix whether its this years work or next years.

I will close this thread up and start searching for some loop samples and see if I can wrap my head around that part.

Thank you!