Displaying Joint tenants on form (1 Viewer)

[Haynesey]

I have set up a tenancy database.

I have a table that holds people records. I have a table that holds properties and a table that holds tenancy information. They are related in the following way. 1 Person can have many tenancies and 1 property can have many tenancies. The tenancies table is used primarily to store information such as tenancy start and end date.

Everything works ok apart from being able to link all people that live in one house together.

On each person record on the person form, I want to be able to display a list of all other people that live in the same house on the 'Household members' tab. However, I cannot think of a way to do this as the property information is in another table. The other tricky part is that it should only pick up the property for each person with the latest tenancy start date.

I have attached the database.

Please help, have spent all today trying to figure this one out!!

Any ideas would be much appreciated.

Cheers in advance

Lee

 

[Rich]

How can one person have many tenancies at the same time?

 

[Haynesey]

Only 1 tenancy at a time.

Hi,

They can't have more than one at a time. I am relying on the users entering a tenancy end date to identify that a tenancy has ended. Then, when they have a new tenancy, hopefully, the new tenancy start date (this will be the latest date) will identify that this is the current tenancy.

If you think of a better way, please let me know.

Thanks again.

Lee

 

[CraigDolphin]

Have a look at the attached db. Your problem has two steps:

1. Identify the property that a person is currently living at.
2. Identify other people who are also currently living at that property.

I created a general query to answer the former and another query to answer the latter.

I then used a subform on your tab control which was bound to the first query with a hidden personID and propertyID controls. I used the Child-Master field relationship between the form/subform to restrict the first query's results to the currently viewed person

Inside that subform, I used another subform (ie a sub-sub form) to show the results of the second query. I used the Child-Master field relationship between the subform/subsubform to restrict the second query's results to tennants of the property identified by the first query.

Essentially it's nested subforms and queries.

My only concern is that you said you plan on having an end date filled in for non-current tennancy records. In the example data you provided, I note that this is not true for Lee Haynes who has two records in the tennancy table, both without end dates. Make sure that your data systems enforce that policy or this approach will not be successful.

As an alternative, you might consider using only the most recent (max) start-date to identify the current property of a person without worrying about end-dates at all. You could modify the example so that qryCurrentPropertyOfPErson has the following sql:

SELECT tblTenancy.PersonID, tblTenancy.PropertyID, tblTenancy.TenancyStartDate
FROM tblTenancy
WHERE (((tblTenancy.TenancyStartDate)=DLookUp("TenancyStartDate","tblTenancy","[PersonID]=" & [PersonID])));

Either way, you need to be sure that the assumption of one property at a time for each person is absolute. If not, then you may need to add some navigation buttons onto the subform so that you can view the members of different households occupied by the same person.

 

[Haynesey]

Thanks

Thanks, that worked great.

Please let me know though if you think of a better way of making sure that the users have ended previous tenancies before starting a new one.

Thanks again.

Lee

 

[Rich]

Why would an existing tenant end a tenancy and then start a new one?

 

[CraigDolphin]

Off the top of my head....Divorce. Marriage. Hates the neighbours. Smell of murdered victims wafting through the floorboards gets too much?

Tenancy here refers to a person, a property, and a time period. If you shift to a new property, this is an end to one tenancy and the start of a new one.

 

[Rich]

Yes I am aware of why tenants move but it's not that common and in the case of an agency or landlord most of the tenants that move would not stay with the same agency/landlord, which is why I suspect there is a design flaw here

 

[CraigDolphin]

*shrug* Couldn't speak to that as I'm not in the land-lording business, but if it is /possible/ in the real world then my (admittedly limited) experience so far has taught me to explictly allow for it in my data model also.

Along those lines, I suspect that multiple tenancies might crop up at some point and force a review of the underlaying assumptions at some point.

Like if you had George and Laura Bush renting a place, but George is also simultaneously renting a little condo for Condaleeza Rice (on the side, quiet-like) from the same agency.

The db should be set up in a way that allows for potential issues like that even if they are rare.

 

[greenfruit]

Tenants may just move around a multi unit complex e.g
Unit 2 might get morning sun only and unit 5 gets afternoon sun.
So tenant ends current tenancy and starts a new one in unit 5 ( when it comes available).