# Calculating An Access Field in Excel - But With a Twist

1. ## Calculating An Access Field in Excel - But With a Twist

Hi there,

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.
A  Register To Reply

2. ## Re: Calculating An Access Field in Excel - But With a Twist

I don't understand what you're trying to do.

From the sample files it looks like you have an Access table: [Example_Input](ID,GroupHash,HNItem,RNBatch,Measure,MeasureDate,ModelName)

Where you export to Excel: GroupHash,HNItem,Measure one GroupHash at a time

So that you can compute the HNItem's percentage of the group: HNItem,Measure / Sum(Measure over GroupHash) as MeasureDerived

This however does not explain where the Monte Carlo simulation is involved, nor why you wouldn't just use a sequence of Access queries instead of an Excel workbook...

Query1 ``Please Login or Register  to view this content.``
Query2 ``Please Login or Register  to view this content.``  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1