Hi there,
Not 100% sure if this is the right area, but I have a bit of a weird one - I think. Basically I have a query in Access that outputs some values, which I then need to calculate by subgroup in Excel, 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 a 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.
A
Not 100% sure if this is the right area, but I have a bit of a weird one - I think. Basically I have a query in Access that outputs some values, which I then need to calculate by subgroup in Excel, 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 a 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.
A