Query To Show First Date for all Clients

[rpeare]

I'm not sure it will reliably fix the problem but you could use:



GroupDate: MIN(Datepart("yyyy", Transdate) & right("0" & datepart("m", transdate), 2))

[UTLee]

geez...still not right. If I use GroupDate: MIN(Datepart("yyyy", Transdate) & right("0" & datepart("m", transdate), 2)) and remove transdate from the query, I only get 2 records...have no idea why this would be.

However, I did discover this: if I remove the 0 and >0 from the criteria of the BegBal and EndingBal fields, I get the correct number of results, 381 but now the BegBal, NetContributions, NetInvestmentChange, and EndingBal field of the query are way off because they are literraly now summing all of the clients intervals and showing that sum in the 1 record...

[UTLee]

And I may not have made this clear but the tblAccountHistory table consists of the client's account history at the account level. So, the reason I was using Sum of BegBal, NetContributions, NetInvestmentChange, and EndingBal was to take the account history and sum them to show the monthly interval for the total portfolio.

[June7]

So these two records have TransDate values in different months? If you are grouping by month, these two records will be generated. If not the month, something else about the grouping criteria is causing these two records to be unique.

Perhaps use the grouping query as source for another query that uses TOP N qualifier?

[rpeare]

let's take a very basic example.

You have these two tables:

tblAccountHistory
ClientID  TransDate  BegBal  NetContributions  NetInvestmentChange  EndingBal
1         1/1/2014   0       100               2                    102
1         1/7/2014   0       200               5                    205
1         6/1/2014   0       500               9                    509
1         6/9/2014   0       400               7                    407
tblClients
Client  ClientName  Cycle
1       Client1     1
2       Client2     2

Have I got the structure right?

You want your end result to be a single record for client ID 1 where the beginning balance is the balance on the EARLIEST record of the month, the sum of the contributions, the sum of the net investment changes and the ending balance of the LATEST record of the month

in this case you'd want

ClientID  ServMonth  BegBal  NetCont  NetChange  EndBal
1         201401     0       300      7          307

If so then what you really want, instead of the SUM of the starting balance you want the FIRST RECORD of the month having a starting balance of 0
Similarly, you want the ENDING balance of the LAST record to be greater than 0

Is that correct? If this is the case you are using a non normalized structure and I'm guess a table with calculated fields which is making your process way harder than it needs to be. It can still be done with your structure it's just clunkier.

[rpeare]

EDIT: I don't know under what conditions the BEGBAL is populated, for instance if in my example the Ending balance from the first record is carried forward to the second record so the starting balance on that second record is 102 which would then force the ending balance on the second line to be 307.

[UTLee]

rpeare, you're exactly right in your example with the ending balance carrying over to the next month's beginning balance

[rpeare]

give me an example of how your data actually appears. Don't give me real data, just the fields we've been talking about, an example that has more than one record for a given month for 2 months in a row

[UTLee]

Sorry, not exactly right. I'll post an example shortly

[UTLee]

I created an example in Excel, how can I post here as text and get all of the columns to line up?

[rpeare]

just upload the excel file

[UTLee]

it made me do zip file

[UTLee]

In the attached excel file above, the example shows two clients each with 3 accounts and assume that they are both Cycle 1 (managed clients). I need the query to find the first date in which a client had an ending balance > 0, sum all of the accounts for this date and return 1 record showing that date and the sum of BegBal, NetContributions, NetInvChg, and EndingBal. So the bottom of the excel file shows what I want. The query I made does this almost 100% correct except one of our clients has two instances where they had a 0 beginning balance and then an ending balance > 0 (they took all of their money out 1 month and then put it all back in a few months later). So it shows this client twice in the query which I don't want. Just the earliest date is what I want in that case.

Your version of tblClients was accurate by the way.

[rpeare]

Is the TRANSDATE in your actual data always the last day of the month?
if a person withdraws money from an account is that recorded as a negative dollar amount in net contributions?

Assume for the sake of argument that the data set in your spreadsheet is all for the same client you would just want this:

ClientID	TransDate	BegBal	NetContributions	NetInvestmentChange	EndingBal
Client1	01/31/14	0	260,000	10,000	270,000

As your resulting set?
In other words at some point they withdrew all their money then started up again.

[UTLee]

Yes, TransDate is always the last day of the month and withdrawals from client accounts are recorded as negatives in the NetContribution field.

Also correct, if the data in my spreadsheet were all one client instead of two, then I would want the query to only show the sum of BegBal, NetContributions, NetInvestmentChange, and EndingBal for the first month in which the client had 0 BegBal and > 0 EndingBal. So in my spreadsheet, if I change those monthly intervals to all belong to Client1, I would want the query to return the 6/30/13 interval showing the sum of the 3 accounts for that month

See the example I attached, Book3 this time.