Jump to content

Finding Data


This topic is 5704 days old. Please don't post here. Open a new topic instead.

Recommended Posts


I am working on a property management database and wondered if anyone can help me figure something out.

What I am doing is making a "tenant roster" or a list (view as list) of all of the tenant records. In the roster I want to be able to select a month via drop down (know how to do this). So now the MOnth Select field reads "January".

In the same table I have 12 fields corresponding to 12 months from now which is a payment matrix and holds a year's worth of amount they paid per month.

So for the first record, a tenant that started paying rent this month, field 1 is April, field 2 is May, Field 3 is June, etc.

The tenant that starts next month will have a matrix come up of 12 months from when they started, so his field 1 is May, field 2 is June, Field 3 is July, etc.

In the roster I want to be able to go next to Tenant 1, select June from the pull down menu

and I want to automatically poplulate another blank field (we will $$$) which basically looks up June in the payment matrix.

For the 2nd record, a.k.a. Tenant 2, I want to select June also, and this time the blank field $$$ it pulls up what this tenant paid -- from this record's Field 2.

I am not sure this makes sense. Essentially I think what I'm trying to do is use Field 1 in Table 1 as a condition, from which I search a matrix (or Table 2?) for a match in Field A, but then it returns the value of the corresponding Field B into Field 2 of Table 1.

Any advice is very much appreciated.

Thank you!!


Link to comment
Share on other sites

Not 100% sure what you are after. I understand you want to keep track of the tenants and what they owe but shouldn't you be looking at this from the property side? I can see 4 tables Property, RentalAgreement, Tenant and Rent.

The RentalAgreement table should hold the Start Date, End Date, ID_Tenant, RentPrice etc... These fields should be used to create the payments due in the Rent table. When a tenant makes a payment this should be credited towards the record containing the appropriate payment due.

Rather than a find for a particular month for a tenant why not a portal showing all matching records from Rent with the latest Payment made being listed first (descending)?

Link to comment
Share on other sites

This topic is 5704 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.