Help for my problem

[isladogs]

Attached is an amended version for you to consider.
Changes made include:
1. New table tblProperties for the details of each house/flat being rented


2. Extra fields added to tblTenants
3. Credit/Debit payments merged into tblPayments using +/- signs & added extra fields

I've created relationships for the 3 tables

I've also taken the liberty of removing all underscores in favour of CamelCase naming
Suggest you remove the euro symbol from the PaymentAmount field

This isn't meant to be a final version but I think it will help you get a working model
Before proceeding further, I strongly recommend you plan out on paper all the info you will need and how it connects together then re-work the tables for your needs.
You can also find many free database designs online to look at. For example http://www.databaseanswers.org/data_models/

HTH

[ArviLaanemets]

And when you start with Colin's version, then you probably will soon see, that you have to remove TenantID from tblProperties, and you have to create tblPropertyTenats: PTID, PropertyID, TenantID, StartDate, EndDate, as it happens that some tenants are leaving and another tenant is renting the same property afterwards.

And the rental payments are depending more on property then on tenant. Add the fact, that property owner may want to change the rental payment at any time moment, and you have another table tblPropertyPayments: PPID, PropertyID, ValidFromDate, MonthlyRent (which means you have to remove the MonthlyRent from tblTenants).

[isladogs]

Yes I agree Arvil.
TBH I was hoping that starmo would realise this for himself(?) when doing detailed planning.

[ArviLaanemets]

OP looked too green at moment, and I was afraid he/she would lost courage at start

There will be enough riffs under water in future, when there is no way back anymore

[isladogs]

Arvil
You are a very kind soul ....

[Starmo]

OP looked too green at moment, and I was afraid he/she would lost courage at start

There will be enough riffs under water in future, when there is no way back anymore

Thanks for the support,
You have noticed well that I am "too gren", I'm just lost in the universe of access, at the same time I would not like to be boring. So at first I asked to have patience with me.
In the previous post you gave me thinking materials, but all that needs to be made to look functional, and I'm afraid I can not do it alone.
You also well noticed that "as it happens that some tenants are leaving and another tenant is renting the same property afterwards", I already had such situations.
"Add the fact, that property owner may want to change the rental payment at any time moment" and this was also happening a couple of times.
Whether it is possible and how to give each tenant the program automatically charges rental payment for the past month.

[ArviLaanemets]

To add rentals for previous moth for all tenants, you have to run an append query, which reads rentals for all active tenants from tblPropertyTenants and tblPorpertyPayments, and inserts into tblPaiments payments for those tenants, which don't have the entry for this month jet.

To run the query, you may use:
1. An OnClick event of button on some form (a button <Read Payments for Previous Month>);
or
2. An Open event of main form of your project.

With choice 2, it is best when the code at first checks, are there some tenants with missing rentals for previous month. When not, then the rest of code may be skipped.

[ArviLaanemets]

In attachment is a sketchy database (tables and some queries only)

All rental sums and payments are in table tblSaldo. Probably you need 2 separate forms to enter or view costs and payments. Costs are bound to PropertyID with TenantID calculated, and payments are bound to TenantID with PropertyID calculated (about payments part I'm not sure - when tenant ends rent, but pays liability later, then the user must have a possibility to enter the payment anyway).

I added a table tblAccounts, where you can define different types of entries in tblSaldo. All accounts < 5000 are various costs tenant has to pay for and are entered as negative numbers. All accounts >= 5000 are various payments from tenant, and are entered a spositive numbers. When some correction entries are made, then their sign must be determined by database user.

All other costs, and all payments must be entered through form(s) - for every tenant.

Query aqPropertyRentalPM inserts rentals for previous month into tblSaldo (when they aren't entered jet). It uses query qPropertyRentalsPM1 as source, and qPropertyRentalsPM1 uses qPropertyRentalsPM0 as source. The number of months to be skipped (-1), and PaymentDate (15th of month) are determined in qPropertyRentalsPM0.

qPropertyRentalsPM0 uses UDF ValidValue() to set query filter condition. The function is in module UserDefinedFunctions.

I entered some dummy rentals and payments into tblSaldo, but I didn't enter rentals for previous month - so you can test the append query.

The program allows payments in advance, and takes liabilities into account.

When you keep accounts table correctly structured, then in future it will be easy to get varios specific reports, like communal cost report (in case communals are also paid through your organization, of-course), or payment method report (when you make separate payment accounts for different payment methods), etc.

As example, how to read data from database are 2 queries: qCurrentTenantSaldo and qTenantSaldoAt.


I hope this will give you a good start.

[Starmo]

Thanks ArviLaanemets!

Finally I got something concrete, something I can continue my work. Now I have a base I can upgrade to my needs.
Once again, thank you very much.