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

 

[AOGFADUGBA]

Thanks Sheetspread

 

[AOGFADUGBA]

Thanks, but i want to have a button on the form to run the make/append query command.

 

[sheetspread]

Table:
mysql - Create a table from a query in access VBA - Stack Overflow

Append (But I think you want Update, which replaces records rather than possibly repeating existing ones):
sql - VBA Append Query Using Access - Stack Overflow

 

[Micron]

Just so you know, it's not good practice to store calculated values - they are usually derived in forms and reports by performing calculations on stored values. If a table field value changes for any reason, the stored calculation is wrong. Having said that, you do not need to change the query to a make table, update or append type if it will mess up anything you're doing. You would use the query you have as a table for a new query. As a make table, you will have to deal with prompts you will get when it needs to over-write it, so it may not be the best solution. I avoid them (beyond using once to create a table so I don't have to do it manually in design mode) but not after that. It can contribute to database bloat and should a design change in your query or an underlying factor gets altered, resulting in zero records, you will lose everything you had. I think the update or append query is a better approach.

 

[AOGFADUGBA]

Hi Guys. thanks for your great input. i now have a form that distribute the lumpsum to the various department. i created an append table query so that anytime the lumpsum is entered into the form the ******* command button runs the append table query that distributes the amount based on the percentage in the percentage table. i can then display the result on a report. Another issue is that all payment will be made based on their department, is it possible to add all the Amount that belongs to the same department in the update table?
i tried creating another query from the update table and used (DSum("[Amount]","[Dept_RENUE_UPDATE]","[Dept]='" & [Dept] & "'")) it sums amount in the same department though, but i still have 110 records instead of 55. 110 records because the lumpsum was added twice for the 55 departments.
any idea? Thanks

 

[Micron]

Sounds like your second query is an append query, thus the second set of records. Or does the update table contain 110 records? If so, maybe there is something wrong with the first update query. The info you provide (one field with an expression) is not really enough to help much. If you post the sql statements for both of the 'update' queries, it might provide some clue. We might need the table names/field names as well. I for one don't know why you can't just add the department amount during the first update, but again, not enough info for me.

 

[AOGFADUGBA]

Thanks. this is the sql statement for the append query INSERT INTO GHANAAIEOVERHEADREVENUEUPDATE ( SNO, [Date], HEAD, RSUBHEAD, CODE, PARTICULARS, BALANCE, Expr1 )SELECT GHANAAIEOVERHEAD.SNO, GHANAAIEOVERHEAD.Date, GHANAAIEOVERHEAD.HEAD, GHANAAIEOVERHEAD.SUBHEAD, GHANAAIEOVERHEAD.CODE, GHANAAIEOVERHEAD.PARTICULARS, GHANAAIEOVERHEAD.BALANCE, [forms]![allocationdistribution].[amount]*[pct] AS Expr1
FROM GHANAAIEOVERHEADperct LEFT JOIN GHANAAIEOVERHEAD ON GHANAAIEOVERHEADperct.SNO = GHANAAIEOVERHEAD.SNO;

i created a table called GHANAAIEOVERHEADREVENUEUPDATE so the update table query adds 55 records it each time it is executed.
if you remember i mentioned 55 departments earlier. Hence anytime it is executed 55 new records is added to theGHANAAIEOVERHEADREVENUEUPDATE tableso the 55 records will appear twice if the update table query is executed making a total of 110 records in table. if run 3 time it will become 3X55 records.....
so assume one department is social contributions it will appear in the table depending on the amount of times the query is executed.
what am asking is that, is it possible to have the sum of the Expr1 field in social contribution RSUBHEAD in one record instead of it appearing in three records if the update table query is executed three times.
Expr1 is the calculated fieldRSUBHEAD is the department

 

[AOGFADUGBA]

sorry its append table query and not update

 

[sheetspread]

is it possible to add all the Amount that belongs to the same department in the update table?

Yes, either with update, join, or a subquery

is it possible to have the sum of the Expr1 field in social contribution RSUBHEAD in one record instead of it appearing in three records if the update table query is executed three times.

Is the sum appearing 3 times or the sum's components?

maybe there is something wrong with the first update query

Sounds like your second query is an append query, thus the second set of records.

Those were my first thoughts too. AOGFADUGBA, are you deliberately writing an append (insert) instead of an update? Or did you consider them to be interchangeable (they're not)?

 

[Micron]

First, you must get your terminology correct.
Append query: one that ADDS records. Update: UPDATES values even if they are NULL or empty strings - "").
Adding 55 records each time it is run is the behaviour you can expect with an append query where none of the target fields are indexed or keyed. If you can, change it to an update, but (in your case, I think) you must have at least the department values in the table while any fields you want to update can be blank. So you could append the departments one time, then update the other fields by changing the query type to Update, or simply have two; one to append when needed, the other to update. You would not use the append very often - maybe just to add departments.

Or you must make at least one of the target table fields a primary key (or index) which does not allow duplicates. Then when you open the append query, you will have to deal with Access displaying the message that it cannot add all the records due to a rules violation. You can turn off warnings in a macro (be sure to turn them back on) or in code, deal with the specific error generated. The former is easier, but warnings can be left turned off by macros that fail. Leaving warnings off can be pretty much eliminated if done right.

You might have to upload a copy of your db to a file sharing service to get detailed help. I lament that I cannot open db versions higher than 2007.