Repeat an append operation until zero records

[guildrl]

I think the solution to my problem will be easier than me trying to explain it! But here goes. Also, I have no knowledge of programming which is why I love Alpha 5.

I am developing a property rental database.

Reccuring_table has information about the rental, i.e. tenant_id, period (calendar monthly etc), rent_amount (amount of the rent) and due_date.

When a new tenant is created, the above fields are appended to reccuring_table.

I then have another table, rentals_due. This table will contain all the rental due dates for the tenant. When a new tenant is created, the first rental is appended to start from the start date.

I then create an append operation from reccuring_table to rentals_due that does the following:

• Only appends records where due_date + 1 month < today (so that future rents are not put in as they are not due yet)
  
• If rent is due, append the record from reccuring_table to rentals_due table.
  
• When appending, add 1 month to the date in recurring table
  
• Also when appending, add 1 month to the date in the rentals_due

This works well and I thought I could run the operation every time the database is open for example.

However, there is a problem:

Firstly, if I didn't open the database for three months say (which is unlikely), it would only add one month to the rentals_due, so two months would not show (unless I opened the database another two times!)

Secondly, If I want to add a tenant who is already in a property and has been for say 2 years, I would add the tenant, but then I would have to run the append operation 24 times to bring it up to date.

All I need is a way of repeating the append operation until records to append = 0, then it would stop. This too would fix the problem of not opening the database often as it would always bring the rentals_due table up to the current date.

Any help would be greatly appreciated (I am also interested if there is an easier way to do what I suggest)

Many thanks in advance

Adrian

[DaveM]

Re: Repeat an append operation until zero records

Do you use a lease(like for a year)?

[guildrl]

Re: Repeat an append operation until zero records

Do you use a lease(like for a year)?

Yes, normally 3 or 6 months, however that does not really matter because the lease is a 'minimum' term. Often, on the same lease a tenant can be in the property for several years. Here in the UK, a lease automatically rolls on forever until either party gives notice to leave.

Many thanks

Adrian

[DaveM]

Re: Repeat an append operation until zero records

I would suggest you post the database for us to look at. I think there are other/better ways to do what you need, but seeing what you are doing now would give a hint as to what is needed. You can put some sample data in and take out the good stuff if you want(and i suggest it).

dave Mason

[guildrl]

Re: Repeat an append operation until zero records

Ok, thank you, I will do that. I have just started developing it, so I will do so in a few days when I've got some other things done to make it clearer to see. I have developed another database in Alpha 5 so I should be able to get the basics done fairly quickly.

Currently I am using access to do this, which has worked for several years but typical Microsoft it is too unreliable and far too complicated.

How I programmed access is as follows:

As described above, there is tenant database with tenant_id, start_date, rent, period etc.

Then, in order to calculate the amount due on any given day, it simply calculates the number of months between start_date and date() and then multiplies this by rent. For example start date is 1/1/07 and rent is 400.

So if today is 29/9/07 (in US format that's 9/29/07) it calculates that there are 9 months between start_date and date() and multiplies that by 400 (the rent).

This provides an amount due figure of 3600

Then it takes a sum() of all amounts paid.

Then a simple total amount due - sum() amount_paid, produces how much the tenant owes today.

This system works very well and is reliable, but trying to do a report of when rent was due, how much was paid etc. is virtually impossible. In order to produce a report, I have a really complicated export to excel which then has loads of complicated formulas to work out rent due and rent paid.

When I need to go to court on the grounds of rent arrears, I have to produce an easy to understand rent statement which the judge can understand. This could take the form of a common invoice statement as essentially that is all it is, a reccuring invoice system with payments. In addition, tenants always need an easy to understand statement.

This is why I am trying to change the system.

The amount paid would obviously come from it's own table and the amount due and arrears would be calculated from simple sum() functions.

To add further problems, some rents are weekly, some for weekly, some monthly and some quarterly! Although, that is a simple if(period=weekly then add 7 days, if(period=monthly, add 1 month)) and so on...

I am very grateful for the assistance, I just can't seem to get my head round this one... I should have concentrated more in maths at school!!

Many thanks and I will come back in a few days with a skeleton database.

Adrian

[guildrl]

Re: Repeat an append operation until zero records

I managed to get a skeleton database running quicker than I thought and it is attached.

I hope it is all self explanatory but anything you are unsure about, please let me know. I check emails regularly.

Many thanks for your assistance, I am truly grateful.

Adrian

[guildrl]

Re: Repeat an append operation until zero records

Sorry, I should have said. Open Main Menu form to start.

When looking at tenants, you will see that I have added a tenant who started in 2006 but I have not pressed the button to append the recurring table. This where the key problem lies, the button (or link it to opening of database) will need pressing 12 or so times to bring the rentals into line with the current date.

Many thanks

Adrian

[guildrl]

Re: Repeat an append operation until zero records

Just in case anyone is reading this thread and wonders if the problem was fixed, I just figured out how to do it.

Basically I added a new field into the "recurring_table" that contained the julian date of when next rent due.

Then a simple script would only repeat the append operation if the minimum value of this field was not >= the julian date of today.

(The reason I created a julian field was because it seemed difficult to find the minimum value of a date field whereas a julian date is just a number).

Therefore now, the script will keep repeating the append operation until no more rents are due, then stop without being in an endless loop.