DATE FUNCTION

[Guest]

i have a field named "credit". each client has multiple credits on various
dates.

i want to create a field (function) that shows the credit amount of each
client but only at the most recent date. each client has a credit amount at
different dates.

which function do i have to use and how?

thank you in advance for your reply!!!

 

[OfficeDev18 via AccessMonster.com]

If your credits are cumulative, use

SELECT Client, Sum(Credits) As SumOfCredits FROM Table1 WHERE Client =
'YourClient' GROUP BY Client

If they aren't, use

SELECT Client, Max(CreditDateField) As MaxOfCreditDateField, Credit FROM
Table1 WHERE Client = 'YourClient' And CreditDateField = Max(CreditDateField)
GROUP BY Client, Credit

HTH