Someone more clever than I may be able to do this a bit better but:
First Create this query:
Code:
SELECT tblAccountHistory.ClientID, tblAccountHistory.TransDate
FROM tblAccountHistory
GROUP BY tblAccountHistory.ClientID, tblAccountHistory.TransDate
HAVING (((Sum(tblAccountHistory.BegBal))=0) AND ((Sum(tblAccountHistory.EndingBal))<>0))
Name this query Qry_II_Base (initial investment base), this will be a list of all clients where the beginning balance was 0 and the ending balance was non-zero and all the months where that was true.
Second create this query:
Code:
SELECT Qry_Base.ClientID, Min(Qry_Base.TransDate) AS FirstTrans
FROM Qry_Base
GROUP BY Qry_Base.ClientID
Name this query Qry_II_FirstMonth (initial investment first month), this will pick out the first investment month for each client where their starting balance was 0 and their ending balance was non-zero.
Third create this query:
Code:
SELECT Qry_II_FirstMonth.ClientID, Qry_II_FirstMonth.FirstTrans, Sum(tblAccountHistory.BegBal) AS BB, Sum(tblAccountHistory.NetContributions) AS NC, Sum(tblAccountHistory.NetInvestmentChange) AS NIC, Sum(tblAccountHistory.EndingBal) AS EB
FROM Qry_II_FirstMonth LEFT JOIN tblAccountHistory ON (Qry_II_FirstMonth.FirstTrans = tblAccountHistory.TransDate) AND (Qry_II_FirstMonth.ClientID = tblAccountHistory.ClientID)
GROUP BY Qry_II_FirstMonth.ClientID, Qry_II_FirstMonth.FirstTrans
I named this query Qry_II_Final but it doesn't matter what you name it. If you modify the prequeries (their names) you'll have to modify all the queries that use that prequery as well, so just be aware.
I want to mention something else I think it was June7 who mentioned the TOP X functionality. You can definitely use that as well and it is probably a cleaner solution (in other words you may be able to compress this into one query) but frankly I do not know if it's any more efficient, plus I prefer to break these queries out into component issues because raw SQL code is not my strong point so if something breaks it's easier for me to find. if you want to try and look into compressing this into one SQL query you can try these as resources:
http://www.techonthenet.com/access/q...esults2007.php
http://www.dbforums.com/microsoft-ac...-category.html
Just keep in mind that the TOP X function requires your data to be sorted in a specific order every single time to function the way you want, in this case you'd sort by clientID then by transdate or it might cease to give you the results you expect.