Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2010
    Posts
    2

    Unanswered: Alternative to DSum

    Hello,

    I'm quite new to Access (just started today really) so I might be missing something quite simple. Anyway: I have a massive amount of data which I need to filter and summarize and filter according to some summaries.

    Basic setup of the table is the following:

    ENTITY DATE (month) TCAP ADJODIV

    Overall, I have ~5 million rows in the database, which is a bit of a pain.

    For every entity I have observations for every month 1975 - 2005. What I am trying to achieve:
    1) Return only entities which have TCAP>0 at 31/01/1985
    AND
    2) Return the sum of ADJODIV for period 1975 - 1985 for those entities.


    To achieve this I created the following query:

    Field Date TCAP TotDiv: DSum("ADJODIV","times","[Date] <#31/01/1985#" And "[ENTITY]=" & [ENTITY])
    Criteria #31/01/1985# >0

    Once I try to run the query, I believe that Access freezes (I will try to run this through the night and see if it actually produces anything).

    Thus, is there a faster method to do this? Or perhaps I am making some mistakes?

    Thank you very much for your help, much appreciated.

  2. #2
    Join Date
    Dec 2010
    Posts
    2
    OK, I might have nailed it:

    SELECT A.ENTITY, A.[DATE], A.TCAP, Last(A.TCAP) AS LastOfTCAP, A.ADJODIV, Sum(B.ADJODIV) as RunningSum
    FROM times AS A LEFT JOIN times AS B
    ON A.ENTITY = B.ENTITY
    AND A.[DATE] > B.[DATE]
    GROUP BY A.ENTITY, A.[DATE], A.TCAP, A.TICKERE, A.ADJODIV
    HAVING (((A.Date)=#1/31/1985#) AND ((Last(A.TCAP))>0));


    I'm not exactly sure how does this work though. Could anyone suggest me some SQL reading that would be useful in understanding how to do stuff in Access?

    Thanks

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    This development technique has helped me over the years when dealing with a huge amount of data. Import a few records that fall inside and outside of your criteria into a new database and do your query development there. This will give you instant results as you run and test your query, and will allow you to know in advance what the results should be (because you've hand-picked a few records).
    Run the successful query in your large database, and do not consider the task completed until you have done further testing and know that your working query gives accurate results from the 5 million rows of data.
    Jerry

Posting Permissions

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