How to populate table with data from another table?

[Josh330]

Hi everyone. I have been spending hours trying to find the answer to this but I just cannot. Here's the situation:



I have a table let's call Clients. In that table I have a numeric field called Investment. I want to be able to inflate the Investment by a certain percentage determined by the user - let's call this field PercentIncrease. I want to set it up so that the user only needs to input the PercentIncrease once on a form, not for each record. Then there would be a PercentIncrease field in the Clients table which would update all records based on the user's input on the form. I have set up a separate table called Percent (which would only have one record based on the user's input on the form). Then I have tried to form a relationship between the tables, but the Clients table does not automatically populate.

Thanks!

Josh

[June7]

Would need to run VBA procedure to automate modifying data in table. Changing data is risky. Since the original value is replaced there is no audit trail to substantiate the change. Would be possible to accidentally repeat the adjustment and distort data.

[Josh330]

The original value is not important in this instance. Is there another solution? I don't necessarily need the PercentageIncrease field in the Clients table. I just need the ability to have a field (maybe in a query which I can use for a report) which stores PercentageIncrease x Investment for each record.

[June7]

Sure. Save value in a table. Join tables in query. Calculate.

Same percentage applied to all clients? Then don't even have to save to table. Query or textbox can reference textbox on form to pull percentage value and calculate. Or form sets value of a TempVar variable (that would require code) and query or textbox refers to TempVar for input of percentage value into calculation.

[Josh330]

No. There are actually two (maybe to be more) PercentageIncreases fields. Only one is applied to a client depending on the client information. What do you suggest is the best way? Thanks!

[June7]

Input to table. Client table or elsewhere.

Automation requires code. The more 'user-friendly', the more code.