+ Reply to Thread
Results 1 to 2 of 2

Calculating An Access Field in Excel - But With a Twist

  1. #1
    Registered User
    Join Date
    03-12-2021
    Location
    London, England
    MS-Off Ver
    16
    Posts
    6

    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
    Attached Files Attached Files

  2. #2
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    274

    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.
    If it'd make you feel better using my answer because of my street cred, then you can go ahead and click Add Reputation below to improve it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Create New Field in MS Access using Excel VBA
    By sunjam in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-11-2019, 10:09 AM
  2. [SOLVED] write to access from excel using a range instead of per field name
    By Megatronixs in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-21-2014, 06:14 AM
  3. Calculating overtime with a twist
    By PyhaWaim in forum Excel General
    Replies: 2
    Last Post: 03-28-2014, 03:30 AM
  4. Calculating between dates w/ twist
    By SLMehta in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-06-2012, 02:22 PM
  5. Replies: 9
    Last Post: 05-22-2012, 08:59 AM
  6. Using ADO in Excel VBA-Search for Field in Access Database
    By JStoops in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-29-2010, 04:11 AM
  7. Excel ADO-Bad Field in Access
    By Al in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-10-2005, 08:06 PM

Bookmarks

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