To show the right data on a giving date

[Achineche]

Anyone could help me on this?

I have two tables, payments and people. They are related through PeopleID.

People must pay a monthly fee.

I have a field (PaymentSituation) in Peoples table that through a calc tells me if a person owes money or not.

What I need to accomplish is to know the right data on a giving date.

For example: if a person didn’t pay on January but now has fully paid, the paymentsituation tells me that doesn’t owe anything, even if I take a look to January.

I don't know if I can't make myself understood

How should I relate the tables to show the right data on any giving date?

Thank you very much.

[comment]

I have a field (PaymentSituation) in Peoples table that through a calc tells me if a person owes money or not.

What is that calc?

[Achineche]

- Payments table:

·TotalAmountByPeople

- People table:

·AmountOwed

·PaymentSituation: case ( people::AmountOwed - Payments::TotalAmountByPeople < 0 ; "Not fully paid" ; people::AmountOwed - Payments::TotalAmountByPeople >= 0 ; "Fully paid" )

[comment]

Well, that doesn't tell me how People::AmountOwed is figured out.

I think that if you define another relationship between the two tables as:

People::PeopleID = Payments 2::PeopleID

AND

People::gDate ≥ Payments 2::PaymentDate

you will be able to make the assessment at the given gDate - provided you know how to calculate the amount owed at the same time.

[Achineche]

The amount owed it's a monthly fee, calculated in another table, how silly!, I didn't think of that, I was trying to make it simple to explain, sorry about that.

Should I make another relationship with the MonthlyFee table?

People::PeopleID = MonthlyFee 2::PeopleID

AND

People::gDate ≥ MonthlyFee 2::Month

[comment]

Probably (I still don't see the full picture).