Please help, the relationalship problems

I would like create a DB concern the buiding.

Tenants hire flats
A building has different flats
There are some buidings

I create 5 tables:

Table_1 : Tenant_ID, Tenant_Name
Table_2 : Building_ID, Buiding_Name
Table_3 : Flat_ID, Flat_number

T_Tenant_Buiding : TE_1_ID, BU_ID
T_Tenant_Flat : TE_2_ID, FL_ID


and 4 Relationships :

Tenant_ID = TE_1_ID
Buiding_ID = BU_ID

Tenant_ID = TE_2_ID
Flat_ID = FL_ID


I use portal for defining tenant / buiding
and the same, portal for defining tenant / flat

the problem that there is no relation between
buiding and flat. And I do not know how to define it!

Can you help me to correct these relationships
so in the reports I can display :

Buiding_1
Flat_1
Tenant_A
Tenant_B
Tenant_C

Flat_2
....
...
Buiding_2
Flat_1
Tenant_H
Tenant_I
Tenant_J

Flat_2
....
...


Thank you for your help

Re: Please help, the relationalship problems

On 2008-04-07 15:18:49 -0700, "Cedric" said:

> Tenants hire flats
> A building has different flats
> There are some buidings
>
> I create 5 tables:
>
> Table_1 : Tenant_ID, Tenant_Name
> Table_2 : Building_ID, Buiding_Name
> Table_3 : Flat_ID, Flat_number
>
> T_Tenant_Buiding : TE_1_ID, BU_ID
> T_Tenant_Flat : TE_2_ID, FL_ID
>
>
> and 4 Relationships :
>
> Tenant_ID = TE_1_ID
> Buiding_ID = BU_ID
>
> Tenant_ID = TE_2_ID
> Flat_ID = FL_ID
>
>
> I use portal for defining tenant / buiding
> and the same, portal for defining tenant / flat
>
> the problem that there is no relation between
> buiding and flat. And I do not know how to define it!

Each flat record should have a Building_ID. You seem to be laboring
under a limitation of 2 key fields per table, and that's not correct.
Each table can have as many IDs as necessary.

Revise your relationships so:

Parent table = Buildings (Building ID)
Child Table = Flats (FlatID and Building ID)
Table = Tenants( Tenant ID)

Join Table (Tenancy) = Tenant x Flat (Tenant ID and Flat ID) put in
start & end dates. A tenant can then have more than one tenancy, say if
they move from one flat to another.

Relate Building to Flat (Building ID in both tables)
Relate Flat and Tenant to the Tenancy file

Tenant then becomes related to Building through the Tenancy file and
Flat table.

Do your reports from the Tenancy table. Bring in the Flat Address and
the Building Name and the Tenant name through the above relationships.

In the Tenancy table you need to include a Start Date & End date of the
tenancy. Do a find on currently open tenancies and you'll be able to
produce the report that you want.
--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA

Re: the report problem

Thank you very much for your help.

"Lynn Allen" wrote >
>
> Do your reports from the Tenancy table. Bring in the Flat Address and the
> Building Name and the Tenant name through the above relationships.
>
> In the Tenancy table you need to include a Start Date & End date of the
> tenancy. Do a find on currently open tenancies and you'll be able to
> produce the report that you want.

Everything is ok except the report !

I can have this report : (not beautiful but correct)

Building_1 Flat_1 Tenant_A Tenant_B
Building_1 Flat_2 Tenant_C Tenant_D
Building_2 Flat_5 Tenant_H Tenant_I
....

And I use "Tenancy table",
with Related table Building (sub-summary when sort by Building_ID)
with Related table Flats (sub-summary when sort by Flat_ID)
and body and I still can NOT have :

Building_1
Flat_1
Tenant_A
Tenant_B
Flat_2
....
...
Building_2
Flat_5
Tenant_H
Tenant_I
...
something are wrong in "Part Setup" or "part definition"
Thank you for your help again.