Not 100% sure if this is the right area, but I have a bit of a strange one - I think. Basically I have a query in Access that outputs some values, which I then need to calculate in Excel (by subgroup), and return the output result to the relevant record in Access. (My possibly naive) thinking is that I'd save the query into a temp table, then update that).
I realise that it's possible to run Excel calculations from Access, but I don't believe it's possible in this instance as the calculation is the result of a 100K row Monte Carlo simulation in Excel that's not using the normal distribution. (Unlike the sample, which is just calculating an average). It's also why the sample spreadsheet is setup horizontally rather than vertically.
So from a logical viewpoint, it is copying all the values in a subgroup, calculating the value for each in Excel, then adding the resultant value back to Access.
Attached are a demo DB and Excel file. The DB has two tables, the intent to highlight what before (Example_Input) and after (Example_Result) look like, as well as show the subgrouping within.
I'd be interested in any suggestions on how to approach this. My coding skills are poor , but I know enough to do some clunky editing on existing code.
Thanks in advance for looking.