Using Max with a date

[Moxioron]

Hello.

I have a query where the intent is to return the most recent Statement Closing Date by Account Identifier.

This works great

SELECT [ME - Balances and Activity at Cycle End_Updated].[Principal Bank Identifier], [ME - Balances and Activity at Cycle End_Updated].[Account Identifier], [ME - Balances and Activity at Cycle End_Updated].[Member Number], Max([ME - Balances and Activity at Cycle End_Updated].[Statement Closing Date]) AS [MaxOfStatement Closing Date]
FROM [ME - Balances and Activity at Cycle End_Updated]
GROUP BY [ME - Balances and Activity at Cycle End_Updated].[Principal Bank Identifier], [ME - Balances and Activity at Cycle End_Updated].[Account Identifier], [ME - Balances and Activity at Cycle End_Updated].[Member Number];

However, when I introduce variable data, such as a balance field where the balance is different every Statement Closing Date, I get everything.

My question is how do I get my query to return just the most recent Statement Closing Date, regardless of the variable data?

Thank you.

 

[Brianwarnock]

Having identified the records you want in this query, you can join the query back to the table on those fields and obtain the variable data that does not form part of the identification.

Brian

 

[redalert]

If it works great, exactly as you want it to work I assume, then don't change it as any changes will always produce a result that deviates from the perfect result already achieved.

By grouping on more fields you are increasing the number records in the resultant dataset and decreasing the number of records that each resultant record represents.