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
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.
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?
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.