Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Dec 2005
    Posts
    45

    Unanswered: Looking up values in Arrays...

    I have an array in vb, 3 columns and many rows. How can I lookup a value in Column 1 and return the corresponding value in Column 3?

    Thanks!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You have to index it ... Then report back the result ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Dec 2005
    Posts
    45
    Thanks for the reply!

    I'm not sure exactly what you mean, however. I've found references to index and seek for recordsets, but not arrays.

    Could you provide a short example?

    Thanks again!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    You have to index it ... Then report back the result ...
    That sounds ever so swish. I thought you have to loop through. How does that work?

    BTW - HB - you have another thread re arrays however you populate the array from a recordset. You can do these things more easily in recordsets. I know of no advantage to using arrays. There is also the recordcount property that would be an alternative answer your other thread.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2005
    Posts
    45
    From what I understand, speed would be a considerable advantage of arrays over recordsets. Is this not correct?

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by HeavyBall
    From what I understand, speed would be a considerable advantage of arrays over recordsets. Is this not correct?
    No. Not for searching and sorting.

    For iterating through an array versus a recordset, the array is faster

    For searching a recordset or sorting the recordset, the recordset is faster.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    That sounds ever so swish. I thought you have to loop through. How does that work?

    BTW - HB - you have another thread re arrays however you populate the array from a recordset. You can do these things more easily in recordsets. I know of no advantage to using arrays. There is also the recordcount property that would be an alternative answer your other thread.

    HTH
    Aw Geez Poots ... I'm mixing my metaphors here ... Indexing as related to the indice of each array element.
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Dec 2005
    Posts
    45
    Maybe being more specific will help:

    I have an table with 1000 account numbers, and each account number has between 250-260 months (@250,000 records). I need to perform dozens of calculations on each record for each account number. I have the entire table in an array, and need to isolate each account's records and process them, then move to the next account's records.

    I tried using SQL to load each accounts records into a recordset, then getrows to get them into their own array, but looping through each account this way takes to long.

    I just need to isolate certain records in the array, process them, them move on to the next group of records. Kind of like a conditional select statement for arrays.

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    there is no cunning way to do anything with an array unless you know the index of the array element you are trying to play with. there is no 'search' or 'find' or 'seek' other than manually starting at the top and visiting each array index in turn and checking if one or more of the elements at that index satisfy your criteria.

    try a loop that arranges for SQL to deliver just that subset of array elements that you want to play with in this step of your loop (e.g. all entries for a given account).

    if you can construct a pure SQL approach (no arrays), probably you should try that as well and run timing tests between the two... the pure SQL route will surprise you with it's speed big-if you can get your calcs into the SQL

    putting a mixed bunch of 250,000 records into an array and then wondering how to find the subset you want to play with seems ...unfortunate!

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Mar 2006
    Posts
    163
    HeavyBall

    What 'processing' are you actually doing?

    Perhaps if you gave an example some other approach could be suggested.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    Aw Geez Poots ... I'm mixing my metaphors here ... Indexing as related to the indice of each array element.
    Ahhh (sound of penny hitting the floor) Gotcha
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    HB - Izy and Norie are spot on.
    Gist is - row based analysis is rarely more effiecient than set based (there are exceptions but give us your business logic and let's see if this is one of them )
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Dec 2005
    Posts
    45
    Thanks for all the help!

    I am starting with a table, Acct Number, Month, and 20 more fields. Anywhere from 1 to 2000 Acct Numbers, and 240 months per Acct Number (max of 560,000 records).

    For each record, I perform about 35 calculations to finally arrive at a cash flow for each month for each Acct, then calculate an IRR from the cash flows for each loan.

    I tried to assign records to an array, Acct Number by Acct Number, but just looping through 'rs.open sql...' and 'array.getrows' for each Acct took almost a second per record, and this is without any of the calculations.

    I noticed that I can loop through a 100,000 array in no time at all, so I think calculating everything in code this way will work, I just need to calculate the first Acct's 240 records, then do IRR, then the next Acct, etc.

    Make any sense?

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    where is the data?
    is this a remote Access/JET datastore sitting on a fileserver?

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Dec 2005
    Posts
    45
    The main table is in a stand alone Access db on a network drive, and I am linking it in to the production db on my desktop, where I am performing all the calcs.

Posting Permissions

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