Find unique rent amount and date this was first charged

[Moss100]

Hello I have a table (Accounts) with the following fields

DateDue
RentAmount

This stores rent charged each month. Every so often the rent will be changed (usuually increased).

I need to look at the table and record each unique rent value and the date of its FIRST occurance.

For example:

I may have in the table 20 records where RentAmount is 400
then 15 records where RentAmount is 500
then 25 records where RentAmount is 550

I would like prefereably a string to output as follows:

25/12/2005 - £400
25/06/2010 - £500
25/10/2020 - £550

So showing the date of first increase and rent amount.

Many thanks for any help - Mark

 

[dhookom]

This could be

SELECT Min(DateDue) as MinDate, RentAmount
FROM SomeTable
GROUP BY RentAmount

There could be an issue if the rent went up and then back down to a previous amount.

 

[SkipVought]

Hi,

Rent goes up and down.

I set up a small Structured Table with Date and Rent and then added a Chg column to display the British date - pounds sterling whenever the Rent <> Previous Rent value.
Then used the UNIQUE() function to capture the unique values in the Chg column.
Caveat: the UNIQUE() function is only available in Microsoft 365
The Formula Bar displays the formula in the Chg column and G2 displays the formula in E2.