Need Help With Due Dates and Updating Info -- Access

[eagle1468]

OK.... So here is my project:



I am creating a Database for my mother who manages a self storage facility.
I'm including my table and relationship info... And I may have this all wrong already! Please feel free to beat me up - I am new to Access and I feel like an Idiot!

Here is what I have right now:

1. I Setup a tenant with my New Tenant Form > Save and Open New Lease Form
2. Fill Out New Lease Form > Save and Open New Payment Form

The main problem I am having is with DueDate, PastDueDate and CertifiedDate.

I need the following things:
When a New Tenant > Lease > Payment is Setup I need the

1. DueDate To be 30 days from StartDate (Setup Date)
2. PastDueDate to be 31 Days From StartDate (Setup Date)
3. CertifiedDate to be 45 days from PastDueDate

But PastDueDate Should only be populated if CurrentDate is Greater than DueDate... And CertifiedDate to be populated Only if the Current Date is Greater than 45 days from PastDue Date....

I am trying to set it up so that when a Tenant > Lease > Payment is Past Due or needs a Certified Letter sent it is shown on a daily Report.

Like I said before I could have this all wrong.. I thought of Yes/No Fields for PastDueDate and CertifiedDate But Couldn't figure out how to do that and get the Date that it is Past Due or a Certified Letter needs to be sent out... She needs to see the date as well.

Also if a Tenant Pays their rent on time i Need the Due Date to go to the Next Month with the Same StartDate Day.... And the Same if they are late paying.

Also I need a Late payment Fee of $10 to go on the AmountDue 11 days after the DueDate. I just havent figured out how to factor in Fees...(Do I need a Fee Table? Or put more feilds into the Payment Table.... I'm Lost!!!!!)

I know I am asking a lot and I really do apologize! I'm just trying to get this done for my mom!

Thanks you very much!

Here is my current Tables - Fields & Relationships

[June7]

Not sure you should even populate fields with PastDueDate and CertifiedDate. Usually not a good idea to save calculated data. Calculate when needed. Calculations can be in query or textboxes on form/report.

Whether or not a customer is past due is dynamic data. Account status must be calculated from various factors. It will change depending on account balance and date of last billing and date of last payment. It doesn't make sense to save a fixed value in table.

Bing: Access database rental income

http://www.microsoftaccesstemplate.com/info/rental.asp
http://www.fileguru.com/apps/access_...ental_property

[eagle1468]

Not sure you should even populate fields with PastDueDate and CertifiedDate. Usually not a good idea to save calculated data. Calculate when needed. Calculations can be in query or textboxes on form/report.

Whether or not a customer is past due is dynamic data. Account status must be calculated from various factors. It will change depending on account balance and date of last billing and date of last payment. It doesn't make sense to save a fixed value in table.

Bing: Access database rental income

http://www.microsoftaccesstemplate.com/info/rental.asp
http://www.fileguru.com/apps/access_...ental_property

Thank you. I will look into all of this and post any questions I may have!