Solved Combining Field Values (1 Viewer)

[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

 

[Pat Hartman]

and it works however only for the record that has focus.

That is correct. If you want to update more than one row, use an update query. However, what you are doing sounds like a poorly structured schema and poor application design.

The update query will solve your immediate problem but the underlying problem with the structure will remain. Before running an update query from a form's class module, save the current record if it is dirty before running the update query to avoid update conflicts. If you know how to write code, you can use a DAO loop to read the form's recordSource and update that way. But rethinking the problem is my best advice.

 

[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?

 

[Pat Hartman]

My bank only allows me to download one year's worth of data from my checking account. They have the old dat but they don't keep it active or at least they won't give me access to it. I might be able to pay them to give me all transactions since my account opened but they probably can't do that either. They probably only keep data as long as statutory requirements specify which is most likely 7 years.

The point is that there is a right way to do certain things and a wrong way. Most of the time, it doesn't really take more effort to do it right and then you never have to worry about it again. You also don't have to solve the same problem over and over again. Once you understand that you need to store a DOB rather than an age, you would never store the age again. Even if someone gives you their age, you should calculate at that time a DOB and store it.

Pat so what is wrong with my structure?

Sounds like you are making a new database each year or a new table (even worse). Both are spreadsheet thinking. In a relational database, one year is the same as the next. Only the dates change. On January 1, 2022, you still know the balance from last year because it is right there in the table. You just have to sum it.

If you want to reduce the number of rows you need to sum, you can but only if you have a firm closing date. For example, you can allow updates to 2021 data until Jan 15 and after that NO change can be made to older data. You can add a YearEnd closing amount for Dec 31, 2021 and sum from that record to include the previous balance. This is much the same as having stock take records in an inventory application. They provide a firm, verified balance at a point in time. But, they only work if you prevent updating older data.

 

[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!

 

[Pat Hartman]

Overlaying an estimate means you have no audit trail. You can never tell if your estimates are even close to accurate. That is the way our government runs. They propose a project, estimate how much it will cost and then never think about it again. There is no accountability. No way to improve and even worse, no way to stop the gravy train once it is in motion. Not that I'm saying that this is your problem. I just don't like not being able to audit certain types of data transformations.

I don't really understand what you are doing by adding the data from one field to another and zeroing out the original. I'm pretty sure the better way to do this would be with an append query if you want to create a new row with a new date. Otherwise use an update query. In almost all circumstances a query beats a VBA loop hands down for simplicity and execution speed. You will get better help if you include before and after data samples.