Finding Data

[Moosh]

Hello!

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!!

Moosh

[sbg2]

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)?