Query To Show First Date for all Clients

[UTLee]

I would not want it to show the next time (1/31/14 in book3) the accounts had 0 beg bal and end bal > 0. I only want this query to show 1 record per client, the earliest date the accounts had assets flow into them.

[rpeare]

Someone more clever than I may be able to do this a bit better but:

First Create this query:

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:

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:

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.

[UTLee]

Yep, that worked. I took your second query (Qry_II_FirstMonth) and added tblAccountHistory to it and summed BegBal, NetContributions, NetInvestmentChange, and EndingBalance just to see if I could reduce the number of queries required to produce the data and it worked perfect, gave the correct number of clients and the correct sums of their first intervals. So I think I don't need the third one. Thanks again for all of your help!