Query To Show First Date for all Clients

[UTLee]

Need some help with a query. I'm trying to show the first date for each client where beginning balance = 0, ending balance > 0. The query below works for the most part because almost all clients only have 1 record where balance = 0 and ending balance > 0. However, I noticed that one client has 2 records where this occurred and so I only want to show the earliest date in this situation.



Here's what I have so far and I'm sure there is a much more efficient way of doing this but I have very limited SQL knowledge:

SELECT tblAccountHistory.ClientID, tblAccountHistory.TransDate, Sum(tblAccountHistory.BegBal) AS SumOfBegBal, Sum(tblAccountHistory.NetContributions) AS SumOfNetContributions, Sum(tblAccountHistory.NetInvestmentChange) AS SumOfNetInvestmentChange, Sum(tblAccountHistory.EndingBal) AS SumOfEndingBal
FROM tblAccountHistory INNER JOIN tblClients ON tblAccountHistory.ClientID = tblClients.client
GROUP BY tblAccountHistory.ClientID, tblAccountHistory.TransDate, tblClients.cycle
HAVING (((Sum(tblAccountHistory.BegBal))=0) AND ((Sum(tblAccountHistory.EndingBal))>0) AND ((tblClients.cycle)=1));

Thanks in advance

[June7]

Try the Min function.

[UTLee]

The only way I know how to use the Min function is in query design. So I selected min under the TransDate field and it only showed 2 records (there should be 381)

[June7]

You did this in the GROUP BY query you posted?

If you want to still group by TransDate, will have to apply Min function to a copy of the TransDate field in the query grid.

[UTLee]

Yes, I did this to the query above in the design mode. I selected Min under the TransDate field but that didn't work. How do I "apply Min function to a copy of the TransDate field in the query grid"? Could you elaborate?

Thanks

[June7]

Just drag the TransDate field into the grid again and use Min function on one and Group By on the other.

[UTLee]

Ok, did that but it didn't change the results of the original query. Its like it didn't have any effect on the query. I still see 382 records and there should only be 381 (the same particular client is still showing up twice).

[June7]

That's because TransDate is included in the GROUP BY. But you said if it's not included in the GROUP BY there are only 2 records?

[UTLee]

yea, If I have only one TransDate field in the query and I select Min instead of Group By then I only get 2 records

[June7]

That's odd. There are only 2 clients in tblAccountHistory?

[UTLee]

no there are thousands of records in tblAccountHistory - it holds our clients' account history (beginning balance, end balance, etc on a monthly basis) for every account they have. So the query takes all of those monthly account intervals and sums them together at the client level so that I only see 1 aggregate interval per month for each client instead of seeing their individual account intervals.

[rpeare]

If you're trying to group your data by month don't GROUP BY TRANSDATE, try creating a field that's looks at the month/year combination of the TRANSDATE like:

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

this should give you 201401 for january of 2014

[UTLee]

So I removed the TransDate field from the query and added "GroupDate: Datepart("yyyy", Transdate) & right("0" & datepart("m", transdate), 2)" to the query using the builder feature and it displays what you said it would, YYYYMM, but I'm still getting the same results as before. I'm getting 382 records when I should only be getting 381 and the reason is because one particular client has two monthly intervals where BegBal = 0 and EndBal > 0. So I want the query to only display the earliest interval for each client where BegBal = 0 and EndBal > 0. The point of this query is to show each client's inception date and I'm defining inception as the first instance where BegBal = 0 and EndBal > 0.

Thanks again for everyone's help.

[rpeare]

how are you identifying the inception record, is that the CYCLE field? or are you doing it strictly based on date?

if your GROUP BY clause is CLIENTID, the month calculation and the CYCLE the CYCLE must be the item that's forcing the second record. Are those cycles incremental? Can you reliably determine which is the 'first' record by either choosing the minimum or maximum value for a specific client in that field?

[UTLee]

Sorry, Cycle indicates whether the Client is a managed or non-managed client. Basically, the cycle field is either the number 1 or 2. 1 means this is a managed client, 2 means this is a non-managed client so I don't want the non-managed clients in the query. I'm trying to identify the inception date strictly based on date.