Page 3 of 3 FirstFirst 123
Results 31 to 33 of 33
  1. #31
    UTLee is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    73

    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.

  2. #32
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

  3. #33
    UTLee is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    73
    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!

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 11-04-2013, 07:08 AM
  2. Query list of clients in date range
    By scorpiogray in forum Queries
    Replies: 2
    Last Post: 05-20-2013, 01:34 PM
  3. Replies: 9
    Last Post: 02-12-2013, 03:14 PM
  4. Query to seperate active/inactive clients
    By csnyder1582 in forum Queries
    Replies: 5
    Last Post: 05-05-2011, 12:01 PM
  5. query to show gaps in a date field
    By Lockrin in forum Database Design
    Replies: 1
    Last Post: 05-28-2010, 10:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums