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.

 

[Moss100]

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.

I have had to alter some field names, but I tried this off a button, but errors. Could you help please? Thank you


Dim Rents As Variant

rents = SELECT Min(Date) as MinDate, RentAmount
From qryRentsLocal
GROUP BY RentAmount

MsgBox = Rents

 

[dhookom]

My reply was the SQL view of a query that could return many records. You are using code to supply a message box. You could display the results of my query in a form or list box.

If you truly want a string containing all of the records, you will need to concatenate them using a function like this query FAQ.

Please tell us your design specification for this task.