more than 255 calculated fields on report ????

[fibayne]

Hi...
I am trying to build a report for a life assurance illustration spanning 10 years with many variables building up the monthly totals and am only half way thru adding all the fields to the report and have got an error stating too many fields (over 255) by the end of the report I would need roughly 850 calculated fields to get the illustration covering 10 years
I think there must be an easier more automated way to do this in a Function perhaps ??

Initial Premium [IP] less Establishment Fee [EstFee] less IFA Fee [ICIFA] less Investment Adviser Fee [ICIA] less Withdrawal [WA] less Annual Management Charge [AMC] this is then grossed up by [GRL]3% / [GRM]4% / [GRH]5% compounded to show growth comparisons

I have attached how it works on an Excel spreadsheet would anyone have some hints on how i can do this is Access if at all ?? as always any help is greatly appreciated....cheers Fi

 

[gemma-the-husky]

some random-ish musings that occurred to me - so sorry if its all a bit disjointed

do you have a jpeg of what the finished output SHOULD look like - i guess the attachment isnt exactly the formatted result

---------------
the point is you cant (or shouldnt) say - i need 10 years data with (say) 20 bits of data in each row = 200 calculated fields - you have to let access do the work - so you need to find a way of generating this information in a 10year table (or query) so you only actually have 20 BOUND fields, (not 255+) and your report automatically reports however many rows of those 20 fields, as are in the table/query

--------------

i think you need to create a table (or an array structure) with the same fields as these column heads, then find a way of populating the table/array with the calculations.

maybe populate the rows wiith just!) the number of periods you need, then write an update query or function, to populate the table for each row.

THEN base your report on the table (perhaps the array structure is easier)

doable, but not easy and a bit laborious

-------------
alternatively generate the spreadsheet directly from access - maybe have a template with formulas already in, then just populate the dynamic fields

not sure how you print a spreadsheet from access though.

------------
doing stuff that isnt directly related to data isnt really access's thing - so you need to find a way turn it into something that access can handle with (bulk) queries - if not its awkward whatever tool you use - as I guess life offices find when they do it!

or just format the answers in excel and print them there.

 

[fibayne]

Hi Gemma...many thanks for your well described musings I am at the moment trying to join as many of the calculated fields together as poss so as to limit the number of fields needed to create the final report..will post back how I get on...again thanks for your thoughts much appreciated..cheers Fi