auto distribute figures into form

[AOGFADUGBA]

Hi Again,
am trying to distribute an amount into several field in a form at once. is there a way to go about it.
This is the senerio. i have a form bound to a table with 55 fields all currency/number fields and i want to create a unbound text box to input the lumpsum amount into and it will distribute it based on percentage into the 55 fields on the form. is it possible?
thanks

 

[sheetspread]

If you want unbound, then you can use VBA to print the field totals in each box. Or have calculated controls with quotients of dsum formulas.

 

[AOGFADUGBA]

Hi thanks for your response. but am a bit new to vba coding, can you please take me through how it will work. using the two options you listed.
thanks a lot.

 

[sheetspread]

After reading your original post again, I'm thinking it would probably be better to write a query from a single-value table whose source is a form. The query will include formulas in each column that takes a portion of the lump sum. You can then leave the table as is or print it to another table (i.e. a make table query). Do you really need 55 fields if they're all currency? Why not just have a single field that you can pivot for presentation purposes if so desired? (Maybe it's not that easy but worth considering).

 

[AOGFADUGBA]

once again thanks for the prompt response. The issue is that each fields in the form represent lets call it a department and each department handle the deductions (payments) concerning it. These deductions(payments) are entered in another form bound to another table. so instead of entering the revenue table for each individual department one after the other i figure that there should be a way to input the lumpsum for it to be distributed into individual departments based on a percentage for each 55 departments.
is there a way to go about this?
thanks

 

[sheetspread]

Does each department have a different percentage, or are they all the same?

 

[AOGFADUGBA]

Each department have different percentage which will be entered in a percentage unbound text box on the form.
Thanks

 

[sheetspread]

Ok, then why not have the form bound to a table where the percentages can be stored, write a query to join the percentages to each department, and multiply in a separate field? That's why it's better to just have a single Department field you can pivot than repeat fields numerous times (a database faux pas) but what I've described will work with either method.

 

[AOGFADUGBA]

thanks so much for your reply. can you put me thru achieving this. The department table has four fields. Department (text field), sub department (text field), department code (number field) and balance (currency field).
the lumpsum should be distributed in to the balance field of each department record. can you please help?
Thanks

 

[sheetspread]

Create a table called percentages, with each department in one field, each percentage in another. Next create a query called distribution, where you inner join the percentages table to the department table (easiest method is in design view, choose "show table" from the menu above, then drag the department from one into the department of the other), and add another field that multiplies balance by percentage.

https://www.youtube.com/watch?v=qceAxjRciXI is a good demo of joins in Access