Results 1 to 5 of 5
  1. #1
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    36

    Dynamic Arrays


    CONTEXT: I wrote a function that calculates recursive values in a query. To see how the function works please download the file ( fcr4.zip ) and read my step-by-step notes. When you run the query, you’ll see the query produces results that look something like this:

    table value table value query
    bga (n-1) +bmg
    weight
    density
    bga
    60 700 0.2982
    60 700 0.5985
    60 700 0.9010
    60 700 1.2056
    60 700 1.5123
    60 700 1.8213
    60 700 2.1324

    PROBLEM: The field called bga is recursive (that's why I wrote the function to calculate the values), however, there are many other values I also calculated in the function that I would also like to post to a column in the query (when you read my notes in the file you’ll see what I mean). After everything is said and done, this is what I would like the query to look like:

    table value table value query query query query query
    weight*density/1000+bga oldBm*.01 cfr*.71 bga (n-1) +bmg oldBm+bmg
    weight
    density
    oldBm
    cfr
    bmg
    bga
    newBm
    60 700 42.0000 0.4200 0.2982 0.2982 42.2982
    60 700 42.2982 0.4230 0.3003 0.5985 42.5985
    60 700 42.5985 0.4260 0.3024 0.9010 42.9010
    60 700 42.9010 0.4290 0.3046 1.2056 43.2056
    60 700 43.2056 0.4321 0.3068 1.5123 43.5123
    60 700 43.5123 0.4351 0.3089 1.8213 43.8213
    60 700 43.8213 0.4382 0.3111 2.1324 44.1324

    RECOMMENDED SOLUTION: I’ve tried a few different things, and after many hours of trial and error, I think I have an idea how to solve this problem (please refer to the attached file for more information), but I can’t seem to make it work like I was expecting…of course not…. but I think one of you pro's will at least know what I’m talking about after reading it. Any help would be appreciated. Thank you for your time.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    how does this requirement differ from the solution provided here? https://www.accessforums.net/showthr...712#post454712

    daynum wt dn bm cfr bmg bga bmcf
    1 60 700 42 0.42 0.2982 0.2982 42.2982
    2 60 700 42.2982 0.422982 0.30031722 0.5964 42.5964
    3 60 700 42.5964 0.425964 0.30243444 0.8946 42.8946
    4 60 700 42.8946 0.428946 0.30455166 1.1928 43.1928
    5 60 700 43.1928 0.431928 0.30666888 1.491 43.491
    6 60 700 43.491 0.43491 0.3087861 1.7892 43.7892
    7 60 700 43.7892 0.437892 0.31090332 2.0874 44.0874

    the only difference I can see is you are introducing rounding to the cfr value which impacts the values going forward. I am really busy with other things right now and do not have any time to progress this further, but perhaps someone can take the time to look at your requirement in detail

  3. #3
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    36
    Ajax,

    You’re solution isn’t truly recursive. Take a look at the column bga in your solution. You add .2982 to bga every time (.2982+.2982 = .5964, .5964+.2982 = .8946, 1.1928+.2982 = 1.491) – that’s linear and not exponential like this formula needs to be. I don’t need recursion to do that.

    Next, look at my table which I produced via VBA in this thread (not SQL like what you did). Notice that my bga column is slightly different than yours – it’s because it uses recursion bga(n-1)+bmg, i.e; .2982+.3003= .5985 .5985+.3024=.9010, .9010+.3046=1.2056. Although this difference may seem small for the sample size I provided, the difference gets very large over the course of several weeks. Also, correct me if I’m wrong, but I didn’t think Access had SQL recursive support like other platforms such as mySQL and Oracle. That’s why I wrote this using VBA and made it a separate post.

    Also, my function already calculates the recursive values I need for all columns (that's not what this post is about). I just need some help posting it to the query - currently, I'm using a scripting.dictionary method and am just looking for help in translating that to a dynamic multi-dimensional array (arr() as double) which I think needs to take place. I also tried to be as detailed as I could in the demo file I attached to hopefully save time.
    Last edited by neuk; 05-27-2020 at 09:22 AM.

  4. #4
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    36
    It looks like the scripting.dictionary method can assign arrays to keys via the collection method. So, I have all the columns assigned to each key (I think), but when I try and post a specific collection item it errors out… Any ideas how to approach this?

    The updated file is here:


    Last edited by neuk; 05-27-2020 at 08:37 PM.

  5. #5
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    36
    I was able to make the dictionary method work after all. I guess forget my post then.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Passing Arrays
    By VSCurtis in forum Programming
    Replies: 8
    Last Post: 04-21-2017, 12:02 PM
  2. Arrays - varinats and alternatives
    By Middlemarch in forum Programming
    Replies: 4
    Last Post: 07-27-2016, 05:04 PM
  3. Array class for working with Arrays
    By Ruegen in forum Programming
    Replies: 7
    Last Post: 03-11-2015, 10:01 PM
  4. sql query arrays
    By drewdrew in forum Programming
    Replies: 3
    Last Post: 07-03-2014, 03:35 AM
  5. passing arrays io a report
    By Seamus59 in forum Reports
    Replies: 3
    Last Post: 05-21-2013, 10:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums