Relationships

[stevieleser]

I manage a portfolio of 300 properties and I want to create a relationship between the tenants, visits done x 4, photos taken at each visit and letters sent for each visit. I have created 4 tables (tenants, visits, photos & letters) with a primary key in each and a foreign key linking all the forms. Is this correct? I keep getting errors saying 'relationship cannot be created.



I could have put all the fields in 1 table but it would have been too long

Any ideas?

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[orange]

Since a Property could have different Tenants with time, I would expect to see a Property table.

Here is a tutorial that you should work through to better understand relationships.
Here is a sample data model that may help you with a design for Property management. It isn't specific to your requirement, but parts of it may be useful to you.

Good luck with your project.

[stevieleser]

Thanks guys, I'll work through the tutorials. I didn't think of treating it as an 'estate agent' would opens up a whole lot more I could do. In the example I take it PF is "primary field" PK is "primary key" & FK is 'foreign key'

[MrRuz]

I'm curious, what did you end up doing? Did you build another table for property/apt, or both?

[stevieleser]

Yes I got the gist of it through the data model, still a bit confused though. I have many tenants who I visit 4 times, taking pictures & sending letters for each visit. I have set the r
TenncyId to relate to the visits, photos & letters tables but
T CNT seem to get the relationship right
. Are they all 1 to many as 1tenant can have many visits, photos & letters?
H

[June7]

Sounds like 1 to many to me. Unless a photo/letter can apply to more than one tenant.

[MrRuz]

Yeah that sound like its one to many (one tenant to many visits).
It's difficult to say without seeing the data.

[orange]

Do you really visit Tenants OR Properties?
Can you post a copy of your relationships window as a jpg.
Make sure all tables are expanded so all fields are visible.

[stevieleser]

Here are the tables & relationship

[June7]

Multiple similar fields indicates a non-normalized structure. Why are there 4 Visit fields in Visits table? Same for Letters. Shouldn't each record be a single visit or a single letter?

Are these photos to document conditions during tenant's occupancy?

You have PhotoID in Visits and Letters and Property tables but don't show links. Can same photos be associated with multiple letters and visits and properties?

[MrRuz]

Yeah, you need to normalize. This makes no sense. You have a field in the Tenants table for visits and then a table for visits too?

Try:
Table: Properties
PK: Property
[Property related fields]

Table: Tenants
PK: Tenent ID (autonumber)
[Tenant related fields]

Table: VisitNumber
PK: Visit Number (1,2,3,4)


Table: Tenant_Property
PK: Tenant ID
PK: Property ID
[Tenant to property related fields]

Table: Tenant_Property_Visit
PK: Tenant ID
PK: Property ID
PK: Visit number (1 of 4) (Referential Integrity)




I threw this together because I'm short on time. I'm unsure on the best way to run the visits table, but I'm sure other's will chime in

[stevieleser]

Before we go legal on tenancy breaches I have to visit 4 times with 7 days between each visit. The photos and letters are supposed to be attachments for each visit which is why I thought they had to be individual fields. In the tenants table that was to show all the visits, photos & letters and it works but the form is too big.

I'll try your options & keep you posted.

[MrRuz]

yeah, then I think you'll want a visits specific table, with the letter, photo, and whatever else. Just concatenate visit, property, tenant as the ID. (That way, if a tenant moves to a separate property you can still track that tenant with minimal effort/redundancy).

I'd also run a "managers" table too.