Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2005
    Posts
    45

    Unanswered: Need help with Access design...

    I am working to accomplish a task in Access, and could use some advice concerning design. I have a table with 10 fields than can have 300,000 or more records. This table represents 360 records per account number:

    Acct---Month
    1------1
    1------2
    1------...
    1------360
    2------1
    2------2

    From the original fields, I have to calculate an additional 50 columns to arrive at the final values for each record. I am currently doing this by dividing the calculated fields into 3 separate tables, and appending to each table sequentially so that Access isn't overloaded by trying to do 40 columns of calculations for this many records.

    The problem is that is takes about 7 minutes to run 100,000 records, and I could conceivably have many more.

    I was thinking about loading each source field for each Acct into an array and doing all the calculations in vb, creating new arrays for the calculated fields. Does this sound like it would perform better?

    Thanks for any help. I know this is probably not particularily clear.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What problem are you trying to solve with this layout? I'm having a little trouble getting my head around the generic "columns" and "calculations" you're referring to.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2005
    Posts
    45
    Teddy,

    I have a source table with @ 10 columns, each is a numeric value that will be used in further calculations. I am using expressions in an append query to populate a second table based on the 10 columns in the source table.

    Most of these expressions include iif statements, some have nested iifs.

    Once I run the first append query, I now have a second table whose columns are based on the original table. This second table now becomes my source for the next append query, which populates a third table with numerous expressions.

    Excel is more appropriate, but the data originates in access, and the row count is much to great for excel. I was thinking VB might be more efficient in handling numerous calculations.

    If it would help, I could post an excel version that would give you an idea of what I am trying to duplicate in Access.

    Thanks again!

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Well, right off the bat, if you have nested iif's in your queries, that's killing your efficiency. Create a module with functions that do all of the calculating for each row and put those functions in the query. Otherwise, Access has to compile the multiple, nested functions on EVERY ROW, which doesn't hurt the average user, but obviously hurts you.

    Also, creating secondary tables is not improving your efficiency, just adding time. To the DB engine, a table is the same as a query - it's a recordset pointer to a group of data. Running a query to create a table so that you then use the table in a subsequent query is 2 extra steps you don't need.

    Beyond this, I can't get a clear picture of what you are trying to do, but as you said, it can probably be done more efficiently in code with recordsets instead of queries.

    tc

  5. #5
    Join Date
    Dec 2005
    Posts
    45
    Tcace,

    Thanks for the reply. I always felt like I needed to use code somehow. Calling functions in the queries should definitely help performance.

    Also, I can't run all 300,000 records together. I need to run 360 records for each Account Number individually. Would the best performance come from loading recordsets into arrays, then doing the calculations on the arrays?

    Thanks!

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I can't run all 300,000 records together. I need to run 360 records for each Account Number individually.
    Why?

    In any case, I'm still fuzy on what you need, but here's a shot in the dark:

    You can run a "totals" query on the main table that groups by Account - that gives you a list of accounts. Using "Distinct" will give you the same result - I'm not sure which is faster.

    Then, you have code that pulls a recordset for each account number it gets from the query (1 at a time) and peforms that calculations in code and a second recordset that provides a destination for the calculated results.

    Code:
    Set RecordSetAcct for all Acct in QueryDistinctAccounts
    Do 
        Set RecordSetData for all records where Acct = RecordSetAcct("Acct")
        Do    
            Get the data from first row
            Perform the calculations
            RecordSetData.MoveNext
        Loop Until RecordSetData.EOF
        RecordSetResults.AddNew
        Save the results to RecordSetResults
        RecordSetResults.Update
        RecordSetAcct.NoveNext
    Loop Until RecordSetAcct.EOF
    No matter what you do, you want as few functions in your queries as possible (a good target is NONE).


    btw: Opening a large recordset is just as resource intensive as opening a large query (same difference to Jet). Using recordsets is certainly faster than using domain functions and doing the calculations in code with recordsets saves the 299,999 recompiles the query has to go through.

    tc

  7. #7
    Join Date
    Dec 2005
    Posts
    45
    A couple of the calculations for each record involve values from the prior record:

    For instance, in order to get the Principal balance for each month, I need to use the Funding expense for the previous month (record). In other words, The principal balance calculation for month 3 for all Accounts is based upon the prior month's Funding Expense. The only way I know to do this is to create a query with Acct, Funding Expense and PriorMO:[Month]-1, and now I have the prior months funding expense for each month for each Acct.

    I just don't know how I would do this in VB.

    Also, I can't sum by Acct. I am generating monthly cash flows, I am finding a return on a set of cash flows. They must be calculated separately.

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    you definately want to use code then - I can see why the queries are so slow: not only are they constantly compiling functions, but they are running double, having to calculate back rows

Posting Permissions

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